def export_to_excel_ws(data, ws_number, header_filename='../data/ws567header.xlsx'): if ws_number in [5, 6, 7]: header_filename = '../data/ws567header.xlsx' elif ws_number in [4, 8]: header_filename = '../data/ws48header.xlsx' wb = load_workbook(header_filename) sheets = wb.sheetnames Sheet1 = wb[sheets[0]] row = 5 col = 1 Sheet1.cell(1, 21).value = f'{ws_number} ЦЕХ' Sheet1.cell(1, 23).value = 'с' Sheet1.cell(1, 24).value = data['results'][0]['date'] Sheet1.cell(1, 27).value = 'по' Sheet1.cell(1, 28).value = data['results'][-1]['date'] for idx, record in enumerate(data['results']): Sheet1.cell(row, col).value = record['date'] Sheet1.cell(row, col + 1).value = record['count_piglets_at_start'] Sheet1.cell(row, col + 2).value = '' Sheet1.cell(row, col + 3).value = record['count_piglets_at_start'] Sheet1.cell(row, col + 4).value = '' tr_in_qnty = record['tr_in_qnty'] if record['tr_in_qnty'] else '' tr_in_aka_weight_in_qnty = f"({record['tr_in_aka_weight_in_qnty']})" \ if record['tr_in_aka_weight_in_qnty'] else '' Sheet1.cell(row, col + 5).value = f"{tr_in_qnty}{tr_in_aka_weight_in_qnty}" Sheet1.cell(row, col + 6).value = record['tr_in_aka_weight_in_total'] tr_out_qnty = record['tr_out_qnty'] if record['tr_out_qnty'] else '' tr_out_aka_weight_in_qnty = f"({record['tr_out_aka_weight_in_qnty']})" if record[ 'tr_out_aka_weight_in_qnty'] else '' Sheet1.cell(row, col + 11).value = f"{tr_out_qnty}{tr_out_aka_weight_in_qnty}" Sheet1.cell(row, col + 15).value = record['padej_qnty'] Sheet1.cell(row, col + 16).value = record['padej_total_weight'] if ws_number in [5, 6, 7]: Sheet1.cell(row, col + 20).value = record['spec_qnty'] Sheet1.cell(row, col + 21).value = record['spec_total_weight'] Sheet1.cell(row, col + 25).value = record['vinuzhd_qnty'] Sheet1.cell(row, col + 26).value = record['vinuzhd_total_weight'] if ws_number in [4, 8]: Sheet1.cell(row, col + 29).value = record['prirezka_qnty'] Sheet1.cell(row, col + 30).value = record['prirezka_total_weight'] if idx < len(data['results']) - 1: Sheet1.cell( row, col + 31).value = data['results'][idx + 1]['count_piglets_at_start'] Sheet1.cell(row, col + 32).value = 0 Sheet1.cell( row, col + 33).value = data['results'][idx + 1]['count_piglets_at_start'] row += 1 thin = Side(border_style="thin", color="000000") for col_number in range(1, 35): Sheet1.cell(row, col_number).font = Font(name='Arial', size=14, bold=True) Sheet1.cell(row, col_number).border = Border(top=thin, left=thin, right=thin, bottom=thin) Sheet1.cell(row, col).value = 'Итого' total_tr_in_qnty = data['total_info']['total_tr_in_qnty'] \ if data['total_info']['total_tr_in_qnty'] else '' total_tr_in_aka_weight_in_qnty = f"({data['total_info']['total_tr_in_aka_weight_in_qnty']})" \ if data['total_info']['total_tr_in_aka_weight_in_qnty'] else '' Sheet1.cell( row, col + 5).value = f"{total_tr_in_qnty}{total_tr_in_aka_weight_in_qnty}" Sheet1.cell( row, col + 6).value = data['total_info']['total_tr_in_aka_weight_in_total'] Sheet1.cell(row, col + 11).value = data['total_info']['total_tr_out_qnty'] Sheet1.cell(row, col + 15).value = data['total_info']['total_padej_qnty'] Sheet1.cell(row, col + 16).value = data['total_info']['total_padej_total_weight'] Sheet1.cell(row, col + 20).value = data['total_info']['total_spec_qnty'] Sheet1.cell(row, col + 21).value = data['total_info']['total_spec_total_weight'] Sheet1.cell(row, col + 25).value = data['total_info']['total_vinuzhd_qnty'] Sheet1.cell(row, col + 26).value = data['total_info']['total_vinuzhd_total_weight'] if ws_number in [4, 8]: Sheet1.cell(row + 3, col + 11).value = 'Процент падежа от прихода поросят' padej_qnty = data['total_info']['total_padej_qnty'] if data[ 'total_info']['total_padej_qnty'] else 0 prirezka_qnty = data['total_info']['total_prirezka_qnty'] if data[ 'total_info']['total_prirezka_qnty'] else 0 vinuzhd_qnty = data['total_info']['total_vinuzhd_qnty'] if data[ 'total_info']['total_vinuzhd_qnty'] else 0 trs_in = data['total_info']['total_tr_in_qnty'] if data['total_info'][ 'total_tr_in_qnty'] else 1 Sheet1.cell(row + 4, col + 11).value = \ 100 * (padej_qnty + prirezka_qnty +vinuzhd_qnty) / trs_in Sheet1.cell(row + 5, col + 11).value = 'Ср.вес 1 головы' Sheet1.cell(row + 5, col + 14).value = 'падежа' Sheet1.cell(row + 5, col + 15).value = data['total_info']['total_padej_avg_weight'] Sheet1.cell(row + 5, col + 17).value = 'прирезки' Sheet1.cell(row + 5, col + 18).value = data['total_info']['total_prirezka_avg_weight'] Sheet1.cell(row + 5, col + 19).value = 'в.убой' Sheet1.cell(row + 5, col + 20).value = data['total_info']['total_vinuzhd_avg_weight'] wb.save(f"../data/ws{ws_number}_output.xlsx")
def get(self,request,*args,**kwargs): campo = int(request.GET.get('campo')) query = Datos.objects.filter(edad = campo) wb = Workbook() bandera = True cont = 1 controlador = 4 for q in query: if bandera: ws = wb.active ws.title = 'Hoja'+str(cont) bandera = False else: ws = wb.create_sheet('Hoja'+str(cont)) #Crear el título en la hoja ws['B1'].alignment = Alignment(horizontal = "center",vertical = "center") ws['B1'].border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws['B1'].fill = PatternFill(start_color = '66FFCC', end_color = '66FFCC', fill_type = "solid") ws['B1'].font = Font(name = 'Calibri', size = 12, bold = True) ws['B1'] = 'REPORTE PERSONALIZADO EN EXCEL CON DJANGO' #Cambiar caracteristicas de las celdas ws.merge_cells('B1:E1') ws.row_dimensions[1].height = 25 ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 20 ws.column_dimensions['E'].width = 20 #Crear la cabecera ws['B3'].alignment = Alignment(horizontal = "center", vertical = "center") ws['B3'].border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws['B3'].fill = PatternFill(start_color = '66CFCC', end_color = '66CFCC', fill_type = "solid") ws['B3'].font = Font(name = 'Calibro', size = 10, bold = True) ws['B3'] = 'Nombres' ws['C3'].alignment = Alignment(horizontal = "center", vertical = "center") ws['C3'].border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws['C3'].fill = PatternFill(start_color = '66CFCC', end_color = '66CFCC', fill_type = "solid") ws['C3'].font = Font(name = 'Calibro', size = 10, bold = True) ws['C3'] = 'Apellidos' ws['D3'].alignment = Alignment(horizontal = "center", vertical = "center") ws['D3'].border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws['D3'].fill = PatternFill(start_color = '66CFCC', end_color = '66CFCC', fill_type = "solid") ws['D3'].font = Font(name = 'Calibro', size = 10, bold = True) ws['D3'] = 'Dirección' ws['E3'].alignment = Alignment(horizontal = "center", vertical = "center") ws['E3'].border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws['E3'].fill = PatternFill(start_color = '66CFCC', end_color = '66CFCC', fill_type = "solid") ws['E3'].font = Font(name = 'Calibro', size = 10, bold = True) ws['E3'] = 'Edad' #Pintamos los datos en el reporte ws.cell(row = controlador, column = 2).alignment = Alignment(horizontal = "center") ws.cell(row = controlador, column = 2).border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws.cell(row = controlador, column = 2).font = Font(name = 'Calibri', size = 8) ws.cell(row = controlador, column = 2).value = q.nombre ws.cell(row = controlador, column = 3).alignment = Alignment(horizontal = "center") ws.cell(row = controlador, column = 3).border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws.cell(row = controlador, column = 3).font = Font(name = 'Calibri', size = 8) ws.cell(row = controlador, column = 3).value = q.apellidos ws.cell(row = controlador, column = 4).alignment = Alignment(horizontal = "center") ws.cell(row = controlador, column = 4).border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws.cell(row = controlador, column = 4).font = Font(name = 'Calibri', size = 8) ws.cell(row = controlador, column = 4).value = q.direccion ws.cell(row = controlador, column = 5).alignment = Alignment(horizontal = "center") ws.cell(row = controlador, column = 5).border = Border(left = Side(border_style = "thin"), right = Side(border_style = "thin"), top = Side(border_style = "thin"), bottom = Side(border_style = "thin") ) ws.cell(row = controlador, column = 5).font = Font(name = 'Calibri', size = 8) ws.cell(row = controlador, column = 5).value = q.edad cont += 1 #Establecer el nombre de mi archivo nombre_archivo = "ReportePersonalizadoExcel.xlsx" #Definir el tipo de respuesta que se va a dar response = HttpResponse(content_type = "application/ms-excel") contenido = "attachment; filename = {0}".format(nombre_archivo) response["Content-Disposition"] = contenido wb.save(response) return response
def get(self, request, *args, **kwargs): today = datetime.datetime.now() today = today.strftime('%Y-%m-%d') query = ProduccionDet.objects.all() wb = Workbook() ws = wb.active ws.tittle = 'Producción Embolsados' #Establer el nombre del archivo nombre_archivo = str(today) + "Reporte Producción Embolslados.xlsx" ws['B1'].alignment = Alignment(horizontal='left', vertical='center') ws['B1'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B1'].font = Font(name='calibri', size=12, bold=True) ws['B1'] = 'Mar Bran S.A. de C.V.' ws.merge_cells('B1:F1') ws['B2'].alignment = Alignment(horizontal='left', vertical='center') ws['B2'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B2'].font = Font(name='calibri', size=12, bold=True) ws['B2'] = 'Innovación, Mejora Continua y Six Sigma' ws.merge_cells('B2:F2') ws['B3'].alignment = Alignment(horizontal='left', vertical='center') ws['B3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B3'].font = Font(name='calibri', size=12, bold=True) ws['B3'] = 'Reporte de Producción Embolsados' ws['G3'].alignment = Alignment(horizontal='left', vertical='center') ws['G3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['G3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['G3'].font = Font(name='calibri', size=12, bold=True) ws['G3'] = 'FECHA' ws['H3'].alignment = Alignment(horizontal='left', vertical='center') ws['H3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['H3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['H3'].font = Font(name='calibri', size=12, bold=True) ws['H3'] = today ws.merge_cells('B3:F3') ws.row_dimensions[1].height = 20 ws.row_dimensions[2].height = 20 ws.row_dimensions[3].height = 20 ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 20 ws.column_dimensions['E'].width = 30 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 60 ws.column_dimensions['H'].width = 60 ws.column_dimensions['G'].width = 20 ws.column_dimensions['J'].width = 60 ws['B6'].alignment = Alignment(horizontal='center', vertical='center') ws['B6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['B6'].font = Font(name='calibri', size=11, bold=True) ws['B6'] = 'Fecha' ws['C6'].alignment = Alignment(horizontal='center', vertical='center') ws['C6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['C6'].font = Font(name='calibri', size=11, bold=True) ws['C6'] = 'Planta' ws['D6'].alignment = Alignment(horizontal='center', vertical='center') ws['D6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['D6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['D6'].font = Font(name='calibri', size=11, bold=True) ws['D6'] = 'Línea' ws['E6'].alignment = Alignment(horizontal='center', vertical='center') ws['E6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['E6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['E6'].font = Font(name='calibri', size=11, bold=True) ws['E6'] = 'Supervisor' ws['F6'].alignment = Alignment(horizontal='center', vertical='center') ws['F6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['F6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['F6'].font = Font(name='calibri', size=11, bold=True) ws['F6'] = 'Turno' ws['G6'].alignment = Alignment(horizontal='center', vertical='center') ws['G6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['G6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['G6'].font = Font(name='calibri', size=11, bold=True) ws['G6'] = 'Plantilla' ws['H6'].alignment = Alignment(horizontal='center', vertical='center') ws['H6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['H6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['H6'].font = Font(name='calibri', size=11, bold=True) ws['H6'] = 'Proc./term.' ws['I6'].alignment = Alignment(horizontal='center', vertical='center') ws['I6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['I6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['I6'].font = Font(name='calibri', size=11, bold=True) ws['I6'] = 'Producto' ws['J6'].alignment = Alignment(horizontal='center', vertical='center') ws['J6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['J6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['J6'].font = Font(name='calibri', size=11, bold=True) ws['J6'] = 'Peso (Lbs)' ws['K6'].alignment = Alignment(horizontal='center', vertical='center') ws['K6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['K6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['K6'].font = Font(name='calibri', size=11, bold=True) ws['K6'] = 'Cantidad (cajas)' ws['L6'].alignment = Alignment(horizontal='center', vertical='center') ws['L6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['L6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['L6'].font = Font(name='calibri', size=11, bold=True) ws['L6'] = 'Resto (lbs)' ws['M6'].alignment = Alignment(horizontal='center', vertical='center') ws['M6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['M6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['M6'].font = Font(name='calibri', size=11, bold=True) ws['M6'] = 'Producción (lbs)' ws['N6'].alignment = Alignment(horizontal='center', vertical='center') ws['N6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['N6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['N6'].font = Font(name='calibri', size=11, bold=True) ws['N6'] = 'Merma(%)' controlador = 7 for q in query: id_enc = q.produccion_id query2 = ProduccionEnc.objects.filter(id=id_enc) for x in query2: ws.cell(row=controlador, column=2).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=2).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=2).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=2).value = x.fecha_produccion ws.cell(row=controlador, column=3).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=3).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=3).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=3).value = str(x.planta) response = HttpResponse(content_type='application/ms-excel') contenido = "attachment; filename = {0}".format(nombre_archivo) response["Content-Disposition"] = contenido wb.save(response) return response
def set_border(): bd = Side(style='thin', color="000000") #设置单元格边框为细线,颜色为黑色 border = Border(left=bd, top=bd, right=bd, bottom=bd) #设置上下左右边框 return border
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000') ) b_border = Border( bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name+' 报告期消耗' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '能耗' ws['B8'].border = f_border ws['B9'].font = title_font ws['B9'].alignment = c_c_alignment ws['B9'] = '单位面积值' ws['B9'].border = f_border ws['B10'].font = title_font ws['B10'].alignment = c_c_alignment ws['B10'] = '环比' ws['B10'].border = f_border col = 'B' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '7'].fill = table_fill ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = round(reporting_period_data['subtotals_per_unit_area'][i], 2) ws[col + '9'].border = f_border ws[col + '10'].font = name_font ws[col + '10'].alignment = c_c_alignment ws[col + '10'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '10'].border = f_border # TCE TCO2E end_col = col # TCE tce_col = chr(ord(end_col) + 1) ws[tce_col + '7'].fill = table_fill ws[tce_col + '7'].font = name_font ws[tce_col + '7'].alignment = c_c_alignment ws[tce_col + '7'] = "吨标准煤 (TCE)" ws[tce_col + '7'].border = f_border ws[tce_col + '8'].font = name_font ws[tce_col + '8'].alignment = c_c_alignment ws[tce_col + '8'] = round(reporting_period_data['total_in_kgce'] / 1000, 2) ws[tce_col + '8'].border = f_border ws[tce_col + '9'].font = name_font ws[tce_col + '9'].alignment = c_c_alignment ws[tce_col + '9'] = round(reporting_period_data['total_in_kgce_per_unit_area'] / 1000, 2) ws[tce_col + '9'].border = f_border ws[tce_col + '10'].font = name_font ws[tce_col + '10'].alignment = c_c_alignment ws[tce_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgce'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate_in_kgce'] is not None else "-" ws[tce_col + '10'].border = f_border # TCO2E tco2e_col = chr(ord(end_col) + 2) ws[tco2e_col + '7'].fill = table_fill ws[tco2e_col + '7'].font = name_font ws[tco2e_col + '7'].alignment = c_c_alignment ws[tco2e_col + '7'] = "吨二氧化碳排放 (TCO2E)" ws[tco2e_col + '7'].border = f_border ws[tco2e_col + '8'].font = name_font ws[tco2e_col + '8'].alignment = c_c_alignment ws[tco2e_col + '8'] = round(reporting_period_data['total_in_kgco2e'] / 1000, 2) ws[tco2e_col + '8'].border = f_border ws[tco2e_col + '9'].font = name_font ws[tco2e_col + '9'].alignment = c_c_alignment ws[tco2e_col + '9'] = round(reporting_period_data['total_in_kgco2e_per_unit_area'] / 1000, 2) ws[tco2e_col + '9'].border = f_border ws[tco2e_col + '10'].font = name_font ws[tco2e_col + '10'].alignment = c_c_alignment ws[tco2e_col + '10'] = str(round(reporting_period_data['increment_rate_in_kgco2e'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate_in_kgco2e'] is not None else "-" ws[tco2e_col + '10'].border = f_border else: for i in range(6, 10 + 1): ws.row_dimensions[i].height = 0.1 ################################################# has_ele_peak_flag = True if "toppeaks" not in reporting_period_data.keys() or \ reporting_period_data['toppeaks'] is None or \ len(reporting_period_data['toppeaks']) == 0: has_ele_peak_flag = False if has_ele_peak_flag: ws['B12'].font = title_font ws['B12'] = name+' 分时电耗' ws['B13'].fill = table_fill ws['B13'].font = name_font ws['B13'].alignment = c_c_alignment ws['B13'].border = f_border ws['C13'].fill = table_fill ws['C13'].font = name_font ws['C13'].alignment = c_c_alignment ws['C13'].border = f_border ws['C13'] = '分时电耗' ws['B14'].font = title_font ws['B14'].alignment = c_c_alignment ws['B14'] = '尖' ws['B14'].border = f_border ws['C14'].font = title_font ws['C14'].alignment = c_c_alignment ws['C14'].border = f_border ws['C14'] = round(reporting_period_data['toppeaks'][0], 2) ws['B15'].font = title_font ws['B15'].alignment = c_c_alignment ws['B15'] = '峰' ws['B15'].border = f_border ws['C15'].font = title_font ws['C15'].alignment = c_c_alignment ws['C15'].border = f_border ws['C15'] = round(reporting_period_data['onpeaks'][0], 2) ws['B16'].font = title_font ws['B16'].alignment = c_c_alignment ws['B16'] = '平' ws['B16'].border = f_border ws['C16'].font = title_font ws['C16'].alignment = c_c_alignment ws['C16'].border = f_border ws['C16'] = round(reporting_period_data['midpeaks'][0], 2) ws['B17'].font = title_font ws['B17'].alignment = c_c_alignment ws['B17'] = '谷' ws['B17'].border = f_border ws['C17'].font = title_font ws['C17'].alignment = c_c_alignment ws['C17'].border = f_border ws['C17'] = round(reporting_period_data['offpeaks'][0], 2) pie = PieChart() pie.title = name + ' 分时电耗' labels = Reference(ws, min_col=2, min_row=14, max_row=17) pie_data = Reference(ws, min_col=3, min_row=13, max_row=17) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 ################################################ current_row_number = 19 has_kgce_data_flag = True if "subtotals_in_kgce" not in reporting_period_data.keys() or \ reporting_period_data['subtotals_in_kgce'] is None or \ len(reporting_period_data['subtotals_in_kgce']) == 0: has_kgce_data_flag = False if has_kgce_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 吨标准煤 (TCE) 占比' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '吨标准煤 (TCE) 占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgce'][i] / 1000, 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' ' + ws.cell(column=3, row=table_start_row_number).value labels = Reference(ws, min_col=2, min_row=table_start_row_number+1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 7.25 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'D' + str(table_start_row_number) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 current_row_number += 1 ##################################################### has_kgco2e_data_flag = True if "subtotals_in_kgco2e" not in reporting_period_data.keys() or \ reporting_period_data['subtotals_in_kgco2e'] is None or \ len(reporting_period_data['subtotals_in_kgco2e']) == 0: has_kgco2e_data_flag = False if has_kgco2e_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 吨二氧化碳排放 (TCO2E) 占比' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 75 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '吨二氧化碳排放 (TCO2E) 占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgco2e'][i] / 1000, 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' ' + ws.cell(column=3, row=table_start_row_number).value labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 7.75 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'D' + str(table_start_row_number) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 current_row_number += 1 ############################################### has_detail_data_flag = True table_start_draw_flag = current_row_number + 1 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] ca_len = len(report['reporting_period']['names']) real_timestamps_len = timestamps_data_not_equal_0(report['parameters']['timestamps']) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' table_start_row_number = (current_row_number + 2) + ca_len * 6 + real_timestamps_len * 7 current_row_number = table_start_row_number time = times[0] has_data = False if len(time) > 0: has_data = True if has_data: ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '日期时间' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = reporting_period_data['names'][i] + \ " (" + reporting_period_data['units'][i] + ")" ws[col + str(current_row_number)].border = f_border current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = time[i] ws['B' + str(current_row_number)].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) ws[col + str(current_row_number)].border = f_border current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = '小计' ws['B' + str(current_row_number)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)] = round(reporting_period_data['subtotals'][i], 2) ws[col + str(current_row_number)].border = f_border # line line = LineChart() line.title = '报告期消耗 - ' + ws.cell(column=3+i, row=table_start_row_number).value labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(table_start_draw_flag + 6 * i) ws.add_chart(line, chart_cell) current_row_number += 2 ######################################## has_child_flag = True if "child_space" not in report.keys() or "energy_category_names" not in report['child_space'].keys() or \ len(report['child_space']["energy_category_names"]) == 0 \ or 'child_space_names_array' not in report['child_space'].keys() \ or report['child_space']['energy_category_names'] is None \ or len(report['child_space']['child_space_names_array']) == 0 \ or len(report['child_space']['child_space_names_array'][0]) == 0: has_child_flag = False if has_child_flag: child = report['child_space'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 子空间数据' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '子空间' ca_len = len(child['energy_category_names']) col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = child['energy_category_names'][i] + ' (' + child['units'][i] + ')' space_len = len(child['child_space_names_array'][0]) for i in range(0, space_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = child['child_space_names_array'][0][i] ws['B' + row].border = f_border col = '' for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = name_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(child['subtotals_array'][j][i], 2) ws[col + row].border = f_border table_end_row_number = current_row_number current_row_number += 1 chart_start_row_number = current_row_number # Pie for i in range(0, ca_len): pie = PieChart() labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 8 pie.title = ws.cell(column=3 + i, row=table_start_row_number).value s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True chart_cell = '' if i % 2 == 0: chart_cell = 'B' + str(chart_start_row_number) else: chart_cell = 'E' + str(chart_start_row_number) chart_start_row_number += 5 ws.add_chart(pie, chart_cell) current_row_number = chart_start_row_number if ca_len % 2 == 1: current_row_number += 5 current_row_number += 1 ########################################## current_sheet_parameters_row_number = table_start_draw_flag + ca_len * 6 + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 'B' for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].border = f_border col = chr(ord(table_current_col_number) + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = table_current_col_number parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = chr(ord(col) + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = chr(ord(table_current_col_number) + 3) ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def ler_projeto_id(id_arquivo): Cell_NomeDoProjeto = "A2" Cell_VP = "D2" Cell_Formula_Fase = "E2" Cell_AN = "F2" Cell_GP = "G2" Cell_LT = "H2" Cell_Lider_Teste = "I2" Cell_Gerente_Desenvolvimento = "J2" Cell_Formula_Status_Projeto = "K2" Cell_Descricao = "A4" Cell_Nome_Arquivo = "A9" Cell_Inicio_Desenv = "C9" Cell_Termino_Desenv = "D9" Cell_Complitude1 = "E9" Cell_Inicio_Teste_Integrado = "F9" Cell_Termino_Teste_Integrado = "G9" Cell_Complitude2 = "H9" Cell_Inicio_HML = "I9" Cell_Termino_HML = "J9" Cell_Complitude3 = "K9" Cell_Problemas_Riscos = "A12" Cell_SubCausa = "E12" Cell_Plano_de_Acao = "G12" requested = "Requested" at_risk = "At Risk" delayed = "Delayed" fora_pipeline = "fora pipeline" juliana = "Juliana Alves Castro Perez" leonardo = "Leonardo Augusto Mendes Leandro" thales = "Thales Antonio Silva De Freitas" fase1 = "Fase 1" fase2 = "Fase 2" fase3 = "Fase 3" fase4 = "Fase 4" parado = "Parado" formulafase = "X" LinhaInicial = 14 print("Lendo projeto ...") BASE_DIR = os.path.dirname(os.path.abspath(__file__)) db_path = os.path.join(BASE_DIR, "Projetos.db") conn = sqlite3.connect(db_path) cursor = conn.cursor() cursor.execute(""" SELECT Nome_Projeto, VP, Formula_Fase, an, gp, LT, Lider_Teste, Gerente_Desenv, Formula_Status_Projeto, Descricao, Nome_Arquivo, Ini_desenv, Term_desenv, Completude1, Ini_Teste_Integrado, Term_Teste_Integrado, Completude2, Ini_hml, Fim_hml, Completude3, Problema_Risco, SubCausa, Plano_Acao, causa FROM projetos WHERE Gerente_Desenv in (?, ?, ?) AND Formula_Status_Projeto not in (?, ?) """,(juliana,leonardo,thales,requested,fora_pipeline,)) i = 0 wbTemplate = load_workbook('Template.xlsx') wbTempSource = wbTemplate.active thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for Nome_Projeto, VP, Formula_Fase, an, gp, LT, Lider_Teste, Gerente_Desenv, Formula_Status_Projeto, Descricao, Nome_Arquivo, Ini_desenv, Term_desenv, Completude1, Ini_Teste_Integrado, Term_Teste_Integrado, Completude2, Ini_hml, Fim_hml, Completude3, Problema_Risco, SubCausa, Plano_Acao, causa in cursor.fetchall(): i = i + 1 wsTemplate = wbTemplate['Template'] #print (wbTemplate.get_sheet_names()) wsCopia = wbTemplate.copy_worksheet(wbTempSource) if (str(Formula_Fase).strip() == fase1): formulafase = "F1" elif (str(Formula_Fase).strip() == fase2): formulafase = "F2" elif (str(Formula_Fase).strip() == fase3): formulafase = "F3" elif (str(Formula_Fase).strip() == fase4): formulafase = "F4" else: formulafase = "XX" if (Gerente_Desenv == leonardo): wsCopia.title = formulafase + "L"+str(i) elif (Gerente_Desenv == thales): wsCopia.title = formulafase + "T"+str(i) else: wsCopia.title = formulafase + "J"+str(i) if (Formula_Status_Projeto == at_risk): wsCopia.sheet_properties.tabColor = "FFFF00" elif (Formula_Status_Projeto == delayed): wsCopia.sheet_properties.tabColor = "FF0000" Prob_risco_upper = str(Problema_Risco).upper() intPalavraAmbiente = Prob_risco_upper.find("AMBIENTE") if (intPalavraAmbiente > 0): #pinta de roxo wsCopia.sheet_properties.tabColor = "660066" print (i,Nome_Projeto) wsCopia[Cell_NomeDoProjeto] = Nome_Projeto wsCopia[Cell_VP] = VP wsCopia[Cell_Formula_Fase] = Formula_Fase wsCopia[Cell_AN] = an wsCopia[Cell_GP] = gp wsCopia[Cell_LT] = LT wsCopia[Cell_Lider_Teste] = Lider_Teste wsCopia[Cell_Gerente_Desenvolvimento] = Gerente_Desenv wsCopia[Cell_Formula_Status_Projeto] = Formula_Status_Projeto wsCopia[Cell_Descricao] = Descricao wsCopia[Cell_Nome_Arquivo] = Nome_Arquivo wsCopia[Cell_Inicio_Desenv] = Ini_desenv wsCopia[Cell_Termino_Desenv] = Term_desenv wsCopia[Cell_Complitude1] = Completude1 wsCopia[Cell_Inicio_Teste_Integrado] = Ini_Teste_Integrado wsCopia[Cell_Termino_Teste_Integrado] = Term_Teste_Integrado wsCopia[Cell_Complitude2] = Completude2 wsCopia[Cell_Inicio_HML] = Ini_hml wsCopia[Cell_Termino_HML] = Fim_hml wsCopia[Cell_Complitude3] = Completude3 wsCopia[Cell_Problemas_Riscos] = Problema_Risco wsCopia[Cell_SubCausa] = SubCausa wsCopia[Cell_Plano_de_Acao] = Plano_Acao ListaDeRmsParaOProjeto = RetornaRms(Nome_Projeto) LinhaInicial = 14 for row in ListaDeRmsParaOProjeto: CellRM = "A"+str(LinhaInicial) CellResumo = "B"+str(LinhaInicial) CellResp = "C"+str(LinhaInicial) CellDataCriacao = "D"+str(LinhaInicial) CellDataComite = "E"+str(LinhaInicial) CellSistema = "F"+str(LinhaInicial) CellDataInicioTI = "G"+str(LinhaInicial) CellDataFimTI = "H"+str(LinhaInicial) CellDataIniHML = "I"+str(LinhaInicial) CellDataFimHML = "J"+str(LinhaInicial) CellStatus = "K"+str(LinhaInicial) wsCopia[CellRM] = row[0] wsCopia[CellResumo] = row[1] wsCopia[CellResp] = row[2] wsCopia[CellDataCriacao] = str(row[3])[0:10] wsCopia[CellDataComite] = str(row[4])[0:10] wsCopia[CellSistema] = row[5] wsCopia[CellDataInicioTI] = str(row[6])[0:10] wsCopia[CellDataFimTI] = str(row[7])[0:10] wsCopia[CellDataIniHML] = str(row[8])[0:10] wsCopia[CellDataFimHML] = str(row[9])[0:10] wsCopia[CellStatus] =row[10] wsCopia[CellRM].border = thin_border LinhaInicial = LinhaInicial + 1 wsCopia.sheet_view.showGridLines = False #print (wbTemplate.get_sheet_names()) wbTemplate.save("ProjetosLidos10.xlsx") #print (wbTemplate.get_sheet_names()) if conn: conn.close()
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) # data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################################# # First: 统计分析 # 6: title # 7: table title # 8~ca_len table_data ################################################# reporting_period_data = report['reporting_period'] has_energy_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_energy_data_flag = False filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename if has_energy_data_flag: ws['B6'].font = title_font ws['B6'] = name + ' 统计分析' category = reporting_period_data['names'] # table_title ws['B7'].fill = table_fill ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期' ws['B7'].border = f_border ws['C7'].font = title_font ws['C7'].alignment = c_c_alignment ws['C7'] = '算术平均数' ws['C7'].border = f_border ws['D7'].font = title_font ws['D7'].alignment = c_c_alignment ws['D7'] = '中位数' ws['D7'].border = f_border ws['E7'].font = title_font ws['E7'].alignment = c_c_alignment ws['E7'] = '最小值' ws['E7'].border = f_border ws['F7'].font = title_font ws['F7'].alignment = c_c_alignment ws['F7'] = '最大值' ws['F7'].border = f_border ws['G7'].font = title_font ws['G7'].alignment = c_c_alignment ws['G7'] = '样本标准差' ws['G7'].border = f_border ws['H7'].font = title_font ws['H7'].alignment = c_c_alignment ws['H7'] = '样本方差' ws['H7'].border = f_border # table_data for i, value in enumerate(category): row = i * 2 + 8 ws['B' + str(row)].font = name_font ws['B' + str(row)].alignment = c_c_alignment ws['B' + str(row)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + " )" ws['B' + str(row)].border = f_border ws['B' + str(row + 1)].font = name_font ws['B' + str(row + 1)].alignment = c_c_alignment ws['B' + str(row + 1)] = "环比" ws['B' + str(row + 1)].border = f_border ws['C' + str(row)].font = name_font ws['C' + str(row)].alignment = c_c_alignment ws['C' + str(row)] = round(reporting_period_data['means'][i], 2) \ if reporting_period_data['means'][i] is not None else '' ws['C' + str(row)].border = f_border ws['C' + str(row)].number_format = '0.00' ws['C' + str(row + 1)].font = name_font ws['C' + str(row + 1)].alignment = c_c_alignment ws['C' + str(row + 1)] = str(round(reporting_period_data['means_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['means_increment_rate'][i] is not None else '0.00%' ws['C' + str(row + 1)].border = f_border ws['D' + str(row)].font = name_font ws['D' + str(row)].alignment = c_c_alignment ws['D' + str(row)] = round(reporting_period_data['medians'][i], 2) \ if reporting_period_data['medians'][i] is not None else '' ws['D' + str(row)].border = f_border ws['D' + str(row)].number_format = '0.00' ws['D' + str(row + 1)].font = name_font ws['D' + str(row + 1)].alignment = c_c_alignment ws['D' + str(row + 1)] = str(round(reporting_period_data['medians_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['medians_increment_rate'][i] is not None else '0.00%' ws['D' + str(row + 1)].border = f_border ws['E' + str(row)].font = name_font ws['E' + str(row)].alignment = c_c_alignment ws['E' + str(row)] = round(reporting_period_data['minimums'][i], 2) \ if reporting_period_data['minimums'][i] is not None else '' ws['E' + str(row)].border = f_border ws['E' + str(row)].number_format = '0.00' ws['E' + str(row + 1)].font = name_font ws['E' + str(row + 1)].alignment = c_c_alignment ws['E' + str(row + 1)] = str(round(reporting_period_data['minimums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['minimums_increment_rate'][i] is not None else '0.00%' ws['E' + str(row + 1)].border = f_border ws['F' + str(row)].font = name_font ws['F' + str(row)].alignment = c_c_alignment ws['F' + str(row)] = round(reporting_period_data['maximums'][i], 2) \ if reporting_period_data['maximums'][i] is not None else '' ws['F' + str(row)].border = f_border ws['F' + str(row)].number_format = '0.00' ws['F' + str(row + 1)].font = name_font ws['F' + str(row + 1)].alignment = c_c_alignment ws['F' + str(row + 1)] = str(round(reporting_period_data['maximums_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['maximums_increment_rate'][i] is not None else '0.00%' ws['F' + str(row + 1)].border = f_border ws['G' + str(row)].font = name_font ws['G' + str(row)].alignment = c_c_alignment ws['G' + str(row)] = round(reporting_period_data['stdevs'][i], 2) \ if reporting_period_data['stdevs'][i] is not None else '' ws['G' + str(row)].border = f_border ws['G' + str(row)].number_format = '0.00' ws['G' + str(row + 1)].font = name_font ws['G' + str(row + 1)].alignment = c_c_alignment ws['G' + str(row + 1)] = str(round(reporting_period_data['stdevs_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['stdevs_increment_rate'][i] is not None else '0.00%' ws['G' + str(row + 1)].border = f_border ws['H' + str(row)].font = name_font ws['H' + str(row)].alignment = c_c_alignment ws['H' + str(row)] = round(reporting_period_data['variances'][i], 2) \ if reporting_period_data['variances'][i] is not None else '' ws['H' + str(row)].border = f_border ws['H' + str(row)].number_format = '0.00' ws['H' + str(row + 1)].font = name_font ws['H' + str(row + 1)].alignment = c_c_alignment ws['H' + str(row + 1)] = str(round(reporting_period_data['variances_increment_rate'][i] * 100, 2)) + "%" \ if reporting_period_data['variances_increment_rate'][i] is not None else '0.00%' ws['H' + str(row + 1)].border = f_border ################################################# # Second: 报告期消耗 # 9 + ca_len * 2: title # 10 + ca_len * 2: table title # per_unit_area_start_row_number + 2 ~ per_unit_area_start_row_number + 2 + ca_len : table_data ################################################# if has_energy_data_flag: names = reporting_period_data['names'] ca_len = len(names) per_unit_area_start_row_number = 9 + ca_len * 2 ws['B' + str(per_unit_area_start_row_number)].font = title_font ws['B' + str(per_unit_area_start_row_number)] = name + ' 单位面积值' + str( report['shopfloor']['area']) + 'M²' category = reporting_period_data['names'] # table_title ws['B' + str(per_unit_area_start_row_number + 1)].fill = table_fill ws['B' + str(per_unit_area_start_row_number + 1)].font = title_font ws['B' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['B' + str(per_unit_area_start_row_number + 1)] = '报告期' ws['B' + str(per_unit_area_start_row_number + 1)].border = f_border ws['C' + str(per_unit_area_start_row_number + 1)].font = title_font ws['C' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['C' + str(per_unit_area_start_row_number + 1)] = '算术平均数' ws['C' + str(per_unit_area_start_row_number + 1)].border = f_border ws['D' + str(per_unit_area_start_row_number + 1)].font = title_font ws['D' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['D' + str(per_unit_area_start_row_number + 1)] = '中位数' ws['D' + str(per_unit_area_start_row_number + 1)].border = f_border ws['E' + str(per_unit_area_start_row_number + 1)].font = title_font ws['E' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['E' + str(per_unit_area_start_row_number + 1)] = '最小值' ws['E' + str(per_unit_area_start_row_number + 1)].border = f_border ws['F' + str(per_unit_area_start_row_number + 1)].font = title_font ws['F' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['F' + str(per_unit_area_start_row_number + 1)] = '最大值' ws['F' + str(per_unit_area_start_row_number + 1)].border = f_border ws['G' + str(per_unit_area_start_row_number + 1)].font = title_font ws['G' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['G' + str(per_unit_area_start_row_number + 1)] = '样本标准差' ws['G' + str(per_unit_area_start_row_number + 1)].border = f_border ws['H' + str(per_unit_area_start_row_number + 1)].font = title_font ws['H' + str(per_unit_area_start_row_number + 1)].alignment = c_c_alignment ws['H' + str(per_unit_area_start_row_number + 1)] = '样本方差' ws['H' + str(per_unit_area_start_row_number + 1)].border = f_border # table_data for i, value in enumerate(category): row_data = per_unit_area_start_row_number + 2 + i ws['B' + str(row_data)].font = name_font ws['B' + str(row_data)].alignment = c_c_alignment ws['B' + str(row_data)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + "/M²)" ws['B' + str(row_data)].border = f_border ws['C' + str(row_data)].font = name_font ws['C' + str(row_data)].alignment = c_c_alignment if reporting_period_data['means_per_unit_area'][i] \ or reporting_period_data['means_per_unit_area'][i] == 0: ws['C' + str(row_data)] = round( reporting_period_data['means_per_unit_area'][i], 2) ws['C' + str(row_data)].border = f_border ws['C' + str(row_data)].number_format = '0.00' ws['D' + str(row_data)].font = name_font ws['D' + str(row_data)].alignment = c_c_alignment if reporting_period_data['medians_per_unit_area'][i] \ or reporting_period_data['medians_per_unit_area'][i] == 0: ws['D' + str(row_data)] = round( reporting_period_data['medians_per_unit_area'][i], 2) ws['D' + str(row_data)].border = f_border ws['D' + str(row_data)].number_format = '0.00' ws['E' + str(row_data)].font = name_font ws['E' + str(row_data)].alignment = c_c_alignment if reporting_period_data['minimums_per_unit_area'][i] \ or reporting_period_data['minimums_per_unit_area'][i] == 0: ws['E' + str(row_data)] = round( reporting_period_data['minimums_per_unit_area'][i], 2) ws['E' + str(row_data)].border = f_border ws['E' + str(row_data)].number_format = '0.00' ws['F' + str(row_data)].font = name_font ws['F' + str(row_data)].alignment = c_c_alignment if reporting_period_data['maximums_per_unit_area'][i] \ or reporting_period_data['maximums_per_unit_area'][i] == 0: ws['F' + str(row_data)] = round( reporting_period_data['maximums_per_unit_area'][i], 2) ws['F' + str(row_data)].border = f_border ws['F' + str(row_data)].number_format = '0.00' ws['G' + str(row_data)].font = name_font ws['G' + str(row_data)].alignment = c_c_alignment if (reporting_period_data['stdevs_per_unit_area'][i]) \ or reporting_period_data['stdevs_per_unit_area'][i] == 0: ws['G' + str(row_data)] = round( reporting_period_data['stdevs_per_unit_area'][i], 2) ws['G' + str(row_data)].border = f_border ws['G' + str(row_data)].number_format = '0.00' ws['H' + str(row_data)].font = name_font ws['H' + str(row_data)].alignment = c_c_alignment if reporting_period_data['variances_per_unit_area'][i] \ or reporting_period_data['variances_per_unit_area'][i] == 0: ws['H' + str(row_data)] = round( reporting_period_data['variances_per_unit_area'][i], 2) ws['H' + str(row_data)].border = f_border ws['H' + str(row_data)].number_format = '0.00' ######################################################## # Third: 详细数据 # detailed_start_row_number~ detailed_start_row_number+time_len: line # detailed_start_row_number+1: table title # i + analysis_end_row_number + 2 + 6 * ca_len~: table_data ######################################################## has_timestamps_flag = True if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_timestamps_flag = False if has_timestamps_flag: timestamps = reporting_period_data['timestamps'][0] values = reporting_period_data['values'] names = reporting_period_data['names'] ca_len = len(names) time_len = len(timestamps) # title line_charts_row_number = 6 * ca_len analysis_end_row_number = 12 + 3 * ca_len detailed_start_row_number = analysis_end_row_number + line_charts_row_number + 1 ws['B' + str(detailed_start_row_number)].font = title_font ws['B' + str(detailed_start_row_number)] = name + ' 详细数据' # table_title ws['B' + str(detailed_start_row_number + 1)].fill = table_fill ws['B' + str(detailed_start_row_number + 1)].font = name_font ws['B' + str(detailed_start_row_number + 1)].alignment = c_c_alignment ws['B' + str(detailed_start_row_number + 1)] = "时间" ws['B' + str(detailed_start_row_number + 1)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(detailed_start_row_number + 1)].font = name_font ws[col + str(detailed_start_row_number + 1)].alignment = c_c_alignment ws[col + str(detailed_start_row_number + 1)] = names[ i] + " - (" + reporting_period_data['units'][i] + ")" ws[col + str(detailed_start_row_number + 1)].border = f_border # table_date for i in range(0, time_len): rows = i + detailed_start_row_number + 2 ws['B' + str(rows)].font = name_font ws['B' + str(rows)].alignment = c_c_alignment ws['B' + str(rows)] = timestamps[i] ws['B' + str(rows)].border = f_border for index in range(0, ca_len): col = chr(ord('C') + index) ws[col + str(rows)].font = name_font ws[col + str(rows)].alignment = c_c_alignment ws[col + str(rows)] = round(values[index][i], 2) ws[col + str(rows)].number_format = '0.00' ws[col + str(rows)].border = f_border # 小计 row_subtotals = detailed_start_row_number + 2 + time_len ws['B' + str(row_subtotals)].font = name_font ws['B' + str(row_subtotals)].alignment = c_c_alignment ws['B' + str(row_subtotals)] = "小计" ws['B' + str(row_subtotals)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(row_subtotals)].font = name_font ws[col + str(row_subtotals)].alignment = c_c_alignment ws[col + str(row_subtotals)] = round( reporting_period_data['subtotals'][i], 2) ws[col + str(row_subtotals)].border = f_border ws[col + str(row_subtotals)].number_format = '0.00' ######################################################## # third: LineChart # LineChart requires data from the detailed data table in the Excel file # so print the detailed data table first and then print LineChart ######################################################## for i in range(0, ca_len): line = LineChart() line.title = "报告期消耗" + " - " + names[ i] + "(" + reporting_period_data['units'][i] + ")" line.style = 10 line.x_axis.majorTickMark = 'in' line.y_axis.majorTickMark = 'in' line.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True times = Reference(ws, min_col=2, min_row=detailed_start_row_number + 2, max_row=detailed_start_row_number + 2 + time_len) line_data = Reference(ws, min_col=3 + i, min_row=detailed_start_row_number + 1, max_row=detailed_start_row_number + 1 + time_len) line.add_data(line_data, titles_from_data=True) line.set_categories(times) ser = line.series[0] ser.marker.symbol = "diamond" chart_col = 'B' chart_cell = str(analysis_end_row_number + 6 * i) ws.add_chart(line, chart_col + chart_cell) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
shiftlist.append(1) else: shiftlist.append(1) elif day.value == "×": shiftlist.append("×") shiftlist = [worker.value] + shiftlist shift[worker.value] = shiftlist sheet.append(shiftlist) wb.save('シフト表.xlsx') for row in sh.rows: for cell in row: if cell.value != None: cell.alignment = Alignment(horizontal='center') cell.border = Border(outline=True, left=Side(style="medium", color="FF000000"), right=Side(style="medium", color="FF000000"), top=Side(style="medium", color="FF000000"), bottom=Side(style="medium", color="FF000000")) for row in sh2.rows: for cell in row: if cell.value != None: cell.alignment = Alignment(horizontal='center') cell.border = Border(outline=True, left=Side(style="medium", color="FF000000"), right=Side(style="medium", color="FF000000"), top=Side(style="medium", color="FF000000"), bottom=Side(style="medium", color="FF000000")) for row in sheet.rows: for cell in row: if cell.value != None:
from openpyxl.styles import Border, Side, Font, Alignment, PatternFill # Заливка серым сплошным цветом fill_1 = PatternFill(fgColor='D9D9D9', fill_type='solid') # Заливка желтым цветом fill_2 = PatternFill(fgColor='F7E2AE', fill_type='solid') # Обрамление черными, тонкими границами border_1 = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) # Основной шрифт font_1 = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') # Повернуть шрифт вертикально alignment_1 = Alignment(text_rotation=180, wrap_text=False, shrink_to_fit=False, indent=0)
def create_workbook(campus_network_id): data_manager = GridDataManager() workbook_name = data_manager.get_workbook_name(campus_network_id) create_workbook_from_db(campus_network_id) workbook = xlrd.open_workbook('/tmp/nita-webapp/temp.xlsx') #print workbook.sheet_names() from yamltoexcel import xls2yaml,yaml2xls xls2yaml_instance = xls2yaml.ExcelToYaml(workbook_name,'./') yaml2xls_instance = yaml2xls.YamlToExcel("") for sheet_name in workbook.sheet_names(): xls2yaml_instance.process_by_sheet(workbook, sheet_name) #group_and_host_vars = OrderedDict() wb = open_workbook() ws = wb.active ws.title = 'base' # Styling value_font = Font(name="Bitstream Charter", size=10) vthin = Side(border_style="thin", color="000000") vborder = Border(top=vthin, left=vthin, right=vthin, bottom=vthin) valignment = Alignment(wrap_text=True) value_style = NamedStyle(name = "value", font = value_font, border = vborder, alignment = valignment) wb.add_named_style(value_style) header_font = Font(name="Bitstream Charter", size=10, bold=True) hthin = Side(border_style="thin", color="000000") hfill = PatternFill(fill_type='solid', fgColor="33bbff") hborder = Border(top=hthin, left=hthin, right=hthin, bottom=hthin) header_style = NamedStyle(name = "header", font = header_font, fill = hfill, border = hborder) wb.add_named_style(header_style) ws.cell(row=1, column=1).value = "host" ws.cell(row=1, column=1).style = "header" ws.cell(row=1, column=2).value = "name" ws.cell(row=1, column=2).style = "header" ws.cell(row=1, column=3).value = "value" ws.cell(row=1, column=3).style = "header" sheet_last_row_index = {} sheet_last_row_index['base'] = 1 yaml2xls_instance.put_border(wb) for host_file in xls2yaml_instance.sheet_data: base_yaml_content = OrderedDict(xls2yaml_instance.sheet_data[host_file]) #base_yaml_content=ordered_dump(OrderedDict(xls2yaml_instance.sheet_data[host_file]), Dumper=yaml.SafeDumper, default_flow_style=False, explicit_start=True) host_name = host_file yaml2xls_instance.parse_yaml_files(wb, ws, base_yaml_content, host_name, sheet_last_row_index) directory = '/tmp/nita-webapp/export' if not os.path.exists(directory): os.makedirs(directory) wb.save('/tmp/nita-webapp/export/'+workbook_name)
def GetBorder(): border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) return border
def test_write_dxf(self): redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), fill_type=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF"), bold=True, italic=True, underline='single', strikethrough=True) medium_blue = Side(border_style='medium', color=Color(colors.BLUE)) blueBorder = Border(left=medium_blue, right=medium_blue, top=medium_blue, bottom=medium_blue) cf = ConditionalFormatting() cf.add( 'A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml( xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="000000FF"></color> </left> <right style="medium"> <color rgb="000000FF"></color> </right> <top style="medium"> <color rgb="000000FF"></color> </top> <bottom style="medium"> <color rgb="000000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
LENGTH_CELL_COL = "F" ASSES_CELL_WIDTH = 50 LENGTH_CELL_WIDTH = 20 GRADE_COL = 0 CLASS_COL = 1 STDNUM_COL = 2 NAME_COL = 3 ASSES_COL = 4 LENGTH_COL = 5 dataFrameList = [] classTextList = [] alignCenter = Alignment(horizontal='center', vertical='center') wrapText = Alignment(vertical="center", wrapText=True) allroundBorder = Border(left=Side(border_style="thin", color='000000'), right=Side(border_style="thin", color='000000'), top=Side(border_style="thin", color='000000'), bottom=Side(border_style="thin", color='000000')) #엑셀 테이블 항목 선택 시 내용 표시 함수 def exlActivateEdit(self): focusedItem = self.exlClassListWidget.currentItem() if (focusedItem is None): self.exlAseEdit.setPlainText("") return if (focusedItem.column() == ASSES_COL): content = self.exlClassListWidget.currentItem().text() self.exlAseEdit.setPlainText(content)
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## reporting_period_data = report['reporting_period'] has_cost_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B5'].font = title_font ws['B5'] = name + ' 报告期收入' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B6'].fill = table_fill ws['B6'].border = f_border ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期收入' ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '环比' ws['B8'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '8'].border = f_border col = chr(ord(col) + 1) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['total'], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '8'].border = f_border else: for i in range(6, 8 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 10 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 收入占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '收入' ws['D' + str(current_row_number)].fill = table_fill ws['D' + str(current_row_number)].font = name_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '收入占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) income_sum = Decimal(0.0) for i in range(0, ca_len): income_sum = round(reporting_period_data['subtotals'][i], 2) + income_sum for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) ws['D' + str(current_row_number)].font = title_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '{:.2%}'.format(round( reporting_period_data['subtotals'][i], 2) / income_sum) if income_sum is not None and \ income_sum != Decimal(0.0) else " " current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' 收入占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'F' + str(table_start_row_number - 1) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(13, 22 + 1): ws.row_dimensions[i].height = 0.1 ############################################# current_row_number = 14 reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) table_row = (current_row_number + 1) + ca_len * 6 + real_timestamps_len * 7 + 1 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border line = LineChart() line.title = \ '报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(current_row_number + 1 + 6 * i) chart_start_row_number = current_row_number ws.add_chart(line, chart_cell) row = str(max_row + 1) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = '小计' ws['B' + row].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = str(max_row + 1) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row )] = '总计 (' + report['reporting_period']['total_unit'] + ')' ws[col + str(table_row)].border = f_border total_sum = 0 for j in range(0, len(time)): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment every_day_sum = reporting_period_values_every_day_sum( reporting_period_data, j, ca_len) total_sum += every_day_sum ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border row = str(table_row + 1 + len(time)) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(total_sum, 2) ws[col + row].border = f_border else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 ##################################### has_associated_equipment_flag = True time_len = len(times[0]) current_row_number = time_len + table_row + 3 if "associated_equipment" not in report.keys() or \ "energy_category_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_category_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_category_names']) for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col_subtotal = chr(ord('C') + ca_len) ws[col_subtotal + str(current_row_number)].fill = table_fill ws[col_subtotal + str(current_row_number)].font = name_font ws[col_subtotal + str(current_row_number)].alignment = c_c_alignment ws[col_subtotal + str(current_row_number)].border = f_border ws[col_subtotal + str(current_row_number )] = '总计 (' + report['reporting_period']['total_unit'] + ')' associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border subtotal = Decimal(0.0) for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border subtotal += associated_equipment['subtotals_array'][j][i] ws[col_subtotal + row].font = title_font ws[col_subtotal + row].alignment = c_c_alignment ws[col_subtotal + row] = round(subtotal, 2) ws[col_subtotal + row].border = f_border print(subtotal) ########################################## current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6 + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws[ 'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 2 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = format_cell.get_column_letter(table_current_col_number) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = format_cell.get_column_letter(table_current_col_number + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = table_current_col_number + 3 ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def mainf(onlinefinishtime,offlinefinishtime,fileName): # 遍历文件夹中所有文件 if fileName[-3:len(fileName)]=="xls": data = xlrd.open_workbook(fileName) table = data.sheets()[0] #将表格内容写入类------------------------线上 #创建新表格 newtable=[] onlinefinishtime=[onlinefinishtime]*int(table.nrows-1) traintype=fileName.split("-")[0]+"-"+fileName.split("-")[1] traintype=[traintype]*int(table.nrows-1) newtablehead=['姓名','身份证','性别','联系电话','工作单位','培训完成日期','培训类别','地市','区县','乡镇街道'] newtable.append(table.col_values(2,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(1,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(3,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(6,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(5,start_rowx=1,end_rowx=None)) newtable.append(onlinefinishtime) newtable.append(traintype) newtable.append(table.col_values(9, start_rowx=1, end_rowx=None))#市 newtable.append(table.col_values(16,start_rowx=1,end_rowx=None))#区县 newtable.append(table.col_values(17, start_rowx=1, end_rowx=None))#街道 #将表格内容写入类: classdata=[] classd=[] for data in newtable: for d in data: classdata.append(d) for i in range(0,table.nrows-1): classd.append(employee(classdata[i],classdata[i+(table.nrows-1)*1],classdata[i+(table.nrows-1)*2],classdata[i+(table.nrows-1)*3],classdata[i+(table.nrows-1)*4],classdata[i+(table.nrows-1)*5],classdata[i+(table.nrows-1)*6],classdata[i+(table.nrows-1)*7],classdata[i+(table.nrows-1)*8],classdata[i+(table.nrows-1)*9])) i+=1 #删除重复的地址 areaname=["江北区","鄞州区","镇海区","象山县","宁波石化开发区","北仑区","奉化区","宁海县","宁波保税区","余姚市","慈溪市","高新技术开发区","杭州湾新区","大榭开发区","东钱湖旅游度假区"] #创建总表 workbook = openpyxl.Workbook() worksheet = workbook.create_sheet(index=0, title="培训数据填报表") # 写入表头 j = 1 font = Font("等线", size=11) alignment = Alignment(horizontal='center', vertical='center') blue_fill = PatternFill(fill_type='solid', fgColor="BDD7EE") border = Border(left=Side(style='thin', color='FF000000'), right=Side(style='thin', color='FF000000'), top=Side(style='thin', color='FF000000'), bottom=Side(style='thin', color='FF000000')) for head in newtablehead: worksheet.cell(1, j).value = head worksheet.cell(1, j).font = font worksheet.cell(1, j).alignment = alignment worksheet.cell(1, j).fill = blue_fill worksheet.cell(1, j).border = border j = j + 1 #写入表格 m=2 for cell in classd: if cell.county in areaname: worksheet.cell(m, 1).value = cell.name worksheet.cell(m, 1).font = font worksheet.cell(m, 1).alignment = alignment worksheet.cell(m, 2).value = cell.id worksheet.cell(m, 2).font = font worksheet.cell(m, 2).alignment = alignment worksheet.cell(m, 3).value = cell.gender worksheet.cell(m, 3).font = font worksheet.cell(m, 3).alignment = alignment worksheet.cell(m, 4).value = cell.phonenum worksheet.cell(m, 4).font = font worksheet.cell(m, 4).alignment = alignment worksheet.cell(m, 5).value = cell.wp worksheet.cell(m, 5).font = font worksheet.cell(m, 5).alignment = alignment worksheet.cell(m, 6).value = cell.finishdata worksheet.cell(m, 6).font = font worksheet.cell(m, 6).alignment = alignment worksheet.cell(m, 7).value = cell.traintype worksheet.cell(m, 7).font = font worksheet.cell(m, 7).alignment = alignment worksheet.cell(m, 8).value = cell.city worksheet.cell(m, 8).font = font worksheet.cell(m, 8).alignment = alignment worksheet.cell(m, 9).value = cell.county worksheet.cell(m, 9).font = font worksheet.cell(m, 9).alignment = alignment worksheet.cell(m, 10).value = cell.street worksheet.cell(m, 10).font = font worksheet.cell(m, 10).alignment = alignment m = m + 1 # 自适应行间距 worksheet.column_dimensions["A"].width = 10.22 worksheet.column_dimensions["B"].width = 21.44 worksheet.column_dimensions["C"].width = 8.22 worksheet.column_dimensions["D"].width = 15.22 worksheet.column_dimensions["E"].width = 38.11 worksheet.column_dimensions["F"].width = 13.11 worksheet.column_dimensions["G"].width = 41.67 worksheet.column_dimensions["H"].width = 10.22 worksheet.column_dimensions["I"].width = 10.33 worksheet.column_dimensions["J"].width = 10.33 workbook.save(fileName[-12:-4]+"-线上" + ".xlsx") #将表格内容写入类------------------线下 #创建新表格 newtable=[] offlinefinishtime=[offlinefinishtime]*int(table.nrows-1) traintype=fileName.split("-")[0]+"-"+fileName.split("-")[1] traintype=[traintype]*int(table.nrows-1) newtablehead=['姓名','身份证','性别','联系电话','工作单位','培训完成日期','培训类别','地市','区县','乡镇街道'] newtable.append(table.col_values(2,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(1,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(3,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(6,start_rowx=1,end_rowx=None)) newtable.append(table.col_values(5,start_rowx=1,end_rowx=None)) newtable.append(offlinefinishtime) newtable.append(traintype) newtable.append(table.col_values(9, start_rowx=1, end_rowx=None))#市 newtable.append(table.col_values(16,start_rowx=1,end_rowx=None))#区县 newtable.append(table.col_values(17, start_rowx=1, end_rowx=None))#街道 #将表格内容写入类: classdata=[] classd=[] for data in newtable: for d in data: classdata.append(d) for i in range(0,table.nrows-1): classd.append(employee(classdata[i],classdata[i+(table.nrows-1)*1],classdata[i+(table.nrows-1)*2],classdata[i+(table.nrows-1)*3],classdata[i+(table.nrows-1)*4],classdata[i+(table.nrows-1)*5],classdata[i+(table.nrows-1)*6],classdata[i+(table.nrows-1)*7],classdata[i+(table.nrows-1)*8],classdata[i+(table.nrows-1)*9])) i+=1 #删除重复的地址 areaname=["江北区","鄞州区","镇海区","象山县","宁波石化开发区","北仑区","奉化区","宁海县","宁波保税区","余姚市","慈溪市","高新技术开发区","杭州湾新区","大榭开发区","东钱湖旅游度假区","宁波国家高新区"] #创建总表 workbook1 = openpyxl.Workbook() worksheet = workbook1.create_sheet(index=0, title="培训数据填报表") #表格格式 # 写入表头 j = 1 font = Font("等线", size=11) alignment = Alignment(horizontal='center', vertical='center') blue_fill = PatternFill(fill_type='solid', fgColor="BDD7EE") border = Border(left=Side(style='thin', color='FF000000'), right=Side(style='thin', color='FF000000'), top=Side(style='thin', color='FF000000'), bottom=Side(style='thin', color='FF000000')) for head in newtablehead: worksheet.cell(1, j).value = head worksheet.cell(1, j).font = font worksheet.cell(1, j).alignment = alignment worksheet.cell(1, j).fill = blue_fill worksheet.cell(1, j).border = border j = j + 1 #写入表格 m=2 for cell in classd: if cell.city == "宁波市": worksheet.cell(m, 1).value = cell.name worksheet.cell(m, 1).font = font worksheet.cell(m, 1).alignment = alignment worksheet.cell(m, 2).value = cell.id worksheet.cell(m, 2).font = font worksheet.cell(m, 2).alignment = alignment worksheet.cell(m, 3).value = cell.gender worksheet.cell(m, 3).font = font worksheet.cell(m, 3).alignment = alignment worksheet.cell(m, 4).value = cell.phonenum worksheet.cell(m, 4).font = font worksheet.cell(m, 4).alignment = alignment worksheet.cell(m, 5).value = cell.wp worksheet.cell(m, 5).font = font worksheet.cell(m, 5).alignment = alignment worksheet.cell(m, 6).value = cell.finishdata worksheet.cell(m, 6).font = font worksheet.cell(m, 6).alignment = alignment worksheet.cell(m, 7).value = cell.traintype worksheet.cell(m, 7).font = font worksheet.cell(m, 7).alignment = alignment worksheet.cell(m, 8).value = cell.city worksheet.cell(m, 8).font = font worksheet.cell(m, 8).alignment = alignment worksheet.cell(m, 9).value = cell.county worksheet.cell(m, 9).font = font worksheet.cell(m, 9).alignment = alignment worksheet.cell(m, 10).value = cell.street worksheet.cell(m, 10).font = font worksheet.cell(m, 10).alignment = alignment m = m + 1 # 自适应行间距 worksheet.column_dimensions["A"].width = 10.22 worksheet.column_dimensions["B"].width = 21.44 worksheet.column_dimensions["C"].width = 8.22 worksheet.column_dimensions["D"].width = 15.22 worksheet.column_dimensions["E"].width = 38.11 worksheet.column_dimensions["F"].width = 13.11 worksheet.column_dimensions["G"].width = 41.67 worksheet.column_dimensions["H"].width = 10.22 worksheet.column_dimensions["I"].width = 10.33 worksheet.column_dimensions["J"].width = 10.33 workbook1.save(fileName[-12:-4]+"-线下"+".xlsx")
def export_threat_modeling(request, pk): if request.method == "POST": #help: https://djangotricks.blogspot.com/2019/02/how-to-export-data-to-xlsx-files.html response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', ) response[ 'Content-Disposition'] = 'attachment; filename={date}-{name}-report.xlsx'.format( date=datetime.now().strftime('%Y-%m-%d'), name=Process.objects.get(pk=pk).name.replace(" ", "_")) workbook = Workbook() # Get active worksheet/tab worksheet = workbook.active worksheet.title = 'Threat_modeling_REPORT' columns = [ 'Asset name', 'Asset type', 'Asset attributes', 'Threats', 'Policy per asset' ] row_num = 1 # Assign the titles for each cell of the header for col_num, column_title in enumerate(columns, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.value = column_title cell.font = Font(name="Times New Roman", size=12, bold=True, color='FF0000') cell.border = Border( left=Side(border_style="thin", color='FF000000'), right=Side(border_style="thin", color='FF000000'), top=Side(border_style="thin", color='FF000000'), bottom=Side(border_style="thin", color='FF000000'), ) assets = Asset.objects.filter(process=Process.objects.get(pk=pk)) attributes = [] threats = [] for asset in assets: attributes.append(Asset_has_attribute.objects.filter(asset=asset)) for list_attribute in attributes: for attribute in list_attribute: attribute = attribute.attribute threats.append( Threat_has_attribute.objects.filter(attribute=attribute)) attributes_list = [] for attribute in attributes: attr_sublist = [] for element in attribute: attr_sublist.append(element.attribute.attribute_value.value) attributes_list.append(attr_sublist) threats_list = [] for threat in threats: threat_sublist = [] for element in threat: threat_sublist.append(element.threat.name) threats_list.append(threat_sublist) controls_per_asset = [] for asset in threats: list_controls = [] for threat in asset: threat = threat.threat controls_per_threat = Threat_has_control.objects.filter( threat=threat) for control in controls_per_threat: control = control.control if control not in list_controls: list_controls.append(control) controls_per_asset.append(list_controls) for asset, attribute, threat, control in zip(assets, attributes_list, threats_list, controls_per_asset): row_num += 1 if not threat: threat0 = '' else: threat0 = str(threat[0]) # Define the data for each cell in the row row = [ asset.name, asset.asset_type.name, str(attribute[0]), threat0, "CIS." + str(control[0].pk) + " - " + str(control[0]) ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.value = cell_value cell.font = Font(name="Times New Roman", size=11, bold=False, color='FF000000') cell.border = Border( left=Side(border_style="thin", color='FF000000'), right=Side(border_style="thin", color='FF000000'), top=Side(border_style="thin", color='FF000000'), bottom=Side(border_style="thin", color='FF000000'), ) count_attr = 0 old_row = row_num while count_attr < len(attribute) - 1: count_attr += 1 row_num += 1 row = ['', '', str(attribute[count_attr]), ''] for col_num, cell_value in enumerate(row, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.value = cell_value cell.font = Font(name="Times New Roman", size=11, bold=False, color='FF000000') cell.border = Border( left=Side(border_style="thin", color='FF000000'), right=Side(border_style="thin", color='FF000000'), top=Side(border_style="thin", color='FF000000'), bottom=Side(border_style="thin", color='FF000000'), ) count_threats = 0 count_controls = 0 row_num = old_row while count_threats < len(threat) - 1 or count_controls < len( control) - 1: row_num += 1 if count_threats < len(threat) - 1 and count_controls < len( control) - 1: count_threats += 1 count_controls += 1 row = [ '', '', '', str(threat[count_threats]), "CIS." + str(control[count_controls].pk) + " - " + str(control[count_controls]) ] elif count_threats < len( threat) - 1 and not count_controls < len(control) - 1: count_threats += 1 row = ['', '', '', str(threat[count_threats]), ''] else: count_controls += 1 row = [ '', '', '', '', "CIS." + str(control[count_controls].pk) + " - " + str(control[count_controls]) ] for col_num, cell_value in enumerate(row, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.value = cell_value cell.font = Font(name="Times New Roman", size=11, bold=False, color='FF000000') cell.border = Border( left=Side(border_style="thin", color='FF000000'), right=Side(border_style="thin", color='FF000000'), top=Side(border_style="thin", color='FF000000'), bottom=Side(border_style="thin", color='FF000000'), ) #Per effettuare il resize delle celle in base a quella più grande dims = {} for row in worksheet.rows: for cell in row: if cell.value: dims[cell.column_letter] = max( (dims.get(cell.column_letter, 0), len(str(cell.value)))) for col, value in dims.items(): worksheet.column_dimensions[col].width = value workbook.save(response) return response
def style_dict_to_named_style(style_dict, number_format=None): """ Change css style (stored in a python dictionary) to openpyxl NamedStyle """ style_and_format_string = str({ 'style_dict': style_dict, 'parent': style_dict.parent, 'number_format': number_format, }) if style_and_format_string not in known_styles: # Font font = Font(bold=style_dict.get('font-weight') == 'bold', color=style_dict.get_color('color', None), size=style_dict.get('font-size')) # Alignment # 坤泽修改 vertical = style_dict.get('vertical-align', 'center') if vertical not in { 'bottom', 'justify', 'distributed', 'top', 'center' }: vertical = 'center' alignment = Alignment(horizontal=style_dict.get( 'text-align', 'general'), vertical=vertical, wrap_text=style_dict.get('white-space', 'nowrap') == 'normal') # Fill bg_color = style_dict.get_color('background-color') fg_color = style_dict.get_color('foreground-color', Color()) fill_type = style_dict.get('fill-type') if bg_color and bg_color != 'transparent': fill = PatternFill(fill_type=fill_type or FILL_SOLID, start_color=bg_color, end_color=fg_color) else: fill = PatternFill() # Border border = Border(left=Side(**get_side(style_dict, 'left')), right=Side(**get_side(style_dict, 'right')), top=Side(**get_side(style_dict, 'top')), bottom=Side(**get_side(style_dict, 'bottom')), diagonal=Side(**get_side(style_dict, 'diagonal')), diagonal_direction=None, outline=Side(**get_side(style_dict, 'outline')), vertical=None, horizontal=None) name = 'Style {}'.format(len(known_styles) + 1) pyxl_style = NamedStyle(name=name, font=font, fill=fill, alignment=alignment, border=border, number_format=number_format) known_styles[style_and_format_string] = pyxl_style return known_styles[style_and_format_string]
def outputs_report(workbook, sfm, spn, dp, fy, qtr, cc): sheet = workbook.active # Insert values in header area. sheet['B2'] = qtr.description sheet['C2'] = fy.financialYear sheet['B3'] = cc.costCentre sheet['E3'] = dp curr = '"$"#,##0_);("$"#,##' # An Excel currency format. wrapped = Alignment(vertical='top', wrap_text=True) row = 6 # Note that openpyxl start row indexing at 1. # Repeat rows at the top. sheet.add_print_title(5) # For each of the service priority numbers, take a subset of the query # and insert values as required. for s in spn: # Where there are no SFMServicePriority objects for the given # servicePriorityNo and financialYear, skip the priority no. if not SFMServicePriority.objects.filter( servicePriorityNo=s, financialYear=fy.financialYear).exists(): continue sfm_metrics = sfm.filter(servicePriorityNo=s) sfm_service_pri = SFMServicePriority.objects.get( servicePriorityNo=s, financialYear=fy.financialYear) ibm_data = IBMData.objects.filter(financialYear=fy.financialYear, servicePriorityID=s, costCentre=cc.costCentre) ibm_ids = set(ibm_data.values_list('ibmIdentifier', flat=True)) gl = GLPivDownload.objects.filter(financialYear=fy.financialYear, costCentre=cc.costCentre, codeID__in=ibm_ids) for k, metric in enumerate(sfm_metrics): # Subquery if k == 0: # First row only of subquery. cell = sheet.cell(column=1, row=row, value=sfm_service_pri.servicePriorityNo) cell.alignment = wrapped cell = sheet.cell(column=2, row=row, value=sfm_service_pri.description) cell.alignment = wrapped cell = sheet.cell(column=3, row=row, value=sfm_service_pri.description2) cell.alignment = wrapped ytd_a = gl.aggregate(Sum('ytdActual')) cell = sheet.cell(column=4, row=row, value=ytd_a['ytdActual__sum']) cell.number_format = curr cell.alignment = wrapped ytd_b = gl.aggregate(Sum('ytdBudget')) cell = sheet.cell(column=5, row=row, value=ytd_b['ytdBudget__sum']) cell.number_format = curr cell.alignment = wrapped cell = sheet.cell(column=6, row=row, value=metric.metricID) cell.alignment = wrapped cell = sheet.cell(column=7, row=row, value=metric.descriptor) cell.alignment = wrapped measure_type_col = { 'Quantity': 8, 'Percentage': 9, 'Hectare': 10, 'Kilometer': 11 } ytd_measure = { 'Quantity': 0, 'Percentage': 0, 'Hectare': 0, 'Kilometer': 0 } quarter_col = { 'Q1 (Jul - Sep)': 16, 'Q2 (Oct - Dec)': 17, 'Q3 (Jan - Mar)': 18, 'Q4 (Apr - Jun)': 19 } measurements_ytd = MeasurementValue.objects.filter( quarter__financialYear=qtr.financialYear, costCentre=cc, sfmMetric=metric, value__isnull=False) for m in measurements_ytd: # Aggregate the YTD measurements of each unit type: ytd_measure[m.measurementType.unit] += m.value # Write all quarter achievements text (columns 12-15) cell = sheet.cell(column=quarter_col[m.quarter.description], row=row, value=m.comment) cell.alignment = wrapped # Write the YTD measurements (columns 7-10): for k, v in ytd_measure.iteritems(): if v > 0: cell = sheet.cell(column=measure_type_col[k], row=row, value=v) cell.alignment = wrapped # Current quarter measurements (columns 11-14) measurements_qtr = MeasurementValue.objects.filter( quarter=qtr, costCentre=cc, sfmMetric=metric, value__isnull=False) if measurements_qtr: for m in measurements_qtr: cell = sheet.cell( column=measure_type_col[m.measurementType.unit] + 4, row=row, value=m.value) cell.alignment = wrapped # Set the row height to 16.5 (22 pixels). sheet.row_dimensions[row].ht = 16.5 row += 1 # Re-apply cell borders in the header row. thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) for c in [sheet.cell(column=col, row=5) for col in range(1, 20)]: c.border = thin_border
# -*- coding: utf8 -*- # step1. 관련 패키지 및 모듈 불러오기 # 이미지는 가져오지 않음 from re import I from selenium import webdriver import time from bs4 import BeautifulSoup as bs import openpyxl import platform from openpyxl.styles import Alignment, Border, Side import re thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # step2. 네이버 뉴스 댓글정보 수집 함수 def get_book_site(url, wait_time=5, delay_time=0.1): wb = openpyxl.Workbook() ws = wb.active # 크롬 드라이버로 해당 url에 접속 if platform.system() == "Windows": driver = webdriver.Chrome( "D:/7.Software/chromedriver_win32/chromedriver") else: driver = webdriver.Chrome(
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## current_row_number = 6 reporting_period_data = report['reporting_period'] has_names_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_names_data_flag = False if has_names_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期消耗' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \ " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '消耗' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 category_dict = group_by_category( reporting_period_data['energy_category_names']) for category_dict_name, category_dict_values in category_dict.items(): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '消耗' current_row_number += 1 for i in category_dict_values: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, 'D' + str(table_start_row_number)) if len(category_dict_values) < 4: current_row_number = current_row_number - len( category_dict_values) + 4 current_row_number += 1 ##################################### has_values_data = True has_timestamps_data = True if 'values' not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_values_data = False if 'timestamps' not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0 or \ len(reporting_period_data['timestamps'][0]) == 0: has_timestamps_data = False if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] real_timestamps_len = timestamps_data_not_equal_0( report['parameters']['timestamps']) ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_row_number = current_row_number current_row_number += ca_len * 6 + real_timestamps_len * 7 + 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '日期时间' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = time[i] col = 'C' for j in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \ if reporting_period_data['values'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '小计' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 2 format_time_width_number = 1.0 min_len_number = 1.0 min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0 if period_type == 'hourly': format_time_width_number = 4.0 min_len_number = 2 min_width_number = 12.0 elif period_type == 'daily': format_time_width_number = 2.5 min_len_number = 4 min_width_number = 14.0 elif period_type == 'monthly': format_time_width_number = 2.1 min_len_number = 4 min_width_number = 12.4 elif period_type == 'yearly': format_time_width_number = 1.5 min_len_number = 5 min_width_number = 11.5 for i in range(0, ca_len): line = LineChart() line.title = '报告期消耗 - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = format_time_width_number * len(time) if len( time) > min_len_number else min_width_number if line.width > 24: line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) ##################################### has_associated_equipment_flag = True if "associated_equipment" not in report.keys() or \ "energy_item_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_item_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_item_names']) for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + str(current_row_number)].fill = table_fill ws[row + str(current_row_number)].font = name_font ws[row + str(current_row_number)].alignment = c_c_alignment ws[row + str(current_row_number)].border = f_border ws[row + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border ########################################## current_sheet_parameters_row_number = chart_start_row_number + 1 has_parameters_names_and_timestamps_and_values_data = True if 'parameters' not in report.keys() or \ report['parameters'] is None or \ 'names' not in report['parameters'].keys() or \ report['parameters']['names'] is None or \ len(report['parameters']['names']) == 0 or \ 'timestamps' not in report['parameters'].keys() or \ report['parameters']['timestamps'] is None or \ len(report['parameters']['timestamps']) == 0 or \ 'values' not in report['parameters'].keys() or \ report['parameters']['values'] is None or \ len(report['parameters']['values']) == 0 or \ timestamps_data_all_equal_0(report['parameters']['timestamps']): has_parameters_names_and_timestamps_and_values_data = False if has_parameters_names_and_timestamps_and_values_data: ############################### # new worksheet ############################### parameters_data = report['parameters'] parameters_names_len = len(parameters_data['names']) parameters_ws = wb.create_sheet('相关参数') parameters_timestamps_data_max_len = \ get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps'])) # Row height parameters_ws.row_dimensions[1].height = 102 for i in range(2, 7 + 1): parameters_ws.row_dimensions[i].height = 42 for i in range(8, parameters_timestamps_data_max_len + 10): parameters_ws.row_dimensions[i].height = 60 # Col width parameters_ws.column_dimensions['A'].width = 1.5 parameters_ws.column_dimensions['B'].width = 25.0 for i in range(3, 12 + parameters_names_len * 3): parameters_ws.column_dimensions[format_cell.get_column_letter( i)].width = 15.0 # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") parameters_ws.add_image(img, 'B1') # Title parameters_ws.row_dimensions[3].height = 60 parameters_ws['B3'].font = name_font parameters_ws['B3'].alignment = b_r_alignment parameters_ws['B3'] = 'Name:' parameters_ws['C3'].border = b_border parameters_ws['C3'].alignment = b_c_alignment parameters_ws['C3'].font = name_font parameters_ws['C3'] = name parameters_ws['D3'].font = name_font parameters_ws['D3'].alignment = b_r_alignment parameters_ws['D3'] = 'Period:' parameters_ws['E3'].border = b_border parameters_ws['E3'].alignment = b_c_alignment parameters_ws['E3'].font = name_font parameters_ws['E3'] = period_type parameters_ws['F3'].font = name_font parameters_ws['F3'].alignment = b_r_alignment parameters_ws['F3'] = 'Date:' parameters_ws['G3'].border = b_border parameters_ws['G3'].alignment = b_c_alignment parameters_ws['G3'].font = name_font parameters_ws[ 'G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local parameters_ws.merge_cells("G3:H3") parameters_ws_current_row_number = 6 parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数' parameters_ws_current_row_number += 1 parameters_table_start_row_number = parameters_ws_current_row_number parameters_ws.row_dimensions[ parameters_ws_current_row_number].height = 80 parameters_ws_current_row_number += 1 table_current_col_number = 'B' for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[table_current_col_number + str(parameters_ws_current_row_number - 1)].border = f_border col = decimal_to_column( column_to_decimal(table_current_col_number) + 1) parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i] table_current_row_number = parameters_ws_current_row_number for j, value in enumerate(list(parameters_data['timestamps'][i])): col = table_current_col_number parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = value col = decimal_to_column(column_to_decimal(col) + 1) parameters_ws[col + str(table_current_row_number)].border = f_border parameters_ws[col + str(table_current_row_number)].font = title_font parameters_ws[ col + str(table_current_row_number)].alignment = c_c_alignment parameters_ws[col + str(table_current_row_number)] = round( parameters_data['values'][i][j], 2) table_current_row_number += 1 table_current_col_number = decimal_to_column( column_to_decimal(table_current_col_number) + 3) ######################################################## # parameters chart and parameters table ######################################################## ws['B' + str(current_sheet_parameters_row_number)].font = title_font ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数' current_sheet_parameters_row_number += 1 chart_start_row_number = current_sheet_parameters_row_number col_index = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue line = LineChart() data_col = 3 + col_index * 3 labels_col = 2 + col_index * 3 col_index += 1 line.title = '相关参数 - ' + \ parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line_data = Reference( parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number, max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number)) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = False line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) current_sheet_parameters_row_number = chart_start_row_number current_sheet_parameters_row_number += 1 ########################################## filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def fm_tso_combine(split_varpd, tsop_start, tso_info_end): #create dataframes of tso abn and fm global fmp fmp = split_varpd fmp.columns = ["FM_Variants"] tsop = pd.DataFrame(tso.iloc[tsop_start:, [0, 5, 6, 7, 8, 9]]) tsop.reset_index(drop=True, inplace=True) #for TSO500, extract protein variant to then combine with gene abnp = [] for i in tsop.iloc[:, 3]: if i.startswith("N"): tso_match = re.search(r"(.*)[(](.*)[)].*", i) pro = tso_match.group(2) abnp.append(pro) else: abnp.append("none") abnpdf = pd.DataFrame(abnp) tsop["protein"] = abnpdf #get rid of none_none in fm dataframe global fmabnp fmabnp = [] for i in fmp.iloc[:, 0]: if i != "none_none": fm_match = re.search(r"(.*)[_[](.*)[]].*", i) pro = fm_match.group(2) fmabnp.append(pro) else: fmabnp.append("none") #get rid of vus fma_novus = [] for i in fmabnp: fma_rvus = i.replace("(VUS) ", '') fma_novus.append(fma_rvus) fmabnpdf = pd.DataFrame(fma_novus) fmp["protein"] = fmabnpdf #get rid of rows with none_none fmp = fmp[~fmp.FM_Variants.str.contains("none_none")] #merge the dfs on the protein using the fm keys only! merge = fmp.merge(tsop, how="left") merge.columns = [ "FM_variant", "Protein in both", "TSO_Gene", "Allele Frequency", "Depth", "P-Dot Notation", "C-Dot Notation", "Consequence" ] tso500genes = [ 'ABL1', 'ABL2', 'ACVR1', 'ACVR1B', 'AKT1', 'AKT2', 'AKT3', 'ALK', 'ALOX12B', 'ANKRD11', 'ANKRD26', 'APC', 'AR', 'ARAF', 'ARFRP1', 'ARID1A', 'ARID1B', 'ARID2', 'ARID5B', 'ASXL1', 'ASXL2', 'ATM', 'ATR', 'ATRX', 'AURKA', 'AURKB', 'AXIN1', 'AXIN2', 'AXL', 'B2M', 'BAP1', 'BARD1', 'BBC3', 'BCL10', 'BCL2', 'BCL2L1', 'BCL2L11', 'BCL2L2', 'BCL6', 'BCOR', 'BCORL1', 'BCR', 'BIRC3', 'BLM', 'BMPR1A', 'BRAF', 'BRCA1', 'BRCA2', 'BRD4', 'BRIP1', 'BTG1', 'BTK', 'C11orf30', 'CALR', 'CARD11', 'CASP8', 'CBFB', 'CBL', 'CCND1', 'CCND2', 'CCND3', 'CCNE1', 'CD274', 'CD276', 'CD74', 'CD79A', 'CD79B', 'CDC73', 'CDH1', 'CDK12', 'CDK4', 'CDK6', 'CDK8', 'CDKN1A', 'CDKN1B', 'CDKN2A', 'CDKN2B', 'CDKN2C', 'CEBPA', 'CENPA', 'CHD2', 'CHD4', 'CHEK1', 'CHEK2', 'CIC', 'CREBBP', 'CRKL', 'CRLF2', 'CSF1R', 'CSF3R', 'CSNK1A1', 'CTCF', 'CTLA4', 'CTNNA1', 'CTNNB1', 'CUL3', 'CUX1', 'CXCR4', 'CYLD', 'DAXX', 'DCUN1D1', 'DDR2', 'DDX41', 'DHX15', 'DICER1', 'DIS3', 'DNAJB1', 'DNMT1', 'DNMT3A', 'DNMT3B', 'DOT1L', 'E2F3', 'EED', 'EGFL7', 'EGFR', 'EIF1AX', 'EIF4A2', 'EIF4E', 'EML4', 'EP300', 'EPCAM', 'EPHA3', 'EPHA5', 'EPHA7', 'EPHB1', 'ERBB2', 'ERBB3', 'ERBB4', 'ERCC1', 'ERCC2', 'ERCC3', 'ERCC4', 'ERCC5', 'ERG', 'ERRFI1', 'ESR1', 'ETS1', 'ETV1', 'ETV4', 'ETV5', 'ETV6', 'EWSR1', 'EZH2', 'FAM123B', 'FAM175A', 'FAM46C', 'FANCA', 'FANCC', 'FANCD2', 'FANCE', 'FANCF', 'FANCG', 'FANCI', 'FANCL', 'FAS', 'FAT1', 'FBXW7', 'FGF1', 'FGF10', 'FGF14', 'FGF19', 'FGF2', 'FGF23', 'FGF3', 'FGF4', 'FGF5', 'FGF6', 'FGF7', 'FGF8', 'FGF9', 'FGFR1', 'FGFR2', 'FGFR3', 'FGFR4', 'FH', 'FLCN', 'FLI1', 'FLT1', 'FLT3', 'FLT4', 'FOXA1', 'FOXL2', 'FOXO1', 'FOXP1', 'FRS2', 'FUBP1', 'FYN', 'GABRA6', 'GATA1', 'GATA2', 'GATA3', 'GATA4', 'GATA6', 'GEN1', 'GID4', 'GLI1', 'GNA11', 'GNA13', 'GNAQ', 'GNAS', 'GPR124', 'GPS2', 'GREM1', 'GRIN2A', 'GRM3', 'GSK3B', 'H3F3A', 'H3F3B', 'H3F3C', 'HGF', 'HIST1H1C', 'HIST1H2BD', 'HIST1H3A', 'HIST1H3B', 'HIST1H3C', 'HIST1H3D', 'HIST1H3E', 'HIST1H3F', 'HIST1H3G', 'HIST1H3H', 'HIST1H3I', 'HIST1H3J', 'HIST2H3A', 'HIST2H3C', 'HIST2H3D', 'HIST3H3', 'HLA-A', 'HLA-B', 'HLA-C', 'HNF1A', 'HNRNPK', 'HOXB13', 'HRAS', 'HSD3B1', 'HSP90AA1', 'ICOSLG', 'ID3', 'IDH1', 'IDH2', 'IFNGR1', 'IGF1', 'IGF1R', 'IGF2', 'IKBKE', 'IKZF1', 'IL10', 'IL7R', 'INHA', 'INHBA', 'INPP4A', 'INPP4B', 'INSR', 'IRF2', 'IRF4', 'IRS1', 'IRS2', 'JAK1', 'JAK2', 'JAK3', 'JUN', 'KAT6A', 'KDM5A', 'KDM5C', 'KDM6A', 'KDR', 'KEAP1', 'KEL', 'KIF5B', 'KIT', 'KLF4', 'KLHL6', 'KMT2B', 'KMT2C', 'KMT2D', 'KMT2A', 'KRAS', 'LAMP1', 'LATS1', 'LATS2', 'LMO1', 'LRP1B', 'LYN', 'LZTR1', 'MAGI2', 'MALT1', 'MAP2K1', 'MAP2K2', 'MAP2K4', 'MAP3K1', 'MAP3K13', 'MAP3K14', 'MAP3K4', 'MAPK1', 'MAPK3', 'MAX', 'MCL1', 'MDC1', 'MDM2', 'MDM4', 'MED12', 'MEF2B', 'MEN1', 'MET', 'MGA', 'MITF', 'MLH1', 'MLL', 'MLL2', 'MLLT3', 'MPL', 'MRE11A', 'MSH2', 'MSH3', 'MSH6', 'MST1', 'MST1R', 'MTOR', 'MUTYH', 'MYB', 'MYC', 'MYCL1', 'MYCN', 'MYD88', 'MYOD1', 'NAB2', 'NBN', 'NCOA3', 'NCOR1', 'NEGR1', 'NF1', 'NF2', 'NFE2L2', 'NFKBIA', 'NKX2-1', 'NKX3-1', 'NOTCH1', 'NOTCH2', 'NOTCH3', 'NOTCH4', 'NPM1', 'NRAS', 'NRG1', 'NSD1', 'NTRK1', 'NTRK2', 'NTRK3', 'NUP93', 'NUTM1', 'PAK1', 'PAK3', 'PAK7', 'PALB2', 'PARK2', 'PARP1', 'PAX3', 'PAX5', 'PAX7', 'PAX8', 'PBRM1', 'PDCD1', 'PDCD1LG2', 'PDGFRA', 'PDGFRB', 'PDK1', 'PDPK1', 'PGR', 'PHF6', 'PHOX2B', 'PIK3C2B', 'PIK3C2G', 'PIK3C3', 'PIK3CA', 'PIK3CB', 'PIK3CD', 'PIK3CG', 'PIK3R1', 'PIK3R2', 'PIK3R3', 'PIM1', 'PLCG2', 'PLK2', 'PMAIP1', 'PMS1', 'PMS2', 'PNRC1', 'POLD1', 'POLE', 'PPARG', 'PPM1D', 'PPP2R1A', 'PPP2R2A', 'PPP6C', 'PRDM1', 'PREX2', 'PRKAR1A', 'PRKCI', 'PRKDC', 'PRSS8', 'PTCH1', 'PTEN', 'PTPN11', 'PTPRD', 'PTPRS', 'PTPRT', 'QKI', 'RAB35', 'RAC1', 'RAD21', 'RAD50', 'RAD51', 'RAD51B', 'RAD51C', 'RAD51D', 'RAD52', 'RAD54L', 'RAF1', 'RANBP2', 'RARA', 'RASA1', 'RB1', 'RBM10', 'RECQL4', 'REL', 'RET', 'RFWD2', 'RHEB', 'RHOA', 'RICTOR', 'RIT1', 'RNF43', 'ROS1', 'RPS6KA4', 'RPS6KB1', 'RPS6KB2', 'RPTOR', 'RUNX1', 'RUNX1T1', 'RYBP', 'SDHA', 'SDHAF2', 'SDHB', 'SDHC', 'SDHD', 'SETBP1', 'SETD2', 'SF3B1', 'SH2B3', 'SH2D1A', 'SHQ1', 'SLIT2', 'SLX4', 'SMAD2', 'SMAD3', 'SMAD4', 'SMARCA4', 'SMARCB1', 'SMARCD1', 'SMC1A', 'SMC3', 'SMO', 'SNCAIP', 'SOCS1', 'SOX10', 'SOX17', 'SOX2', 'SOX9', 'SPEN', 'SPOP', 'SPTA1', 'SRC', 'SRSF2', 'STAG1', 'STAG2', 'STAT3', 'STAT4', 'STAT5A', 'STAT5B', 'STK11', 'STK40', 'SUFU', 'SUZ12', 'SYK', 'TAF1', 'TBX3', 'TCEB1', 'TCF3', 'TCF7L2', 'TERC', 'TERT', 'TET1', 'TET2', 'TFE3', 'TFRC', 'TGFBR1', 'TGFBR2', 'TMEM127', 'TMPRSS2', 'TNFAIP3', 'TNFRSF14', 'TOP1', 'TOP2A', 'TP53', 'TP63', 'TRAF2', 'TRAF7', 'TSC1', 'TSC2', 'TSHR', 'U2AF1', 'VEGFA', 'VHL', 'VTCN1', 'WISP3', 'WT1', 'XIAP', 'XPO1', 'XRCC2', 'YAP1', 'YES1', 'ZBTB2', 'ZBTB7A', 'ZFHX3', 'ZNF217', 'ZNF703', 'ZRSR2' ] #get genes from fm_variants for statistics, see if in tso, and see if not detected yesnogenes = [] for g, p in zip(merge.iloc[:, 0], merge.iloc[:, 5]): gene_only = re.search(r"(.*)[_][[](.*)[]]", g) fmg = gene_only.group(1) if fmg in tso500genes and pd.isna(p): yesnogenes.append("Not detected") else: yesnogenes.append("") yesnogenes_df = pd.DataFrame(yesnogenes) merge["Gene covered by TSO500?"] = yesnogenes_df total_gene_rows = len(merge.iloc[:, 2]) genes_present = merge.iloc[:, 2].count() genes_not_in_tso = total_gene_rows - genes_present accuracy = round((genes_present / total_gene_rows) * 100, 2) #get macro data from dfs and combine global tso_info tso_info = tso.iloc[22:tso_info_end, [0, 1]] tso_info = tso_info.reset_index(drop=1) fm_info = caset.iloc[[0, 2]] fm_info_i = fm_info.reset_index() global fm_info_o fm_info_o = fm_info_i.iloc[:, 1] fm_info_o.loc[2] = "" fm_info_o.loc[3] = "" fm_info_o.loc[4] = "FM Copy Number Changes" fm_info_o = fm_info_o.append(amp_loss_2) fm_info_o = fm_info_o.reset_index(drop=1) global fm_tso fm_tso = pd.concat([fm_info_o, tso_info], axis=1, join="outer") #to save to current file with pd.ExcelWriter(file, engine="openpyxl", mode='a') as writer: fm_tso.to_excel(writer, sheet_name="Python_merge", startrow=0, startcol=0) merge.to_excel(writer, sheet_name="Python_merge", startrow=27, startcol=0) writer.save() #formatting wb2 = openpyxl.load_workbook(file) print(wb2.sheetnames) df1 = wb2["Python_merge"] df1.delete_cols(1, 1) bold14Font = Font(size=14, bold=True) bold = Font(bold=True) border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) yellow_highlight = PatternFill(patternType="solid", fgColor=Color('ffff00')) green_highlight = PatternFill(patternType="solid", fgColor=Color('66FF00')) df1["A27"].fill = yellow_highlight df1["C27"].fill = green_highlight df1["D27"].fill = green_highlight df1["E27"].fill = green_highlight df1["F27"].fill = green_highlight df1["G27"].fill = green_highlight df1["H27"].fill = green_highlight df1["I27"].fill = green_highlight df1["A27"].font = bold14Font df1["C27"].font = bold14Font df1["B2"].font = bold df1["B7"].font = bold df1["B12"].font = bold df1["I21"].font = bold df1["A6"].font = bold df1["A15"].font = bold df1["F1"].font = bold14Font df1["I21"].border = border s = df1["F1:G4"] for i in s: for c in i: c.border = border df1.column_dimensions['A'].width = 35 df1.column_dimensions['B'].width = 30 df1.column_dimensions['C'].width = 22 df1.column_dimensions['D'].width = 13 df1.column_dimensions['E'].width = 10 df1.column_dimensions['F'].width = 28 df1.column_dimensions['G'].width = 25 df1.column_dimensions['H'].width = 20 df1.column_dimensions['I'].width = 25 df1.column_dimensions['J'].width = 15 df1.column_dimensions['K'].width = 15 df1["A1"] = "Case Info" df1["B1"] = "TSO500 Output" df1["C1"] = "" df1["A27"] = "Foundation Medicine" df1["C27"] = "TSO500" df1["D28"] = "Allele Freq" df1["A15"] = "MS and TMB:" df1["A16"] = str(mstmb) df1["F1"] = "Statistics" df1["F2"] = "Variants in FM:" df1["G2"] = total_gene_rows df1["F3"] = "Genes not detected in TSO:" df1["G3"] = genes_not_in_tso df1["F4"] = "Accuracy:" df1["G4"] = accuracy wb2.save(file)
def layout(invoice_details): # ========================================================initialising workbook========================================== wb = openpyxl.load_workbook('Experiment.xlsx') ws = wb.active print("Worksheet created") customer_details = invoice_details['Customer_details'] # =========================================================styles======================================================== ws.column_dimensions['A'].width = 1 ws.column_dimensions['B'].width = 7 ws.column_dimensions['C'].width = 15 ws.column_dimensions['D'].width = 24 ws.column_dimensions['E'].width = 8 ws.column_dimensions['F'].width = 12 ws.column_dimensions['G'].width = 20 ws.column_dimensions['H'].width = 2 ws.row_dimensions[1].height = 42 ws.row_dimensions[2].height = 16 ws.row_dimensions[3].height = 16 ws.row_dimensions[4].height = 22 ws.row_dimensions[5].height = 19 ws.row_dimensions[6].height = 19 ws.row_dimensions[7].height = 16 ws.row_dimensions[8].height = 16 ws.row_dimensions[9].height = 16 ws.row_dimensions[10].height = 16 ws.row_dimensions[11].height = 16 ws.row_dimensions[12].height = 29 for i in range(13, 23): ws.row_dimensions[i].height = 28 ws.row_dimensions[23].height = 15 for i in range(24, 28): ws.row_dimensions[i].height = 16 ws.row_dimensions[28].height = 24 for i in range(29, 33): ws.row_dimensions[i].height = 15 thin = Side(border_style="thin", color="365194") table_border = Side(border_style="thin", color="B2BEB5") thick = Side(border_style="thick", color="365194") data_heading4 = Font(name='Constantia', size=18, color="365194") data_heading3 = Font(name='Franklin Gothic Book', size=10) data_heading2 = Font(name='Franklin Gothic Book', size=12) data_heading1 = Font(name='Constantia', size=28) table_headings = Font(name='Constantia', size=12, bold=True, color="FFFFFF") billing_data_headings = Font(name='Franklin Gothic Book', size=11, color="365194") billing_data = Font(name='Franklin Gothic Book', size=11) ws['B1'].font = data_heading1 ws['B1'].alignment = Alignment(horizontal='left', vertical='bottom') ws['B2'].font = data_heading2 ws['B2'].alignment = Alignment(horizontal='left', vertical='top') ws['B3'].font = data_heading2 ws['B3'].alignment = Alignment(horizontal='left', vertical='top') ws['B4'].font = data_heading2 ws['B4'].alignment = Alignment(horizontal='left', vertical='top') ws['B6'].font = data_heading4 ws['B6'].alignment = Alignment(horizontal='left', vertical='bottom') ws['F6'].font = data_heading4 ws['F6'].alignment = Alignment(horizontal='left', vertical='bottom') ws['F7'].font = data_heading3 ws['F7'].alignment = Alignment(horizontal='left', vertical='bottom') for i in range(7, 12): search_string = "B" + str(i) ws[search_string].font = data_heading2 ws[search_string].alignment = Alignment(vertical='bottom') for i in range(66, 72): search_string2 = chr(i) + "12" ws[search_string2].font = table_headings ws[search_string2].alignment = Alignment(horizontal='center', vertical='center') ws[search_string2].fill = PatternFill("solid", fgColor="365194") ws[search_string2].border = Border(left=table_border, right=table_border, bottom=thin) for i in range(13, 23): for j in range(66, 72): search_string = chr(j) + str(i) ws[search_string].font = data_heading2 ws[search_string].alignment = Alignment(vertical='center', horizontal='center') for i in range(24, 28): search_string6 = "B" + str(i) ws[search_string6].font = data_heading2 ws[search_string6].alignment = Alignment(vertical='center', horizontal='left') search_string3 = "F" + str(i) ws[search_string3].font = billing_data_headings ws[search_string3].alignment = Alignment(vertical='center', horizontal='center') ws['G24'].border = Border(left=thin, right=thin, bottom=table_border) ws['G25'].border = Border(left=thin, right=thin, bottom=table_border) ws['G26'].border = Border(left=thin, right=thin, bottom=table_border) ws['G27'].border = Border(left=thin, right=thin, bottom=table_border) ws['G28'].border = Border(left=thin, right=thin) ws['G28'].fill = PatternFill("solid", fgColor="365194") for i in range(24, 28): search_string4 = "G" + str(i) ws[search_string4].font = billing_data ws[search_string4].alignment = Alignment(vertical='center', horizontal='center') ws['G28'].font = Font(name='Franklin Gothic Book', size=11, color="FFFFFF") ws['G28'].alignment = Alignment(vertical='center', horizontal='center') ws['E28'].font = Font(name='Franklin Gothic Book', size=18, color="365194") ws['E28'].alignment = Alignment(vertical='center', horizontal='center') for i in range(29, 33): search_string = "B" + str(i) ws[search_string].font = data_heading3 ws[search_string].alignment = Alignment(vertical='bottom') for i in range(66, 72): for j in range(len(invoice_details['Items'])): search_string5 = chr(i) + str(13 + j) if j == (len(invoice_details['Items']) - 1): ws[search_string5].border = Border(left=table_border, right=table_border, bottom=thick) if ((12 + j) % 2) == 1: ws[search_string5].fill = PatternFill("solid", fgColor="F5F5F5") else: pass else: ws[search_string5].border = Border(left=table_border, right=table_border, bottom=table_border) if ((12 + j) % 2) == 1: ws[search_string5].fill = PatternFill("solid", fgColor="F5F5F5") else: pass # =========================================================values used in invoice======================================== invoice_number = "Invoice #" + str(invoice_details['Invoice_number']) date = "Date: " + invoice_details['Date'] cgst = invoice_details['CGST'] + "%" sgst = invoice_details['SGST'] + "%" igst = invoice_details['IGST'] + "%" total = 0 for i in invoice_details['Items']: product = int(i['Rate']) * int(i['Qty']) total += product cgst_cal = (int(invoice_details['CGST']) / 100) * total sgst_cal = (int(invoice_details['SGST']) / 100) * total igst_cal = (int(invoice_details['IGST']) / 100) * total final = total + cgst_cal + sgst_cal + igst_cal # =======================================================Values in invoice=============================================== ws['B1'] = "Firm Name" ws['B2'] = "Address of the Firm" ws['B3'] = "Contact Details" ws['B4'] = "GST No. XXXXX-XXXXX" ws['B6'] = "Bill To" ws['F6'] = invoice_number ws['F7'] = date ws['B7'] = str(customer_details['Name']) + " | " + str( customer_details['Phone']) ws['B8'] = "State: " + str(customer_details['Address']) ws['B9'] = "StateCode: " + str(customer_details['Code']) ws['B10'] = "GSTN: " + str(customer_details['GSTN']) ws['B12'] = "S No." ws['C12'] = "HSN Code" ws['D12'] = "Description" ws['E12'] = "Qty." ws['F12'] = "Rate" ws['G12'] = "Amount" ws['B24'] = "Bank Name: Bank Name" ws['B25'] = "Account No.: XXXXXXXXXX" ws['B26'] = "Account Name: Account Name" ws['B27'] = "IFSC: IFSC Code" ws['F24'] = "Subtotal" ws['G24'] = "=SUM(G10:G19)" ws['F25'] = "CGST(" + cgst + ")" ws['G25'] = cgst_cal ws['F26'] = "SGST(" + sgst + ")" ws['G26'] = sgst_cal ws['F27'] = "IGST(" + igst + ")" ws['G27'] = igst_cal ws['E28'] = "Total Cost" ws['G28'] = final ws['B29'] = "Make all checks payable to Company Name" ws['B30'] = "If you have any questions concerning this invoice, use the following contact information:" ws['B31'] = "Contact Details" ws['B32'] = "Thank you for your business!" # ======================================================Item Details in Table============================================ start_row = 13 start_col = 1 serial_no = 1 start_character = 69 for i in range(len(invoice_details['Items'])): product_string = "=PRODUCT(" + chr(start_character) + str( start_row + i) + ", " + chr(start_character + 1) + str(start_row + i) + ")" ws.cell(row=(start_row + i), column=start_col + 1).value = serial_no serial_no += 1 ws.cell(row=start_row + i, column=start_col + 2).value = int( invoice_details['Items'][i]['HSNCode']) ws.cell(row=start_row + i, column=start_col + 3).value = invoice_details['Items'][i]['ItemName'] ws.cell(row=start_row + i, column=start_col + 4).value = int( invoice_details['Items'][i]['Qty']) ws.cell(row=start_row + i, column=start_col + 5).value = int( invoice_details['Items'][i]['Rate']) ws.cell(row=start_row + i, column=start_col + 6).value = product_string # ===============================================merging cells=========================================================== ws.merge_cells('B1:E1') ws.merge_cells('B2:E2') ws.merge_cells('B3:E3') ws.merge_cells('B4:E4') ws.merge_cells('B6:E6') ws.merge_cells('F6:G6') ws.merge_cells('B7:E7') ws.merge_cells('F7:G7') ws.merge_cells('B8:D8') ws.merge_cells('B9:E9') ws.merge_cells('B10:E10') for i in range(24, 28): merge_range = "B" + str(i) + ":D" + str(i) ws.merge_cells(merge_range) ws.merge_cells('E28:F28') for i in range(29, 33): merge_range = "B" + str(i) + ":H" + str(i) ws.merge_cells(merge_range) # ====================================================saving file======================================================== save_name = invoice_number + ".xlsx" wb.save(save_name) os.startfile(save_name, 'print')
b2.value = "FishC" bold_red_font = Font(bold=True, color="FF0000") b2.font = bold_red_font b3 = ws["B3"] b3.value = "Fish" Ita_strike_font = Font(size=16, italic=True, strike=True, color="0000FF") b3.font = Ita_strike_font yellow_fill = PatternFill(fill_type="solid", fgColor="FFFF00") b2.fill = yellow_fill red2gre = GradientFill(fill_type="linear", stop=("FF0000", "00FF00")) b3.fill = red2gre thin_side = Side(border_style="thin", color="000000") double_side = Side(border_style="double", color="FF0000") b2.border = Border(diagonal=thin_side, diagonalUp=True, diagonalDown=True) ws.merge_cells("A1:C2") ws["A1"].value = "FishC" center_align = Alignment(horizontal="center", vertical="center") ws["A1"].alighment = center_align highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) highlight.alignment = Alignment(horizontal="center", vertical="center") wb.add_named_range(highlight) ws["A1"].style = highlight ws["B5"].value = "LOVE" ws["B5"].style = highlight
def getValue(self, name=''): _dict_ = { 1: "RTL00000", 2: "RTL0000", 3: "RTL000", 4: "RTL00", 5: "RTL0", 6: "RTL", } temp = int(name) if len(name) in _dict_: for i in range(1, 14): for j in range(1, 6): self.sheet.cell( i, j ).value = _dict_[len(name)] + str(temp) + self.rutilink temp = temp + 1 word_wrap_string = Alignment(wrapText=True, horizontal="center", vertical='center') double_border_side = Side(border_style='dotted') square_border = Border(top=double_border_side, right=double_border_side, bottom=double_border_side, left=double_border_side) self.sheet.page_margins = PageMargins(left=self.margins_tblr, right=self.margins_tblr, top=self.margins_tblr, bottom=self.margins_tblr) self.sheet.sheet_properties.pageSetUpPr.fitToPage = True self.sheet.print_area = "A1:E13" self.sheet.page_setup = PrintPageSetup( worksheet=self.sheet, orientation='portrait', paperSize=self.sheet.PAPERSIZE_A4, fitToHeight=1, fitToWidth=1, scale=100, horizontalDpi=300, verticalDpi=300) self.sheet.print_options = PrintOptions(horizontalCentered=True, verticalCentered=True) for i in range(1, 6): for j in range(1, 14): self.sheet.cell(j, i).border = square_border self.sheet.cell(j, i).font = self.fontType self.sheet.cell(j, i).alignment = word_wrap_string for cols in self.colsVal: for row in self.rowVal: self.sheet.column_dimensions[cols].width = self.value[1] self.sheet.row_dimensions[row].height = self.value[0] self.wb.save(QDir.homePath() + '/Desktop/' + self.filename) self.wb.close()
size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color="FF000000") fill = PatternFill( # `patternType` alias fill_type=None, # `fgColor` alias start_color="FFFFFFFF", # `bgColor` alias end_color="FF000000", ) border = Border(left=Side(border_style=None, color="FF000000"), right=Side(border_style=None, color="FF000000"), top=Side(border_style=None, color="FF000000"), bottom=Side(border_style=None, color="FF000000"), diagonal=Side(border_style=None, color="FF000000"), diagonal_direction=0, outline=Side(border_style=None, color="FF000000"), vertical=Side(border_style=None, color="FF000000"), horizontal=Side(border_style=None, color="FF000000")) alignment = Alignment(horizontal="general", vertical="bottom", text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) number_format = "General"
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## reporting_period_data = report['reporting_period'] has_cost_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B5'].font = title_font ws['B5'] = name + ' 报告期收入' category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B6'].fill = table_fill ws['B6'].border = f_border ws['B7'].font = title_font ws['B7'].alignment = c_c_alignment ws['B7'] = '报告期收入' ws['B7'].border = f_border ws['B8'].font = title_font ws['B8'].alignment = c_c_alignment ws['B8'] = '环比' ws['B8'].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['subtotals'][i], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['increment_rates'][i] * 100, 2)) + "%" \ if reporting_period_data['increment_rates'][i] is not None else "-" ws[col + '8'].border = f_border col = chr(ord(col) + 1) ws[col + '6'].fill = table_fill ws[col + '6'].font = name_font ws[col + '6'].alignment = c_c_alignment ws[col + '6'] = "总计 (" + reporting_period_data['total_unit'] + ")" ws[col + '6'].border = f_border ws[col + '7'].font = name_font ws[col + '7'].alignment = c_c_alignment ws[col + '7'] = round(reporting_period_data['total'], 2) ws[col + '7'].border = f_border ws[col + '8'].font = name_font ws[col + '8'].alignment = c_c_alignment ws[col + '8'] = str(round(reporting_period_data['total_increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['total_increment_rate'] is not None else "-" ws[col + '8'].border = f_border else: for i in range(6, 8 + 1): ws.row_dimensions[i].height = 0.1 ################################## current_row_number = 10 has_subtotals_data_flag = True if "subtotals" not in reporting_period_data.keys() or \ reporting_period_data['subtotals'] is None or \ len(reporting_period_data['subtotals']) == 0: has_subtotals_data_flag = False if has_subtotals_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 收入占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '收入' ws['D' + str(current_row_number)].fill = table_fill ws['D' + str(current_row_number)].font = name_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '收入占比' current_row_number += 1 ca_len = len(reporting_period_data['names']) wssum = 0 for i in range(0, ca_len): wssum = round(reporting_period_data['subtotals'][i], 2) + wssum for i in range(0, ca_len): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)] = reporting_period_data['names'][i] ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = title_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) ws['D' + str(current_row_number)].font = title_font ws['D' + str(current_row_number)].alignment = c_c_alignment ws['D' + str(current_row_number)].border = f_border ws['D' + str(current_row_number)] = '{:.2%}'.format( round(reporting_period_data['subtotals'][i], 2) / wssum) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = name + ' 收入占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True table_cell = 'F' + str(table_start_row_number - 1) ws.add_chart(pie, table_cell) if ca_len < 4: current_row_number = current_row_number - ca_len + 4 else: for i in range(13, 22 + 1): ws.row_dimensions[i].height = 0.1 ############################################# current_row_number = 14 reporting_period_data = report['reporting_period'] times = reporting_period_data['timestamps'] has_detail_data_flag = True ca_len = len(report['reporting_period']['names']) table_row = (current_row_number + 1) + ca_len * 6 if "timestamps" not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0: has_detail_data_flag = False if has_detail_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' ws.row_dimensions[table_row].height = 60 ws['B' + str(table_row)].fill = table_fill ws['B' + str(table_row)].font = title_font ws['B' + str(table_row)].border = f_border ws['B' + str(table_row)].alignment = c_c_alignment ws['B' + str(table_row)] = '日期时间' time = times[0] has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = table_row + len(time) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row)] = reporting_period_data['names'][ i] + " (" + reporting_period_data['units'][i] + ")" ws[col + str(table_row)].border = f_border # 39 data time = times[i] time_len = len(time) for j in range(0, time_len): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( reporting_period_data['values'][i][j], 2) ws[col + row].border = f_border line = LineChart() line.title = \ '报告期收入 - ' + reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_row + 1, max_row=max_row) line_data = Reference(ws, min_col=3 + i, min_row=table_row, max_row=max_row) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(current_row_number + 1 + 6 * i) ws.add_chart(line, chart_cell) row = str(max_row + 1) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = '小计' ws['B' + row].border = f_border col = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = str(max_row + 1) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['subtotals'][i], 2) ws[col + row].border = f_border col = chr(ord(col) + 1) ws[col + str(table_row)].fill = table_fill ws[col + str(table_row)].font = title_font ws[col + str(table_row)].alignment = c_c_alignment ws[col + str(table_row )] = '总计 (' + report['reporting_period']['total_unit'] + ')' ws[col + str(table_row)].border = f_border total_sum = 0 for j in range(0, len(time)): row = str(table_row + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment every_day_sum = reporting_period_values_every_day_sum( reporting_period_data, j, ca_len) total_sum += every_day_sum ws[col + row] = round(every_day_sum, 2) ws[col + row].border = f_border row = str(table_row + 1 + len(time)) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(total_sum, 2) ws[col + row].border = f_border else: for i in range(37, 69 + 1): ws.row_dimensions[i].height = 0.1 filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def get(self, request, *args, **kwargs): today = datetime.datetime.now() today = today.strftime('%Y-%m-%d') query = TiempoMuertonDet.objects.all() wb = Workbook() ws = wb.active ws.tittle = 'Tiempos Muertos' #Establer el nombre del archivo nombre_archivo = str(today) + "Reporte Tiempos Muertosw.xlsx" ws['B1'].alignment = Alignment(horizontal='left', vertical='center') ws['B1'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B1'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B1'].font = Font(name='calibri', size=12, bold=True) ws['B1'] = 'Mar Bran S.A. de C.V.' ws.merge_cells('B1:F1') ws['B2'].alignment = Alignment(horizontal='left', vertical='center') ws['B2'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B2'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B2'].font = Font(name='calibri', size=12, bold=True) ws['B2'] = 'Innovación, Mejora Continua y Six Sigma' ws.merge_cells('B2:F2') ws['B3'].alignment = Alignment(horizontal='left', vertical='center') ws['B3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['B3'].font = Font(name='calibri', size=12, bold=True) ws['B3'] = 'Reporte de Tiempos Muertos' ws['G3'].alignment = Alignment(horizontal='left', vertical='center') ws['G3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['G3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['G3'].font = Font(name='calibri', size=12, bold=True) ws['G3'] = 'FECHA' ws['H3'].alignment = Alignment(horizontal='left', vertical='center') ws['H3'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['H3'].fill = PatternFill(start_color='66FFCC', end_color='66FFCC', fill_type='solid') ws['H3'].font = Font(name='calibri', size=12, bold=True) ws['H3'] = today ws.merge_cells('B3:F3') ws.row_dimensions[1].height = 20 ws.row_dimensions[2].height = 20 ws.row_dimensions[3].height = 20 ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 20 ws.column_dimensions['E'].width = 30 ws.column_dimensions['F'].width = 20 ws.column_dimensions['G'].width = 60 ws.column_dimensions['H'].width = 60 ws.column_dimensions['G'].width = 20 ws.column_dimensions['J'].width = 60 ws['B6'].alignment = Alignment(horizontal='center', vertical='center') ws['B6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['B6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['B6'].font = Font(name='calibri', size=11, bold=True) ws['B6'] = 'Fecha' ws['C6'].alignment = Alignment(horizontal='center', vertical='center') ws['C6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['C6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['C6'].font = Font(name='calibri', size=11, bold=True) ws['C6'] = 'Planta' ws['D6'].alignment = Alignment(horizontal='center', vertical='center') ws['D6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['D6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['D6'].font = Font(name='calibri', size=11, bold=True) ws['D6'] = 'Línea' ws['E6'].alignment = Alignment(horizontal='center', vertical='center') ws['E6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['E6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['E6'].font = Font(name='calibri', size=11, bold=True) ws['E6'] = 'Supervisor' ws['F6'].alignment = Alignment(horizontal='center', vertical='center') ws['F6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['F6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['F6'].font = Font(name='calibri', size=11, bold=True) ws['F6'] = 'Turno' ws['G6'].alignment = Alignment(horizontal='center', vertical='center') ws['G6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['G6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['G6'].font = Font(name='calibri', size=11, bold=True) ws['G6'] = 'Categoría' ws['H6'].alignment = Alignment(horizontal='center', vertical='center') ws['H6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['H6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['H6'].font = Font(name='calibri', size=11, bold=True) ws['H6'] = 'Causa' ws['I6'].alignment = Alignment(horizontal='center', vertical='center') ws['I6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['I6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['I6'].font = Font(name='calibri', size=11, bold=True) ws['I6'] = 'Tiempo (min)' ws['J6'].alignment = Alignment(horizontal='center', vertical='center') ws['J6'].border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws['J6'].fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws['J6'].font = Font(name='calibri', size=11, bold=True) ws['J6'] = 'Obs' controlador = 7 for q in query: causa = q.causa query3 = CausaTM.objects.filter(descripcion=causa).first() categoria = query3.categoriaTM ws.cell(row=controlador, column=7).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=7).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=7).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=7).value = str(categoria) ws.cell(row=controlador, column=8).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=8).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=8).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=8).value = str(q.causa) ws.cell(row=controlador, column=9).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=9).border = Border(left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=9).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=9).value = q.cantidad ws.cell(row=controlador, column=10).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=10).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=10).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=10).value = q.obs id_enc = q.tiempo_muerto_id query2 = TiempoMuertoEnc.objects.filter(id=id_enc) for x in query2: ws.cell(row=controlador, column=2).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=2).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=2).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=2).value = x.fecha_produccion ws.cell(row=controlador, column=3).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=3).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=3).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=3).value = str(x.planta) ws.cell(row=controlador, column=4).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=4).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=4).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=4).value = str(x.linea) ws.cell(row=controlador, column=5).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=5).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=5).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=5).value = str(x.supervisor) ws.cell(row=controlador, column=6).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=controlador, column=6).border = Border( left=Side(border_style='thin'), right=Side(border_style='thin'), top=Side(border_style='thin'), bottom=Side(border_style='thin')) ws.cell(row=controlador, column=6).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=6).value = str(x.turno) controlador += 1 response = HttpResponse(content_type='application/ms-excel') contenido = "attachment; filename = {0}".format(nombre_archivo) response["Content-Disposition"] = contenido wb.save(response) return response
def render_xlsx(data: dict) -> Workbook: """Render an Excel invoice template. Data is intended to look as follows: { 'invoice_id': str, 'costcenter': str, 'project_number': str, 'customer_id': str, 'customer_name': str, 'agreement': str, 'contact': { 'name': str, 'email': str, 'reference': str, 'customer_name': str, 'address': str }, 'samples': [{ 'name': str, # 'lims_id': str, 'application_tag': str, 'project': str, 'date': str, 'price': int }] } """ pkg_dir = __name__.rpartition(".")[0] sample_type = "pool" if data["pooled_samples"] else "sample" costcenter = data["costcenter"] template_path = resource_filename(pkg_dir, f"templates/{costcenter}_{sample_type}_invoice.xlsx") workbook = load_workbook(template_path) if data["pooled_samples"]: worksheet = workbook["Bilaga Prover"] worksheet["C1"] = costcenter.upper() worksheet["F1"] = f"{data['invoice_id']}-{costcenter}" worksheet["F2"] = dt.datetime.today().date() samples_start = 7 for index, lims_sample in enumerate(data["pooled_samples"]): total_reads = lims_sample.udf.get("Total Reads (M)", "") pool_name = lims_sample.udf.get("pool name", "") row = samples_start + index worksheet[f"A{row}"] = lims_sample.name worksheet[f"B{row}"] = lims_sample.id worksheet[f"C{row}"] = total_reads worksheet[f"D{row}"] = str(pool_name) for column in "ABCDEFG": cell = worksheet[f"{column}{6}"] cell.font = Font(bold=True) cell.border = Border( top=Side(border_style="thin", color="000000"), bottom=Side(border_style="thin", color="000000"), ) cell.fill = PatternFill("solid", fgColor="E5E8E8") worksheet = workbook["Faktura"] worksheet.font = Font(size=14) for row_dimension in worksheet.row_dimensions.values(): row_dimension.height = 30 for col_dimension in worksheet.column_dimensions.values(): if col_dimension.index in "GHI": col_dimension.width = 10 else: col_dimension.width = 18 worksheet["C1"] = costcenter.upper() worksheet["F1"] = f"{data['invoice_id']}-{costcenter}" worksheet["F2"] = dt.datetime.today().date() worksheet["C7"] = data["project_number"] worksheet["C13"] = data["contact"]["name"] worksheet["C14"] = data["contact"]["email"] worksheet["C15"] = data["contact"]["reference"] worksheet["C16"] = data["contact"]["customer_name"] worksheet["C17"] = data["contact"]["address"] worksheet["C20"] = f"{data['customer_id']} / {data['customer_name']}" if data.get("agreement"): worksheet["A21"] = "Avtaltets diarienummer" worksheet["C21"] = data["agreement"] samples_start = 24 for index, record_data in enumerate(data["records"]): row = samples_start + index worksheet[f"A{row}"] = record_data["name"] worksheet[f"B{row}"] = record_data["lims_id"] worksheet[f"C{row}"] = record_data["application_tag"] worksheet[f"D{row}"] = record_data["priority"] worksheet[f"E{row}"] = record_data["project"] worksheet[f"F{row}"] = record_data["date"] worksheet[f"G{row}"] = round(record_data["price"]) if costcenter == "KI": worksheet[f"H{row}"] = round(record_data["price_kth"]) worksheet[f"I{row}"] = round(record_data["total_price"]) worksheet[f"F{row + 2}"] = "Total:" worksheet[f"G{row + 2}"] = f"=SUM(G{samples_start}: G{row})" if costcenter == "KI": worksheet[f"H{row + 2}"] = f"=SUM(H{samples_start}: H{row})" worksheet[f"I{row + 2}"] = f"=SUM(I{samples_start}: I{row})" header_rows = [5, 12, 19, 23, row + 2] used_columns = "ABCDEFGHI" if costcenter == "KI" else "ABCDEFG" for header_row in header_rows: for column in used_columns: cell = worksheet[f"{column}{header_row}"] cell.font = Font(bold=True, size=14) cell.border = Border( top=Side(border_style="thin", color="000000"), bottom=Side(border_style="thin", color="000000"), ) cell.fill = PatternFill("solid", fgColor="E5E8E8") return workbook
def set_border(top, bottom, left, right): border = Border(top=Side(border_style=top, color=colors.BLACK), bottom=Side(border_style=bottom, color=colors.BLACK), left=Side(border_style=left, color=colors.BLACK), right=Side(border_style=right, color=colors.BLACK)) return border
def cx(a, qq): # 新建一个excel文件 wb = Workbook() # 新建一个sheet mySheet = wb.create_sheet(index=0, title=a) #存储相同值的行 rank_list = [0] for i in range(1, row + 1): if sheet.cell(row=i, column=num).value == a: rank_list.append(i) print(rank_list) #不显示网格 mySheet.sheet_view.showGridLines = False fontStyle = Font(name=u'微软雅黑', size=8) fontStyle1 = Font(name=u'微软雅黑', size=5) # 写入sheet中 for hang in range(len(rank_list)): #循环每类的长度 mySheet.row_dimensions[hang + 1].height = 13.5 for lie in range(2, column + 1): #循环列 if hang == 0: mySheet.cell(row=1, column=1, value='序号').font = fontStyle bb = sheet.cell(row=1, column=lie).value mySheet.cell(row=hang + 1, column=lie, value=bb).font = fontStyle else: mySheet.cell(row=hang + 1, column=1, value=hang).font = fontStyle bb = sheet.cell(row=rank_list[hang], column=lie).value #获取原表某行某列的值 mySheet.cell(row=hang + 1, column=lie, value=bb).font = fontStyle #在新建表某行某列的写入值 #设置文字格式 for hang in range(0, len(rank_list)): for lie in range(1, column + 1): zm = chr(lie + 64) lh = zm + str(hang + 1) mySheet[lh].alignment = Alignment(horizontal='center', vertical='center') #设置边框 if hang == 0: #第一行 if lie == 1: #第一列 mySheet[lh].border = Border(top=Side(style='thin', color="000000"), left=Side(style='thin', color="000000"), right=Side(style='hair', color="000000"), bottom=Side(style='hair', color="000000")) elif lie == column: #最后一列 mySheet[lh].border = Border(top=Side(style='thin', color="000000"), right=Side(style='thin', color="000000"), bottom=Side(style='hair', color="000000")) else: mySheet[lh].border = Border(top=Side(style='thin', color="000000"), right=Side(style='hair', color="000000"), bottom=Side(style='hair', color="000000")) elif hang == len(rank_list) - 1: #最后一行 if lie == 1: #第一列 mySheet[lh].border = Border(bottom=Side(style='thin', color="000000"), left=Side(style='thin', color="000000"), right=Side(style='hair', color="000000")) elif lie == column: #最后一列 mySheet[lh].border = Border(bottom=Side(style='thin', color="000000"), right=Side(style='thin', color="000000")) mySheet[lh].font = fontStyle1 mySheet[lh].alignment = Alignment(wrapText=True) else: mySheet[lh].border = Border(bottom=Side(style='thin', color="000000"), right=Side(style='hair', color="000000")) else: if lie == 1: #第一列 mySheet[lh].border = Border(left=Side(style='thin', color="000000"), right=Side(style='hair', color="000000"), bottom=Side(style='hair', color="000000")) elif lie == column: #最后一列 mySheet[lh].border = Border(right=Side(style='thin', color="000000"), bottom=Side(style='hair', color="000000")) mySheet[lh].font = fontStyle1 mySheet[lh].alignment = Alignment(wrapText=True) else: mySheet[lh].border = Border(right=Side(style='hair', color="000000"), bottom=Side(style='hair', color="000000")) path = dir_path + '/' + str(qq) + '/' + str(a) + '.xlsx' wb.save(path) #保存每类的excel