在现有的excel文件中添加一个新的工作表，并绘制d

Question

我希望通过导出pandas dataframe并使用openpyxl将数据图形化，从而将新的工作表添加到excel文件中。目前我可以添加一个新的工作表并插入熊猫数据框，但不能插入数据的图形。在

目前我的代码是这样的。在

import pandas
import openpyxl
from openpyxl.chart import LineChart, Reference

fileSavePath = 'C:/Users/PATH'
filename = "Existing Excel File"

lst1 = [1, 2, 3, 4, 5, 6, 7, 8, 9]
lst2 = [0, 5, 2, 8, 15, 7, 10, 4, 0]

df = pandas.DataFrame({'First Column': lst1, 'Second Column': lst2})

book = openpyxl.load_workbook(fileSavePath + filename + '.xlsx')
with pandas.ExcelWriter(fileSavePath + filename + '.xlsx', engine='openpyxl') as writer:
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df.to_excel(writer, 'new_sheet', index=False)

wb = openpyxl.Workbook()
sheet = wb.active
values = Reference(sheet, min_col=1, min_row=1,
                   max_col=1, max_row=len(lst1))
chart = LineChart()
chart.add_data(values)
chart.title = " LINE-CHART "
chart.x_axis.title = " X-AXIS "
chart.y_axis.title = " Y-AXIS "
sheet.add_chart(chart, "E2")
writer.save()
writer.close()

目前没有错误，数据帧写入，但图形未出现在新添加的工作表中。在

Answer 1

此代码将成功地添加一个新的工作表并将数据图形化。在

import pandas
from openpyxl.chart import LineChart, Reference
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import load_workbook

lst1 = [1, 2, 3, 4, 5, 6, 7, 8, 9]
lst2 = [0, 5, 2, 8, 15, 7, 10, 4, 0]
df = pandas.DataFrame({'First Column': lst1, 'Second Column': lst2})
fileSavePath = 'C:/Users/PathExample'
filename = "filename"
newSheetName = 'newSheet'
sheet = newSheetName
# load in existing workbook
wb = load_workbook(fileSavePath + filename + '.xlsx')
# create new sheet with name assigned by title
wb.create_sheet(newSheetName)
# find which sheet is the new sheet
newSheetIndex = len(wb.sheetnames) - 1
# set the newest sheet as the active sheet
wb.active = newSheetIndex
worksheet = wb.active

# Insert Dataframe
for r in dataframe_to_rows(df, index=False, header=True):
    worksheet.append(r)

#Graph data
chart = LineChart()
data = Reference(worksheet, range_string=f'{sheet}!B2:B10')
category = Reference(worksheet, range_string=f'{sheet}!A2:A10')
chart.add_data(data, titles_from_data=True)
chart.set_categories(category)
worksheet.add_chart(chart, 'D3')
wb.save(fileSavePath + filename + '.xlsx')

问题似乎出在设置活动工作表中。前面的代码添加了工作表，但仍在绘制“活动工作表”，这就是为什么图形没有出现在其他工作表上的原因。Openpyxl使用一个列表来跟踪excel工作表，以便操作所需的工作表，您需要将该索引号设置为活动工作表(wb.活动). 在