如何用Python为MySQL导出的CSV格式Excel文件添加封面？

Got it, let's break this down clearly. First off—CSV files are plain text, so they don't support multiple sheets or formatting like a cover page. When Excel opens a CSV, it just loads it as a single, unformatted sheet. So your first step is to convert that CSV (or your MySQL data directly) into a proper .xlsx Excel file, then add a dedicated cover sheet to it.

Here's a step-by-step implementation using pandas (for handling data/CSV) and openpyxl (for Excel sheet manipulation and formatting)—these are the go-to tools for this job.

Step 1: Export MySQL Data to an Excel File (or Convert Existing CSV) #

If you're already exporting to CSV, you can skip the CSV step entirely and write your MySQL query results directly to an Excel file. If you have a CSV ready, just read it into a DataFrame first.

import pandas as pd
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

# Option 1: Read existing CSV into DataFrame
df = pd.read_csv("your_exported_data.csv")

# Option 2: Directly fetch from MySQL (replace with your DB details)
# import mysql.connector
# conn = mysql.connector.connect(host="your_host", user="user", password="pass", database="db")
# df = pd.read_sql_query("SELECT * FROM your_table", conn)
# conn.close()

# Write DataFrame to Excel (creates a sheet named '数据' by default)
excel_file = "data_with_cover.xlsx"
df.to_excel(excel_file, sheet_name="数据", index=False)

Step 2: Add & Format the Cover Sheet #

Now we'll use openpyxl to open the Excel file, insert a cover sheet at the front, and populate it with your custom info (date, file name, descriptions, etc.).

# Load the existing Excel file
wb = load_workbook(excel_file)

# Insert a new sheet at the very start (index 0) as the cover
cover_sheet = wb.create_sheet(title="封面", index=0)

# Set up formatting styles
title_font = Font(name="微软雅黑", size=18, bold=True)
subtitle_font = Font(name="微软雅黑", size=12, italic=True)
normal_font = Font(name="微软雅黑", size=11)
center_align = Alignment(horizontal="center", vertical="center")

# Merge cells for the main title (A1 to D5)
cover_sheet.merge_cells("A1:D5")
title_cell = cover_sheet["A1"]
title_cell.value = "业务数据报表"
title_cell.font = title_font
title_cell.alignment = center_align

# Add date (auto-generate current date, or use your custom date)
cover_sheet.merge_cells("A6:D6")
date_cell = cover_sheet["A6"]
date_cell.value = f"生成日期: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
date_cell.font = subtitle_font
date_cell.alignment = center_align

# Add additional info (e.g., report name, owner)
cover_sheet["A8"] = "报表名称:"
cover_sheet["B8"] = "月度销售数据"
cover_sheet["A9"] = "负责人:"
cover_sheet["B9"] = "张三"

# Format the info cells
for row in cover_sheet.iter_rows(min_row=8, max_row=9, max_col=2):
    for cell in row:
        cell.font = normal_font

# Adjust column widths for better visibility
for col in range(1, 5):
    cover_sheet.column_dimensions[get_column_letter(col)].width = 20

# Adjust row heights
cover_sheet.row_dimensions[1].height = 40
for row in range(2, 6):
    cover_sheet.row_dimensions[row].height = 20

# Save the final file
wb.save(excel_file)

Key Notes: #

• Why not just edit the CSV? CSV is a flat, plain-text format—there's no concept of multiple sheets or formatting. Excel just renders it as a single sheet, so you can't add a cover without converting to .xlsx.
• Customization: Tweak the merged cells, fonts, alignment, and content to match your needs. You can even add logos by using cover_sheet.add_image() (check openpyxl's built-in docs for that).
• Dependencies: Make sure you install the required packages first:

  pip install pandas openpyxl mysql-connector-python  # Skip mysql-connector if you don't need direct DB access
  

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