Use OpenPyXL to iterate over sheets and cells and update cells with row continentation

Question

I would like to use OpenPyXL to search the book, but I am facing some problems that I hope someone can help.

Here are a few / to -dos obstacles:

• I have an unknown number of sheets and cells
• I want to search a book and put the sheet names in an array
• I want to loop through each element of an array and look for cells containing a specific string
• I have cells with UNC paths that reference the old server. I need to extract all the text after the server name in the UNC path, update the server name and associate the remaining text with the server name
  
  for example. \ File Server \ blah \ blah \ blah.xlsx; extract \ file-server \; replace with \ file-server1 \; put the remaining blah \ blah \ blah.xlsx after the new name.
• Save xlsx document

I'm new to Python, so can someone point me in the right direction? Sample code is welcome, because all I know how to do at this point is to search for a known book with known sheet names, and then print the data. I don't know how to include wildcards when repeating through sheets and cells.

What I did 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 can search through cells and retrieve the server name from the cell, but I cannot save the table because I am in read-only mode. When I try to switch to optimized_write = True, I get an error message:

AttributeError: the 'ReadOnlyCell' object does not have the 'upper' attribute

Here is 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?

Answer 1

I do not think you can update the contents of a cell. You can open the file for reading or open a new file for writing. I think you need to create a new book, and each cell that you read, if you decide not to change it, write it in your new book. In your code example, you rewrite wb (used for reading) with wb (used for writing). Pull it out of the for loop, give it a different name.

Answer 2

Why don't you read the documentation? If you just open a book without 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, refresh the content?

Answer 3

You can update the contents in the 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') 

Answer 4

with lines 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'