创建一个在Python脚本中的Excel VLOOKUP。

huangapple go评论65阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月11日 20:12:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76227534.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定