是否可以插入Excel公式值而不是公式 [英] is it possible to insert a excel formula value not formula

问题描述

我正在尝试使用成功发生的excel公式插入值，但我想保存该值而不是公式，这是到目前为止我正在尝试的代码段.

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

推荐答案

"放弃openpyxl的基本原因是:(1)不支持XLS文件处理；(2)无法解决测试当前版本样式保存的错误；如果遇到以上两个问题，请放弃openpyxl并拥抱xlwings.没有出路." 从

"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.

---

"可以使用xlwings，它使用pywin32对象与Excel进行交互，而不仅仅是读取/写入xlsx或csv文档(如openpyxl和pandas).这样，Excel实际上执行公式，而xlwings抓取结果." 来自此处 .

"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.

---

因此，仅使用Openpyxl或任何其他不支持xls文件处理的库(看起来)，虽然不可能成为Python库，但可以使用xlwings .我在下面添加了一个简单的示例.我只是打开了一个新的工作簿，添加了一个公式并将该公式转换为其计算值.

---

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()

希望以上内容对您有所帮助.铭记于心;我是Python初学者！

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

对于感兴趣的人，可以在此处Openpyxl和xlWings之间差异的一些很好的解释. >.可以在此处

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

这篇关于是否可以插入Excel公式值而不是公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！