4

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

KJTHoward
  • 806
  • 4
  • 16
user12659741
  • 87
  • 7
  • 1
    Not sure if this would work on Python, but in VBA when you want to convert a formula into a value, we do `cell.value=cell.value`. Have you tried adding `cellObj.value=cellObj.value` after inserting the formula? – Foxfire And Burns And Burns Mar 04 '20 at 12:17
  • thanks for your help but it is not working – user12659741 Mar 04 '20 at 12:36
  • Are you using any additional libraries besides `win32com`? I'm getting **'_Worksheet' object is not subscriptable**. Or is `sheet_formula` something other than a worksheet? – Zev Spitz Mar 04 '20 at 14:42
  • Also, the property names appear to be case sensitive, e.g. `Value` and not `value`. – Zev Spitz Mar 04 '20 at 14:49
  • @FoxfireAndBurnsAndBurns, it kind of works just like that with the right library =) – JvdV Mar 04 '20 at 15:06

1 Answers1

2

"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

JvdV
  • 70,606
  • 8
  • 39
  • 70