def ex_temp():
wb = openpyxl.load_workbook("input.xlsm",data_only=False, keep_vba=True)
wb2 = openpyxl.load_workbook("input.xlsm",data_only=True, keep_vba=True)
sheet = wb.get_sheet_by_name('Sheet1')
sheet2=wb2.get_sheet_by_name('Sheet1')
print "With Formula: ",sheet.cell("C2").value, sheet.cell("D2").value
print "With Only Data: ",sheet2.cell("C2").value, sheet2.cell("D2").value
sheet.cell("C2").value=100
wb.save("output.xlsm")
# wb2.save("output2.xlsm")
print "After Writing-------------------------------------------------------"
wb = openpyxl.load_workbook("output.xlsm",data_only=False, keep_vba=True)
wb2 = openpyxl.load_workbook("output.xlsm",data_only=True, keep_vba=True)
print wb.get_sheet_names()
sheet = wb.get_sheet_by_name('Sheet1')
sheet2 = wb2.get_sheet_by_name('Sheet1')
print "With Formula: ",sheet.cell("C2").value,sheet.cell("D2").value
print "With Only Data: ",sheet2.cell("C2").value, sheet2.cell("D2").value
ex_temp()
With Formula: 5 =C2
With Only Data: 5 5
After Writing-------------------------------------------------------
[u'Sheet1']
With Formula: 100 =C2
With Only Data: 100 None
Solution:
Reopen it once and save it.
from win32com.client import Dispatch
def just_open(filename):
xlApp = Dispatch(“Excel.Application”)
xlApp.Visible = False
xlBook = xlApp.Workbooks.Open(filename)
xlBook.Save()
xlBook.Close()
Hope this solution works for you!!
On 8 Oct 2024, at 22:53, Marcelo Lopes wrote:
Hi everyone.
Hi Marcelo,
Recently I faced the same issue presented on this thread, which is: after
saving an excel file and reading this file again using openpyxl all cells
containing formulas return 'None'This occours because openpyxl is not able to force excel to recalculate the
formulas.
This is not quite true: Openpyxl itself never evaluates formulae, which is why you're forced to choose between the formulae or the most recently cached result, if available. This makes sense if you think about it: if ws["A5"] is the sum of cells A1:A4 and you change any of the values, then the previous calculation is invalid.
An alternative to openpyxl is the library xlwings. So the Aman's code could
be written like this:
This is great if you're on a platform with Microsoft Excels such as Windows or MacOS, because XLWings itself relies on Excel for data processing. There are other alternatives, such as using PyCel to do the calculations; or OpenOffice or LibreOffice in headless mode; or OnlyOffice Docs.
import xlwings as xw
from openpyxl import load_workbookdef ex_temp():
# Writing and saving using openpyxl
reading_data(False, 'input.xlsm')# Recalculating formulas and saving using xlwings
saving_file('output.xlsm')# Only reading data AFTER saved by xlwings
reading_data(True, 'output.xlsm')def reading_data(only_reading, file):
wb = load_workbook(file, data_only=False, keep_vba=True)
wb2 = load_workbook(file, data_only=True, keep_vba=True)
I'd suggest using combining read-only mode with data-only mode.
sheet = wb.worksheets[0]
sheet2 = wb2.worksheets[0]
It's almost always advisable to use sheet titles.
print ("With Formula: ",sheet["C2"].value,sheet["D2"].value)
print ("With Only Data: ",sheet2["C2"].value, sheet2["D2"].value)if not only_reading:
sheet["C2"].value = 100
wb.save("output.xlsm")
print('After writing-------------')def saving_file(file):
# Saving data using xlwings
with xw.App(visible=False) as app:
app.display_alerts = False # Disable Excel alerts
wb = app.books.open(file) # Loads workbook
sheet = wb.sheets[0] # Turns Sheet1 activewb.app.calculate() # Recalculating formulas
wb.save()
wb.close()ex_temp()
Output:
With Formula: 100 =C2
With Only Data: 100 100
After writing-------------
With Formula: 100 =C2
With Only Data: 100 *100*
Basically the ideia is using xlwings library Excel to recalculate formulas and
save the file.
Reading can be done either by xlwings or openpyxl.Hope this solutions helps someone.
Regards!
Marcelo Lopes
--
On 11 Oct 2024, at 23:27, Marcelo Lopes wrote:
Hi Charlie,
"Openpyxl itself never evaluates formulae"...I didn't said that...I said
just the opposite ("openpyxl is not able to force excel to recalculate
the formulas") and because of this I presented a solution using xlwings
that worked for me pretty well.
What you said was misleading because Openpyxl never interacts with Excel. If you want to evaluate the formulae you could build your own engine using the tokeniser, but this
By the way...how about you to show a solution instead of keep explaining
the problem?
Why? I provided some additional information which could be useful. I've never need to do this myself, but I wanted to explain why the library behaves as it does and what alternatives are available. The issue has come up before and I'm pretty certain I've provided and example.
Could you post an example of code to solve this issue? Would be more
helpfull than keep telling what doesn't work, isn't it?
I could, or I could continue to work on the library…
Charlie