Xlsxwriter in pandas and outside of pandas lockout

Question

I am using xlsxwriter engine to push some dataframes into excel sheet with pandas.to_excel(), but I need a caption above each table.

Here is an example of what result I would like to achieve:

I did not see any way to use ExcelWriter object of pandas to simply manipulate content of each cell and when I try to do it, I get:

Exception: Sheetname 'sample', with case ignored, is already in use.

I guess xlsxwriter locks it for itself. Here is an example:

xlsx_writer = pd.ExcelWriter(
    get_target_filepath(xlsx_name),
    engine='xlsxwriter'
)

workbook = xlsx_writer.book
worksheet = workbook.add_worksheet(sheet_name)

worksheet.write(1, 1, 'ABC')

_, sample_table = dataframe_tuples[0]
sample_table.to_excel(xlsx_writer, startrow=3, startcol=2, sheet_name=sheet_name)

I could save dataframes and go over it again with openpyxl, but I seriously dislike the way they do formatting, which I have to do next, and if I format it in xlsxwriter and then reopen it with openpyxl, it cannot save the formatting back properly, openpyxl ruins it.

I can not put excel itself onto server to use VBA macros for styling.

Is there any way around this or am I just doing something horribly wrong?

Seawright

Answer 1

You can do it as follows by getting the worksheet reference from pandas and calling standard XlsxWriter methods on it. Like this:

import pandas as pd


# Create some Pandas dataframes from some data.
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_example.xlsx', engine='xlsxwriter')

# Position the dataframes in the worksheet.
df1.to_excel(writer, sheet_name='Sheet1', index=False, startrow=2)
df2.to_excel(writer, sheet_name='Sheet1', index=False, startrow=9)

# Get the worksheet object.
worksheet = writer.sheets['Sheet1']

# Write some titles above the dataframes.
worksheet.write(1, 0, 'Some random title 1')
worksheet.write(8, 0, 'Some random title 2')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

See also Working with Python Pandas and XlsxWriter.

Output:

Auklet

This throws KeyError on 'Sheets1' and any substitution I use. I also don't understand why we get the book object if there is no use for it. Also there are some semicolons in the code, so I just removed those. I tried using 'workbook' to call sheets dictionary, but it has no attribute 'sheets', so that was not it I guess. – Dysprosium

I fixed the minor issues in the example. The workbook object wasn't required in this example. I don't, and shouldn't, get a KeyError on the sheet name. I've attached the output using Pandas 0.20.1 and XlsxWriter 1.0.2. – Auklet

That is interesting, when I print keys from freshly created ExcelWriter.sheets, it is empty. Pandas 0.20.3 XlsxWriter 1.0.2 – Dysprosium

Also, have a look at the XlsxWriter doc section that I linked to. It answers (I think) your fundamental question which is how access the underlying xlsxwriter workbook/worksheet when using Pandas. – Auklet

I detected the error. It seems I cannot get the sheet until pandas to_excel is called. After that, sheet 'exists'. So order of execution was my problem. Thank you and it was a pleasure to meet the mastermind of xlsxwriter project! – Dysprosium

Answer 2

Try to use multi-level columns:

In [137]: df
Out[137]:
   A  B  C
0  1  2  3
1  4  5  6
2  7  8  9

In [138]: df.columns = pd.MultiIndex.from_product([['Caption'], df.columns])

In [139]: df
Out[139]:
  Caption
        A  B  C
0       1  2  3
1       4  5  6
2       7  8  9

In [140]: df.to_excel('c:/temp/a.xlsx')

Result:

Kallick

This is very interesting out-of-the-box approach, I love it! It also works, but the solution is so specific that I don't think it would solve this kind of problem in general, the 'work with xlsxwriter and pandas on xlsxwriter engine together'. – Dysprosium