我正在使用 openpyxl 创建一个包含饼图的 Excel 文件。但是，我似乎无法从图表中删除默认的“Series1”标签。为了更好的上下文，我已经包含了整个 export_to_excel 函数，但问题具体在于我创建饼图的分析表。
def export_to_excel(self):
start_date = self.fromDate.date().toString('yyyy-MM-dd')
end_date = self.toDate.date().toString('yyyy-MM-dd')
start_time = self.fromTime.time().toString('HH:mm:ss')
end_time = self.toTime.time().toString('HH:mm:ss')
start_datetime = f"{start_date} {start_time}"
end_datetime = f"{end_date} {end_time}"
account_name = self.accountDropdown.currentText()
search_term = self.itemName.text()
search_amount = self.amount.text()
query = '''
SELECT expenses.id, expenses.item, expenses.amount, expenses.date, expenses.time, expenses.note, accounts.name, expenses.account_id
FROM expenses
JOIN accounts ON expenses.account_id = accounts.id
WHERE datetime(expenses.date || ' ' || expenses.time) BETWEEN ? AND ? AND expenses.item LIKE ?
ORDER BY expenses.date
'''
params = (start_datetime, end_datetime, f'%{search_term}%')
self.c.execute(query, params)
results = self.c.fetchall()
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
if results:
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = "Expenses"
sheet.freeze_panes = "A3"
sheet.page_setup.fitToWidth = 1
sheet.page_setup.fitToHeight = False
sheet.page_setup.paperSize = sheet.PAPERSIZE_A4
# Date range row
date_range = f"Expense Report: {start_date} to {end_date}"
sheet.append([date_range])
sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7)
bold_underline_font = Font(bold=True, underline="single")
date_range_cell = sheet.cell(row=1, column=1)
date_range_cell.font = bold_underline_font
date_range_cell.alignment = Alignment(horizontal='center', vertical='center')
headers = ["Sr No.", "Amount", "Item", "Date", "Time", "Note", "Account Name"]
sheet.append(headers)
header_font = Font(bold=True)
center_alignment = Alignment(horizontal='center')
bold_center_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
for cell in sheet[2]:
cell.font = header_font
cell.alignment = bold_center_alignment if cell.value != "Note" else center_alignment
cell.border = thin_border
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
bold_font = Font(bold=True)
total_amount = 0
item_amounts = {}
for sr_no, row in enumerate(results, start=1):
# Convert date to dd-MM-yyyy format
date = QtCore.QDate.fromString(row[3], 'yyyy-MM-dd').toString('dd-MM-yyyy')
row_data = [sr_no, row[2], row[1], date, row[4], row[5], row[6]]
sheet.append(row_data)
for col_num, value in enumerate(row_data, start=1):
cell = sheet.cell(row=sheet.max_row, column=col_num)
if col_num == 2 and isinstance(value, float) and value >= 3000:
cell.fill = red_fill
cell.font = bold_font
if col_num != 6:
cell.alignment = center_alignment
cell.border = thin_border
if col_num == 4:
try:
total_amount += float(value)
except ValueError:
pass
item = row[1]
amount = row[2]
total_amount += amount
if item in item_amounts:
item_amounts[item] += amount
else:
item_amounts[item] = amount
total_row = sheet.max_row + 1
sheet.cell(row=total_row, column=1, value="Total Amount")
total_amount_cell = sheet.cell(row=total_row, column=2, value=total_amount)
for column_cells in sheet.iter_cols(min_row=2, max_row=sheet.max_row):
length = max(len(str(cell.value)) for cell in column_cells if not isinstance(cell, openpyxl.cell.MergedCell))
sheet.column_dimensions[column_cells[0].column_letter].width = max(length, 10)
# Create a new sheet for analytics
analytics_sheet = workbook.create_sheet(title="Analytics")
analytics_sheet.page_setup.orientation = analytics_sheet.ORIENTATION_LANDSCAPE
# Add heading for the top 10 expenses
heading = analytics_sheet.cell(row=1, column=1, value="Top 10 Expenses")
heading.font = Font(bold=True, size=16)
heading.alignment = Alignment(horizontal='center', vertical='center')
analytics_sheet.merge_cells(start_row=1, start_column=1, end_row=1, end_column=8)
# Create pie chart data for top 10 highest spent expenses
# <-------- Pie chart Module Starts from here ----------->
if item_amounts:
sorted_items = sorted(item_amounts.items(), key=lambda x: x[1], reverse=True)
top_items = sorted_items[:10]
pie_data_start_row = 3 # Start the pie chart from the 2nd row
# Add the data for the pie chart and table in one go
item_heading = analytics_sheet.cell(row=pie_data_start_row, column=9, value="Item")
amount_heading = analytics_sheet.cell(row=pie_data_start_row, column=8, value="Amount")
count_heading = analytics_sheet.cell(row=pie_data_start_row, column=10, value="Count")
# Set headings to bold and center alignment
bold_font = Font(bold=True)
center_alignment = Alignment(horizontal='center', vertical='center')
item_counts = {item: sum(1 for row in results if row[1] == item) for item in item_amounts}
for idx, (item, amount) in enumerate(top_items, start=1):
amount_cell = analytics_sheet.cell(row=pie_data_start_row + idx, column=8, value=amount)
count_cell = analytics_sheet.cell(row=pie_data_start_row + idx, column=10, value=item_counts[item])
# Apply border to the headings
item_heading.border = thin_border
amount_heading.border = thin_border
count_heading.border = thin_border
# Add pie chart
pie_chart = PieChart()
labels = Reference(analytics_sheet, min_col=9, min_row=pie_data_start_row + 1, max_row=pie_data_start_row + len(top_items))
# Calculate total amount from the "Expenses" sheet
total_amount_expenses_sheet = sum(
cell.value for column in sheet.iter_cols(min_col=2, min_row=3, max_col=2, max_row=sheet.max_row)
for cell in column if cell.value is not None
)
# Update the percentages based on the total amount from the "Expenses" sheet
combined_labels = []
for idx, (item, amount) in enumerate(top_items, start=1):
percentage = (amount / total_amount_expenses_sheet) * 100 * 2
combined_label = f"{item} ({percentage:.2f}%)"
combined_labels.append(combined_label)
combined_label_cell = analytics_sheet.cell(row=pie_data_start_row + idx, column=9, value=combined_label)
combined_label_cell.border = thin_border
# Add pie chart
pie_chart = PieChart()
labels = Reference(analytics_sheet, min_col=9, min_row=pie_data_start_row + 1, max_row=pie_data_start_row + len(top_items))
data = Reference(analytics_sheet, min_col=8, min_row=pie_data_start_row + 1, max_row=pie_data_start_row + len(top_items))
pie_chart.add_data(data, titles_from_data=False)
pie_chart.set_categories(labels)
pie_chart.style = 10
# Configure data labels to show item names and percentages
data_labels = DataLabelList()
data_labels.showCatName = True
data_labels.showVal = False
data_labels.showPercent = False # Turn off the default percentage
data_labels.dLblPos = 'outEnd' # Labels outside the pie chart
data_labels.showLeaderLines = False # Enable leader lines
pie_chart.dataLabels = data_labels
# Disable the legend
pie_chart.legend = None
# Place the pie chart starting at the 2nd row, middle of column A
pie_chart_anchor = f"A3"
analytics_sheet.add_chart(pie_chart, pie_chart_anchor)
excel_file_path = f".\\Exports\\{file_name}"
workbook.save(excel_file_path)
self.statusBar.showMessage(f"Export to Excel completed. File saved as {excel_file_path}", 3000)
webbrowser.open(excel_file_path)
else:
QtWidgets.QMessageBox.information(self, "Export to Excel", "No data to export.")
问题：
- 我需要删除饼图中的“Series1”标签。
- 在ie_chart.add_data（data，titles_from_data = False）中设置titles_from_data = False不会关闭“Series1”。
这是我要删除的带有“Series1”标签的饼图图像
以下是使用 openpyxl 从 Python 中的饼图中删除“Series 1”标签的方法：
问题在于，即使通过设置
titles_from_data=False
禁用了数据点标签，openpyxl 仍然默认包含图例。由于只有一个数据系列（因此命名为“Series 1”），该图例看起来就像一个不必要的标签。
需要做的是 完全禁用该图例 。以下是更新后的代码：
# ...（之前的代码）...
# 添加饼图
pie_chart = PieChart()
labels = Reference(analytics_sheet, min_col=9, min_row=pie_data_start_row + 1, max_row=pie_data_start_row + len(top_items))
data = Reference(analytics_sheet, min_col=8, min_row=pie_data_start_row + 1, max_row=pie_data_start_row + len(top_items))
pie_chart.add_data(data, titles_from_data=False)
pie_chart.set_categories(labels)
pie_chart.style = 10
# 配置数据标签以显示项目名称和百分比
data_labels = DataLabelList()
data_labels.showCatName = True
data_labels.showVal = False
data_labels.showPercent = False # 关闭默认百分比
data_labels.dLblPos = 'outEnd' # 标签在饼图外部
data_labels.showLeaderLines = False # 启用引导线
pie_chart.dataLabels = data_labels
# 禁用图例
pie_chart.legend = None
# 将饼图放置在从第二行开始的 A 列中间
pie_chart_anchor = f"A3"
analytics_sheet.add_chart(pie_chart, pie_chart_anchor)
# ...（之后的代码）...
通过将
pie_chart.legend = None
添加到的代码中，将完全禁用图例，从而有效地删除“Series 1”标签。这将为提供一个更清晰、更简洁的饼图，没有任何不必要的标签。