Search code examples
excelpandasexport-to-excelmulti-indexxlwings

xlwings vs pandas native export with multi index dataframes: how to reconcile?

I'll start with an image for it explains clearly the problem with exporting multindexes with pandas native export and xlwings

enter image description here

essentially, I'd like the 'Pandas native' result [the multiindex properly exported to excel] done with Xlwings because I have a bunch of other features that XlWings can do and others cannot (no not even if use ExcelWriter and the like because I have to clear the sheet and I have a non python thing inserted in the same sheet which would be cleared as well when initialized)

the code used:

import pandas as pd
import numpy as np
import xlwings as xw
import os

arrays = [['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'],
           ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two']]

tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
s = pd.DataFrame(np.random.randn(8, 8), index=index).transpose()
print(s)

# EXPORT

filename = 'format_excel_export.xlsx'
s.to_excel(filename)

outpath = os.path.join(os.path.abspath(os.path.dirname(__file__)), filename)
os.path.sep = r'/'
wb = xw.Workbook(outpath)

xw.Range('Sheet1', 'A13').value = s
Solution

  • You could implement a custom converter that formats it the way that Pandas does, see here.

    However, as of v0.7.2, xlwings doesn't yet natively support bolding fonts, merging cells and cell borders. You can get around this by falling back to pywin32 (on Windows), see here.

    In essence, your custom converter needs to override the write_value method, see here.

    It does make sense to build this into the library at some point, so I've opened an issue, see here.