Python Forum

I am using Python to copy values from Text file to excel, able to succeed in that but the problem is that when pasting values to excel the existing formulas are removed.

I am using "import openpyxl" and also have a code as "data_only= False" when loading workbook. If I use this then values are pasted but formulas are removed, so changed "data_only= True" but unable to paste values corresponding to a column with formulas. Any help would be greatly appreciated. Thank you.

(May-31-2019, 04:44 PM)ksasi2k3 Wrote: [ -> ]I am using "import openpyxl" and also have a code as "data_only= False" when loading workbook. If I use this then values are pasted but formulas are removed

as per the docs this will keep the formulas

Without a sample from you, it is difficult to help.

Maybe you have meant this:

#!/usr/bin/python3
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws["A3"] = "=SUM(A1, A2)"

ws["A1"] = 10
ws["A2"] = 100
wb.save("formula.xlsx")

Thank you heiner55 for your reply.
I have attached example files.

I have a text file 12.txt as attached, I want to insert the values in this file to attached excel and in column "C" named as Finaltotal. This column has formulas and need to paste values from text file to this column.


Secondly: In excel I have column name "Textfile" this has value 12 and need to find text file with name 12, for simplicity purpose I have named the text file as "12". Need to find the value from this "Textfile" column and paste the values from 12 text to corresponding "Pastevalues" column.

Could you also attach the excel-file
which results from "12.txt" and "sample.xls" ?

Hi heiner55, Sample.xlsx is the resulting file.

Then I need the "input.xls".

12.txt + input.xls ==> sample.xls

It is the same excel file which acts as Input and output. For your reference I am attaching it with slightly modified and included formulas. "Sample.xlsx" is the input and output file.[attachment=660]

Sorry I can not help, because I do not understand your issue.
Maybe you could use my very first version:

#!/usr/bin/python3
import openpyxl as xls

#todo: read file 12.txt
tm    = [0]*6
tm[0] = 0.99999982e-01
tm[1] = 9.84491405e-08
tm[2] = 2.08444947e-16
tm[3] = 6.91223128e-24
tm[4] = 8.09480403e-31
tm[5] = 0.00000000e+00
print(tm)

#xls
wb = xls.load_workbook(filename="sample.xlsx")
ws = wb.active

ws["G2"] = tm[0]
ws["H2"] = tm[1]
ws["I2"] = tm[2]

wb.save("sample2.xlsx")

Let me explain in step by step process.

1.I have an excel file with formulas included in columns.
2.I want to paste the values from txt file to excel file.
3.I have a column name "Text file" in the excel sheet, this columns is derived by using formulas.
4. Need to check the number in that column, example here it is "12", so check for the text file with name "12".
5. Copy the last value from text file "12" and paste it in column "Pastevalues" in excel sheet.
6. So input is from text file,reference and output is the same excel.[attachment=661][attachment=661]