OpenPyXL +如何在Excel的单元格中搜索内容，并且如果内容符合搜索条件，则会更新内容? [英] OpenPyXL + How can I search for content in a cell in Excel, and if the content matches the search criteria update the content?

问题描述

我正在一个Python(使用2.7)项目中，通过excel文件搜索正在更改的服务器的UNC路径，然后使用新的UNC路径更新单元格.我是python的新手，我能够找到该单元格并使用以下命令进行打印:

I'm working on a Python (using 2.7) project to search through excel files for a UNC path for a server that is changing, and then update the cell with the new UNC path. I'm new to python and I'm able to find the cell and print it with:

def main():
    wb = load_workbook(filename = 'Book1_test.xlsx', use_iterators = True)
    ws = wb.get_sheet_by_name(name = 'Sheet1')

    for row in ws.iter_rows(): 
        for cell in row:
            print cell.value

但是，我不知道如何用新的字符串更新单元格，并且工作簿似乎处于只读模式.可能是因为ws仅在获取信息.
我在网上找到了很多资源来搜索单元格和打印信息，但是没有找到有关信息后如何更新单元格的资源.关于如何用空白单元格创建新工作簿，然后更新自己的信息，有很多信息.此外，还介绍了如何打开现有工作表并将内容插入新单元格中.我只是找不到我要找的东西，我自己的技能还不存在.

But, I don't know how to update the cell with the new string, and the workbook appears to be in read only mode; probably because ws is only retrieving information.
I found lots of resources online for searching cells and printing information but not on how to update the cell after the information is found. There is a lot of information on how to create a new workbook with blanks cells, and then update your own information; also, on how to open an existing worksheet and insert contents into a new cell. I'm just not finding what I'm looking for and my skills aren't there yet to do it myself.

这是我的目标，如果有人可以提供帮助，我将不胜感激:

Here are my goals, and if someone can help I'd greatly appreciate it:

1. 打开工作簿
2. 遍历单元格以获取UNC路径；作为超链接.
3. 编写新的UNC路径/公式
   注意:我只想更新引用服务器名称的链接的第一部分.例如， file:///\\ enterprise \ ... 将是 file:///\\ file-server \ ...
4. 关闭工作簿

1. Open workbook
2. Iterate through cells for UNC path; as a hyperlink.
3. Write new UNC path/formula
   Note: I only want to update the first part of the link referencing the server name. For example, file:///\\enterprise\... is to be file:///\\file-server\...
4. Close workbook

缺点我希望有人可以帮助我:

Shortcomings I'm hoping someone can help me with:

• 我对循环语法的理解不足以遍历非特定内容的单元格；这意味着我不知道如何仅搜索字符串中的单词.
• 我应该担心捕获手机号/坐标吗?
• 应该通过迭代单元格，将内容存储到变量，解析字符串并仅提取我要查找的内容(服务器名称)，在新服务器名称之前和之后串联其他信息，然后更新来解决此问题单元格内容?

推荐答案

Cell对象的.value属性是可设置的.这意味着您可以随时执行以下操作:

The .value property of the Cell object is settable. This means that you can do any time something like:

cell.value = 'Helloooo ladies'

如果这不起作用，则可能是由于优化程序读取模式所致.如果需要，请检查优化的写入模式...我总是发现openpyxl的文档非常完整且易于遵循.

If this does not work it is probably because of the optimizer read mode. Check the optimized write mode if you needed... I always found the docs of openpyxl very complete and easy to follow.

如何在单元格中查找信息或识别所要查找的内容是一个非常开放的问题，可以通过数千种不同的方法来解决:

How to look for information in the cell, or recognize what you are looking for is a very open question that can be solved in thousand different ways:

• 检查单元格的全部内容
• 检查单元格中是否包含给定的子字符串
• 匹配正则表达式
• ...

但这与openpyxl框架没有任何关系.

But this does not have anything to do with the openpyxl framework.

这篇关于OpenPyXL +如何在Excel的单元格中搜索内容，并且如果内容符合搜索条件，则会更新内容?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！