使用OpenPyXL来遍历工作表和单元格，并使用contantenated字符串更新单元格 [英] Use OpenPyXL to iterate through sheets and cells, and update cells with contantenated string
问题描述
我想使用OpenPyXL来搜索一个工作簿，但我遇到一些我希望有人能够帮助的问题。
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.
这是一些障碍/待办事项：
Here are a few of the obstacles/to-dos:
- 我具有未知数量的纸张&单元格
- 我想通过工作簿进行搜索，并将数据表名称放在数组中
- 我想循环遍历每个数组项并搜索单元格包含一个特定的字符串
- 我有一个单元格，UNC路径引用一个旧的服务器。我需要在UNC路径中提取服务器名称后的所有文本，更新服务器名称，并将其余的文本复制到服务器名称
上。 \file-server\blah\blah\blah.xlsx;提取\file-server\;替换为\file-server1\;在新名称后放置blah\blah\blah.xlsx。 - 保存xlsx文档
- 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
我是Python的新手，有人能指出我的方向正确吗？感谢示例代码，因为我知道如何在这一点上进行的操作是通过已知的工作簿，已知的工作表名称进行搜索，然后打印数据。我不知道如何在通过工作表&细胞。
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.
我做了什么来显示单元格的内容：
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-------------------------
我可以搜索单元格并从单元格中提取服务器名称，但是由于我处于只读模式，因此我无法保存电子表格。当我尝试切换到optimize_write = True时，我会收到错误：
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:
AttributeError：'ReadOnlyCell'对象没有属性'upper'
AttributeError: 'ReadOnlyCell' object has no attribute 'upper'
这是我的代码：
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()
有谁知道如何更新单元格内容？
推荐答案
不要以为你可以更新单元格内容。您可以打开一个文件来阅读，或打开一个新文件来写入。
我想你必须创建一个新的工作簿，并且您读取的每个单元格，如果您选择不修改它，请写入您的新工作簿。在您的示例代码中，您将使用wb（用于写入）覆盖wb（用于读取）。将其从for循环中拉出，为其分配不同的名称。
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.
这篇关于使用OpenPyXL来遍历工作表和单元格，并使用contantenated字符串更新单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
