python基于openpyxl生成excel文件

作者:Zoe_yan 时间:2022-08-03 03:10:47 

项目需要,需要自动生成PDF测试报告。经过对比之后,选择使用了reportlab模块。 项目背景:开发一个测试平台,供测试维护测试用例,执行测试用例,并且生成测试报告(包含PDF和excel),将生成的测试报告以邮件的形式发送相关人。

excel生成代码如下:


from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment, Side, Border
import shutil

# 生成测试计划的excel文件
class GenerateCaseExcel(object):
 def __init__(self, file_name):
   self.file_name = file_name
   self.file_path = '/xxx/xxx/xxx/'
   self.font_title = Font(name=u"宋体", size=12, bold=True)
   self.font_body = Font(name=u"宋体", size=10)
   self.alignment_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
   self.alignment_left = Alignment(horizontal='left', vertical='center', wrap_text=True)
   self.thin = Side(border_style="thin")
   self.border = Border(top=self.thin, left=self.thin, right=self.thin, bottom=self.thin)

def generateExcel(self, basic_data, case_set_list, case_data_info):
   shutil.copy(u'/xxx/xxx/xxx/测试用例模板.xlsx', self.file_path + self.file_name + '.xlsx')

wb = load_workbook(self.file_path + self.file_name + '.xlsx')
   # 综合评估页面
   ws_first = wb.worksheets[0]
   ws_first.cell(2, 2).value = basic_data['project_name']
   ws_first.cell(2, 4).value = basic_data['report_code']
   ws_first.cell(2, 6).value = basic_data['report_date']
   ws_first.cell(3, 2).value = basic_data['task_id']
   ws_first.cell(3, 4).value = basic_data['task_name']
   ws_first.cell(3, 6).value = basic_data['task_owner']

ws_first.cell(4, 2).value = basic_data['task_priority']
   ws_first.cell(4, 4).value = basic_data['task_status']
   ws_first.cell(4, 6).value = basic_data['task_module']
   ws_first.cell(5, 2).value = basic_data['app_version']
   ws_first.cell(5, 4).value = basic_data['product_id']
   ws_first.cell(5, 6).value = basic_data['device_id']

ws_first.cell(6, 2).value = basic_data['firmware_key']
   ws_first.cell(6, 4).value = basic_data['firmware_version']
   ws_first.cell(6, 6).value = basic_data['mcu_version']
   ws_first.cell(7, 2).value = basic_data['gateway_version']
   ws_first.cell(7, 4).value = basic_data['chip_module']

ws_first.cell(8, 2).value = basic_data['task_result']
   ws_first.cell(9, 2).value = basic_data['note']
   ws_first.cell(10, 2).value = basic_data['router']
   ws_first.cell(11, 2).value = basic_data['test_mobile']

for i in range(8, 12):
     for j in range(2, 7):
       ws_first.cell(i, j).border = self.border

# 动态生成测试任务用例集信息
   if len(case_set_list) > 0:
     # 合并单元格处理
     merge_num = int(11) + len(case_set_list)
     ws_first.merge_cells("A12:A" + str(merge_num))
     ws_first.cell(12, 1, value="测试流程")
     ws_first.cell(12, 1).alignment = self.alignment_center
     ws_first.cell(merge_num, 1).border = self.border

for i in range(len(case_set_list)):
       cur_row = int(12) + i
       ws_first.cell(12 + i, 2, value="用例集名称")
       ws_first.cell(12 + i, 2).alignment = self.alignment_center
       ws_first.cell(12 + i, 2).border = self.border
       ws_first.merge_cells("C" + str(cur_row) + ":D" + str(cur_row))
       ws_first.cell(12 + i, 3, value=case_set_list[i]['set_name'])
       ws_first.cell(12 + i, 3).alignment = self.alignment_center
       ws_first.cell(12 + i, 3).border = self.border
       ws_first.cell(12 + i, 4).border = self.border
       ws_first.cell(12 + i, 5, value="用例负责人")
       ws_first.cell(12 + i, 5).alignment = self.alignment_center
       ws_first.cell(12 + i, 5).border = self.border
       ws_first.cell(12 + i, 6, value=case_set_list[i]['set_owner'])
       ws_first.cell(12 + i, 6).alignment = self.alignment_center
       ws_first.cell(12 + i, 6).border = self.border

