Excel 表格 (xlsx)

Excel 表格（xlsx）

概述

xlsx 是处理 Excel 电子表格的技能。它的核心是：创建、读取、编辑和分析 Excel 文件。

触发条件

当用户提到以下内容时使用：

• Excel
• XLSX 文件
• 电子表格
• 数据表格

常用操作

1. 创建工作簿

import openpyxl

wb = openpyxl.Workbook()
ws = wb.active
ws['A1'] = 'Hello'
wb.save('workbook.xlsx')

2. 添加数据

import openpyxl

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

# 添加表头
ws['A1'] = '姓名'
ws['B1'] = '年龄'
ws['C1'] = '城市'

# 添加数据
ws['A2'] = '张三'
ws['B2'] = '25'
ws['C2'] = '北京'

# 批量添加
data = [
    ['李四', '30', '上海'],
    ['王五', '28', '广州']
]
for row in data:
    ws.append(row)

wb.save('data.xlsx')

3. 格式化

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

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

# 设置字体
ws['A1'].font = Font(name='Arial', size=12, bold=True)

# 设置填充色
ws['A1'].fill = PatternFill(
    start_color='FF0000',
    end_color='FF0000',
    fill_type='solid'
)

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

# 设置列宽
ws.column_dimensions['A'].width = 15

# 设置行高
ws.row_dimensions[1].height = 25

wb.save('formatted.xlsx')

4. 添加公式

import openpyxl

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

ws['A1'] = '产品'
ws['B1'] = '单价'
ws['C1'] = '数量'
ws['D1'] = '小计'

ws['A2'] = '苹果'
ws['B2'] = 5
ws['C2'] = 10
ws['D2'] = '=B2*C2'

ws['A3'] = '香蕉'
ws['B3'] = 3
ws['C3'] = 8
ws['D3'] = '=B3*C3'

# 求和
ws['D4'] = '=SUM(D2:D3)'

wb.save('formula.xlsx')

5. 合并单元格

import openpyxl
from openpyxl.styles import Alignment

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

# 合并单元格
ws.merge_cells('A1:D1')

# 设置合并后的内容
ws['A1'] = '标题'
ws['A1'].alignment = Alignment(horizontal='center')

wb.save('merged.xlsx')

6. 创建图表

import openpyxl
from openpyxl.chart import BarChart, Reference

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

# 添加数据
ws['A1'] = '月份'
ws['B1'] = '销售额'
ws['A2'] = '1月'
ws['B2'] = 100
ws['A3'] = '2月'
ws['B3'] = 150
ws['A4'] = '3月'
ws['B4'] = 120

# 创建图表
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, 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, 'E2')

wb.save('chart.xlsx')

场景示例

场景 1：销售报表

需求：创建月度销售报表

步骤：
1. 添加表头
2. 填充销售数据
3. 添加汇总公式
4. 创建图表
5. 格式化样式

场景 2：数据分析

需求：分析数据并可视化

步骤：
1. 读取数据
2. 计算统计值
3. 创建图表
4. 生成报告

速查卡片

创建

Workbook() + save()

数据

ws[‘A1’] = value ws.append()

格式化

Font, PatternFill, Alignment

公式

=SUM(), =AVERAGE()

图表

BarChart + Reference()

---

查看源文件: GitHub原始文件