Reputation: 1335
I'll start with an image for it explains clearly the problem with exporting multindexes with pandas native export and xlwings
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
Upvotes: 2
Views: 1643
Reputation: 7070
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.
Upvotes: 3