python 读写excel工具openPyXL

openPyXL 是excel读写python包，支持 Excel 2010 xlsx/xlsm/xltx/xltm 格式文件。参见：https://openpyxl.readthedocs.io/en/stable/

如果要插入图片，必须安装pillow工具包，命令如下：pip install pillow

1、简单示例

from openpyxl import Workbookwb = Workbook()# grab the active worksheetws = wb.active# Data can be assigned directly to cellsws['A1'] = 42# Rows can also be appendedws.append([1, 2, 3])# Python types will automatically be convertedimport datetimews['A2'] = datetime.datetime.now()# Save the filewb.save("sample.xlsx")

2、基本方法

创建工作簿 wb = Workbook()

取默认的工作表 ws = wb.active

创建工作表 ws = wb.create_sheet('sheet0')

设置工作表名称： ws.title='上海地区'

设置工作表页签背景色： ws.sheet_properties.tabColor = '00ff00'

根据工作表名称查找： ws = wb['上海地区']

打印工作表名称：print(wb.sheetnames)

或者循环打印： for sheet in wb:

print(sheet.tutle)

拷贝工作表：source = wb['上海地区']

target = wb.copy_worksheet(source)

target.title = '北京地区'

单元格赋值与读取： c1 = ws['A1']

ws['A2']=100

c2 = ws.cell(row=1,column=1,value=100)

c1.value = 'hello world'

读取列：c1 = ws['A']

c2 = ws['A:H']

读取行： row1 = ws[10]

row2 = ws[1:10]

遍历行： for row in ws.iter_rows(min_row=1,max_col=3,max_row =3):

for cell in row:

print(cell)

遍历列：  for col in ws.iter_cols(min_row =1, max_col =3, max_row =3):

for cell in col:

print(cell)

遍历所有行： for row in ws.rows:

for cell in row:

print(cell)

遍历所有列：for col in ws.columns:

for cell in col:

print(cell)

保存文件：wb.save('test.xlsx')

打开已有的文件： wb = load_workbook('text.xlsx')

插入图片： from openpyxl.drawing.image  import Image

img = Image('Koala.jpg')

ws.add_image(img,'A1')

工作表(sheet)保护：只支持工作表保护，不支持整个工作簿的保护

ws.protection.set_password('test')