python操作excel生成报表openpyxl

python操作excel生成报表openpyxl

前言

python操作excel有很多方式，每种方式都有一些缺陷和遗憾。

• xlrd 和 xlwt 是最不想采用的方式。
• xlwings 功能是非常强大的，但是有一个巨大的坑，辛辛苦苦在Windows环境下开发出来，不能在服务器上使用。不支持Linux的系统。
• openpyxl 功能也比较强大，就是一点小缺点，不支持xls文件。这个在当前应该不算什么问题。

操作excel的基本流程结构

1. 安装

   pip install openpyxl
   

2. 打开excel文件，对文件进行操作

   import openpyxl
   # 打开文件，创建workbook对象
   wb = openpyxl.load_workbook(path)
   # 通过工作表名称，创建工作表对象
   sheet = wb['模板']  
   #对工作表进行操作
   ……
   # 按照原路径保存结束操作
   wb.save(path)
   

3. 新建文件，对文件进行操作，一般在报表中很少用

   import openpyxl
   #创建workbook对象
   wb = openpyxl.Workbook()
   #创建工作表对象
   ws = wb.active
   #对工作表写入数据
   ……
   #保存
   wb.save(path)
   

4. 只读方式打开，实现数据导入，公式获取计算结果

   import openpyxl
   # 打开文件，创建workbook对象
   wb = openpyxl.load_workbook(path,data_only=True)
   #一定要指定data_only为真，Excel文件打开过，才能读取公式的值
   sheet = wb["工作表名"]
   ……
   #只读操作，用完后关闭
   wb.close()
   

工作表的基本操作

1. 工作表的复制copy_worksheet函数 new_sheet = wb.copy_worksheet(sheet)`
2. 删除工作表remove 函数 wb.remove(sheet)
3. 工作表命名title属性 sheet.title = '升阳集团'
4. 创建工作表create_sheet wb.create_sheet("Mysheet", 0) 在最开始位置创建工作表，位置可以省略
5. 获取工作表名称列表sheetnames属性 wb.sheetnames

获取单元格

• 通过单元格名称直接获取 sheet['A1'] sheet[f'A{row_id}']

• 通过列号和行号获取 sheet.cell(row=row_id,column=cell.column)

• 循环获取

  for row in sheet.iter_rows(min_row=4, max_col=17, max_row=sheet.max_row-1):
      for cell in row:
          copy_cell(cell,newSheet.cell(row=row_id,column=cell.column))  #cell.column 获取单元格的列号
  

• 获取最大行，最大列

  sheet.max_row  #获取最大行
  sheet.max_column  #获取最大列
  

单元格的基本操作

• 单元格写入值 sheet['A1']='升阳集团工资汇总表'
• 写入多个数据 sheet.append([1, 2, 3])
• 读取单元格的值（包括公式） sheet['A1'].value
• 单元格合并 merge_cells 函数 sheet.merge_cells(f'a{row_id}:d{row_id}')
• 输入公式 sheet[f'h{row_id}']=f'=SUM(I4:I{row_id-1})'

单元格格式操作

• 导入相应的包

  from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
  

• 设置字体，要一个单元进行设置，不能区域设置 cell.font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)

• 定义边框样式和字体

   # 定义边框样式
  thin_border = Border(left=Side(style='thin'), 
               right=Side(style='thin'), 
               top=Side(style='thin'), 
               bottom=Side(style='thin'))
  # 为一个范围内的单元格设置边框
  for row in sheet.iter_rows(min_row=row_id, max_col=17, max_row=row_id):
      for cell in row:
          cell.border = thin_border
          cell.font = Font(size=9,name='宋体',bold=False)
  

• 定义单元格数据格式 sheet[f'h{row_id}'].number_format='¥#,##0.0'

• 定义单元格对齐方式 sheet[f'J{row_id}'].alignment=Alignment(horizontal='right')