I have one excel workbook containing two sheets. Sheet2 contains whole Dump while Sheet1 has formulas which fetches certain data from Sheet2. So, I want to remove formulas from Sheet1, so that I can do rest of the modifications. But it's not working. There's one more issue that my table header starts from row 4. import openpyxl # Load the workbook wb = openpyxl.load_workbook(file_path) # Select the worksheet ws = wb['Sheet1'] # Iterate over cells with formulas for row in ws.iter_rows(): for cell in row: if cell.data_type == 'f': # 'f' stands for formula cell.value = cell.value # This will replace the formula with its value if isinstance(cell.value, str): # Check if the value is a string cell.data_type = 's' # 's' stands for string else: cell.data_type = 'n' # 'n' stands for number wb.save(file_path) I tried using Pandas and it worked but it removed all the formatting and sheet2. Then I tried OpenPyXl but it couldn't remove the formulas. I'm expecting same workbook with 2 sheets with same formatting as before but with no formulas. Continue reading...
