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

i'll start image explains problem exporting multindexes pandas native export , xlwings

essentially, i'd 'pandas native' result [the multiindex exported excel] done xlwings because have bunch of other features xlwings can , others cannot (no not if use excelwriter , because have clear sheet , have non python thing inserted in same sheet cleared when initialized)

the code used:

import pandas pd import numpy np import xlwings 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 

you implement custom converter formats way pandas does, see here.

however, of v0.7.2, xlwings doesn't yet natively support bolding fonts, merging cells , cell borders. can around falling pywin32 (on windows), see here.

in essence, custom converter needs override write_value method, see here.

it make sense build library @ point, i've opened issue, see here.