如何在Python的openpyxl中移除饼图的默认"Series1"标签

我用Python的openpyxl库生成带饼图的Excel文件，但饼图上默认的"Series1"标签怎么都删不掉。已经设置了pie_chart.add_data(data, titles_from_data=False)，但标签还是存在。以下是完整的export_to_excel函数代码，问题出在创建饼图的Analytics工作表部分：

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"标签；
• 已设置pie_chart.add_data(data, titles_from_data=False)但该标签仍未消失。

饼图问题截图：

问题核心 #

你的代码有两个关键问题：

1. 饼图创建逻辑被放到了遍历top_items的循环里，导致每次循环都新建一个饼图并叠加到工作表上；
2. 仅设置titles_from_data=False不够，需要显式清空饼图系列的默认名称才能去掉"Series1"。

修改代码 #

把饼图创建代码移到循环外面，并添加清空系列名称的逻辑：

# Update the percentages based on the total amount from the "Expenses" sheet
combined_labels = []
for idx, (item, amount) in enumerate(top_items, start=1):
    # 修正百分比计算，去掉多余的*2（如果是有意设置可保留）
    percentage = (amount / total_amount_expenses_sheet) * 100
    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

# 饼图创建逻辑移到循环外，只生成一次
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

# 显式清空系列名称，移除Series1标签
if pie_chart.series:
    pie_chart.series[0].title = ""

# Configure data labels to show item names and percentages
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

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

额外说明 #

• 原代码中百分比计算多乘了*2，已修正，若为业务需求可自行加回；
• 循环内创建饼图会导致工作表上叠加多个饼图，移到循环外后仅生成一个正确的饼图。

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