title: python模块之openpyxl #标题tags: #标签
date: 2022-05-14
categories: python # 分类

openpyxl

安装模块：pip install openpyxl

workbook对象的部分属性

  1. active：获取活跃的worksheet
  2. read_only：是否以只读模式打开excel文档
  3. encoding：文档的字符集编码
  4. properties：文档的元数据，如标题、创建者、创建日期等
  5. worksheets：以列表的形式返回所有的worksheet
  6. sheetnames：获取所有表格的名称

workbook对象的部分方法

  1. get_sheet_by_name：通过表格名称获取worksheet对象，高版本python中使用wb["table_name"] 来获取即可
  2. copy_worksheet：复制sheet，如下是要复制名为'表格2'的sheet，并且将复制的sheet命名为'new_sheet'
  3. '''
  4. excel_name = 'body.xlsx'
  5. wb = load_workbook(excel_name)
  6. ws = wb['表格2']
  7. print(wb.sheetnames)
  8. ws=wb.copy_worksheet(ws)
  9. ws.title='new_sheet'
  10. print(wb.sheetnames)
  11. wb.save(excel_name)
  12. '''
  13. remove：删除指定的sheet
  14. '''
  15. excel_name = 'body.xlsx'
  16. wb = load_workbook(excel_name)
  17. print(wb.sheetnames)
  18. ws = wb['new_sheet']
  19. wb.remove(ws)
  20. print(wb.sheetnames)
  21. wb.save(excel_name)
  22. '''
  24. create_sheet：创建一个新的sheet
  25. '''
  26. excel_name = 'body.xlsx'
  27. wb = load_workbook(excel_name)
  28. print(wb.sheetnames)
  29. ws=wb.create_sheet()
  30. ws.title="create_new_table"      # 指定sheet名为create_new_table
  31. print(wb.sheetnames)
  32. wb.save(excel_name)
  33. '''

worksheet对象的部分属性

我们可以通过worksheet对象来获取worksheet，如下：

  1. # 实例化workbook对象并获取指定名称“表格2”的worksheet
  2. wb = load_workbook('body.xlsx')
  3. ws = wb['表格2']

worksheet部分属性如下：

  1. title：表格的标题
  2. dimensions：表格的大小，这里的大小是指含有数据的表格大小。假如表格有4列6行的数据，那么表格大小就为A1:D6
  3. max_row：表格的最大行
  4. min_row：表格的最小行
  5. max_column：表格的最大列
  6. min_column：表格的最小列
  7. rows：按行获取单元格（cell对象）
  8. columns：按列获取单元格（cell对象）
  9. freeze_panes：冻结窗格
  10. values：按行获取表格的内容（数据）

worksheet对象的部分方法

  1. iter_rows：按行获取所有单元格（cell对象）
  2. '''
  3. wb = load_workbook('body.xlsx')
  4. ws = wb['Sheet']
  5. for row in ws.iter_rows():
  6.     print([*[cell.value for cell in row]])
  7. # 输出第二行到第四行的第一列到第三列的数据
  8. for row in ws.iter_rows(min_row=2,max_row=4,min_col=1,max_col=3):
  9.     print([*[cell.value for cell in row]])
  10. '''
  11. append：在表格末尾添加数据，要添加的数据需要按顺序写入列表中，然后进行添加
  12. '''
  13. wb = load_workbook('body.xlsx')
  14. ws = wb['Sheet']
  15. line = ['a', 'b', 'c', 'd']
  16. ws.append(line)
  17. '''

cell对象的部分属性

  1. row：单元格所在的行
  2. column：单元所在的列
  3. value：单元格的取值

从文件中读取并写入excel

  1. # 假设full_body.txt文件中内容如下：
  2. {"barcode":"T55d86c7edf7aa45075b9b125b7347b8b","datasource":"SAP","syntype":"0","sourcebillcode":[{"barcode":"SAPSKDH904020221300004544","optype":"LINK"}]}
  3. {"barcode":"T2e98569bbc5d980cbba1f449d692737f","datasource":"SAP","syntype":"0","sourcebillcode":[{"barcode":"SAPSKDH904020221300004545","optype":"LINK"}]}
  4. {"barcode":"T4199f8af13ca80e6f34ec9917391d75c","datasource":"SAP","syntype":"0","sourcebillcode":[{"barcode":"SAPSKDH904020221300004546","optype":"LINK"}]}
  5. {"barcode":"Tba928eb9dad7682429df5e92bf99b814","datasource":"SAP","syntype":"0","sourcebillcode":[{"barcode":"SAPSKDH600020221300003197","optype":"LINK"}]}
  6. {"barcode":"T61bb58309a3fdaa48c66119861d58797","datasource":"SAP","syntype":"0","sourcebillcode":[{"barcode":"SAPSKDH904020221300004548","optype":"LINK"}]}

想要写入excel后的格式如下：

python模块之openpyxl - 图1

那么对应的代码如下：

