Formatting below the level of the cell is not supported by openpyxl. To use it you’d have to implement your own code when writing as openpyxl just stores whatever strings it receives. Full read/write support would add a great deal of complexity.
Read the OpenPyXL Documentation Iteration over all worksheets in a workbook, for instance: for n, sheet in enumerate(wb.worksheets): print(‘Sheet Index:[{}], Title:{}’.format(n, sheet.title)) Output: Sheet Index:[0], Title: Sheet Sheet Index:[1], Title: Sheet1 Sheet Index:[2], Title: Sheet2 Iteration over all rows and columns in one Worksheet: worksheet = workbook.get_sheet_by_name(‘Sheet’) for row_cells in worksheet.iter_rows(): for cell in row_cells: … Read more
You should be using pandas own ExcelWriter class: from pandas import ExcelWriter # from pandas.io.parsers import ExcelWriter Then the save_xls function works as expected: def save_xls(list_dfs, xls_path): with ExcelWriter(xls_path) as writer: for n, df in enumerate(list_dfs): df.to_excel(writer,’sheet%s’ % n)
wb = openpyxl.load_workbook(filename, data_only=True) The data_only flag helps.
Thank you. I believe that a complete example could be good for anyone else who have the same issue: import pandas as pd import numpy as np path = r”C:\Users\fedel\Desktop\excelData\PhD_data.xlsx” x1 = np.random.randn(100, 2) df1 = pd.DataFrame(x1) x2 = np.random.randn(100, 2) df2 = pd.DataFrame(x2) writer = pd.ExcelWriter(path, engine=”xlsxwriter”) df1.to_excel(writer, sheet_name=”x1″) df2.to_excel(writer, sheet_name=”x2″) writer.save() writer.close() Here … Read more