XLSX 技能 v2.0

概述

使用 openpyxl 进行创建/编辑、pandas 进行数据分析，实现完整的 Excel 电子表格处理。支持公式、格式、图表和复杂数据操作。

安装与依赖

必需

bash pip install pandas openpyxl xlsxwriter

可选

bash

图表支持

pip install numpy

PDF 导出

brew install --cask libreoffice

快速入门

读取 Excel 文件

python import pandas as pd

读取第一个工作表

df = pd.read_excel(file.xlsx)

读取指定工作表

df = pd.readexcel(file.xlsx, sheetname=Sheet1)

读取所有工作表

allsheets = pd.readexcel(file.xlsx, sheet_name=None)

创建 Excel 文件

python from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws[A1] = Hello
ws[B1] = World
wb.save(output.xlsx)

添加公式

python from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws[A1] = 10
ws[A2] = 20
ws[A3] = =SUM(A1:A2) # Excel 公式
wb.save(formula.xlsx)

完整 API 参考

使用 pandas 读取数据

python
import pandas as pd

基本读取

df = pd.read_excel(data.xlsx)

读取指定列

df = pd.read_excel(data.xlsx, usecols=[A, B, C])

带标题行读取

df = pd.read_excel(data.xlsx, header=0)

无标题读取

df = pd.read_excel(data.xlsx, header=None)

读取指定行

df = pd.read_excel(data.xlsx, skiprows=range(1, 10)) # 跳过第1-9行

读取多个工作表

sheetsdict = pd.readexcel(data.xlsx, sheet_name=None) for sheetname, df in sheetsdict.items(): print(f工作表: {sheet_name}, 行数: {len(df)})

数据探索

df.head() # 前5行 df.tail() # 后5行 df.info() # 列信息 df.describe() # 统计信息 df.shape # (行数, 列数) df.columns # 列名 df.dtypes # 数据类型

使用 pandas 写入数据

python
import pandas as pd

创建 DataFrame

df = pd.DataFrame({ 姓名: [Alice, Bob, Charlie], 年龄: [25, 30, 35], 城市: [纽约, 洛杉矶, 芝加哥] })

写入 Excel

df.to_excel(output.xlsx, index=False)

写入指定工作表

df.toexcel(output.xlsx, sheetname=数据, index=False)

写入多个工作表

with pd.ExcelWriter(多工作表.xlsx) as writer: df1.toexcel(writer, sheetname=Sheet1, index=False) df2.toexcel(writer, sheetname=Sheet2, index=False)

使用 openpyxl 创建

python
from openpyxl import Workbook
from openpyxl.styles import Font, Fill, PatternFill, Border, Side, Alignment, Color
from openpyxl.utils import getcolumnletter

wb = Workbook()
ws = wb.active
ws.title = 数据

写入值

ws[A1] = 标题 1 ws[B1] = 标题 2 ws.append([第1行第1列, 第1行第2列]) ws.append([第2行第1列, 第2行第2列])

单元格格式

ws[A1].font = Font(bold=True, size=14, color=FFFFFF) ws[A1].fill = PatternFill(solid, start_color=4472C4) ws[A1].alignment = Alignment(horizontal=center)

列宽

ws.column_dimensions[A].width = 20 ws.column_dimensions[B].width = 15

行高

ws.row_dimensions[1].height = 25

合并单元格

ws.merge_cells(A1:B1)

保存

wb.save(formatted.xlsx)

公式

python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

基本公式

ws[A1] = 10 ws[A2] = 20 ws[A3] = =SUM(A1:A2) # 求和 ws[A4] = =AVERAGE(A1:A2) # 平均值 ws[A5] = =MAX(A1:A2) # 最大值 ws[A6] = =MIN(A1:A2) # 最小值 ws[A7] = =COUNT(A1:A2) # 计数

跨工作表引用

wb.create_sheet(Sheet2) ws2 = wb[Sheet2] ws2[A1] = =数据!A1 # 引用 Sheet1

保存并重新计算

wb.save(formulas.xlsx)

图表

python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, LineChart, PieChart

wb = Workbook()
ws = wb.active

添加数据

data = [ [类别, 数值], [A, 10], [B, 15], [C, 20], [D, 25] ] for row in data: ws.append(row)

创建柱状图

chart = BarChart() chart.type = col chart.style = 10 chart.title = 销售图表 chart.y_axis.title = 数值 chart.x_axis.title = 类别

定义数据范围

dataref = Reference(ws, mincol=2, minrow=1, maxrow=5) cats = Reference(ws, mincol=1, minrow=2, max_row=5)

chart.adddata(dataref, titlesfromdata=True)
chart.set_categories(cats)
chart.shape = 4
ws.add_chart(chart, E1)

wb.save(chart.xlsx)

表格

python
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

添加数据

data = [ [姓名, 年龄, 城市], [Alice, 25, 纽约], [Bob, 30, 洛杉矶], [Charlie, 35, 芝加哥] ] for row in data: ws.append(row)

创建表格

tab = Table(displayName=Table1, ref=A1:C4) style = TableStyleInfo(name=TableStyleMedium9, showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False) tab.tableStyleInfo = style ws.add_table(tab)

wb.save(table.xlsx)

条件格式

python
from openpyxl import Workbook
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

添加数据

for i in range(1, 11): ws.cell(row=i, column=1, value=i * 10)

高亮大于50的单元格

redfill = PatternFill(startcolor=FF0000, endcolor=FF0000, filltype=solid) ws.conditional_formatting.add( A1:A10, CellIsRule(operator=greaterThan, formula=[50], fill=red_fill) )

使用公式高亮偶数

greenfill = PatternFill(startcolor=00FF00, endcolor=00FF00, filltype=solid) ws.conditional_formatting.add( A1:A10, FormulaRule(formula=[IS EVEN(A1)], fill=green_fill) )

wb.save(conditional.xlsx)

数据验证

python
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active

下拉列表

dv = DataValidation(type=list, formula1=是,否,可能, allow_blank=True) dv.error = 请从列表中选择 dv.errorTitle = 无效选择 ws.adddatavalidation(dv) dv.add(A1:A10)

数字范围

dv2 = DataValidation(type=whole, operator=between, formula1=[1, 100]) dv2.error = 请输入1到100之间的数字