is it possible to insert a excel formula value not formula

user_12659741 0
Viewed 631

i am trying to insert a value using excel formula which is happening successfully but i want to save the value not formula ,here is the piece of code i am trying so far.

print("Adding formula to " + filename)         


for i,cellObj in enumerate(sheet_formula['P'],1):
        cellObj.value='=IF(AND(OR(A{0}="g_m",A{0}="s_m"),ISNUMBER(SEARCH("A", E{0}))), "A", VLOOKUP(A{0},\'i ma\'!A:B, 2, FALSE))'.format(i)
        sheet_formula.cell(row=1, column=16).value = 'C'

this piece of is able to insert formula but i want to save the value not formula

excel python
1 Answers

"The basic reasons for abandoning openpyxl are: (1) XLS file processing is not supported; (2) the bug of testing current version style preservation is not solved; If you encounter the above two problems, give up openpyxl and embrace xlwings. There is no way out." Grabbed from here.

"It's possible using xlwings which uses pywin32 objects to interact with Excel, rather than just reading/writing xlsx or csv documents like openpyxl and pandas. This way, Excel actually executes the formula, and xlwings grabs the result." Grabbed from here.

So while it's not possible (so it seems) using just Openpyxl, or any other library that does not support xls file processing, as a Python library, it is possible using xlwings. I have added a simple sample below. I simply opened a fresh workbook, added a formula and transformed the formula to it's calculated value.

import xlwings as xw
app = xw.App(visible=False, add_book=False)
wb = app.books.add()
ws = wb.sheets.active
ws['A1'].value = '=3+5'
ws['A1'].value = ws['A1'].value
wb.save(r'C:\Users\...\test.xlsx')
wb.close()
app.quit()
exit()

Hopefully the above helps. Please keep in mind; I'm a Python beginner!

For those who are interested, some good explaination about the difference between Openpyxl and xlWings can be found here. And a somewhat similar problem with some answers can be found here

user_97581940
Related