python生成xlsx文件_python之openpyxl生成excel文件

from openpyxl importload_workbookfrom openpyxl.styles importFont, Alignment, Side, Borderimportshutil#生成测试计划的excel文件

classGenerateCaseExcel(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)defgenerateExcel(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.borderfor i inrange(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= 10CASE_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 inrange(len(case_set_list)):#title需要是unicode类型

ws_name = wb.create_sheet(title=case_set_list[i]['set_name'])#用例第一行初始化

for j inrange(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 = 10ws_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规则数据

defgenerateTableData(self, sheet_name, data_list, fields):

row_index= 2

for data indata_list:

col_index= 1

for title infields:

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_leftelse:

sheet_name.cell(row=row_index, column=col_index).alignment =self.alignment_center

col_index+= 1row_index+= 1