8

Given the following data frame: import pandas as pd

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()
d

enter image description here

I'd like to export this with the same alignment with respect to the index (see how the left-most column is centered vertically?). The rub is that when exporting this to Excel, the left column is aligned to the top of each cell:

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
f=workbook.add_format({'align': 'vcenter'})
d.to_excel(writer, sheet_name='Sheet1')
writer.save()

...produces...

enter image description here

Is there any way to center column A vertically via XLSX Writer or another library?

Thanks in advance!

Dance Party2
  • 7,214
  • 17
  • 59
  • 106
  • Try the structure [here](http://xlsxwriter.readthedocs.io/format.html#set_align). It looks similar to what you've done, but I don't know enough about the xlsxwriter library to know if they're identical. – 3novak Dec 28 '16 at 15:18

1 Answers1

10

You are trying to change the formatting of the header so you should first reset the default header settings

from pandas.io.formats.excel import ExcelFormatter
ExcelFormatter.header_style = None

Then apply the formatting as required

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)

here is complete working code

d=pd.DataFrame({'a':['a','a','b','b'],
               'b':['a','b','c','d'],
               'c':[1,2,3,4]})
d=d.groupby(['a','b']).sum()

pd.core.format.header_style = None

writer = pd.ExcelWriter('pandas_out.xlsx', engine='xlsxwriter')
workbook  = writer.book
d.to_excel(writer, sheet_name='Sheet1')

worksheet = writer.sheets['Sheet1']

format = workbook.add_format()
format.set_align('center')
format.set_align('vcenter')

worksheet.set_column('A:C',5, format)
writer.save()
Oskar Austegard
  • 4,599
  • 4
  • 36
  • 50
Shijo
  • 9,313
  • 3
  • 19
  • 31