title: How to save a pandas pivot table with xlsxwriter in excel
body: <p>I want to save a pandas pivot table proberly and nice formatted into an excel workbook.</p>
  
  <p>I have an pandas pivot table, based on this formula:</p>
  
  <pre><code>table = pd.pivot_table(d2, values=['Wert'], index=['area', 'Name'], columns=['Monat'],
                            aggfunc={'Wert': np.sum}, margins=True).fillna('')
  </code></pre>
  
  <p>From my original dataframe:</p>
  
  <pre><code>df
  Area Name2 Monat Wert
  A    A      1     2
  A    A      1     3
  A    B      1     2
  A    A      2     1
  </code></pre>
  
  <p>so the pivot table looks like this:</p>
  
  <pre><code>          Wert
       Monat 1   2  All
  Area  Name 
  A     A    5   1  6
        B    2      2
  All        7   1  8
  </code></pre>
  
  <p>Then I want to save this in an excel workbook with the following code:</p>
  
  <pre><code>import pandas as pd
  import xlsxwriter
  workbook = xlsxwriter.Workbook('myexcel.xlsx)
  worksheet1 = workbook.add_worksheet('table1')
  caption = 'Table1'
  worksheet1.set_column(1, 14, 25) #irrelevant, just a random size right now
  worksheet1.write('B1', caption)
  worksheet1.add_table('B3:K100', {'data': table.values.tolist()}) #also wrong size from B3 to K100
  workbook.close()
  </code></pre>
  
  <p>But this looks like this (with different values), so the headers are missing:
  <a href="https://i.stack.imgur.com/Udq8x.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Udq8x.png" alt="enter image description here"></a></p>
  
  <p>How can I adjust it and save a pivot table in excel?</p>
  
  <p>If I am using the pandas command <code>.to_excel</code> it looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/SorcD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SorcD.png" alt="enter image description here"></a></p>
  
  <p>Which is fine, but the column name is not respecting the width of the names and the background color is not looking nice, and I am also missing a capturing.</p>