使用Python的Openpyxl进行索引匹配 [英] Using Python's Openpyxl for an index match

问题描述

我正在尝试构建一个工具，该工具将在电子表格的单元格中运行索引匹配，然后在python中显示公式的结果.我的理解是Openpyxl不会实际运行公式，但是我可以写到excel，然后刷新文件来运行它?

I am trying to build a tool which will run an index match in a cell on a spreadsheet, and then display the result of the formula in python. My understanding is Openpyxl will not actually run the formulas but I can write to excel and then refresh the file to run it?

from openpyxl import load_workbook
path= "C:\\Users\\Me\\Documents\\Python\\File.xlsx"
myworkbook=load_workbook(path)
worksheet=myworkbook.get_sheet_by_name('Sheet1')
mycell=worksheet['B2']
mycell.value="index(B4:B72,match(B1,A4:A72,0))"
print(mycell)

无论如何，我收到并出错，我不确定发生了什么.欧普特:

Anyway, I receive and error and I am not sure what is going on. Ouput:

DeprecationWarning:调用已弃用的函数get_sheet_by_name(使用wb [sheetname]). worksheet = myworkbook.get_sheet_by_name('Sheet1')

DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]). worksheet=myworkbook.get_sheet_by_name('Sheet1')

以退出代码0结束的过程

Process finished with exit code 0

需要明确的是，如果我只是在Excel中执行此公式，则该公式将起作用，因为B1已填充在文件中.

To be clear, the formula works if I just do it in Excel because B1 is populated in the file.

我不确定输出在做什么.它没有引发错误，但是我不知道<Cell 'Sheet1'.B2>和退出代码为0的过程已完成"是什么试图告诉我.我希望输出字符串，因为我正在尝试在句子中建立索引.

I am not sure what the output is doing. It is not throwing an error but I have no idea what <Cell 'Sheet1'.B2> and "Process Finished with Exit code 0" is trying to tell me. I expected a string output because the I am trying to index in a sentence.

推荐答案

A)运行公式需要在Excel中打开文件.如您所述:

A) Running the formulas requires opening the file in Excel. As you state:

我的理解是Openpyxl实际上不会运行公式，但是我可以写到excel，然后刷新文件以运行它?

My understanding is Openpyxl will not actually run the formulas but I can write to excel and then refresh the file to run it?

如果刷新文件以运行它"是指在Excel中打开文件，则可以，Excel将执行该公式.

If by "refresh the file to run it" you mean open the file in Excel, then yes, Excel will execute the formula.

B)您得到的错误实际上只是一个警告，告诉您使用的功能在以后的Openpyxl版本中将不可用.在这种情况下，警告还会告诉您正确的处理方法.

B) The error you get is actually just a warning telling you that a feature you used will not be available in future versions of Openpyxl. In this case, the warning also tells you the correct way to acccess the sheet.

调用已弃用的函数get_sheet_by_name (使用wb [sheetname]) [添加了重点]

Call to deprecated function get_sheet_by_name (Use wb[sheetname]) [emphasis added]

为避免警告，将行替换为:

To avoid the warning, replace the line with:

worksheet=myworkbook['Sheet1']

C)使用Openpyxl将公式添加到单元格时，必须在公式的开头包含=字符，因此您的行应为:

C) When you add a formula to a cell using Openpyxl, you have to include the = character at the beginning of the formula, so your line should be:

mycell.value="=index(B4:B72,match(B1,A4:A72,0))"

D)在您的print语句中，向其传递mycell对象，这就是为什么它打印对象<Cell 'Sheet1'.B2>的名称"的原因.如果要验证您的mycell变量是否指向工作簿中的正确单元格，这可能会很有用，但这不是您想要的.要访问单元格的内容，您需要引用该单元格的.value.将您的打印对帐单修改为:

D) In your print statement, you pass it the mycell object, which is why it prints the 'name' of the object <Cell 'Sheet1'.B2>. This could be useful if you want to verify that your mycell variable is pointing to the correct cell in the workbook, but it isn't what you want. To access the contents of a cell, you need to refer to the .value of that cell. Modify your print statement to be:

print(mycell.value)

将所有内容放在一起:

from openpyxl import load_workbook
path= "C:\\Users\\Me\\Documents\\Python\\File.xlsx"
myworkbook=load_workbook(path)
worksheet=myworkbook['Sheet1']
mycell=worksheet['B2']
mycell.value="=index(B4:B72,match(B1,A4:A72,0))"
print(mycell.value)

这篇关于使用Python的Openpyxl进行索引匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！