I am working on a Python project (using 2.7) to search through excel files for the UNC path for a changing server, and then update the cell with a new UNC path. I am new to python and I can 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
But I do not know how to update a cell with a new row, and the workbook looks in read-only mode; probably because ws only retrieves information.
I found many resources on the Internet for finding cells and printing information, but not about how to update a cell after the information is found. There is a lot of information on how to create a new book with spaces, and then update your own information; also about how to open an existing worksheet and paste content into a new cell. I just can’t find what I’m looking for, and my skills are not yet available to do it myself.
Here are my goals, and if anyone can help, I would really appreciate it:
- Open book
- Iterate through cells for a UNC path; like a hyperlink.
- Write a new UNC path / formula
Note. I only want to update the first part of the link that refers to the server name. For example, file: /// \\ enterprise \ ... should be file: /// \\ file-server \ ... - Close book
Disadvantages I hope someone can help me:
- I don't understand if loop syntax is enough to iterate through cells for non-specific content; that I don’t know how to search only a word inside a string.
- Should I worry about capturing cell numbers / coordinates?
- If this problem is resorted to iterating the cells, storing the contents in a variable, parsing and extracting only the content that I am looking for (server name), combine other information before and after the new server name, and then update the cell contents?
source share