I'm using Pandas to edit an Excel file which other people are using. But when I save it using df.to_excel, Pandas adds an ugly looking black border to cells in the header and in the index. I want it to be written in a plain format, how a CSV file would look if I opened it up in Excel. It would be even better if it was written back using the same styles it was read in
Is there anyway to make df.to_excel write without styling or with the original styles?
Thanks.
Try this trick:
import io
pd.read_csv(io.StringIO(df.to_csv()), header=None)
.to_excel("output.xlsx", header=None, index=None)
If you still want index and header values - but without styling, you can use this (requires openpyxl):
def insert_dataframe(df,sheet,start_row=1,start_col=1):
"""inserts a dataframe into an openpyxl sheet at the given (row,col) position.
Parameters
----------
df : pandas.Dataframe
Any dataframe
sheet : openpyxl.worksheet.worksheet
The sheet where the dataframe should be inserted
start_row : int
The row where the dataframe should be insterted (default is 1)
start_col : int
The column where the dataframe should be insterted (default is 1)
"""
#iterate dataframe index names and insert
for name_idx, name in enumerate(df.index.names):
label_col=start_col+name_idx
sheet.cell(row=start_row, column=label_col, value=name)
#for each name iterate values as rows in the current index name column
value_row=start_row+1
for i_value in list(df.index.values):
if isinstance(df.index, pd.MultiIndex):
val=i_value[name_idx]
else:
val=i_value
sheet.cell(row=value_row, column=label_col, value=val)
#goto next row
value_row+=1
row_idx=0
col_idx=label_col+1
#insert values
for label,content in df.items():
sheet.cell(row=start_row, column=col_idx, value=label)
for row_idx,value_ in enumerate(content):
sheet.cell(row=start_row+row_idx+1, column=col_idx, value=value_)
col_idx+=1
Gist: https://gist.github.com/Aer0naut/094ff1b6838b2177a4222591ace8f6bf
