CODEWITHSUNDEEP
pythonpandasdataframeopenpyxl
Dylan_w
Dylan_w

Reputation: 482

to_excel() without index layout

I'm using to_excel to write multiple DataFrames to multiple Excel documents. This works fine except that the index of the Dataframes is appended in bold with a border around each cell (see image).

enter image description here

The following code is a simplification of the code I use but has the same problem:

import pandas as pd
from openpyxl import load_workbook

df = pd.DataFrame(np.random.randint(50,60, size=(20, 3)))

xls_loc = r'test_doc.xlsx'
wb = load_workbook(xls_loc)

writer = pd.ExcelWriter(xls_loc, engine='openpyxl') 
writer.book = wb

df.to_excel(writer, sheet_name='test sheet',index=True,startrow=1,startcol=1, header=False)

writer.save()
writer.close()

Is there a way to append the index without making the index bold and add borders?

Upvotes: 4

Views: 10148

Answers (3)

Sourav Ghosh
Sourav Ghosh

Reputation: 39

import pandas as pd
data = [11,12,13,14,15]
df = pd.DataFrame(data)
wb = pd.ExcelWriter('FileName.xlsx', engine='xlsxwriter')
df.style.set_properties(**{'text-align': 'center'}).to_excel(wb, sheet_name='sheet_01',index=False,header=None)
wb.save()

In to_excel() method index=False & header=None is the main trick

Upvotes: 1

mouwsy
mouwsy

Reputation: 1933

You could insert the dataframe using xlwings to avoid formatting:

import pandas as pd
import xlwings as xw

df = pd._testing.makeDataFrame()

with xw.App(visible=False) as app:
    wb = xw.Book()
    wb.sheets[0]["A1"].value = df
    wb.save("test.xlsx")
    wb.close()

Upvotes: 0

Ian
Ian

Reputation: 1033

Make the index a new column and then set index=False in to_excel()

df.insert(0, 'index', df.index)

Upvotes: 3

Related Questions