1.安装
pip install openpyxl
2.使用方法
2.1 给单元格和行赋值
from openpyxl importWorkbook
wb=Workbook()#获取第一个活动的sheet,默认创建excel会有一个sheet
ws =wb.active#单元格直接赋值
ws['A1'] = 42
#按行赋值
ws.append([1,2,3])importdatetime
ws['A2'] =datetime.datetime.now()
wb.save("1.xlsx")
2.2 创建sheet
from openpyxl importWorkbook
wb=Workbook()#在末尾插入
ws1 = wb.create_sheet("Mysheet")
ws1.title= "New Title"
#在开始插入
ws2 = wb.create_sheet("Mysheet", 0)
ws2.title="你好"
#在倒数第二个位置插入
ws3= wb.create_sheet("Mysheet", -1)
ws3.title="third"ws1.sheet_properties.tabColor= "1072BA"
for sheet inwb:print(sheet.title)
wb.save("create_sheet.xlsx")
2.3 操作单元格
from openpyxl importWorkbook
wb=Workbook()
ws1=wb.create_sheet("Mysheet")
ws1["A1"]=124.45ws1["B2"]="您好"
#为第四行第二列也就是B4赋值10
d=ws1.cell(row=4, column=2, value=10)
wb.save("cell.xlsx")
2.4 批量操作单元格
from openpyxl importWorkbook
wb=Workbook()
ws=wb.activefor row in ws.iter_rows(min_row=1, max_col=3, max_row=2):for cell inrow:
cell.value="test"
print(cell)for col in ws.iter_rows(min_row=3, max_col=3, max_row=4):for cell incol:
cell.value="hello"
print(cell)
ws['c9']='hello world'tuple(ws.rows)
wb.save("batchcell.xlsx")
执行完之后,会在A1,B1,C1,A2,B2,C2赋值test,在A3,B3,C3,A4,B4,C4赋值hello
2.5 操作已经存在的Excel
#-*- coding: utf-8 -*-
from openpyxl importWorkbookfrom openpyxl importload_workbook
wb= load_workbook('sample.xlsx')
wb.guess_types= True #猜测格式类型
ws&#