Find and replace in cells from excel in python

Question

Where I have a cell in an .xlsx file that is "=..." I want to replace the "=" with '=, so can see the cells as strings rather than as the values.

For example,

A1 = 5

A2 = 10

A3 = (A1/A2) = 0.5

I want to see =A1/A2 rather than 0.5.

Thank you in advance for any and all help.

You may use openpyxl (https://openpyxl.readthedocs.io/en/stable) as suggested in (https://stackoverflow.com/questions/42102674/how-can-i-see-the-formulas-of-an-excel-spreadsheet-in-pandas-python) — , Sep 11 '18 at 13:03

score 2 · Accepted Answer · answered Sep 11 '18 at 13:33

As suggested openpyxl solves this problem:

import openpyxl
from openpyxl.utils.cell import get_column_letter

wb = openpyxl.load_workbook('example.xlsx')
wb.sheetnames
sheet = wb["Sheet1"]
amountOfRows = sheet.max_row
amountOfColumns = sheet.max_column

for i in range(amountOfColumns):
    for k in range(amountOfRows):
        cell = str(sheet[get_column_letter(i+1)+str(k+1)].value)
        if( str(cell[0]) == "="):
            newCell = "'=,"+cell[1:]
            sheet[get_column_letter(i+1)+str(k+1)]=newCell

wb.save('example_copy.xlsx')

Find and replace in cells from excel in python

1 Answers1

Linked