Let's resolve your line chart display discrepancy step by step! The core issue is that your profit margin (a percentage-based metric) is being plotted on the same Y-axis as revenue/net profit (large absolute values), which compresses the line to an unreadable flat state. Here's how to adjust your code to match the web page's chart:

Key Adjustments Needed #

• Use a Secondary Y-Axis for the profit margin line to accommodate its smaller value range
• Properly handle non-numeric placeholder values (///) to avoid rendering issues
• Refine line and data label styling to match the web page's appearance
• Configure axis formatting for clear percentage display on the secondary axis

Modified Code with Explanations #

from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText, Paragraph, ParagraphProperties
from openpyxl.drawing.text import CharacterProperties

# Assuming `cp` is your pre-defined CharacterProperties (adjust as needed)
cp = CharacterProperties(...)

# 1. Set up the primary bar chart (revenue/net profit)
chart1 = BarChart()
chart1.title = "Annual Financial Highlight"
# Data for bar chart (columns 36-37: revenue/net profit)
chart1Data = Reference(lws, min_col=36, min_row=74, max_row=84, max_col=37)
chart1.add_data(chart1Data, titles_from_data=True)

# Configure data labels for bars
s1 = chart1.series[0]
s2 = chart1.series[1]
for series in [s1, s2]:
    series.dLbls = DataLabelList()
    series.dLbls.showVal = True
    series.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])

# 2. Set up the secondary line chart (profit margin)
chart2 = LineChart()
# Data for line chart (column 35: profit margin)
chart2Data = Reference(lws, min_col=35, min_row=74, max_row=84, max_col=35)
chart2.add_data(chart2Data, titles_from_data=True)

# Configure line series for secondary axis
s3 = chart2.series[0]
s3.dLbls = DataLabelList()
s3.dLbls.showVal = True
s3.smooth = True
# Move this series to the secondary Y-axis to separate value ranges
s3.y_axis = chart2.y_axis
# Set percentage axis bounds (adjust based on your actual data range)
chart2.y_axis.scaling.min = 0
chart2.y_axis.scaling.max = 100
# Format axis to show percentage values
chart2.y_axis.number_format = "0.0%"
# Style line and markers to match web page
s3.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
s3.graphicalProperties.line.width = 10000  # Adjust line thickness (1pt = 10000 EMUs)
s3.marker.symbol = "circle"  # Add visible marker points
s3.marker.size = 5

# 3. Combine charts with secondary axis enabled
chart1 += chart2
# Set X-axis categories (quarters)
quarterChart = Reference(lws, min_col=34, min_row=75, max_row=84)
chart1.set_categories(quarterChart)

# 4. Final chart styling
chart1.shape = 4
chart1.height = 10
chart1.width = 20
# Style axis text to match your existing formatting
chart1.x_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
chart1.y_axis.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=cp), endParaRPr=cp)])
chart1.x_axis.scaling.orientation = "maxMin"
chart1.y_axis.scaling.orientation = "minMax"

# Add chart to worksheet
lws.add_chart(chart1, "X52")

Additional Notes #

• Handling /// Values: Convert cells with /// to None (blank) before charting—openpyxl will skip blank cells, preventing rendering errors from non-numeric values.
• Exact Style Matching: If the web page uses specific colors for the line/markers, add s3.graphicalProperties.line.solidFill = "HEX_COLOR_CODE" and s3.marker.graphicalProperties.solidFill = "HEX_COLOR_CODE" to replicate the look.
• Axis Range Tuning: Adjust chart2.y_axis.scaling.min and max based on your actual profit margin range to remove unnecessary empty space in the line chart.

内容的提问来源于stack exchange，提问作者Ilham Learning