python导入openpyxl_python系列之（6）Python使用Openpyxl操作Excel

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&#