Python 使用 OpenPyXL 遍历工作表和单元格，并使用连续字符串更新单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25247742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Use OpenPyXL to iterate through sheets and cells, and update cells with contantenated string
提问by Tellander
I would like to use OpenPyXL to search through a workbook, but I'm running into some issues that I'm hoping someone can help with.
我想使用 OpenPyXL 来搜索工作簿，但我遇到了一些问题，希望有人可以提供帮助。
Here are a few of the obstacles/to-dos:
以下是一些障碍/待办事项：
- I have an unknown number of sheets & cells
- I want to search through the workbook and place the sheet names in an array
- I want to cycle through each array item and search for cells containing a specific string
- I have cells with UNC paths that reference an old server. I need to extract all the text after the server name within the UNC path, update the server name, and contatenate the remaining text back on the server name
e.g. \file-server\blah\blah\blah.xlsx; extract \file-server\; replace with \file-server1\; put remaining blah\blah\blah.xlsx after new name. - Save xlsx document
- 我有未知数量的工作表和单元格
- 我想搜索工作簿并将工作表名称放在一个数组中
- 我想遍历每个数组项并搜索包含特定字符串的单元格
- 我有引用旧服务器的 UNC 路径的单元格。我需要提取 UNC 路径中服务器名称之后的所有文本，更新服务器名称，并将剩余的文本重新连接到服务器名称上，
例如 \file-server\blah\blah\blah.xlsx; 提取\文件服务器\; 替换为 \file-server1\; 将剩余的 blah\blah\blah.xlsx 放在新名称之后。 - 保存 xlsx 文档
I'm new to Python, so would someone be able to point me in the right direction? Sample code is appreciated, because all I know how to do at this point is search through a known workbook, with known sheet names, and then print the data. I don't know how to include wildcards when iterating through worksheets & cells.
我是 Python 新手，所以有人能指出我正确的方向吗？示例代码很受欢迎，因为此时我所知道的就是搜索已知工作簿，使用已知的工作表名称，然后打印数据。我不知道在迭代工作表和单元格时如何包含通配符。
What I've done to show the contents of the cells:
我为显示单元格的内容所做的工作：
from openpyxl import load_workbook, worksheet
def main():
#read workbook to get data
wb = load_workbook(filename = 'Book1_test.xlsx', use_iterators = True)
ws = wb.get_sheet_by_name(name = 'Sheet1')
#ws = wb.worksheets
#Iterate through worksheet and print cell contents
for row in ws.iter_rows():
for cell in row:
print cell.value
#Iterate through workbook & print worksheets
#for sheet in wb.worksheets:
# print sheet
if __name__ == '__main__':
main()
-----------------------Update-------------------------
- - - - - - - - - - - -更新 - - - - - - - - - - - - -
I'm able to search through the cells and extract the server name from the cell, but I I'm not able to save the spreadsheet because I'm in read only mode. When I try to switch to optimized_write=True I get the error:
我能够搜索单元格并从单元格中提取服务器名称，但我无法保存电子表格，因为我处于只读模式。当我尝试切换到 optimization_write=True 时，出现错误：
AttributeError: 'ReadOnlyCell' object has no attribute 'upper'
AttributeError: 'ReadOnlyCell' 对象没有属性 'upper'
Here's my code:
这是我的代码：
from openpyxl import load_workbook, worksheet, Workbook
def main():
#read workbook to get data
wb = load_workbook(filename = 'Book1_test.xlsx', use_iterators = True)
ws = wb.get_sheet_by_name(name = 'Sheet1')
#ws = wb.worksheets
#Iterate through worksheet and print cell contents
for row in ws.iter_rows():
for cell in row:
cellContent = str(cell.value)
#Scans the first 14 characters of the string for the server name
if cellContent[:14] == '\\file-server\':
#open workbook in write mode?
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
#update cell content
ws[cell] = '\\file-server1\' + cellContent[14:]
print cellContent[:14]
#save workbooks
wb.save('Book1_test.xlsx')
if __name__ == '__main__':
main()
Does anyone know how to update cell contents?
有谁知道如何更新单元格内容？
采纳答案by user3203010
I dont think you can update cell contents. You can open a file to read, or open a new file to write to. I think you have to create a new workbook, and every cell that you read, if you choose to not modify it, write it out to your new workbook. In your sample code, you are overwriting wb (used to read) with the wb (used to write). Pull it out of the for loop, assign a different name to it.
我认为您无法更新单元格内容。您可以打开一个文件进行读取，也可以打开一个新文件进行写入。我认为您必须创建一个新工作簿，并且您阅读的每个单元格，如果您选择不修改它，请将其写到您的新工作簿中。在您的示例代码中，您使用 wb（用于写入）覆盖 wb（用于读取）。将其从 for 循环中拉出，为其分配不同的名称。
回答by Charlie Clark
Why don't you read the documentation? If you simply open the workbook with no flags you can edit it.
你为什么不阅读文档？如果您只是打开没有标志的工作簿，您可以对其进行编辑。
This is a duplicate of OpenPyXL + How can I search for content in a cell in Excel, and if the content matches the search criteria update the content?
回答by user6189938
You can update the content in a cell. You need to assign a value:
您可以更新单元格中的内容。您需要分配一个值：
workBook = load_workbook('example.xlsx')
sheet = workBook.get_sheet_by_name('sheet')
a = sheet.cell(row=i,column=j)
a.value = 'nuevo valor'
and then save:
然后保存：
workBook.save('example.xlsx')
回答by PepeContraPepe TradeMark
by rows something like this (as an idea) works:
像这样的行（作为一个想法）有效：
sheet = wb.create_sheet(index = 1, title = 'Hipster') # name of the obj. sheet
for counter in range(1,11):
sheet['A'+ str(counter)] = 'Hola'
