Python Xlsxwriter charting

Question

I am trying to build a simple python script that reads data from a .csv file, formats the data to an easy to read layout, then either writes it to a new xlsx file or appends to an existing xlsx file, depending on user input. That all works well and I write to the new file using;

    with pd.ExcelWriter(file_path) as writer:
    df.to_excel(writer, sheet_name='Master')

Now I want to add a second sheet that contains excel charts from the data and have extended the above code to;

with pd.ExcelWriter(file_path) as writer:
    df.to_excel(writer, sheet_name='Master')
    book = writer.book
    sheet = writer.sheets['Master']
    chart_a = book.add_chart({'type': 'line'})
    chart_a.add_series({
        'categories': ['Master', 1, 0, trend_data_row, 0],
        'values': ['Master', 1, 1, trend_data_row, 1],
    })
    chart_a.set_x_axis({'name': 'time', 'position_axis': 'on_tick'})
    chart_a.set_y_axis({'name': 'value'})
    chart_a.set_legend({'position': 'Bottom'})
    sheet.insert_chart('A11', chart_a)
    writer.save()

This adds the chart to the 'Master' sheet as expected, I don't understand how to create the second sheet and insert the chart there instead. I have tried changing sheet = writer.sheets to a new name [Graphs] but I guess its looking for an existing sheet with that name rather than creating one. Any help is really appreciatted.

https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas see this can help you. — Avinash Dalvi, Oct 23 '19 at 10:27

jmcnamara · Accepted Answer · 2019-10-23T14:46:34.957

I don't understand how to create the second sheet and insert the chart there instead.

You can do it like this:

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

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

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Master')

# Get the xlsxwriter workbook object.
workbook  = writer.book

# Add a new worksheet.
worksheet = workbook.add_worksheet('Graphs')

# Create a chart object.
chart = workbook.add_chart({'type': 'column'})

# Configure the series of the chart from the dataframe data.
chart.add_series({'values':  ['Master', 1, 1, 7, 1]})

# Insert the chart into the worksheet.
worksheet.insert_chart('D2', chart)

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

Output:

then either writes it to a new xlsx file or appends to an existing xlsx file

XlsxWriter cannot write to an existing file.

Python Xlsxwriter charting

1 Answers1

Linked