最终目标:使用openpyxl完成一个excel的工具类。
目录
主要来自官网,指路👇
https://openpyxl.readthedocs.io/en/stable/index.html
一、工作表操作
1、新建工作簿 create a workbook
from openpyxl import Workbook
wb = Workbook()
一个workbook最少会创建一个worksheet,激活worksheet
ws = wb.active
这种方法默认ws为workbook的第一个sheet。
2、插入工作表
ws1 = wb.create_sheet("Mysheet") # insert at the end (default) 默认在最后插入一个表
ws2 = wb.create_sheet("Mysheet", 0) # insert at first position 在最前面插入表
ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position 在倒数第二个位置插入表
3、修改sheet的名字。
在创建sheet时,会自动给一个名字(Sheet1,Sheet2等)。
ws.title = "New Title" #改名字
ws.sheet_properties.tabColor = "1072BA" #修改表名的颜色
4、通过名字获取sheet
ws = wb["New Title"] #通过sheet名(New Title)获取worksheet
5、获取工作表中所有sheet名
ws = wb.sheetnames
print(ws)
或
for sheet in wb:
print(sheet.title)
6、拷贝一个工作表到新表
source = wb.active
target = wb.copy_worksheet(source)
7、载入表格
wb = load_workbook('document.xlsx')
8、 保存文件
wb.save('document_template.xltx')
二、表中的数据操作
1、读写一个单元格。
一个单元格中的数据,称为一个cell。获取cell数据:
c = ws['A4'] #通过坐标获得其值
d = ws.cell(row = 4, column = 2) #读取指定单元格
ws['A4'] = 4 #同样也可以通过坐标赋值
d = ws.cell(row = 4, column = 2, value = 1) #通过row,column赋值
2、读写几个单元格。
(1)
colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
(2)通过Worksheet.iter_rows()读取数据
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
for cell in row:
print(cell)
'''
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
'''
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell)
'''
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>
'''
(3)tuple
ws = wb.active
ws['C9'] = 'hello world'
tuple(ws.rows)
'''
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
'''