Read and write xlsx/xlst using pandas/openpyxl while preserving Excel formatting
Read and write Excel xlsx using pandas/openpyxl without destroying formatting.
Sometimes you have a nicely formatted worksheet, but you'd like to work with it using pandas, or perhaps you want to write data to an Excel template.
Pandas can read and write Excel files using
xlrd, but treats them like csvs.
xlPandas uses openpyxl to access data
while preserving template formatting, macros, and other worksheet attributes.
pip install xlpandas
import xlpandas as xpd # Read excel file df = xpd.read_file('template.xlsx', skiprows=2) print(df.columns) # Modify dataframe df['new_column'] = True # Access openpyxl worksheet sheet = df.to_sheet() sheet.cell(1,1).value = 'title' # From openpyxl worksheet df = xpd.xlDataFrame(sheet) # Write file df.to_file('out.xlsx')
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.