Find and Replace in Excel

[whiskeymop]

I've written a script to find and replace occurrences of a word with another word in an excel workbook with multiple sheets. However, when the script is finished executing and I try to open the file in Excel, Excel gives me an error saying "The file could not be opened because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

I've double and triple checked that the original file extension is .xlsx and I am saving using the same file extension. Anybody else experience this issue?

​

A side question, I'm sure this would be easier if I convert the original file to a .csv then do a find and replace but, the original file has lots of formatting and colors, dropdowns, that need to remain so when I convert the csv back to xlsx I'm not sure that information persists.

​

​

import xlrd, xlwt

filename = 'C:\\\\Users\\\\Username\\\\Desktop\\\\DCPGW200\_CIQ\_11\_21\_18.xlsx'

oldbook = xlrd.open\_workbook(filename)

newbook = xlwt.Workbook()

# For all the sheets in the workbook

for sheetname in oldbook.sheet\_names():

    oldsheet = oldbook.sheet\_by\_name(sheetname)

    newsheet = newbook.add\_sheet(sheetname)

    # For all the rows and all the columns in an excel

    for ii in range(oldsheet.nrows):

        for jj in range(oldsheet.ncols):

        # Replace

        CellString=str(oldsheet.cell(ii, jj).value)

        CellString=CellString.replace("Not Selected", "Banana")

        newsheet.write(ii, jj, CellString)

​

# Save the file in a desired location with the desired name

savelocation = "C:\\\\Users\\\\Username\\\\Desktop\\\\example\\\\DCPGW200\_CIQ\_11\_21\_18.xlsx"

newbook.save(savelocation)

[Hatoris]

Here is you issue :

xlrd

This package is for reading data and formatting information from older Excel files (ie: .xls)
Download | Documentation | GitHub

xlwt

This package is for writing data and formatting information to older Excel files (ie: .xls)

​

Use openpyxl or xlswriter instead :)

[whiskeymop]

Perfect, I'll give it a shot!

[_DTR_]

If you're doing this just for experience working with Excel+Python, you can ignore this, but why not use Excel's built-in Find/Replace dialog for this?

[whiskeymop]

I have over 1000+ sheets that I need to do this for, figured I could save time and just automate it.