Python实现CSV数据导入Excel模板并生成带图表的新文件

Alright, let's solve this problem properly. The main goals here are to replace the data in the 'data' sheet of your template Excel with the CSV data, keep the chart in the 'graph' sheet working with the new data, and make sure nothing gets lost. Here's a solid approach using Python's openpyxl (great for handling Excel files with charts) and pandas (super easy for reading CSVs):

1. Install Required Libraries #

First, make sure you have the necessary packages installed. Run this in your terminal:

pip install openpyxl pandas

2. Full Working Code #

Here's the complete script with explanations for each part:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.utils import get_column_letter

# Step 1: Read the input CSV file
# We're assuming the first row of input.csv is a text header (since it has no numbers)
# If your CSV has no header (first row is data), use header=None instead
df = pd.read_csv('input.csv')

# Step 2: Load the template Excel file (preserves charts and existing formatting)
# data_only=False ensures we keep formulas and chart references intact
wb = load_workbook('template.xlsx', data_only=False)
ws_data = wb['data']

# Step 3: Clear old data from the 'data' sheet
# Deleting all rows is cleaner than just clearing cell values
ws_data.delete_rows(1, ws_data.max_row)

# Step 4: Write the CSV data into the 'data' sheet
# dataframe_to_rows converts our pandas DataFrame into Excel-friendly rows
# index=False skips the pandas index column, header=True includes the CSV header
for row in dataframe_to_rows(df, index=False, header=True):
    ws_data.append(row)

# Step 5: Update the chart in the 'graph' sheet to use the new data
ws_graph = wb['graph']
# Get the full range of our new data in the 'data' sheet
max_col = ws_data.max_column
max_row = ws_data.max_row
full_data_range = f"data!$A$1:${get_column_letter(max_col)}${max_row}"
# Adjust this if your chart's category labels are in a different column (not A)
category_range = f"data!$A$2:${get_column_letter(1)}${max_row}"

# Update every series in every chart on the 'graph' sheet
for chart in ws_graph._charts:
    for series in chart.series:
        # Update the data values the series uses
        series.values = full_data_range
        # Update the category labels (like X-axis labels)
        series.categories = category_range

# Step 6: Save the modified file (never overwrite your original template!)
wb.save('updated_output.xlsx')

3. Customization Tips #

• CSV Header Adjustment: If your input.csv doesn't have a header (first row is actual data with no numbers), change the CSV reading line to df = pd.read_csv('input.csv', header=None) and set header=False in the dataframe_to_rows call.
• Chart Tweaks: If your chart uses multiple series from specific columns (not the entire data range), you'll need to adjust the series.values line for each series. For example, if a series uses column B, set it to f"data!$B$2:${get_column_letter(2)}${max_row}".
• Preserve Cell Formatting: If you want to keep the original cell styles (colors, fonts, etc.) in the 'data' sheet, replace the row deletion step with this code to clear only cell values:

  for row in ws_data.iter_rows():
      for cell in row:
          cell.value = None
  

  Then append the new data as before—this keeps your original formatting intact.
• Safe Saving: Always save to a new file (like updated_output.xlsx) instead of overwriting template.xlsx to avoid losing your original template.

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