基于Pandas-XlsxWriter示例，如何在Excel图表下添加描述或表格？

Absolutely! You can absolutely add descriptions or tables below your Excel chart when using Pandas with XlsxWriter—no need to rely on pylab for this, since we're working directly within the Excel file itself. Here's how to do it, building on the grouped column chart example you referenced:

Step 1: Start with the Base Chart Code #

First, let's set up the core code for the grouped column chart (matching the example you're using):

import pandas as pd

# Sample data for the chart
data = {
    'Apples': [30, 20, 25, 15],
    'Oranges': [20, 25, 30, 10],
    'Bananas': [10, 15, 20, 25]
}
df = pd.DataFrame(data, index=['Q1', 'Q2', 'Q3', 'Q4'])

# Create Excel writer with XlsxEngine
writer = pd.ExcelWriter('fruit_sales.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='SalesData')

# Get access to the underlying XlsxWriter workbook/worksheet objects
workbook = writer.book
worksheet = writer.sheets['SalesData']

# Build the grouped column chart
chart = workbook.add_chart({'type': 'column', 'subtype': 'grouped'})
sheet_name = 'SalesData'

# Add data series to the chart
for col_idx, col_name in enumerate(df.columns):
    chart.add_series({
        'name':       [sheet_name, 0, col_idx + 1],
        'categories': [sheet_name, 1, 0, len(df), 0],
        'values':     [sheet_name, 1, col_idx + 1, len(df), col_idx + 1],
    })

# Insert chart into the worksheet (starting at cell G2)
chart_position = 'G2'
worksheet.insert_chart(chart_position, chart)

Step 2: Add a Text Description Below the Chart #

To add a descriptive text block, you just need to write to cells below the chart. First, estimate where your chart ends (e.g., if it starts at G2, it might span rows 2-13). We'll start our description at row 15:

# Create a format for the description header (optional but improves readability)
desc_header_format = workbook.add_format({
    'bold': True,
    'font_size': 12,
    'font_color': '#2F5496'
})

# Write description content
worksheet.write('G15', 'Chart Overview', desc_header_format)
worksheet.write('G16', 'This grouped column chart tracks quarterly unit sales for three fruit types.')
worksheet.write('G17', 'Apples had the highest sales in Q1, while Bananas saw steady growth throughout the year.')

Step 3: Add a Table Below the Chart #

If you want to include a summary table (like total annual sales), you can write raw data or use XlsxWriter's built-in table feature for a polished, Excel-native table:

# Calculate total annual sales for each fruit
total_sales = df.sum().reset_index()
total_sales.columns = ['Fruit', 'Total Annual Sales']

# Option 1: Write a basic formatted table
# Define table starting position (below the description)
table_start_col = 'G'
table_start_row = 19

# Write table headers with formatting
header_format = workbook.add_format({
    'bold': True,
    'bg_color': '#D9E1F2',
    'border': 1
})
worksheet.write(f'{table_start_col}{table_start_row}', total_sales.columns[0], header_format)
worksheet.write(f'{table_start_col}{table_start_row+1}', total_sales.columns[1], header_format)

# Write table data
for row_idx, (fruit, total) in enumerate(total_sales.itertuples(index=False), start=table_start_row+2):
    worksheet.write(f'{table_start_col}{row_idx}', fruit)
    worksheet.write(f'{table_start_col}{row_idx}', total)

# Option 2: Add an Excel-native formatted table (uncomment to use)
# worksheet.add_table(f'G19:H22', {
#     'data': total_sales.values.tolist(),
#     'columns': [{'header': col} for col in total_sales.columns],
#     'style': 'Table Style Medium 9'
# })

Step 4: Save the Final File #

Don't forget to close the writer to save all changes:

writer.close()

Key Notes #

• The critical piece is accessing the underlying xlsxwriter worksheet/workbook objects from Pandas' ExcelWriter—this unlocks all of XlsxWriter's native features beyond what Pandas exposes directly.
• Adjust cell positions (like G15, G19) based on your chart's size; you can check the initial chart-only Excel file to see where the chart ends.
• Using formats makes your description/table match Excel's native styling for a professional look.

内容的提问来源于stack exchange，提问作者friggler