Excel 处理 (XLSX)

以编程方式创建和操作 Microsoft Excel 电子表格。

环境准备

安装

pip install openpyxl

基本用法

import openpyxl

# Create workbook
wb = openpyxl.Workbook()
ws = wb.active

# Add data
ws['A1'] = 'Hello'
ws['B1'] = 'World'

# Save
wb.save('spreadsheet.xlsx')

工作簿结构

工作表

import openpyxl

wb = openpyxl.Workbook()

# Create new sheet
ws = wb.create_sheet('Data')

# Access sheet
ws = wb['Data']

# List all sheets
print(wb.sheetnames)

单元格

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

# Set values
ws['A1'] = 'Name'
ws['B1'] = 'Value'

# Access values
name = ws['A1'].value
print(name)  # Output: Name

# Iterate over cells
for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=2):
    for cell in row:
        print(cell.value)

数据操作

写入数据

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

# Single cell
ws['A1'] = 'Hello'

# Multiple cells
data = [
    ['Name', 'Age', 'City'],
    ['John', 25, 'New York'],
    ['Jane', 30, 'London'],
    ['Bob', 35, 'Paris'],
]

for row in data:
    ws.append(row)

wb.save('data.xlsx')

读取数据

import openpyxl

wb = openpyxl.load_workbook('data.xlsx')
ws = wb.active

# Read specific cell
value = ws['A1'].value

# Read range
for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)

# Read all data
data = ws.values
for row in data:
    print(row)

格式设置

单元格格式

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

wb = openpyxl.Workbook()
ws = wb.active

# Font
ws['A1'].font = Font(
    name='Arial',
    size=12,
    bold=True,
    color='FF0000'
)

# Fill
ws['A1'].fill = PatternFill(
    start_color='FFFF00',
    end_color='FFFF00',
    fill_type='solid'
)

# Alignment
ws['A1'].alignment = Alignment(
    horizontal='center',
    vertical='center'
)

# Border
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
ws['A1'].border = thin_border

列宽

# Set column width
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 15

# Set row height
ws.row_dimensions[1].height = 30

图表

创建图表

import openpyxl
from openpyxl.chart import BarChart, Reference

wb = openpyxl.Workbook()
ws = wb.active

# Add data
data = [
    ['Product', 'Q1', 'Q2', 'Q3', 'Q4'],
    ['Widget A', 100, 150, 200, 250],
    ['Widget B', 200, 180, 220, 280],
    ['Widget C', 150, 160, 190, 210],
]

for row in data:
    ws.append(row)

# Create chart
chart = BarChart()
chart.title = "Sales by Quarter"
chart.y_axis.title = "Sales"
chart.x_axis.title = "Product"

# Reference data
data = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)

chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

ws.add_chart(chart, "A8")

公式

使用公式

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

# Add data
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = 30

# Add formula
ws['A4'] = '=SUM(A1:A3)'

# Other formulas
ws['B1'] = '=AVERAGE(A1:A3)'
ws['B2'] = '=MAX(A1:A3)'
ws['B3'] = '=MIN(A1:A3)'

数据验证

添加验证

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

wb = openpyxl.Workbook()
ws = wb.active

# Create validation
dv = DataValidation(
    type="list",
    formula1='"Option1,Option2,Option3"',
    allow_blank=True
)

# Add validation to cells
ws.add_data_validation(dv)
dv.add('A1:A10')

最佳实践

• 使用有意义的工作表名称
• 清晰地设置表头格式
• 添加数据验证
• 使用公式进行计算
• 考虑文件大小优化
• 在不同 Excel 版本中测试
• 提供清晰的文档