to_excel() without index layout

Question

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).

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?

score 3 · Answer 1 · answered Nov 18 '19 at 14:24

3

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

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

answered Nov 18 '19 at 14:24

Ian

933
12
17

1

Thanks for your answer, this was indeed also my thought but I hoped there is a solution without doing that (I could have add that to the question). Feels a bit random that Pandas add some formatting to the data. – Dylan_w Nov 18 '19 at 14:30
2

Check out this discussion to evaluate how to change the formatting to suit your needs (but I think the new index column will be less invasive): https://stackoverflow.com/questions/39892684/how-can-i-format-the-index-columns-with-xlsxwriter – Ian Nov 18 '19 at 14:35

Sourav Ghosh · Answer 2 · 2022-05-24T12:12:37.877

1

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

edited May 24 '22 at 12:12

answered May 24 '22 at 12:06

Sourav Ghosh

39
3

score 0 · Answer 3 · answered Nov 30 '21 at 20:34

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()

to_excel() without index layout

3 Answers3