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:
- I need to remove the "Series1" label in the pie chart.
- Setting titles_from_data=False in pie_chart.add_data(data, titles_from_data=False) is not turning off the "Series1".
Here's an image of the pie chart with the "Series1" label that I want to remove
Solution
When I encounter such problems, I do the following:
Minimizing the code to the necessary to avoid scrolling through a wall of code. In this case, only code which creates a pie chart.
Reading documentation. I this case, openpyxl.chart.label.DataLabel.
In documentation we find 7 show...-properties:
- showBubbleSize
- showCatName
- showLeaderLines
- showLegendKey
- showPercent
- showSerName
- showVal
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:
- Python method chaining in functional programming style
- flask-jwt-extended: Fake Authorization Header during testing (pytest)
- For loop through the list unless empty?
- Polars make all groups the same size
- Is there a way to specify a default base-template for all templates in django?
- How to tackle time limit exceeded error in leetcode
- Is pd.get_dummies() updated in newer versions of Pandas making it default to Booleans (True/False) instead of (0/1)?
- What's the function like sum() but for multiplication? product()?
- How to type hint a dynamically-created dataclass
- Issue with pulling the data with EIA API with Python
- 403 Forbidden Error when scraping a site, user-agents already used and updated. Any ideas?
- Fullstack web-hosting services
- How to handle an AnalysisException on Spark SQL?
- Python requests is slow and takes very long to complete HTTP or HTTPS request
- Is there a way to modify an element in a Numpy array based on the value of other elements?
- Tkinter grid manager height/width nonconsistent
- Sql Alchemy Insert Statement failing to insert, but no error
- How can I create a Polars struct while eval-ing a list?
- Excel using win32com and python
- django: on pypy, psyco, unladen swallow or cpython, which one is the fastest?
- How convert a list into multiple columns and a dataframe?
- Name not defined in type annotation
- Static type checkers and Language Servers not recognizing attributes of objects that are subclasses
- How do I get multiple OID values in PySNMP?
- how to log a file in Django
- Is there a simple and efficient way to evaluate Elementary Symmetric Polynomials in Python?
- Iterating over two lists one after another
- Python -i flag for production
- What is PyCompilerFlags in Python C API?
- How to make Python check whether a variable is a number or letter