import openpyxl as vb
工作簿
属性
actve 获取当前活跃的worksheet
worksheets 以列表形式返回所有worksheet（表格）
sheetnames 获取工作簿中的表（列表）
data_only （默认false) True时只读取数据，不显示公式
read_only 判断是否以只读模式打开excel文档
encoding 获取文档字符集编码
properties 获取文档的元数据，如标题、创建者、创建时间方法
worksheet.sheetnames
worksheet["sheet1"]
workbook.active
worksheet.remove
workbook.create_sheet
copy_worksheet
工作表
- 属性
title, dimensions,max_row, min_column,
rows, columns 按行/列获取cell对象(生成器)
workbooko.freeze_panes = "C3" 冻结窗格 - 方法
iter_rows, iter_columns 按行/列获取单元格，内置属性（min_row, mzx_row, min_col, max_col)
append 表格末尾添加数据
merged_cells 合并多个单元格
unmerged_cells 移除合并的单元格
# 创建工作簿
workbook1 = vb.Workbook(path)
workbook1.save(path)
# 打开工作表
workbook2 = vb.load_workbook(path)
worksheet2 = workbook2["Sheet1"]
= workbook2.active]
# 读取单元格内容时，不要读公式，只读数据结果
workbook = vb.load_workbook(path, data_only=True)
# 显示工作簿中所有工作表和表名
for worksheet in workbook.worksheets:
print(worksheet.title)
#新建、删除指定工作表
path = r"...test.xlsx"
workbook = vb.load_workbook(path)
worksheet = workbook["4月"]
workbook.create_sheet("5月")
workbook.remove(worksheet)
workbook.save(path)
# 复制工作表
worksheet_new = workbook.copy_worksheet(workbook["5月"])
workbook_new.title="5月_new"
workbook.save(path)
单元格读取操作
# 获取单元格值
cell= worksheet["A1"]
= worksheet.cell(row=1,column=1)
print(cell.value)]
# 获取1，3，5，7行的数据
for i in range(1,8,2):
print(i, worksheet.cell(row=i, column=2),value)
# 获取一个区域的单元格
area = worksheet["A1:C10"] # 用["1:10"] ["A:C"]会自动扩展到used cells
for row in area:
for cell in row:
print(cell.value)
# 获取某个区域的单元格
# 一般只需要定位起点就够够了
for row in worksheet.iter_rows(min_row=1, min_col=1):
for cell in row:
print(cell.value)
# 标记空单元格
for row in worksheet.iter_rows(min_row=2, min_col=2):
for cell in row:
if cell.value == None:
cell.value = "空"
workbook.save(path)
# 获取所有行/列
for row in worksheet.rows:
for cell in row:
print(cell.value)
# 获取单元格所在行列
row_1 = cell.row
col_2 = cell.column
# 列数字和数字之前的转换
col_text = vb.utils.get_column_letter(2)
col_num = vb.utils.column_index_from_string("D")
常用单元格操作
# 向一个单元格写入数据
worksheet.cell(1,5, value="写入数据")
worksheet["E2"] = "写入数据"
# 在最后一行写入数据
worksheet.append(list)
# 向一个内容区域写入数据
for row in worksheet["A1:B4"]
for cell in row:
cell.value = 12344
# 插入、删除行列
worksheet,insert_cols(idx=2, amount=5)
worksheet.insert_rows(idx=2, amount=5)
worksheet.delete-cols(idx=2, amount=5)
# 移动单元格
worksheet.move_range("A1:C3",row=10,cols=10) # 向右10，向下10
# 冻结单元格
worksheet.freeze_panes = "C3"
# 直接在单元格内写公式
worksheet["F1"] = "sum(C1:E1)"
# 添加批注
comment_1 = vb.comments.Comment("这里写了批注","jyc")
workwheet["F20"].comment = comment_1
workbook.save(path)
表格格式设置
# 字体对象
font_style = vb.styles.Font(name=u"微软雅黑", bold=True, italic=False, size=9,
vertAlign=None, underline=None, strike=False, color="FF000000")
worksheet["A2"].font = font_style
# 单元格对齐 Alignment
Alignment(horizontal="general", vertical="bottom", text_rotation=0, wrap_text=False,
shrink_to_fit=False, indent=0)
# horinzontal: "general", "center","left", "justify"(两端对齐),"right", "centerContinuous"(跨列居中), "distributed"(分散对齐),
# vertical: "center","top","bottom","justify","distributed"
# 边框 Side
Side(style="连线样式"， color="边线颜色")
Border(left="左边线样式"， right= , top= , bottom= )
# style: double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
side = vb.style.Side(style="thin", color="FF000000")
border = vb.styles.Border(left=side, right=side, top=side, bottom=side)
worksheet["A1"].border = border
# 填充 PatternFill
PatternFill(fill_type=None, start_color="FFFFFFFF", end_color="FF000000")
# fill_type: None, solid, darGray, mediumGray, lightGray, gray0625, darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis, lightHorizontal, ...
#start_color/fgColor 背景颜色
#end_color/bgColor 图案颜色
# 渐变填充 GradientFill
GradientFill(stop=("渐变颜色1", "渐变颜色2", ...))
# fill_type: linear, path(中心扩散)
# degree: 旋转角度
# stop：一个元组，（起始颜色,...,结束颜色)
# 锁定单元格、隐藏公式
Protection(locked=True, hidden=False)
# 只有开启”保护工作表“之后，锁定单元格和隐藏公式才能生效
# 行高和列宽
worksheet.row_dimensions[1].height=200
worksheet.column_dimensions["B"].width=100
