xlwings vs pandas native export with multi index dataframes: how to reconcile?
<p>I'll start with an image for it explains clearly the problem with exporting multindexes with pandas native export and xlwings</p>

<p><a href="https://i.stack.imgur.com/lowF6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lowF6.png" alt="enter image description here"></a></p>

<p>essentially, I'd like the '<code>Pandas</code> native' result [the multiindex properly exported to excel] done with <code>Xlwings</code> because I have a bunch of other features that <code>XlWings</code> can do and others cannot (no not even if use <code>ExcelWriter</code> 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)</p>

<p>the code used:</p>

<pre><code>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
</code></pre>