如何用Python对PowerPoint图表数据排序？基于Openpyxl取数场景

Absolutely, you can handle this with openpyxl—whether you want to sort the source data before linking it to charts or adjust things after loading the workbook. Let’s break down practical approaches, including handling multi-series charts and applying this to every chart in your document.

1. Pre-Import: Sort Source Data First (Cleanest Approach) #

The most reliable way to keep chart data aligned (even for multi-series) is to sort the source cell ranges that the chart references before the chart is created (or before you load the workbook). Since charts dynamically link to cell data, sorting the source range will automatically update the chart without breaking relationships between labels and series values.

For multi-series charts, you need to sort all related columns together (e.g., labels, Series 1 values, Series 2 values) based on one primary column (e.g., sort by Series 1 descending). This ensures labels and all series values stay paired correctly.

Example Code for Pre-Import Sorting #

from openpyxl import Workbook

# Create a sample workbook (or load an existing one with load_workbook)
wb = Workbook()
ws = wb.active

# Sample multi-series data: A=Labels, B=Sales, C=Profit
data = [
    ["Product", "Sales", "Profit"],
    ["Laptop", 1200, 300],
    ["Phone", 800, 200],
    ["Tablet", 1500, 400]
]

for row in data:
    ws.append(row)

# Sort by Sales (column B, index 2) in descending order, including all related columns
ws.sort(
    ref="A2:C4",  # Range includes labels and both series
    sortFields=[
        {"col": 2, "descending": True}  # Sort column 2 (B) from largest to smallest
    ]
)

# Now create a chart linked to this sorted data (chart will inherit the sorted order)
# ... (chart creation code here)

wb.save("sorted_source_data.xlsx")

2. Post-Import: Sort Data After Loading the Workbook #

If you’re working with an existing workbook with already-created charts, you can still sort the source data linked to each chart. The key is to:

1. Identify which cell ranges each chart’s series reference
2. Sort those ranges as a single block (to keep labels and series paired)
3. Save the workbook—charts will automatically update to reflect the sorted data

Example Code for Post-Import Sorting (All Charts) #

This script will iterate through every chart in every worksheet, find its linked data range, and sort it by the first series column in descending order:

from openpyxl import load_workbook

def sort_chart_source_data(chart, sort_col_index, ascending=False):
    """
    Sort the source data range linked to a chart
    :param chart: Target openpyxl Chart object
    :param sort_col_index: Column index (1-based) to sort by
    :param ascending: True for ascending, False for descending
    """
    # Get the first series to extract base data range and worksheet
    first_series = chart.series[0]
    sheet_name, data_range = first_series.values.split("!")
    ws = chart.parent.parent[sheet_name]  # Get the worksheet containing the chart
    
    # Parse start/end rows from the data range (e.g., "B2:B4" -> start_row=2, end_row=4)
    start_cell, end_cell = data_range.split(":")
    start_row = int(''.join(filter(str.isdigit, start_cell)))
    end_row = int(''.join(filter(str.isdigit, end_cell)))
    
    # Include the category (label) column if the chart uses it
    sort_start_col = ""
    if hasattr(first_series, "categories") and first_series.categories:
        # Extract category column (e.g., "A2:A4" -> "A")
        sort_start_col = first_series.categories.split("!")[1][0]
    else:
        # Fallback to the first column of the data range
        sort_start_col = start_cell[0]
    
    # Find the last column used by any series in the chart
    last_col = max(
        [series.values.split("!")[1][0] for series in chart.series],
        key=lambda col: ord(col)
    )
    
    # Define the full range to sort (labels + all series columns)
    sort_range = f"{sort_start_col}{start_row}:{last_col}{end_row}"
    
    # Execute the sort
    ws.sort(
        ref=sort_range,
        sortFields=[{"col": sort_col_index, "descending": not ascending}]
    )

# Load your existing workbook
wb = load_workbook("your_existing_file.xlsx")

# Iterate through every worksheet and every chart
for ws in wb.worksheets:
    # Access charts stored in the worksheet
    for chart in ws._charts:
        # Sort by the second column (e.g., Sales column, 1-based index) descending
        sort_chart_source_data(chart, sort_col_index=2, ascending=False)

# Save the updated workbook
wb.save("charts_sorted.xlsx")

Key Notes for Multi-Series Charts #

• Never sort a single series column alone: This will break the alignment between labels and other series values. Always include all related columns (labels + all series) in your sort range.
• Adjust sort_col_index as needed: Set this to the 1-based index of the column you want to sort by (e.g., 3 for column C if that’s your primary series).
• Handle non-contiguous ranges: If your chart uses non-contiguous data ranges, you’ll need to adjust the code to identify shared row ranges and sort those rows across all relevant columns.

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