# 测试用例集用例详细信息
   fields = "case_id,case_module,case_priority,case_tags,case_name,case_step,expect_result,case_operator,real_result,note".split(",")
   CASE_FIELD_LENGHT = 10
   CASE_FIELD_DES = ["用例编号", "模块", "优先级", "标签", "标题", "测试步骤", "期望结果", "执行人", "实际结果", "备注"]
   COLUMN_DES = {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E', 6: 'F', 7: 'G', 8: 'H', 9: 'I', 10: 'J'}
   if len(case_set_list) > 0:
     for i in range(len(case_set_list)):
       # title需要是unicode类型
       ws_name = wb.create_sheet(title=case_set_list[i]['set_name'])
       # 用例第一行初始化
       for j in range(CASE_FIELD_LENGHT):
         ws_name.cell(1, j + 1, value=CASE_FIELD_DES[j])
         if j == 3 or j == 4 or j == 5 or j == 6 or j == 9:
           ws_name.column_dimensions[COLUMN_DES[j + 1]].width = 35
         else:
           ws_name.column_dimensions[COLUMN_DES[j + 1]].width = 10
         ws_name.cell(1, j + 1).font = self.font_title
         ws_name.cell(1, j + 1).alignment = self.alignment_center
         ws_name.cell(1, j + 1).border = self.border
         ws_name.row_dimensions[1].height = 30
       if case_set_list[i]['set_name'] in case_data_info.keys() and len(case_data_info[case_set_list[i]['set_name']]) > 0:
         self.generateTableData(ws_name, case_data_info[case_set_list[i]['set_name']], fields)

wb.save(filename=self.file_path + self.file_name + '.xlsx')
   wb.close()

# 生成table规则数据
 def generateTableData(self, sheet_name, data_list, fields):
   row_index = 2
   for data in data_list:
     col_index = 1
     for title in fields:
       sheet_name.cell(row=row_index, column=col_index, value=data[title])
       sheet_name.cell(row=row_index, column=col_index).border = self.border
       sheet_name.cell(row=row_index, column=col_index).font = self.font_body
       sheet_name.row_dimensions[row_index].height = 25
       if col_index == 5 or col_index == 6 or col_index == 7 or col_index == 10:
         sheet_name.cell(row=row_index, column=col_index).alignment = self.alignment_left
       else:
         sheet_name.cell(row=row_index, column=col_index).alignment = self.alignment_center
       col_index += 1
     row_index += 1

生成效果:

python基于openpyxl生成excel文件

python基于openpyxl生成excel文件

来源:https://www.cnblogs.com/zoe-yan/p/11356542.html

标签:python,excel文件,openpyxl
0
投稿

猜你喜欢

  • 详解基于webpack和vue.js搭建开发环境

    2024-04-30 10:40:15
  • Python绘图之二维图与三维图详解

    2023-01-09 11:25:05
  • vue使用ElementUI时导航栏默认展开功能的实现

    2024-05-09 15:18:14
  • 15款Python编辑器的优缺点,别再问我“选什么编辑器”啦

    2021-10-06 12:37:55
  • 10分钟快速上手VueRouter4.x教程

    2024-05-02 17:10:38
  • 6个卓越Web设计细节[译]

    2010-03-24 18:34:00
  • 用pandas中的DataFrame时选取行或列的方法

    2023-05-15 07:43:04
  • 解决IDEA GIT记录无法查看提交文件的问题

    2022-12-08 01:46:25
  • Git配置.gitignore文件忽略被指定的文件上传

    2022-09-23 03:00:08
  • Python单元测试及unittest框架用法实例解析

    2022-06-18 05:35:29
  • python数据结构的排序算法

    2021-03-11 04:04:10
  • SQL Server误区30日谈 第3天 即时文件初始化特性可以在SQL Server中开启和关闭

    2024-01-16 18:38:28
  • Python实现的简单hangman游戏实例

    2021-04-11 19:26:47
  • JavaScript登录验证码的实现

    2023-08-23 21:03:12
  • yolov5特征图可视化的使用步骤

    2022-07-22 01:25:40
  • python实现的登录与提交表单数据功能示例

    2021-04-28 21:16:38
  • python分分钟绘制精美地图海报

    2021-11-10 11:44:25
  • linux中使用boost.python调用c++动态库的方法

    2023-01-19 19:21:03
  • python 处理数字,把大于上限的数字置零实现方法

    2022-11-13 09:20:56
  • 一个带采集远程文章内容,保存图片,生成文件等完整的采集功能

    2011-04-02 11:02:00
  • asp之家 网络编程 m.aspxhome.com