openpyxl读写操作

前提知识：

测试数据：

安装 ：pip install openpyxl

from openpyxl import load_workbook# 导入excel文件workbook = load_workbook(filename='test.xlsx’) 或workbook = open("test.xlsx")# 打印该文件所有表名print(workbook.sheetnames)# 切换表sheet = workbook['sheet1']# 打印该表单元格规模print(sheet.dimensions)# 取该表的某个单元格cell = sheet['A1']# 打印该单元格的坐标print(cell.row, cell.column, cell.coordinate)# 获取多个单元格for cell in sheet['A1:A10']for cell in sheet['A1:G10']for rows in sheet.iter_rows(min_row=1,max_row=5,min_col=2,max_col=7):for cell in rows:print(cell.value,end='')print()# 获取单元格对象的值,[必须是单元格对象]，如果取到元组是点不出value的print(cell.value)

# 保存workbook.save('test.xlsx')# 修改cell = sheet['A1'] cell.value = 'A1'# 追加到最后最后一行sheet.append([15, None, None, 14, 'qq.com'])# 添加公式sheet['K11'] = '=AVERAGE(K1:K10)'# 在第二行处增加4行sheet.insert_rows(2,4)# 在第二列处增加4列sheet.insert_cols(2,4)# 删除行sheet.delete_rows(2,4)# 删除列sheet.delete_cols(2,4)# 最后一定要保存一遍workbook.save('test.xlsx')# 创建excelfrom openpyxl import Workbookworkbook = Workbook()