Script to find, replace and save xlsx file

Question

I have a requirement I need find, replace and Save As to create multiple .xlxs Excel sheet from one sheet, the requirement is wherever I find a text "202" replace it with (203......290) and save as with same name.

I wrote a code in python where I am successfully able to replace the string and save the file, but the problem is as below:

1. On Blank cells now "None" is printed.
2. If I put a if condition if s=="None":
   ws.cell(r,c).value=s.replace("None",str(''))
   I am successfully able to see blank cells but other cells which has none value originally is now blank.
3. Any integer value is also like string.

import openpyxl
from openpyxl.utils.cell import get_column_letter
for value in range(204, 205):
    wb=openpyxl.load_workbook("location of script\\sample.xlsx")
    for i in wb.sheetnames:
        ws = wb[i]
        for r in range(1,ws.max_row+1):
            for c in range(1,ws.max_column+1):
                s=str(ws.cell(r,c).value)
                if s=="None":
                    ws.cell(r,c).value=s.replace("None",str(''))

                elif "202 in s":
                    ws.cell(r,c).value=s.replace("202",str(value))
                    print("row{} col{}: {}".format(r,c,s))
                
    wb.save("location of script"+str(value)+'sample'+'.xlsx') .

Request you to please help me to edit code so that I can following requirement:

1. Blank cell should not have any value.
2. Cells which are Integer should remain integer
3. or if you other suggestion like writing Macro

Answer 1

My take on this. Using pandas may be more efficient and seems a bit simpler.

import pandas as pd

filename = "File.xlsx"

value_to_replace = 202
new_value = 269

df = pd.read_excel(filename)

for column in range(df.shape[1]):
    col_name = df.columns[column]
    df.loc[df[col_name] == value_to_replace, col_name] = new_value

df.to_excel(filename, index=False)

It overwrites the current excel sheet.

In order to read a sheet and save to a different file you need to use:

df = pd.read_excel(filename_input, sheet_name="Sheet1")
df.to_excel(filename_output, sheet_name="Sheet1")

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Answer 2

Thanks Ducu for help, now its working for me.

import openpyxl
from openpyxl.utils.cell import get_column_letter
for value in range(207, 208):
    wb=openpyxl.load_workbook("Location")
    for i in wb.sheetnames:
        ws = wb[i]
        for r in range(1,ws.max_row+1):
            for c in range(1,ws.max_column+1):
                s=ws.cell(r,c).value
                #print(type(s))
                #print(s)
                if s is None or isinstance(s, int):
                    print(s)
                elif '202' in str(s):
                    ws.cell(r,c).value=s.replace("202",str(value))
                    #print("row{} col{}: {}".format(r,c,s))
                
    wb.save("Location"+str(value)+'Sample'+'.xlsx')