pythonchartsopenpyxlexcel-charts

How to Remove the "Series1" Label from a Pie Chart in "openpyxl" in Python?

I'm using openpyxl to create an Excel file that includes a pie chart. However, I can't seem to remove the default "Series1" label from the chart. I have included the entire export_to_excel function for better context, but the issue specifically lies in the analytics sheet where I create the pie chart.

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.")

The issue:

Here's an image of the pie chart with the "Series1" label that I want to removeenter image description here

Solution

  • When I encounter such problems, I do the following:

    1. Minimizing the code to the necessary to avoid scrolling through a wall of code. In this case, only code which creates a pie chart.

    2. Reading documentation. I this case, openpyxl.chart.label.DataLabel.

    In documentation we find 7 show...-properties:

    Of these, showSerName sounds exactly as the setting you not want. They are booleans, so set False.

    Now it comes to the experience using different Excel versions. Microsoft often changes the defaults of boolean settings from version to version. For example until Excel 2007 the 7 show...-properties defaulted to False if not set. Then this changed to True if not set. And current Microsoft 365 again defaults to False if not set. Your Excel version seems to default the 7 show...-properties to True if not set.

    To avoid these version differences, I decided to always set all 7 show...-properties. I do this in alphabetic oder to not forget one.

    Complete example:

    from openpyxl import Workbook

from openpyxl.chart import (
    PieChart,
    Reference
)
from openpyxl.chart.label import DataLabelList

data = [
    ['Pie', 'Sold'],
    ['="Apple (" & TEXT(B2/SUM(B2:B5),"0%") & ")"', 50],
    ['="Cherry (" & TEXT(B3/SUM(B2:B5),"0%") & ")"', 30],
    ['="Pumpkin (" & TEXT(B4/SUM(B2:B5),"0%") & ")"', 10],
    ['="Chocolate (" & TEXT(B5/SUM(B2:B5),"0%") & ")"', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie_chart = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie_chart.add_data(data, titles_from_data=True)
pie_chart.set_categories(labels)
pie_chart.title = "Pies sold by category"

# Configure data labels to show item names and percentages
data_labels = DataLabelList()

data_labels.showBubbleSize = False # Disable show bubble size
data_labels.showCatName = True # Enable show leader lines
data_labels.showLeaderLines = True  # Enable show leader lines
data_labels.showLegendKey = True # Enable show legend key
data_labels.showPercent = False  # Disable show percent
data_labels.showSerName = False # Disable show series name
data_labels.showVal = False # Disable show series name

data_labels.dLblPos = 'outEnd'  # Labels outside the pie chart

pie_chart.dataLabels = data_labels

# Disable the legend
pie_chart.legend = None

ws.add_chart(pie_chart, "D1")

wb.save("pie.xlsx")

    Should result in:

    enter image description here