Python自动化办公之openpyxl

向大家推荐一个python操作excel的最好用的包——openpyxl，没有之一

安装

pip install openpyxl

使用

openpyxl支持的文件格式：.xlsx .xlsm .xltx .xltm

读取内容

1. 打开Excel表格并获取表格名称 workbook.sheetnames

   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   workbook.sheetnames
   

2. 通过sheet名称获取表格

   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   workbook.sheetnames
   sheet = workbook["Sheet1"]
   print(sheet)  # 返回sheet1对象
   

3. 获取表格sheet的尺寸大小，表示表格的数据有几行几列

   sheet.dimensions
   

4. 获取表格内某个格子的数据

   1. sheet["A1"]方式

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      cell = sheet["A1"]
      print(cell.value)  # 获取表格中的值
      

   2. sheet.cell(row=, column=)方式 即定位行列坐标

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      cell = sheet.cell(row=1, column=2)  # 获取第一行第二列的格子
      print(cell.value)
      

   3. 获取某个格子的行数、列数、坐标

      cell.coordinate  # 返回坐标  例如:C33
      

5. 获取一系列格子

   1. sheet[]方式

      cell = sheet["A1:F8"]
      for i in cell:
        print(i[0].value, i[1].value)
      

   2. iter_rows()方式 按行读取 iter_cols() 按列读取

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      for i in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=2):
        for j in i:
          print(j.value)
      

6. 获取所有行

   • sheet.rows()

7. 获取所有列

   • sheet.columns()

写入内容

1. 修改表中的内容

   1. 向某个格子中写入内容并保存 workbook.save(filename=)

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      # 更改表格数据
      sheet["C3"] = "呵呵哒"
      workbook.save(filename="other.xlsx")  # 可以存到另一个文件
      

   2. .append()向表格中插入行数据

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      data = [
        ["Cindy", "male", 98],
        ["Nancy", "female", 56]
      ]
      
      for row in data:
        sheet.append(row)
      workbook.save(filename="test.xlsx")
      

   3. .insert_cols() .insert_rows() 插入空行和空列

      • insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在idx列数的左侧插入
      • .insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在idx行数的下方插入

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      sheet.insert_cols(idx=4, amount=2)
      sheet.insert_rows(idx=5, amount=4)
      workbook.save(filename="test.xlsx")
      

   4. .delete_rows() delete_cols()

      • .delete_rows(idx=数字编号, amount=要删除的列数)
      • .delete_cols(idx=数字编号, amount=要插入的行数)

   5. move_range("数据区域", rows=, cols=) 移动格子。正整数为向下或向右，负整数为向左或向上

      sheet.move_range("C1:D4", rows=2, cols=-1)
      

   6. create_sheet() 创建新的sheet表格

   7. remove() 删除某个sheet表

   8. copy_worksheet() 复制一个sheet表到另外一张excel表

   9. sheet.title 修改sheet表的名称

      from openpyxl import load_workbook
      
      workbook = load_workbook(filename="test.xlsx")
      sheet = workbook.active  # 打开激活表格
      
      sheet.title = "修改后的名字"
      

   10. 创建新的excel表格文件

       from openpyxl import Workbook
       
       workbook = Workbook()
       sheet = workbook.active
       
       sheet.title = "表格1"
       workbook.save(filename="新建的excel表格")
       

添加工具

1. sheet.freeze_panes="单元格" 冻结窗口

   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   sheet.freeze_panes = "C3"
   
   workbook.save(filename="花园.xlsx")
   
   # 冻结窗口后，你可以打开源文件，进行检验；
   

2. sheet.auto_filter.ref 给表格添加筛选器

   1. auto_filter.ref = sheet.dimension给所有字段添加筛选器
   2. auto_filter.ref = sheet["A1"] 给第一列添加筛选器

批量修改字体和样式

1. 修改字体样式

   Font(name=字体名称, size=字体大小, bold=是否加粗, italic=是否斜体, color=字体颜色)

   from openpyxl.styles import Font
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   cell = sheet["A1"]
   font = Font(name="微软雅黑", size=20, bold=True, italic=True, color="FF0000")
   workbook.save(filename="花园.xlsx")
   

2. 获取表格中的格子的字体样式

   from openpyxl.styles import Font
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   cell = sheet["A2"]
   font = cell.font
   print(font.name, font.size, font.bold, font.italic, font.color)
   

3. 设置对齐样式

   • Alignment(horizontal=水平对齐模式, vertical=垂直对齐模式, text_rotation=旋转角度, wrap_text=是否自动换行)
   • 水平对齐：distributed justify center leftfill centerContinuous right general
   • 垂直对齐：bottom distributed justify center top

   from openpyxl.styles import Alignment
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   cell = sheet["A1"]
   alignment = Alignment(horizontal="center", vertical="center", text_rotation=45, wrap_text=T)
   cell.alignment = alignment
   workbook.save(filename="test.xlsx")
   

4. 设置边框样式

   • Side(style=边线样式, color=边线颜色)
   • Border(left=左边线样式，right=右边线样式, top=上边线样式, bottom=下边线样式)
   • style参数种类：double mediumDashDotDot slantDashDot dashDotDot dotted hair mediumDashed dashed dashDot thin mediumDashDot medium thick

   from openpyxl.styles import Side, Border
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   cell = sheet["D6"]
   side1 = Side(style="thin", color="FF0000")
   side2 = Side(style="thick", color="FFFF0000")
   border = Border(left=side1, right=side1, top=side2, bottom=side2)
   cell.border = border
   workbook.save(filename="test.xlsx")
   

5. 设置填充样式

   • PatternFill(fill_type=填充样式, fgColor=填充颜色)
   • GradientFill(stop=(渐变色1， 渐变色2 ....))

   from openpyxl.styles import PatternFill, GradientFill
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   cell_b9 = sheet["B9"]
   pattern_fill = PatternFill(fill_type="solid", fgColor="99ccff")
   cell_b9.fill = pattern_fill
   cell_b10 = sheet["B10"]
   gradient_fill = GradientFill(stop("FFFFFF", "99ccff", "000000"))
   cell_b10.fill = gradient_fill
   workbook.save(filename="test.xlsx")
   

6. 设置行高和列宽

   • row_dimensions[行编号].height = 行高
   • column_dimensions[列编号].width = 列宽

   from openpyxl.styles import PatternFill, GradientFill
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   
   # 设置第一行的高度
   sheet.row_dimensions[1].height = 50
   # 设置B列的宽度
   sheet.column_dimensions["B"].width = 20
   workbook.save(filename="test.xlsx")
   

7. 合并单元格

   • merge_cells(待合并的格子编号)
   • merge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号, end_column=结束列号)

   from openpyxl.styles import PatternFill, GradientFill
   from openpyxl import load_workbook
   
   workbook = load_workbook(filename="test.xlsx")
   sheet = workbook.active  # 打开激活表格
   sheet.merge_cells("C1:D2")
   sheet.merger_cells(start_row=7, start_column=1, end_row=8, end_column=3)
   workbook.save(filename="test.xlsx")
   

   • unmerge_cells(待取消合并格子的编号)
   • unmerge_cells(start_row=起始行号, start_column=起始列号, end_row=结束行号, end_column=结束列号)