tools-only Excel Report Generator - API Reference
详细的 pandas 和 openpyxl API 参考指南
目录
pandas Excel 操作
读取 Excel
import pandas as pd
# 基本读取
df = pd.read_excel('file.xlsx')
# 指定工作表
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')
# 读取多个工作表
excel_file = pd.ExcelFile('file.xlsx')
df1 = excel_file.parse('Sheet1')
df2 = excel_file.parse('Sheet2')
# 读取所有工作表到字典
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)
# 高级参数
df = pd.read_excel(
'file.xlsx',
sheet_name='Sheet1',
header=0, # 标题行
index_col=0, # 索引列
usecols='A:D', # 只读取特定列
skiprows=2, # 跳过前 2 行
nrows=100, # 只读取 100 行
dtype={'A': str}, # 指定数据类型
na_values=['N/A', ''], # 缺失值标识
converters={'B': lambda x: x.strip()} # 自定义转换
)
写入 Excel
# 基本写入
df.to_excel('output.xlsx', index=False)
# 使用 ExcelWriter（推荐）
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
df1.to_excel(writer, sheet_name='Sheet1', index=False)
df2.to_excel(writer, sheet_name='Sheet2', index=False)
# 追加到现有文件
with pd.ExcelWriter('existing.xlsx', engine='openpyxl', mode='a') as writer:
df.to_excel(writer, sheet_name='NewSheet')
# 高级参数
df.to_excel(
'output.xlsx',
sheet_name='Data',
index=False, # 不写入索引
header=True, # 写入列名
startrow=0, # 起始行
startcol=0, # 起始列
na_rep='', # 缺失值表示
float_format='%.2f', # 浮点数格式
columns=['A', 'B'], # 只写入特定列
freeze_panes=(1, 0) # 冻结窗格
)
openpyxl 核心 API
工作簿操作
from openpyxl import Workbook, load_workbook
# 创建新工作簿
wb = Workbook()
wb = Workbook(write_only=True) # 只写模式（性能优化）
# 加载工作簿
wb = load_workbook('file.xlsx')
wb = load_workbook('file.xlsx', read_only=True) # 只读模式
wb = load_workbook('file.xlsx', data_only=True) # 只读取值，不读取公式
# 保存工作簿
wb.save('output.xlsx')
wb.save('output.xlsx')
wb.close() # 只读模式需要关闭
工作表操作
# 获取工作表
ws = wb.active # 活动工作表
ws = wb['SheetName'] # 按名称
ws = wb.worksheets[0] # 按索引
# 创建工作表
ws = wb.create_sheet('NewSheet') # 末尾添加
ws = wb.create_sheet('FirstSheet', 0) # 指定位置
ws = wb.copy_worksheet(wb['Sheet1']) # 复制工作表
# 工作表属性
ws.title = 'New Name' # 重命名
ws.sheet_properties.tabColor = 'FF0000' # 标签颜色
# 删除工作表
wb.remove(ws)
del wb['SheetName']
# 遍历工作表
for sheet in wb:
print(sheet.title)
单元格操作
# 读取单元格
value = ws['A1'].value
value = ws.cell(row=1, column=1).value
# 写入单元格
ws['A1'] = 'Hello'
ws['A1'] = 123
ws['A1'] = datetime.now()
ws.cell(row=1, column=1, value='Hello')
# 单元格范围
for row in ws['A1:C3']:
for cell in row:
print(cell.value)
# 按行遍历
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3):
for cell in row:
print(cell.value)
# 按列遍历
for col in ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=3):
for cell in col:
print(cell.value)
# 追加行
ws.append([1, 2, 3])
ws.append({'A': 1, 'B': 2, 'C': 3})
# 插入/删除
ws.insert_rows(1, 3) # 在第 1 行前插入 3 行
ws.insert_cols(1, 2) # 在第 1 列前插入 2 列
ws.delete_rows(5, 2) # 从第 5 行开始删除 2 行
ws.delete_cols(3, 1) # 删除第 3 列
# 合并/取消合并
ws.merge_cells('A1:D1')
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4)
ws.unmerge_cells('A1:D1')
样式和格式化
字体 (Font)
from openpyxl.styles import Font
# 基本字体
font = Font(
name='Arial', # 字体名称
size=12, # 字号
bold=True, # 粗体
italic=False, # 斜体
underline='single', # 下划线: single, double, singleAccounting, doubleAccounting
strike=False, # 删除线
color='FF0000', # 颜色（16进制）
vertAlign='baseline' # 垂直对齐: baseline, superscript, subscript
)
ws['A1'].font = font
填充 (PatternFill)
from openpyxl.styles import PatternFill
# 实心填充
fill = PatternFill(
start_color='FFFF00', # 开始颜色
end_color='FFFF00', # 结束颜色
fill_type='solid' # 填充类型
)
# 渐变填充
gradient_fill = PatternFill(
fill_type='lightGray', # 填充图案类型
start_color='FFFFFF',
end_color='000000'
)
ws['A1'].fill = fill
# 常用填充类型
# solid, darkGray, mediumGray, lightGray, gray125, gray0625
# darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis
# lightHorizontal, lightVertical, lightDown, lightUp, lightGrid, lightTrellis
边框 (Border)
from openpyxl.styles import Border, Side
# 定义边框样式
side = Side(
style='thin', # 线条样式
color='000000' # 颜色
)
# 创建边框
border = Border(
left=side,
right=side,
top=side,
bottom=side,
diagonal=side,
diagonal_direction=0 # 0: 无, 1: 向下, 2: 向上, 3: 双向
)
ws['A1'].border = border
# 线条样式选项
# thin, medium, thick, double, hair, dotted, dashed, dashDot, dashDotDot
# mediumDashed, mediumDashDot, mediumDashDotDot, slantDashDot
对齐 (Alignment)
from openpyxl.styles import Alignment
alignment = Alignment(
horizontal='center', # 水平对齐: left, center, right, fill, justify, centerContinuous, distributed
vertical='center', # 垂直对齐: top, center, bottom, justify, distributed
text_rotation=0, # 文本旋转: 0-180
wrap_text=True, # 自动换行
shrink_to_fit=False, # 缩小字体以适应
indent=0 # 缩进级别
)
ws['A1'].alignment = alignment
数字格式 (Number Format)
# 内置格式
ws['A1'].number_format = numbers.FORMAT_NUMBER # 常规数字
ws['A2'].number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1 # 千分位
ws['A3'].number_format = numbers.FORMAT_PERCENTAGE # 百分比
ws['A4'].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE # 美元货币
ws['A5'].number_format = numbers.FORMAT_DATE_XLSX14 # 日期
# 自定义格式
ws['B1'].number_format = '#,##0.00' # 千分位，两位小数
ws['B2'].number_format = '0.00%' # 百分比，两位小数
ws['B3'].number_format = '$#,##0.00' # 美元货币
ws['B4'].number_format = '¥#,##0.00' # 人民币
ws['B5'].number_format = 'yyyy-mm-dd' # 日期格式
ws['B6'].number_format = 'h:mm:ss AM/PM' # 时间格式
ws['B7'].number_format = '[Red]-#,##0.00;[Blue]#,##0.00' # 正负不同颜色
命名样式 (Named Styles)
from openpyxl.styles import NamedStyle
# 创建命名样式
highlight = NamedStyle(name='highlight')
highlight.font = Font(bold=True, size=12)
highlight.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
highlight.border = Border(left=Side(style='thin'), right=Side(style='thin'))
# 注册样式
wb.add_named_style(highlight)
# 使用样式
ws['A1'].style = 'highlight'
图表 API
柱状图 (BarChart)
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.type = 'col' # col: 柱状图, bar: 条形图
chart.style = 10 # 图表样式 1-48
chart.title = "Sales Chart"
chart.y_axis.title = 'Sales'
chart.x_axis.title = 'Products'
# 数据引用
data = Reference(ws, min_col=2, min_row=1, max_row=10, max_col=3)
categories = Reference(ws, min_col=1, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
# 图表位置和大小
chart.width = 15 # 宽度（厘米）
chart.height = 7.5 # 高度（厘米）
ws.add_chart(chart, "E5")
# 堆叠和分组
chart.grouping = 'stacked' # clustered, stacked, percentStacked
chart.overlap = 100 # 重叠百分比
折线图 (LineChart)
from openpyxl.chart import LineChart, Reference
chart = LineChart()
chart.title = "Sales Trend"
chart.style = 12
chart.y_axis.title = 'Sales'
chart.x_axis.title = 'Month'
# 平滑线条
chart.smooth = True
data = Reference(ws, min_col=2, min_row=1, max_row=13)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, "E5")
饼图 (PieChart)
from openpyxl.chart import PieChart, Reference
from openpyxl.chart.series import DataPoint
chart = PieChart()
chart.title = "Market Share"
chart.style = 10
data = Reference(ws, min_col=2, min_row=2, max_row=6)
labels = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.add_data(data, titles_from_data=False)
chart.set_categories(labels)
# 数据标签
chart.dataLabels = DataLabelList()
chart.dataLabels.showCatName = True
chart.dataLabels.showVal = True
chart.dataLabels.showPercent = True
ws.add_chart(chart, "E5")
散点图 (ScatterChart)
from openpyxl.chart import ScatterChart, Reference, Series
chart = ScatterChart()
chart.title = "Correlation Analysis"
chart.x_axis.title = 'X Values'
chart.y_axis.title = 'Y Values'
xvalues = Reference(ws, min_col=1, min_row=2, max_row=20)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=20)
series = Series(values=yvalues, xvalues=xvalues, title="Series 1")
chart.series.append(series)
ws.add_chart(chart, "E5")
条件格式 API
色阶规则 (ColorScaleRule)
from openpyxl.formatting.rule import ColorScaleRule
# 两色色阶
rule = ColorScaleRule(
start_type='min',
start_color='F8696B', # 红色
end_type='max',
end_color='63BE7B' # 绿色
)
# 三色色阶
rule = ColorScaleRule(
start_type='min',
start_color='F8696B', # 红色
mid_type='percentile',
mid_value=50,
mid_color='FFEB84', # 黄色
end_type='max',
end_color='63BE7B' # 绿色
)
ws.conditional_formatting.add('A1:A100', rule)
# 类型选项: min, max, num, percent, percentile, formula
数据条规则 (DataBarRule)
from openpyxl.formatting.rule import DataBarRule
rule = DataBarRule(
start_type='min',
start_value=0,
end_type='max',
end_value=100,
color='4472C4', # 蓝色
showValue=True, # 显示数值
minLength=0, # 最小长度
maxLength=100 # 最大长度
)
ws.conditional_formatting.add('B1:B100', rule)
单元格规则 (CellIsRule)
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill, Font
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
red_font = Font(color='9C0006')
# 小于
rule = CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font)
ws.conditional_formatting.add('C1:C100', rule)
# 大于
rule = CellIsRule(operator='greaterThan', formula=['1000'], fill=green_fill)
ws.conditional_formatting.add('C1:C100', rule)
# 操作符选项:
# lessThan, lessThanOrEqual, equal, notEqual, greaterThanOrEqual, greaterThan
# between, notBetween, containsText, notContainsText, beginsWith, endsWith
图标集规则 (IconSetRule)
from openpyxl.formatting.rule import IconSetRule
rule = IconSetRule(
icon_style='3TrafficLights1', # 图标集样式
type='percent', # num, percent, formula, percentile
values=[0, 33, 67], # 阈值
showValue=True, # 显示数值
reverse=False # 反转图标顺序
)
ws.conditional_formatting.add('D1:D100', rule)
# 图标集样式选项:
# 3Arrows, 3ArrowsGray, 3Flags, 3TrafficLights1, 3TrafficLights2, 3Signs, 3Symbols, 3Symbols2
# 4Arrows, 4ArrowsGray, 4RedToBlack, 4Rating, 4TrafficLights
# 5Arrows, 5ArrowsGray, 5Rating, 5Quarters
高级功能
数据验证 (Data Validation)
from openpyxl.worksheet.datavalidation import DataValidation
# 下拉列表
dv = DataValidation(
type="list",
formula1='"优秀,良好,一般,较差"',
allow_blank=True
)
ws.add_data_validation(dv)
dv.add('E2:E100')
# 整数范围
dv = DataValidation(type="whole", operator="between", formula1=0, formula2=100)
dv.add('F2:F100')
# 日期范围
from datetime import datetime
dv = DataValidation(
type="date",
operator="between",
formula1=datetime(2024, 1, 1),
formula2=datetime(2024, 12, 31)
)
dv.add('G2:G100')
# 自定义公式
dv = DataValidation(type="custom", formula1='=ISTEXT(A2)')
dv.add('H2:H100')
# 错误提示
dv.error = '输入无效！'
dv.errorTitle = '错误'
dv.showErrorMessage = True
# 输入提示
dv.prompt = '请选择一个选项'
dv.promptTitle = '提示'
dv.showInputMessage = True
表格 (Table)
from openpyxl.worksheet.table import Table, TableStyleInfo
# 创建表格
tab = Table(displayName="SalesData", ref="A1:D10")
# 表格样式
style = TableStyleInfo(
name="TableStyleMedium9", # 表格样式名称
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True, # 显示行条纹
showColumnStripes=False # 显示列条纹
)
tab.tableStyleInfo = style
ws.add_table(tab)
# 表格样式选项:
# TableStyleLight1-21, TableStyleMedium1-28, TableStyleDark1-11
工作表保护
from openpyxl.styles import Protection
# 保护工作表
ws.protection.sheet = True
ws.protection.password = 'mypassword'
ws.protection.enable()
# 保护选项
ws.protection.selectLockedCells = True
ws.protection.selectUnlockedCells = True
ws.protection.formatCells = False
ws.protection.formatColumns = False
ws.protection.formatRows = False
ws.protection.insertColumns = False
ws.protection.insertRows = False
ws.protection.deleteColumns = False
ws.protection.deleteRows = False
# 解锁特定单元格
ws['A1'].protection = Protection(locked=False)
冻结窗格
# 冻结第一行
ws.freeze_panes = 'A2'
# 冻结第一列
ws.freeze_panes = 'B1'
# 冻结前两行和前两列
ws.freeze_panes = 'C3'
# 取消冻结
ws.freeze_panes = None
自动筛选
# 启用自动筛选
ws.auto_filter.ref = 'A1:D10'
ws.auto_filter.ref = ws.dimensions # 整个数据区域
# 添加筛选条件
from openpyxl.worksheet.filters import FilterColumn, CustomFilters, CustomFilter
# 数值筛选
ws.auto_filter.add_filter_column(1, ['Value1', 'Value2'])
# 自定义筛选
flt = FilterColumn(colId=2)
flt.customFilters = CustomFilters()
flt.customFilters.customFilter.append(CustomFilter(operator='greaterThan', val=100))
ws.auto_filter.filterColumn.append(flt)
性能优化技巧
只写模式 (Write-Only Mode)
from openpyxl import Workbook
# 适用于写入大量数据（>50,000 行）
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# 只能使用 append() 方法
for row in range(100000):
ws.append([row, row*2, row*3])
wb.save('large_file.xlsx')
只读模式 (Read-Only Mode)
# 适用于读取大文件
wb = load_workbook('large_file.xlsx', read_only=True, data_only=True)
ws = wb.active
for row in ws.iter_rows():
for cell in row:
print(cell.value)
wb.close() # 记得关闭
批量操作技巧
# ❌ 慢速方法：逐个设置样式
for row in range(1, 10000):
ws.cell(row=row, column=1).font = Font(bold=True)
# ✅ 快速方法：创建样式对象并复用
bold_font = Font(bold=True)
for row in range(1, 10000):
ws.cell(row=row, column=1).font = bold_font
# ✅ 更快：使用 iter_rows
for row in ws.iter_rows(min_row=1, max_row=10000, min_col=1, max_col=1):
row[0].font = bold_font
内存优化
# 使用生成器而不是列表
def data_generator():
for i in range(100000):
yield [i, i*2, i*3]
wb = Workbook(write_only=True)
ws = wb.create_sheet()
for row in data_generator():
ws.append(row)
wb.save('output.xlsx')
# 及时清理不需要的对象
del wb
import gc
gc.collect()
pandas 性能优化
# 分块读取大文件
chunk_size = 10000
chunks = pd.read_excel('large_file.xlsx', chunksize=chunk_size)
for chunk in chunks:
# 处理每个数据块
process_chunk(chunk)
# 使用 xlsxwriter 引擎（更快）
with pd.ExcelWriter('output.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Data')
# 禁用不需要的功能
df.to_excel('output.xlsx', index=False, freeze_panes=(1, 0))
常见问题解决
中文乱码
# pandas 读取
df = pd.read_csv('data.csv', encoding='utf-8-sig')
# pandas 写入
df.to_excel('output.xlsx', encoding='utf-8-sig')
日期格式问题
# 确保日期列被正确识别
df['date'] = pd.to_datetime(df['date'])
# 写入时保持日期格式
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
df.to_excel(writer, sheet_name='Data')
ws = writer.sheets['Data']
for cell in ws['B']: # 假设 B 列是日期
if cell.row > 1:
cell.number_format = 'yyyy-mm-dd'
公式不计算
# 加载时设置 data_only=False
wb = load_workbook('file.xlsx', data_only=False)
# 或在 Excel 中打开文件后公式会自动计算
其他资源
最后更新: 2025-01-12