Adding a Chartsheet in an excel sheet using Python XlsxWriter module

The XlsxWriter module in Python is a powerful external library that allows you to create Excel files with data, formatting, and charts. Unlike Python's built-in libraries, XlsxWriter specializes in generating Excel files with advanced features like charts, images, and complex formatting.

What is a Chartsheet?

A chartsheet is a separate worksheet in Excel that contains only a chart, without any data cells. This is different from inserting a chart into a regular worksheet − the entire sheet becomes the chart.

Creating a Pie Chart in a Chartsheet

Let's create a pie chart that displays data about different food categories and their quantities ?

import xlsxwriter

# Create a new workbook and add a worksheet for data
workbook = xlsxwriter.Workbook('pie_chart_example.xlsx')
worksheet = workbook.add_worksheet('Data')

# Add the data to be plotted
food_categories = ['milk', 'fruit', 'eggs', 'grains']
quantities = [27, 34, 12, 8]

# Write data to worksheet
worksheet.write_column('A1', food_categories)
worksheet.write_column('B1', quantities)

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

# Configure the chart series
chart.add_series({
    'categories': '=Data!$A$1:$A$4',
    'values': '=Data!$B$1:$B$4',
    'name': 'Food Distribution'
})

# Set chart title
chart.set_title({'name': 'Food Category Distribution'})

# Create a chartsheet and insert the chart
chartsheet = workbook.add_chartsheet('Pie Chart')
chartsheet.set_chart(chart)

# Close the workbook
workbook.close()

Key Methods for Chartsheets

When working with chartsheets, you'll use these essential methods ?

• add_chartsheet(name) − Creates a new chartsheet with the given name
• set_chart(chart) − Assigns a chart object to the chartsheet
• set_title() − Adds a title to the chart
• add_series() − Defines the data series for the chart

Chartsheet vs Regular Worksheet Chart

Complete Example with Multiple Charts

import xlsxwriter

# Create workbook
workbook = xlsxwriter.Workbook('multiple_charts.xlsx')

# Add data worksheet
data_sheet = workbook.add_worksheet('Sales Data')
months = ['Jan', 'Feb', 'Mar', 'Apr']
sales = [1000, 1200, 900, 1500]

data_sheet.write_row('A1', months)
data_sheet.write_row('A2', sales)

# Create pie chart
pie_chart = workbook.add_chart({'type': 'pie'})
pie_chart.add_series({
    'categories': '=Sales Data!$A$1:$D$1',
    'values': '=Sales Data!$A$2:$D$2',
})
pie_chart.set_title({'name': 'Monthly Sales Distribution'})

# Create chartsheet for pie chart
pie_chartsheet = workbook.add_chartsheet('Sales Pie')
pie_chartsheet.set_chart(pie_chart)

workbook.close()

Conclusion

Chartsheets in XlsxWriter allow you to create dedicated chart worksheets that display full-size charts without any data cells. This is perfect for creating presentation-ready charts that focus entirely on data visualization rather than the underlying data itself.