openpyxl(python操作Excel)
2024-09-06 03:34:21
一、安装
>>> pip install openpyxl
import openpyxl
二、常用操作
1、创建与保存一个工作簿
wb = openpyxl.Workbook() wb.save("sample.xlsx")
2、获取第一个工作表(将工作看成一个二维数组)
ws = wb.active ws.title ws["A1"] = 520 ws.append([1, 2, 3])
3、打开一个工作簿
wb = openpyxl.load_workbook("sample.xlsx")
4、获取工作表名称列表,再通过工作表名称获取工作表对象
sheetnames = wb.sheetnames or wb.get_sheet_names()
# ["sheet", "example", "maoyan"] example = wb.get_sheet_by_name("example") or wb["example"]
5、创建和删除工作表
wb.create_sheet(index=0, title="example") # index:工作表位置 title:工作表名称 wb.remove_sheet(wb["example"]) # 需要传入一个工作表对象
6、定位单元格
example["A1"] # 获取单元格对象
example["A"] # 获取单元格元组(列)
example[""] # 获取行单元格元组(行) c = example["C4"]
c.offset(1, 3) # 单元格偏移
# C+1=D,4+3=7,D7
7、'AAA'是多少?
观察工作表发现工作表的列索引为A-Z、AA-AZ、BA-BZ······
获取列数字索引对应的字符格式和字符索引对应的数字格式
openpyxl.cell.cell.get_column_letter(496)
# 'SB' openpyxl.cell.cell.column_index_from_string("SB")
#
8、访问多个单元格(先行后列)
方式一:
ws["A1":"B2"]
# ((<Cell 'MaoYan'.A1>, <Cell 'MaoYan'.B1>), (<Cell 'MaoYan'.A2>, <Cell 'MaoYan'.B2>), (<Cell 'MaoYan'.A3>, <Cell 'MaoYan'.B3>)) ws["A":"B"]
# ((<Cell 'MaoYan'.A1>, <Cell 'MaoYan'.A2>, <Cell 'MaoYan'.A3>, <Cell 'MaoYan'.A4>, <Cell 'MaoYan'.A5>, <Cell 'MaoYan'.A6>, <Cell 'MaoYan'.A7>, <Cell 'MaoYan'.A8>, <Cell 'MaoYan'.A9>, <Cell 'MaoYan'.A10>), (<Cell 'MaoYan'.B1>, <Cell 'MaoYan'.B2>, <Cell 'MaoYan'.B3>, <Cell 'MaoYan'.B4>, <Cell 'MaoYan'.B5>, <Cell 'MaoYan'.B6>, <Cell 'MaoYan'.B7>, <Cell 'MaoYan'.B8>, <Cell 'MaoYan'.B9>, <Cell 'MaoYan'.B10>))
方式二:
# ws.rows、ws、columns 生成器
for row in ws.rows: # 多行
print(row[0]) for row in ws.iter_rows(min_row=2, min_col=1, max_row=4, max_col=2): # 指定行
print(row[0])
9、拷贝工作表
wb.copy_worksheet(ws["example"]) # 工作表对象
10、个性化工作表标签栏
ws.sheet_properties.tabColor = "FFFFFF"
11、调整行高和列宽
ws.row_dimensions[2].height = 100 # 修改行高 ws.column.dimensions["A"] .width = 20 # 修改列宽
12、合并和拆分单元格(合并拆分要一致)
ws.merge_cells("A1:C3") ws.unmerge_cells("A1:C3")
13、冻结窗口
ws.freeze_panes = "B8" # 指定冻结的单元格后,往下滚动,小于第行的不动,往右滚动,小于第B列的不动 ws.freeze_panes = "A1" # 解冻,设置为A1就可以了
最新文章
- NIO及Reactor模式
- 我的AngularJS 学习之旅
- windows获取硬盘使用率等信息
- [翻译]AKKA笔记 - ACTOR MESSAGING - REQUEST AND RESPONSE -3
- C#打开摄像头抓取照片然后退出
- 蓝凌表单的表体调用Javascript
- 外部表查询时出现ORA-29913和ORA-29400错误
- MySQL 大DML操作建议
- IOS UI 第十篇: UITABLEVIEW
- 结构-行为-样式-angularJs 指令实现滚动文字
- for循环游标
- BUPT2017 wintertraining(15) #2 题解
- STM32 CAN
- git技巧
- Latex 算法过长 分页显示方法
- SOA, EDA, 和 ESB
- 别人的Linux私房菜(2)Linux简介
- hashlib模块 md5 sha1
- LINQ TO SQL:操作有层次关系的对象
- sublime package control以及常用插件