在看代码前，先来总结下，其实就是先将要写入的数据，根据title按顺序放到一个列表中，每个列表就是一行记录，然后把列表追加到worksheet中，如此反复，直至所有的数据写入worksheet后，再通过save方法，进行保存即可。

  1. import json
  2. from openpyxl import Workbook
  4. # 定义要写入的文件名
  5. excel_name = 'body.xlsx'
  7. # 实例化workbook对象并获取活跃的worksheet
  8. wb = Workbook()
  9. ws = wb.active
  10. # 定义excel中的标题并通过append进行写入
  11. title = ["barcode", "datasource", "syntype", "sourcebillcode"]
  12. ws.append(title)
  13. # 读取原始文件full_body.txt并按行读取
  14. with open(r'full_body.txt', encoding='utf-8') as f:
  15.     for i in f:
  16.         # 通过json.loads以字典类型加载到内存中
  17.         json_line = json.loads(i)
  18.         # 拼接每行的数据，其实就是要把对应的字段值，按定义的title顺序放到一个列表中，值类型必须为字符串
  19.         line = [json_line.get('barcode'), json_line.get('datasource'), json_line.get('syntype'),
  20.                 str(json_line.get('sourcebillcode'))]
  21.         # 将行数据追加到表格中
  22.         ws.append(line)
  23. # 所有行数据追加完成后，通过save方法保存excel工作簿
  24. wb.save(excel_name)
  25. print(f"{excel_name}文件写入完成！！！")

从列表中读取并写入excel文件

万变不离其宗，只要知道如何从文件中读取并写入excel文件中，那么从列表中读取也是小cass。废话不多说，上代码。

  1. from openpyxl import Workbook
  3. # 测试数据，下面的数据是要写入excel中的
  4. test_data = [{"barcode": "T55d86c7edf7aa45075b9b125b7347b8b", "datasource": "SAP", "syntype": "0",
  5.               "sourcebillcode": [{"barcode": "SAPSKDH904020221300004544", "optype": "LINK"}]},
  6.              {"barcode": "T2e98569bbc5d980cbba1f449d692737f", "datasource": "SAP", "syntype": "0",
  7.               "sourcebillcode": [{"barcode": "SAPSKDH904020221300004545", "optype": "LINK"}]},
  8.              {"barcode": "T4199f8af13ca80e6f34ec9917391d75c", "datasource": "SAP", "syntype": "0",
  9.               "sourcebillcode": [{"barcode": "SAPSKDH904020221300004546", "optype": "LINK"}]},
  10.              {"barcode": "Tba928eb9dad7682429df5e92bf99b814", "datasource": "SAP", "syntype": "0",
  11.               "sourcebillcode": [{"barcode": "SAPSKDH600020221300003197", "optype": "LINK"}]},
  12.              {"barcode": "T61bb58309a3fdaa48c66119861d58797", "datasource": "SAP", "syntype": "0",
  13.               "sourcebillcode": [{"barcode": "SAPSKDH904020221300004548", "optype": "LINK"}]}]
  15. # 定义要写入的文件名
  16. excel_name = 'body.xlsx'
  18. # 实例化workbook对象并获取活跃的worksheet
  19. wb = Workbook()
  20. ws = wb.active
  21. # 定义excel中的标题并通过append进行写入
  22. title = ["barcode", "datasource", "syntype", "sourcebillcode"]
  23. ws.append(title)
  24. # 读取原始文件full_body.txt并按行读取
  25. for dic in test_data:
  26.     # 拼接每行的数据，其实就是要把对应的字段值，按定义的title顺序放到一个列表中，值类型必须为字符串
  27.     line = [dic.get('barcode'), dic.get('datasource'), dic.get('syntype'),
  28.             str(dic.get('sourcebillcode'))]
  29.     # 将行数据追加到表格中
  30.     ws.append(line)
  32. # 所有行数据追加完成后，通过save方法保存excel工作簿
  33. wb.save(excel_name)
  34. print(f"{excel_name}文件写入完成！！！")

从excel中读取数据加载至内存

  1. import openpyxl
  3. wb = openpyxl.load_workbook("body.xlsx")
  4. ws = wb.active
  6. # 方式一: 通过worksheet的values方法打印表格中的数据
  7. # 每一行数据都是一个元组
  8. for row in ws.values:
  9.     print(*row)
  11. # 方式二: 通过worksheet的rows属性打印表格中的数据，row属性会按行返回cell对象
  12. # 每一行数据都是一个列表
  13. for row in ws.rows:
  14.     # print([cell.value for cell in row])
  15.     print(*[cell.value for cell in row])
  17. # 方式三: 通过worksheet的iter_rows方法打印表格中的数据，iter_rows方法不加任何参数的话，和rows属性效果一致
  18. # 每一行数据都是一个列表
  19. for row in ws.iter_rows():
  20.     # print([cell.value for cell in row])
  21.     print(*[cell.value for cell in row])
  23. # 方式四: 最麻烦，也是最容易想到的方式。
  24. # 首先获取到表格的最小行数和最大行数，然后获取最小列数和最大列数，通过行和列的索引能确定一个唯一单元格
  25. # 确定单元格后，打印单元格的值
  26. # 这种方式是每确定一个单元格打印一次，因此，我们在print函数中将end参数取值为空格来避免换行，并在内层for循环结束以后，显示的进行换行
  27. for i in range(ws.min_row, ws.max_row + 1):
  28.     for j in range(ws.min_column, ws.max_column + 1):
  29.         print(ws.cell(row=i, column=j).value, end=' ')
  30.     print()