openPyXL 是excel读写python包,支持 Excel 2010 xlsx/xlsm/xltx/xltm 格式文件。参见:https://openpyxl./en/stable/ 如果要插入图片,必须安装pillow工具包,命令如下:pip install pillow 1、简单示例 from openpyxl import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws['A1'] = 42 # Rows can also be appended ws.append([1, 2, 3]) # Python types will automatically be converted import datetime ws['A2'] = datetime.datetime.now() # Save the file wb.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') |
|
来自: 网海拾贝网络猪 > 《python技巧》