目录
一. 几种操作excel的lib比较
- xlwings:简单强大,可替代VBA
- openpyxl:简单易用,功能广泛
- pandas:使用需要结合其他库,数据处理是pandas立身之本
- win32com:不仅仅是excel,可以处理office;不过它相当于是 windows COM 的封装,新手使用起来略有些痛苦。
- Xlsxwriter:丰富多样的特性,缺点是不能打开/修改已有文件,意味着使用 xlsxwriter 需要从零开始。
- DataNitro:作为插件内嵌到excel中,可替代VBA,在excel中优雅的使用python
- xlutils:结合xlrd/xlwt,老牌python包,需要注意的是你必须同时安装这三个库
二. openpyxl 基本用法
- 重要数据类型:
- NULL空值:对应于python中的None,表示这个cell里面没有数据。
- numberic: 数字型,统一按照浮点数来进行处理。对应于python中的float。
- string: 字符串型,对应于python中的unicode。
- 三个对象
- workbook: 工作簿,一个excel文件包含多个sheet。
- sheet: 工作表,一个workbook有多个,表名识别,如“sheet1”,“sheet2”等。
- cell: 单元格,存储数据对象。
1. xlsx文件和工作表的常用操作
# 新建一个excel文件
wb_name = 'test.xlsx'
wb = openpyxl.Workbook()
ws = wb.active
wb.save(wb_name )
# 加载一个已有的excel文件
wb_name = 'test.xlsx'
wb = openpyxl.load_workbook(wb_name)
# 修改excel文件名
wb.title = wb_name
# 获取所有表名
ws_name = wb.sheetnames
# 遍历所有表
for ws in wb:
print(ws.title)
# 根据表名获取表
ws_name = 'test_sheet'
ws = wb[ws_name]
# 修改表名
ws.title = 'test_sheet'
# 获取excel默认打开的表
ws = wb.active
# 新建表
ws_name = 'test1'
# 插在开头
ws = wb.create_sheet(index=0, title=ws_name)
# 插在末尾
ws = wb.create_sheet(title=ws_name)
# 复制表
ws4=wb.copy_worksheet(ws1)
# 删除表
ws_name = 'test_sheet'
wb.remove(wb[ws_name])
2. 单元格的常用操作
# 获取第一行,第二列的的单元格对象
ce = ws['A2']
ce = ws.cell(1, 2)
# 获取单元格的数据
cell_value = ce.value
# 获取单元格的行和列的序号
col_id = ce.column
row_id = ce.row
# 序号的英文和数字的转换 (openpyxl.utils)
from openpyxl.utils import get_column_letter, column_index_from_string
# 字母转数字
column_index_from_string(char)
# 数字转字母
get_column_letter(number)
3. 行和列的常用操作
# 遍历所有行
for row in sw.rows:
for cell in row:
print(cell.value)
for row in sw.iter_rows():
for cell in row:
print(cell.value)
# 遍历所有列
for column in sw.columns:
for cell in column:
print(cell.value)
# 遍历某一行
for cell in list(sw.rows)[0]:
print(cell.value)
list_row_list = []
for cell in ws['C']:
list_row_list.append(cell.value)
# 遍历某一列
for i in range(1, ws.max_row + 1):
print(ws.cell(row=i,column=2).value)
# 遍历给定范围的单元格
for spaces in sw['A1': 'B2']:
for cell in spaces:
print(cell.value)
# 获取最大行和最大列
sw.max_row
sw.max_column
# 写入一行
row_content = [1, 2, 3, 4]
sw.append(row_content)
# 写入多行
rows_content = [
['ID', 'Name', 'Department'],
['001', 'Lee','CS'],
['002', 'John','MA'],
['003', 'Amy','IS']
]
sheet.append(rows_content)
# 添加一个数据
cell_value = test
ws.cell(row=2,column=3,value=cell_value)
ws.cell(row=2,column=3).value = cell_value
ws['A1'] = cell_value
4. 表格样式常用操作
# 表格样式支持:字体、颜色、模式、边框、数字格式...
# B11单元格 等线24号加粗斜体,字体颜色浅蓝色
ws["B11"].font = openpyxl.styles.Font(name="宋体", size=24, italic=True, color="00CCFF", bold=True)
# B11单元格 水平上下居中
sheet1["B11"].alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center")
# 第一行高度设置为30
sheet1.row_dimensions[1].height = 30
# C列的宽度设置为35
sheet1.column_dimensions["C"].width = 35
# 合并单元格
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
# 拆分单元格
sheet.unmerge_cells('A1:C3')
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
参考博客:
https://openpyxl.readthedocs.io/en/stable/
https://blog.csdn.net/weixin_43094965/article/details/82226263