英文:
creating an excel vlookup in python script
问题
I'm creating an Excel spreadsheet with data from my AWS account. That's working fine, but I'm trying to insert a VLOOKUP via the script. The formula gets written to the cell, but Excel doesn't evaluate it. Excel is set to calculate formulas automatically. When I open the file, if I activate the formula bar and click the green tick, the formula runs, and I get the expected result. I'm wondering if I'm missing anything in my Python code. Also, I'm using openpyxl to create the workbook.
for row in range(2, wrkbook.active.max_row + 1):
wrkbook.active[f'B{row}'].value = f'=VLOOKUP(A{row},Table1,2,FALSE)'
When I open the XLSX file, a #NAME error is displayed.
Contents of the formula bar: =VLOOKUP(A2,Table1,2,FALSE)
When I open the file, if I activate the formula bar and click the green tick, the formula runs, and I get the expected result.
英文:
i'm creating an excel spreadsheet with data from my aws account. That's working fine, but i'm trying to insert a vlookup via the script. the formula gets written to the cell but excel doesnt evaluate it. Excel is set to calculate formulas automatically. When I open the file if I activate the formula bar and click the green tick the formula runs and I get the expected result. I'm wondering if i'm missing anything in my python code. Also using openpyxl to create the workbook. Any help would be really appreciated!
for row in range(2, wrkbook.active.max_row + 1):
wrkbook.active[f'B{row}'].value = f'=VLOOKUP(A{row},Table1,2,FALSE)'
When I open the xlsx file, #NAME error is displayed.
Contents of formula bar: =VLOOKUP(A2,Table1,2,FALSE)
When I open the file if I activate the formula bar and click the green tick the formula runs and I get the expected result.
答案1
得分: 1
"Openpyxl"编辑Excel xlsx文件的xml文件部分。它无法计算公式或影响Excel在打开文件时如何处理公式或公式的内容。因此,如果打开文件后公式显示为#NAME?
,那么"Openpyxl"无法处理这个问题。
您可以在工作簿/工作表打开时选择刷新单元格。但是,由于"Openpyxl"与Excel没有互动,因此它在这方面无能为力,无法告诉Excel在打开时刷新范围。
但是,您可以使用一个利用Excel的模块打开工作簿并刷新单元格,这将解决您的问题。像"Xlwings"这样的模块可以为您执行此操作。同时需要注意的是,"Xlwings"还可以添加数据、创建表格并输入vlookup公式。如果您在"Xlwings"中完成了这些步骤(或者至少用它输入了公式),那么在Excel中打开工作簿时应该会计算公式,因此不需要刷新,也就是说,如果要使用"Xlwings"进行刷新,您可能也可以使用它来写入所有数据,从而避免首次出现的问题。
英文:
Openpyxl
edits the xml files that make up an Excel xlsx file. It cannot calculate formulas or influence how or what Excel then does with the formulas when it opens the file. Therefore if the result when opening the file is the formulas show #NAME?
there is nothing Openpyxl
can do about that.<br>
You have the option to refresh the cells when the workbook/worksheet is open. <br>
Again Openpyxl cannot do anything here since it has no interaction with Excel. It cannot tell Excel to refresh the range on open.<br>
However you can open the workbook using a module that utilises Excel and refresh the cells and that will fix your issue. That is something a module like Xlwings
could do for you. It should be noted at the same time that Xlwings
can also add the data, create the table and enter the vlookup formulas. If you completed these steps in Xlwings (or at least used it to enter the formulas), when you open the workbook in Excel the formulas should be calculated and so refreshing is not required, i.e. if you were to use Xlwings to refresh you may as well use it to write the all data and avoid the issue in the first place.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论