使用python将公式添加到Excel电子表格 [英] Adding formulas to excel spreadsheet using python

问题描述

我正在尝试使用python将公式插入到Excel电子表格中.公式的示例是:

• = VLOOKUP(B3 |结算信息"！$ B $ 2:$ R $ 2199 | 17 | FALSE)
• = SUMIFS(付款和费用"！$ I $ 2:$ I $ 6445 |付款和费用"！$ B $ 2:$ B $ 6445 |摘要！$ B3)
• = + E3-F3-G3-I3
• = IF(AND(I3> 0 | I3-N3> =-0.1 | I3-N3 <= 0.1)|是" |否")

我尝试使用xlsxwriter，并且在excel中打开ss时，它会通过删除"unread"来修复.内容，这些单元格显示为0.我已经看到这样的评论:使用xlsxwriter时，应在重新打开工作表时进行重新计算，但看起来并没有完成(

I am attempting to insert formulas into an excel spreadsheet using python. Examples of the formulas are:

• =VLOOKUP(B3|"Settlement Info"!$B$2:$R$2199|17|FALSE)
• =SUMIFS("Payment and Fees"!$I$2:$I$6445|"Payment and Fees"!$B$2:$B$6445|Summary!$B3)
• =+E3-F3-G3-I3
• =IF(AND(I3>0|I3-N3>=-0.1|I3-N3<=0.1)|"Yes"|"No")

I tried using xlsxwriter and when opening the ss in excel it repairs by removing the "unreadable" content and those cells show as 0. I've seen the comment that the recalculation should be done on the reopening of the sheet when using xlsxwriter but that does not look like is is being done (https://xlsxwriter.readthedocs.io/working_with_formulas.html)

Is there some way to get these formulas into excel without them being removed by excel on opening? Thanks for any pointers.

I simplified this down to a simple as possible:

When I run the below code and then attempt to open the excel spreadsheet I get an error saying "We found a problem with some content in ...Do you want us to try to recover..If you trust the source select yes" If I select yes then I get an error " Removed Records: Formula from /xl/worksheets/sheet1.xml part"

And if I continue the sheet opens and there is a 0 in the field.

from xlsxwriter.workbook import Workbook

workbook = Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Summary')
worksheet.write_formula('A2', '=VLOOKUP(B3,"Settlement Info"!$B$2:$R$2199,17,FALSE)')

workbook.close()

If I look at the information at https://xlsxwriter.readthedocs.io/working_with_formulas.html there is the information:

XlsxWriter doesn’t calculate the result of a formula and instead stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don’t have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications.

Which I may not be understanding as I believe that the formula should be left in the sheet.

Thanks,

解决方案

You can XlsxWriter to create any formula that Excel can handle. However, you need to be careful with the formatting of the formula to make sure that it matches the US version of Excel (which is the default format that formulas are stored in).

So your formulas should probably work as expected if you use a comma instead of a pipe:

=VLOOKUP(B3,"Settlement Info"!$B$2:$R$2199,17,FALSE)
=SUMIFS("Payment and Fees"!$I$2:$I$6445,"Payment and Fees"!$B$2:$B$6445,Summary!$B3)
=IF(AND(I3>0,I3-N3>=-0.1|I3-N3<=0.1),"Yes","No")

This one should work without modification:

=+E3-F3-G3-I3

See this section of the XlsxWriter docs on Working with Formulas.

Update in relation to the updated question:

The formula still has an error. You need to use single quotes instead of double quotes. You also need to add another worksheet for the formula to refer to. Like this:

from xlsxwriter.workbook import Workbook

workbook = Workbook('test.xlsx')
worksheet = workbook.add_worksheet('Summary')
worksheet.write_formula('A2', "=VLOOKUP(B3,'Settlement Info'!$B$2:$R$2199,17,FALSE)")

workbook.add_worksheet('Settlement Info')

workbook.close()

There is still an #N/A error in Excel but that is related to not having data in the VLOOKUP range:

Output:

这篇关于使用python将公式添加到Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！