Title: Issue with openpyxl version 3.1.4: X and Y axis labels not displaying in Excel chart
Description:
I encountered an issue with the openpyxl
library where values on the X and Y axes are not displayed correctly in an Excel chart. This issue occurs with openpyxl
version 3.1.4 but works correctly with version 3.1.2.
Environment:
openpyxl
version: 3.1.4 (problematic), 3.1.2 (working)- Python version: 3.11.9
- Operating System: [Windows 10 and 11]
Steps to Reproduce:
- Create a DataFrame with the following data:
<code>import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.utils.dataframe import dataframe_to_rows
data = {
'Year': ['2015', '2016', '2017', '2018', '2019', '2020', '2021', '2022', '2023'],
'serie_1': [1, 2, 3, 4, 5, 6, 7, 8, 9],
'serie_2': [2, 3, 4, 5, 6, 7, 8, 9, 10],
'serie_3': [3, 4, 5, 6, 7, 8, 9, 10, 11]
}
df = pd.DataFrame(data)
# Create a workbook and select the active worksheet
wb = Workbook()
ws = wb.active
# Write the DataFrame to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
# Create a LineChart
chart = LineChart()
chart.title = "Revenue Over Years"
chart.style = 10
chart.x_axis.title = "Year"
chart.y_axis.title = "Revenue"
# Define categories (X axis values) using the first column (Year)
categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
# Add the series to the chart
for i in range(2, ws.max_column + 1):
values = Reference(ws, min_col=i, min_row=2, max_row=ws.max_row)
series = Series(values, title=ws.cell(row=1, column=i).value)
chart.series.append(series)
chart.set_categories(categories)
# Set the size of the chart in centimeters
chart.width = 15 # Width in cm
chart.height = 10 # Height in cm
# Ensure axis labels and gridlines are displayed
chart.x_axis.majorGridlines = ChartLines()
chart.y_axis.majorGridlines = ChartLines()
chart.x_axis.tickLblPos = 'nextTo'
chart.y_axis.tickLblPos = 'nextTo'
# Position the chart on the sheet
ws.add_chart(chart, "E5") # Position the chart starting from cell E5
# Save the Excel file
wb.save("test_graph.xlsx")
I would expect that the problem will be solved in a future version. I use the older one for the time being.
with version 3.1.4 of openpyxl only the label are displayed but not values (2015,2016,... on x and 1..11 on y)
I would help the other onfronted to it to save some time. Just try the version 3.1.2 to see if it solve your problem.
</code>
<code>import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.utils.dataframe import dataframe_to_rows
data = {
'Year': ['2015', '2016', '2017', '2018', '2019', '2020', '2021', '2022', '2023'],
'serie_1': [1, 2, 3, 4, 5, 6, 7, 8, 9],
'serie_2': [2, 3, 4, 5, 6, 7, 8, 9, 10],
'serie_3': [3, 4, 5, 6, 7, 8, 9, 10, 11]
}
df = pd.DataFrame(data)
# Create a workbook and select the active worksheet
wb = Workbook()
ws = wb.active
# Write the DataFrame to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
# Create a LineChart
chart = LineChart()
chart.title = "Revenue Over Years"
chart.style = 10
chart.x_axis.title = "Year"
chart.y_axis.title = "Revenue"
# Define categories (X axis values) using the first column (Year)
categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
# Add the series to the chart
for i in range(2, ws.max_column + 1):
values = Reference(ws, min_col=i, min_row=2, max_row=ws.max_row)
series = Series(values, title=ws.cell(row=1, column=i).value)
chart.series.append(series)
chart.set_categories(categories)
# Set the size of the chart in centimeters
chart.width = 15 # Width in cm
chart.height = 10 # Height in cm
# Ensure axis labels and gridlines are displayed
chart.x_axis.majorGridlines = ChartLines()
chart.y_axis.majorGridlines = ChartLines()
chart.x_axis.tickLblPos = 'nextTo'
chart.y_axis.tickLblPos = 'nextTo'
# Position the chart on the sheet
ws.add_chart(chart, "E5") # Position the chart starting from cell E5
# Save the Excel file
wb.save("test_graph.xlsx")
I would expect that the problem will be solved in a future version. I use the older one for the time being.
with version 3.1.4 of openpyxl only the label are displayed but not values (2015,2016,... on x and 1..11 on y)
I would help the other onfronted to it to save some time. Just try the version 3.1.2 to see if it solve your problem.
</code>
import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series
from openpyxl.utils.dataframe import dataframe_to_rows
data = {
'Year': ['2015', '2016', '2017', '2018', '2019', '2020', '2021', '2022', '2023'],
'serie_1': [1, 2, 3, 4, 5, 6, 7, 8, 9],
'serie_2': [2, 3, 4, 5, 6, 7, 8, 9, 10],
'serie_3': [3, 4, 5, 6, 7, 8, 9, 10, 11]
}
df = pd.DataFrame(data)
# Create a workbook and select the active worksheet
wb = Workbook()
ws = wb.active
# Write the DataFrame to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
# Create a LineChart
chart = LineChart()
chart.title = "Revenue Over Years"
chart.style = 10
chart.x_axis.title = "Year"
chart.y_axis.title = "Revenue"
# Define categories (X axis values) using the first column (Year)
categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
# Add the series to the chart
for i in range(2, ws.max_column + 1):
values = Reference(ws, min_col=i, min_row=2, max_row=ws.max_row)
series = Series(values, title=ws.cell(row=1, column=i).value)
chart.series.append(series)
chart.set_categories(categories)
# Set the size of the chart in centimeters
chart.width = 15 # Width in cm
chart.height = 10 # Height in cm
# Ensure axis labels and gridlines are displayed
chart.x_axis.majorGridlines = ChartLines()
chart.y_axis.majorGridlines = ChartLines()
chart.x_axis.tickLblPos = 'nextTo'
chart.y_axis.tickLblPos = 'nextTo'
# Position the chart on the sheet
ws.add_chart(chart, "E5") # Position the chart starting from cell E5
# Save the Excel file
wb.save("test_graph.xlsx")
I would expect that the problem will be solved in a future version. I use the older one for the time being.
with version 3.1.4 of openpyxl only the label are displayed but not values (2015,2016,... on x and 1..11 on y)
I would help the other onfronted to it to save some time. Just try the version 3.1.2 to see if it solve your problem.
New contributor
user26456091 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.