項(xiàng)目需要，需要自動(dòng)生成PDF測(cè)試報(bào)告。經(jīng)過(guò)對(duì)比之后，選擇使用了reportlab模塊。 項(xiàng)目背景：開(kāi)發(fā)一個(gè)測(cè)試平臺(tái)，供測(cè)試維護(hù)測(cè)試用例，執(zhí)行測(cè)試用例，并且生成測(cè)試報(bào)告（包含PDF和excel），將生成的測(cè)試報(bào)告以郵件的形式發(fā)送相關(guān)人。

excel生成代碼如下：

from openpyxl import load_workbookfrom openpyxl.styles import Font, Alignment, Side, Borderimport shutil# 生成測(cè)試計(jì)劃的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/測(cè)試用例模板.xlsx’, self.file_path + self.file_name + ’.xlsx’) wb = load_workbook(self.file_path + self.file_name + ’.xlsx’) # 綜合評(píng)估頁(yè)面 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 # 動(dòng)態(tài)生成測(cè)試任務(wù)用例集信息 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='測(cè)試流程') 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) + iws_first.cell(12 + i, 2, value='用例集名稱(chēng)')ws_first.cell(12 + i, 2).alignment = self.alignment_centerws_first.cell(12 + i, 2).border = self.borderws_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_centerws_first.cell(12 + i, 3).border = self.borderws_first.cell(12 + i, 4).border = self.borderws_first.cell(12 + i, 5, value='用例負(fù)責(zé)人')ws_first.cell(12 + i, 5).alignment = self.alignment_centerws_first.cell(12 + i, 5).border = self.borderws_first.cell(12 + i, 6, value=case_set_list[i][’set_owner’])ws_first.cell(12 + i, 6).alignment = self.alignment_centerws_first.cell(12 + i, 6).border = self.border # 測(cè)試用例集用例詳細(xì)信息 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 = ['用例編號(hào)', '模塊', '優(yōu)先級(jí)', '標(biāo)簽', '標(biāo)題', '測(cè)試步驟', '期望結(jié)果', '執(zhí)行人', '實(shí)際結(jié)果', '備注'] 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類(lèi)型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 = 30if 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規(guī)則數(shù)據(jù) 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.bordersheet_name.cell(row=row_index, column=col_index).font = self.font_bodysheet_name.row_dimensions[row_index].height = 25if 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_leftelse: sheet_name.cell(row=row_index, column=col_index).alignment = self.alignment_centercol_index += 1 row_index += 1

生成效果：

以上就是python基于openpyxl生成excel文件的詳細(xì)內(nèi)容，更多關(guān)于python 生成excel文件的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！