Skip to main content
 首页 » 编程设计

Python Xlsxwriter 图表

2024年11月01日9arxive

我正在尝试构建一个简单的 python 脚本,该脚本从 .csv 文件读取数据,将数据格式化为易于阅读的布局,然后将其写入新的 xlsx 文件或附加到现有的 xlsx 文件,具体取决于用户输入。一切正常,我使用以下方法写入新文件;

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

现在我想添加第二个工作表,其中包含来自数据的 excel 图表,并将上述代码扩展到;

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() 

这会按预期将图表添加到“主”工作表,我不明白如何创建第二个工作表并将图表插入到那里。我曾尝试将 sheet = writer.sheets 更改为新名称 [Graphs],但我猜它正在寻找具有该名称的现有工作表而不是创建一个。非常感谢任何帮助。

请您参考如下方法:

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

你可以这样做:

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() 
 

输出:

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

XlsxWriter 无法写入现有文件。