def SetCell(ws,wsRow,wsCol,val,fmt): align = None fill = None numFmt = None border = None c = ws.cell(row=wsRow,column=wsCol) for i in fmt: #logging.debug(i) if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] if (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] if (i == 'wrap'): if (not align): align = Alignment() if (fmt[i] == '1'): align.wrap_text = 1 else: align.wrap_text = 0 if (i == 'border'): if (not align): align = Alignment() side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) if (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') if (i == 'orient'): pass if (i == 'bg'): fill = PatternFill(start_color='FFEE1111',end_color='FFEE1111',fill_type='solid') if (i == 'numFmt'): numFmt = fmt[i] if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt c.value = val
def SetCellFmt(self,cell,hAlign,vAlign,fmt,value): if (align == 'C'): align = Alignment(horizontal='center',vertical='center') elif (align == 'L'): align = Alignment(horizontal='left',vertical='center') elif (align == 'R'): align = Alignment(horizontal='right',vertical='center') else: align = Alignment(horizontal='right',vertical='center') side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) if (fmt == 'F'): fmt = '0.00' cell.number_format = fmt cell.alignment = align.copy() cell.border = border.copy() cell.value = value
b3.fill = red_to_green_fill #创建边框对象 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) b3.border = Border(left=double_side, top=double_side, right=double_side, bottom=double_side) #合并单元格 ws.merge_cells(('A4:C4')) ws['A4'].value = 'I love FishC.com' #设置对齐方式为 居中 center_alinment = Alignment(horizontal='center', vertical="center") ws['A4'].alignment = center_alinment #创建一个命名空间 highlight = NamedStyle(name='highlight') #设置它的风格 highlight.font = Font(bold=True, size=20, color="FF8323") highlight.alignment = Alignment(horizontal='center', vertical='center') #注册命名空间风格 更加方便一点这种方式 wb.add_named_style(highlight) ws['A4'].style = highlight ws['B5'].value = 'Love' ws['B5'].style = highlight wb.save(r"C:\Users\Administrator\Desktop\3.xlsx")
def style(ws, fieldnames, report_type): font = Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') font2 = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') color = Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FFFFFF') border = Border(left=Side(border_style=BORDER_THIN, color='FF000000'), right=Side(border_style=BORDER_THIN, color='FF000000'), top=Side(border_style=BORDER_THIN, color='FF000000'), bottom=Side(border_style=BORDER_THIN, color='FF000000')) thin_border = Border(left=Side(border_style=BORDER_THIN, color='00000000'), right=Side(border_style=BORDER_THIN, color='00000000'), top=Side(border_style=BORDER_THIN, color='00000000'), bottom=Side(border_style=BORDER_THIN, color='00000000')) fill = PatternFill(start_color='00C0C0C0', end_color='00C0C0C0', fill_type='solid') fill2 = PatternFill(start_color='ebd3ae', end_color='ebd3ae', fill_type='solid') fill3 = PatternFill(start_color='f6fcc0', end_color='f6fcc0', fill_type='solid') fill4 = PatternFill(start_color='ffffff', end_color='ffffff', fill_type='solid') dd = Font(underline='single', color='000000FF') # row_count = ws.max_row # column_count = ws.max_column for cell1 in ws['1:1']: # cell2.fill = fill cell1.font = font2 for cell2 in ws['2:2']: cell2.fill = fill cell2.font = font ws.row_dimensions[2].height = 40 # ws.row_dimensions[1].height = 40 for row in ws: for cell1 in row: cell1.border = border cell1.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center') column_widths = [] for col in fieldnames: for i in range(len(col)): if len(column_widths) > i: if len(col) > column_widths[i]: column_widths[i] = len(col) else: column_widths += [len(col)] for i, column_width in enumerate(column_widths): ws.column_dimensions[get_column_letter(i + 1)].width = 20
def xls_add_format(PA): constantPA = PA XLSFilepath = outputDir + constantPA + '\\' + constantPA + ' news items ' + highlightDate + '.xlsx' wb = openpyxl.load_workbook(filename=XLSFilepath) worksheet = wb.active worksheet.column_dimensions['F'].hidden = True #hide column thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) #for col in worksheet.columns: # max_length = 0 # column = str(col[0].column) # Get the column name # print('Column: ' + column) # if column in ['B', 'F', 'G']: worksheet.column_dimensions[column].width = 50 # else: # if column == 'C': worksheet.column_dimensions[column].width = 60 #else: worksheet.column_dimensions[column].width = 20 worksheet.column_dimensions['A'].width = 20 worksheet.column_dimensions['B'].width = 50 worksheet.column_dimensions['C'].width = 60 worksheet.column_dimensions['D'].width = 20 worksheet.column_dimensions['E'].width = 20 worksheet.column_dimensions['F'].width = 50 worksheet.column_dimensions['G'].width = 50 worksheet.column_dimensions['H'].width = 20 worksheet.column_dimensions['I'].width = 20 worksheet.column_dimensions['J'].width = 20 worksheet.sheet_view.zoomScale = 80 #print(column) ListOfRowsWithDateDiffs = [] for rows in worksheet.iter_rows(min_row=1, min_col=1): for cell in rows: rowNumber = re.search('^\D(.*)', cell.coordinate).group(1) colNumber = re.search('(^\D).*', cell.coordinate).group(1) worksheet[cell.coordinate].alignment = Alignment(vertical="top", horizontal="left", wrap_text=True) worksheet[cell.coordinate].border = thin_border if (int(rowNumber) % 2) == '0': #check if row is even number worksheet[cell.coordinate].fill = PatternFill( fgColor="ccddff", fill_type="solid") #if yes fill cell contents with color else: worksheet[cell.coordinate].fill = PatternFill( fgColor="ffffff", fill_type="solid") #turn white background if colNumber == 'K': if str(cell.value) == 'True': ListOfRowsWithDateDiffs.append(rowNumber) print(str(cell.value)) worksheet[cell.coordinate].fill = PatternFill( fgColor="ffff00", fill_type="solid" ) #if yes fill cell contents with color if colNumber == 'J': #print('cell %s %s' % (cell.coordinate,cell.value)) worksheet[cell.coordinate].hyperlink = cell.value worksheet[cell.coordinate].value = "View on PSL" worksheet[cell.coordinate].font = Font(color=colors.BLUE, bold=True) #print('cell %s %s' % (cell.coordinate,cell.value)) #print(colNumber) #loop through rows again to highlight any rows that have dates that are different from eachother for rows in worksheet.iter_rows(min_row=1, min_col=1): for cell in rows: rowNumber = re.search('^\D(.*)', cell.coordinate).group(1) if rowNumber in ListOfRowsWithDateDiffs: worksheet[cell.coordinate].fill = PatternFill( fgColor="ffff00", fill_type="solid") #worksheet.set_column(excel_header, 20) worksheet.column_dimensions[ 'K'].hidden = True #hide column worksheet.auto_filter.ref = "A:J" try: wb.save(XLSFilepath) except PermissionError: log('COULD NOT EXPORT DUE TO PERMISSION ERROR: ' + XLSFilepath)
def get(self, request, *args, **kwargs): query = TiempoMuertonDet.objects.all() wb = Workbook() ws = wb.active ws.tittle = 'Tiempos Muertos' #Establer el nombre del archivo nombre_archivo = "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'] = 'Company' 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'] = 'Department' 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.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 = 40 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'] = 'CAtegoria' 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'] = 'Causa' 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'] = 'Tiempo (min)' 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(q.causa) ws.cell(row=controlador, column=8).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=8).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=8).value = str(categoria) 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 saveToExcel(self): print("Now, the results will be written to Excel file") wb = Workbook() ws = wb.active ws.sheet_format.defaultColWidth = 24 # custom width """" Title """ ws.title = "Salary calculation" # sheet name ws.merge_cells('A1:B1') # merge the cells for the image to fit img = Image(DirecPath + "mazars logo.jpg") # open the image img.height = 83 img.width = 335 ws.add_image(img, 'A1') ws.row_dimensions[1].height = 65 """""" ws.cell(row=3, column=1, value='Year') ws.cell(row=3, column=2, value='2019') ws.cell(row=4, column=1, value='Social status') if self.__marital_status == 0: ws.cell(row=4, column=2, value="Single") else: ws.cell(row=4, column=2, value="Married") ws.cell(row=5, column=1, value="Partner") if self.__partner_status == 0: ws.cell(row=5, column=2, value="Does not work") else: ws.cell(row=5, column=2, value="Works") ws.cell(row=6, column=1, value="Kids count") ws.cell(row=6, column=2, value=self.__kids_count) ws.merge_cells('A7:B7') if self.__boss_cost == 1: _ = ws.cell(row=7, column=1, value="Total cost for employer is included") else: _ = ws.cell(row=7, column=1, value="Total cost for employer is not included ") ws.merge_cells('A8:C8') if self.__sale_5 == 1: _ = ws.cell( row=8, column=1, value= "employer's share of social security premium, 5 percent sale is taken into account" ) else: _ = ws.cell( row=8, column=1, value= "employer's share of social security premium, 5 percent sale is not taken into account" ) """Calculation header""" ws.merge_cells('A9:L9') ws.cell(9, 1).font = Font(bold=True, size=15) title_cell = ws.cell(9, 1) title_cell.alignment = Alignment(horizontal='center') """""" """Headers""" if self.__gross_to_net == 1: headers = [ 'Month', 'Gross', 'Total Prim', 'Cumulative Income Base', 'Income Tax', 'Stamp Tax', 'Net', 'Agi', 'Total Salary', 'Total Cost for Employer' ] ws['A9'] = "Gross to Net" elif self.__net_to_gross == 1: ws['A9'] = "Net to Gross" headers = [ 'Month', 'Net', 'Total Prim', 'Cumulative Income Base', 'Income Tax', 'Stamp Tax', 'Gross', 'Agi', 'Total Salary', 'Total Cost for Employer' ] col_Index = 1 # Start Index ws.row_dimensions[10].height = float( 33.75) # row height for the header for header in headers: _ = ws.cell(row=10, column=col_Index, value=header) _.font = Font(bold=True, size=12) _.alignment = Alignment(horizontal='center') col_Index += 1 """""" data_row = 11 for month in self.__salary.keys(): # it goes for 12 times (12 months) self.calculate( month) # a func is invoked to calculate for each months salary data = [ self.__prim, self.__cumul_base, self.__income_tax, self.__stamp_tax, self.__net, self.__agi, self.__total_salary ] data_column = 3 ws.cell(row=data_row, column=1, value=self.month_names[month]) for d in data: if data_column == 7: data_column += 1 continue elif data_column == 4: ws.cell(row=data_row, column=data_column, value=self.__cumul_base[month]) data_column += 1 continue ws.cell(row=data_row, column=data_column, value=d) data_column += 1 ws.cell(row=data_row, column=10, value=self.findBossCost(month)) if self.__gross_to_net == 1: ws.cell(row=data_row, column=2, value=self.__gross) ws.cell(row=data_row, column=7, value=self.__net) elif self.__net_to_gross == 1: ws.cell(row=data_row, column=2, value=self.__net) ws.cell(row=data_row, column=7, value=self.__gross) data_row += 1 wb.save(DirecPath + "Results/Mazars_Gross_Net_Calculation.xlsx")
from excelFNames import combinedFName, unformattedFName, outputFName from openpyxl.styles import Alignment, PatternFill, Border, Side, Font from openpyxl.utils import get_column_letter from myxlutils import format_date_rows, get_column_names_and_index, format_dollar_values from paths import savePath # Need to be in the py_output folder # Open in Openpyxl and apply some cell formatting # Load workbook wbReport = openpyxl.load_workbook(unformattedFName) # Formatting vars blueFill = PatternFill(patternType="solid", fgColor="91B3D7") currentFill = PatternFill(patternType="solid", fgColor="E6B8B7") redBorder = Side(border_style="medium", color="FF0000") boldFont = Font(bold=True) leftAlign = Alignment(horizontal="left", vertical="top") # ***************************************************************** # ************************ Details Tab **************************** # ***************************************************************** # Load details sheet sDetail = wbReport["Details"] detailDict = {} get_column_names_and_index(sDetail, detailDict) # Freeze and left-align top row sDetail.freeze_panes = "A2" for x in range(1, (sDetail.max_column + 1)):
def create_aww_performance_excel_file(excel_data, data_type, month, state, district=None, block=None): aggregation_level = 3 if block else (2 if district else 1) export_info = excel_data[1][1] excel_data = [line[aggregation_level:] for line in excel_data[0][1]] thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) warp_text_alignment = Alignment(wrap_text=True) bold_font = Font(bold=True) blue_fill = PatternFill("solid", fgColor="B3C5E5") grey_fill = PatternFill("solid", fgColor="BFBFBF") workbook = Workbook() worksheet = workbook.active worksheet.title = "AWW Performance Report" worksheet.sheet_view.showGridLines = False # sheet title worksheet.merge_cells('B2:{0}2'.format("J" if aggregation_level == 3 else ( "K" if aggregation_level == 2 else "L"))) title_cell = worksheet['B2'] title_cell.fill = PatternFill("solid", fgColor="4472C4") title_cell.value = "AWW Performance Report for the month of {}".format( month) title_cell.font = Font(size=18, color="FFFFFF") title_cell.alignment = Alignment(horizontal="center") # sheet header header_cells = {"B3", "C3", "D3", "E3", "F3", "G3", "H3", "I3", "J3"} if aggregation_level < 3: header_cells.add("K3") if aggregation_level < 2: header_cells.add("L3") for cell in header_cells: worksheet[cell].fill = blue_fill worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment worksheet.merge_cells('B3:C3') worksheet['B3'].value = "State: {}".format(state) if district: worksheet['D3'].value = "District: {}".format(district) worksheet.merge_cells('E3:F3') if block: worksheet['E3'].value = "Block: {}".format(block) date_description_cell_start = "H3" if aggregation_level == 3 else ( "I3" if aggregation_level == 2 else "J3") date_description_cell_finish = "I3" if aggregation_level == 3 else ( "J3" if aggregation_level == 2 else "K3") date_column = "J3" if aggregation_level == 3 else ( "K3" if aggregation_level == 2 else "L3") worksheet.merge_cells('{0}:{1}'.format( date_description_cell_start, date_description_cell_finish, )) worksheet[date_description_cell_start].value = "Date when downloaded:" worksheet[date_description_cell_start].alignment = Alignment( horizontal="right") utc_now = datetime.now(pytz.utc) now_in_india = utc_now.astimezone(india_timezone) worksheet[date_column].value = custom_strftime('{S} %b %Y', now_in_india) worksheet[date_column].alignment = Alignment(horizontal="right") # table header table_header_position_row = 5 headers = ["S.No"] if aggregation_level < 2: headers.append("District") if aggregation_level < 3: headers.append("Block") headers.extend([ "Supervisor", "AWC", "AWW Name", "AWW Contact Number", "Home Visits Conducted", "Number of Days AWC was Open", "Weighing Efficiency", "Eligible for Incentive" ]) columns = 'B C D E F G H I J' if aggregation_level < 3: columns += " K" if aggregation_level < 2: columns += " L" columns = columns.split() table_header = {} for col, header in zip(columns, headers): table_header[col] = header for column, value in table_header.items(): cell = "{}{}".format(column, table_header_position_row) worksheet[cell].fill = grey_fill worksheet[cell].border = thin_border worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment worksheet[cell].value = value # table contents row_position = table_header_position_row + 1 for enum, row in enumerate(excel_data[1:], start=1): for column_index in range(len(columns)): column = columns[column_index] cell = "{}{}".format(column, row_position) worksheet[cell].border = thin_border if column_index == 0: worksheet[cell].value = enum else: worksheet[cell].value = row[column_index - 1] row_position += 1 # sheet dimensions title_row = worksheet.row_dimensions[2] title_row.height = 23 worksheet.row_dimensions[table_header_position_row].height = 46 widths = {} widths_columns = ['A'] widths_columns.extend(columns) standard_widths = [4, 7, 15] standard_widths.extend([15] * (3 - aggregation_level)) standard_widths.extend([13, 12, 13, 15, 11, 14, 14]) for col, width in zip(widths_columns, standard_widths): widths[col] = width widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0) widths['D'] = 13 + (len(district) * 4 // 3 if district else 0) widths['F'] = max(widths['F'], len(block) * 4 // 3 if block else 0) for column in ["C", "E", "G"]: if widths[column] > 25: worksheet.row_dimensions[3].height = max( 16 * ((widths[column] // 25) + 1), worksheet.row_dimensions[3].height) widths[column] = 25 columns = columns[1:] # column widths based on table contents for column_index in range(len(columns)): widths[columns[column_index]] = max( widths[columns[column_index]], max( len(row[column_index].decode('utf-8') if isinstance( row[column_index], bytes) else six. text_type(row[column_index])) for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0) for column, width in widths.items(): worksheet.column_dimensions[column].width = width # export info worksheet2 = workbook.create_sheet("Export Info") worksheet2.column_dimensions['A'].width = 14 for n, export_info_item in enumerate(export_info, start=1): worksheet2['A{0}'.format(n)].value = export_info_item[0] worksheet2['B{0}'.format(n)].value = export_info_item[1] # saving file file_hash = uuid.uuid4().hex export_file = BytesIO() icds_file = IcdsFile(blob_id=file_hash, data_type=data_type) workbook.save(export_file) export_file.seek(0) icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24) icds_file.save() return file_hash
def set_file_format(self, file_path): # 设置表格式 if not os.path.exists(file_path): print("文件不存在,不处理") return workbook = openpyxl.load_workbook(file_path) sheet = workbook.worksheets[0] # 设置各列宽 sheet.column_dimensions["A"].width = 16 sheet.column_dimensions["B"].width = 10 sheet.column_dimensions["C"].width = 20 sheet.column_dimensions["D"].width = 40 # 设置表名格式 max_row = sheet.max_row for i in range(1, max_row + 1): col1_value = sheet.cell(i, 1).value col2_value = sheet.cell(i, 2).value # 首列有数据,第2列无数据,则为表名 if col1_value and not col2_value: # 合并表名 sheet.merge_cells(start_row=i, start_column=1, end_row=i, end_column=4) # 加粗字体 font = Font(name="微软雅黑", size=12, bold=True, italic=False, color="000000") # 黑色边框 side_style = Side(style="thin", color="000000") border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style) # 居中对齐 cell_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) # 填充背景色 p_fill = PatternFill(fill_type="solid", fgColor="BFBFBF") # 表名cell格式 for j in range(1, 5): sheet.cell(i, j).font = font sheet.cell(i, j).border = border sheet.cell(i, j).alignment = cell_alignment sheet.cell(i, j).fill = p_fill # 若首列和第2列都有数据,则是表内容 if col1_value and col2_value: # 黑色边框 side_style = Side(style="thin", color="000000") border = Border(left=side_style, right=side_style, top=side_style, bottom=side_style) # 表名cell格式 for j in range(1, 5): sheet.cell(i, j).border = border workbook.save(file_path)
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 = 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
sheet = wb.create_sheet(index=0, title=str(i) + '月') # 获取具体日期时间 for j in range(len(calendar.monthcalendar(2019, i))): for k in range(len(calendar.monthcalendar(2019, i)[j])): value = calendar.monthcalendar(2019, i)[j][k] # 将0值变为空值 if value == 0: value = '' sheet.cell(row=j + 9, column=k + 1).value = value else: sheet.cell(row=j + 9, column=k + 1).value = value # 设置字体 sheet.cell(row=j + 9, column=k + 1).font = Font(u'微软雅黑', size=11) # 单元格文字设置,右对齐,垂直居中 align = Alignment(horizontal='right', vertical='center') # 单元格填充色属性设置 fill = PatternFill("solid", fgColor="B9EBF7") # 对单元格进行颜色填充 for k1 in range(1, 100): for k2 in range(1, 100): sheet.cell(row=k1, column=k2).fill = fill # 添加星期几信息行 days = ['星期日', '星期一', '星期二', '星期三', '星期四', '星期五', '星期六'] num = 0 for k3 in range(1, 8): sheet.cell(row=8, column=k3).value = days[num] sheet.cell(row=8, column=k3).alignment = align sheet.cell(row=8, column=k3).font = Font(u'微软雅黑', size=11) # 设置列宽12 c_char = get_column_letter(k3)
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() # todo ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # for i in range(2, 11 + 1): # ws.row_dimensions[i].height = 30 # # for i in range(12, 43 + 1): # ws.row_dimensions[i].height = 30 # 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 \ "values" not in report['reporting_period'].keys() or len(report['reporting_period']['values']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ############################### has_cost_data_flag = True if "values" not in report['reporting_period'].keys() or len( report['reporting_period']['values']) == 0: has_cost_data_flag = False if has_cost_data_flag: ws['B6'].font = title_font ws['B6'] = name + '报告期成本' reporting_period_data = report['reporting_period'] category = report['meter']['energy_category_name'] ca_len = len(category) ws.row_dimensions[7].height = 60 ws['B7'].fill = table_fill ws['B7'].border = f_border 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 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'] = report['meter']['energy_category_name'] + " (" + report[ 'meter']['unit_of_measure'] + ")" 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['total_in_category'], 2) ws[col + '8'].border = f_border ws[col + '9'].font = name_font ws[col + '9'].alignment = c_c_alignment ws[col + '9'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[col + '9'].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'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tce_col + '9'].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'] = str(round(reporting_period_data['increment_rate'] * 100, 2)) + "%" \ if reporting_period_data['increment_rate'] is not None else "-" ws[tco2e_col + '9'].border = f_border else: for i in range(6, 9 + 1): ws.rows_dimensions[i].height = 0.1 ###################################### has_cost_datail_flag = True reporting_period_data = report['reporting_period'] category = report['meter']['energy_category_name'] ca_len = len(category) times = reporting_period_data['timestamps'] parameters_names_len = len(report['parameters']['names']) parameters_data = report['parameters'] parameters_parameters_datas_len = 0 for i in range(0, parameters_names_len): if len(parameters_data['timestamps'][i]) == 0: continue parameters_parameters_datas_len += 1 if "values" not in reporting_period_data.keys() or len( reporting_period_data['values']) == 0: has_cost_datail_flag = False if has_cost_datail_flag: start_detail_data_row_number = 13 + (parameters_parameters_datas_len + ca_len) * 6 ws['B11'].font = title_font ws['B11'] = name + '详细数据' ws.row_dimensions[start_detail_data_row_number].height = 60 ws['B' + str(start_detail_data_row_number)].fill = table_fill ws['B' + str(start_detail_data_row_number)].font = title_font ws['B' + str(start_detail_data_row_number)].border = f_border ws['B' + str(start_detail_data_row_number)].alignment = c_c_alignment ws['B' + str(start_detail_data_row_number)] = '日期时间' time = times has_data = False max_row = 0 if len(time) > 0: has_data = True max_row = start_detail_data_row_number + len(time) if has_data: end_data_row_number = start_detail_data_row_number for i in range(0, len(time)): col = 'B' end_data_row_number += 1 row = str(end_data_row_number) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = time[i] ws[col + row].border = f_border ws['B' + str(end_data_row_number + 1)].font = title_font ws['B' + str(end_data_row_number + 1)].alignment = c_c_alignment ws['B' + str(end_data_row_number + 1)] = '总计' ws['B' + str(end_data_row_number + 1)].border = f_border for i in range(0, ca_len): col = chr(ord('C') + i) ws[col + str(start_detail_data_row_number)].fill = table_fill ws[col + str(start_detail_data_row_number)].font = title_font ws[col + str(start_detail_data_row_number)].alignment = c_c_alignment ws[col + str(start_detail_data_row_number)] = \ report['meter']['energy_category_name']+" (" + report['meter']['unit_of_measure'] + ")" ws[col + str(start_detail_data_row_number)].border = f_border time = times time_len = len(time) for j in range(0, time_len): row = str(start_detail_data_row_number + 1 + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round(reporting_period_data['values'][j], 2) ws[col + row].border = f_border ws[col + str(end_data_row_number + 1)].font = title_font ws[col + str(end_data_row_number + 1)].alignment = c_c_alignment ws[col + str(end_data_row_number + 1)] = round( reporting_period_data['total_in_category'], 2) ws[col + str(end_data_row_number + 1)].border = f_border line = LineChart() line.title = '报告期成本 - ' + report['meter']['energy_category_name'] + \ " (" + report['meter']['unit_of_measure'] + ")" line_data = Reference(ws, min_col=3, min_row=start_detail_data_row_number, max_row=max_row) line.series.append(Series(line_data, title_from_data=True)) labels = Reference(ws, min_col=2, min_row=start_detail_data_row_number + 1, max_row=max_row) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.height = 8.25 line.width = 24 ws.add_chart(line, "B12") else: for i in range(11, 43 + 1): ws.row_dimensions[i].height = 0.0 ########################################## has_parameters_names_and_timestamps_and_values_data = True # 12 is the starting line number of the last line chart in the report period time_len = len(reporting_period_data['timestamps']) current_sheet_parameters_row_number = 12 + ca_len * 6 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 save_to_excel(rst, xlsx_filename): """Pure python codes: save results to excel and as string. rst should be sorted. """ # worksheet wb = Workbook() ws = wb.active ws.title = '高性能计算应用中心' # headers ws.alignment = Alignment(horizontal='right', vertical='center') ws.cell(row=1, column=1, value='姓名') ws.cell(row=1, column=2, value='年份季度') ws.cell(row=1, column=3, value='手机号') ws.cell(row=1, column=4, value='账单月') ws.cell(row=1, column=5, value='账单金额') ws.cell(row=1, column=6, value='季度/号码') ws.cell(row=1, column=7, value='季度/人') # fill the sheet for i, row in enumerate(rst): ws.cell(row=i + 2, column=1, value=rst[i][0]) ws.cell(row=i + 2, column=2, value=rst[i][1]) ws.cell(row=i + 2, column=3, value=rst[i][5]) ws.cell(row=i + 2, column=4, value=rst[i][4]) ws.cell(row=i + 2, column=5, value=rst[i][6]).number_format = '0.00' ws.cell(row=i + 2, column=6).number_format = '0.00' ws.cell(row=i + 2, column=7).number_format = '0.00' # groupby (u_name, u_phone, y_q_str) and sum groups = [] group_sizes = [] for k, group in groupby(rst, (0, 1, 5)): group = [v[6] for v in group] groups.append(group) group_sizes.append(len(group)) group_row_pointers = list(itertools.accumulate([2] + group_sizes)) # sum and merge for i in range(len(groups)): # quarter_sum_per_phone start_row = group_row_pointers[i] end_row = group_row_pointers[i + 1] - 1 excel_merge_cells(ws, start_row=start_row, end_row=end_row, start_column=6, end_column=6, value=sum(groups[i])) # u_phone excel_merge_cells(ws, start_row=start_row, end_row=end_row, start_column=3, end_column=3) # groupby (u_name, y_q_str) and sum groups = [] group_sizes = [] for k, group in groupby(rst, (0, 1)): group = [v[6] for v in group] groups.append(group) group_sizes.append(len(group)) group_row_pointers = list(itertools.accumulate([2] + group_sizes)) # sum and merge for i in range(len(groups)): # quarter_sum_per_user start_row = group_row_pointers[i] end_row = group_row_pointers[i + 1] - 1 excel_merge_cells(ws, start_row=start_row, end_row=end_row, start_column=7, end_column=7, value=sum(groups[i])) # y_q_str excel_merge_cells(ws, start_row=start_row, end_row=end_row, start_column=2, end_column=2) # border set_outline_border(ws, min_row=start_row, max_row=end_row, border_style='thin') set_outline_border(ws, min_row=start_row, max_row=end_row, min_col=1, max_col=1, border_style='thin') # groupby (u_name) and sum groups = [] group_sizes = [] for k, group in groupby(rst, (0, )): group = list(group) groups.append(group) group_sizes.append(len(group)) group_row_pointers = list(itertools.accumulate([2] + group_sizes)) # merge and border for i in range(len(groups)): excel_merge_cells(ws, start_row=group_row_pointers[i], end_row=group_row_pointers[i + 1] - 1, start_column=1, end_column=1) ws.column_dimensions['B'].width = 15 ws.column_dimensions['C'].width = 12 ws.column_dimensions['F'].width = 10 ws.column_dimensions['G'].width = 10 ws.row_dimensions[1].height = 32 # alignment for row in ws.iter_rows(max_row=len(rst) + 1, min_col=1, max_col=4): for cell in row: cell.alignment = Alignment(horizontal='center', vertical='center') # alignment for row in ws.iter_rows(max_row=len(rst) + 1, min_col=5, max_col=7): for cell in row: cell.alignment = Alignment(horizontal='right', vertical='center') # alignment and font for row in ws.iter_rows(max_row=1, min_col=1, max_col=7): for cell in row: cell.alignment = Alignment(horizontal='center', vertical='center') cell.font = Font(bold=True, size=11) # out line bolder set_outline_border(ws, min_row=1, max_row=1, border_style='medium') set_outline_border(ws, min_row=1, max_row=len(rst) + 1, border_style='medium') wb.save(xlsx_filename)
def get_small_value_to_color(path_excel, sheetName): wb = load_workbook(path_excel, data_only=True) ws = wb.get_sheet_by_name(sheetName) # 设置字体样式,设置字体为 微软雅黑,单下划线,颜色为蓝色,字体加粗 yahei_font_u = Font(name=u'微软雅黑', underline='single', color='0000FF', bold=True) fille = PatternFill('solid', fgColor='c6efce') # 设置填充颜色为 橙色 def_fille = PatternFill('solid', fgColor='FFFFFF') # 设置填充颜色为 白色 print(ws.title) ws_rows_len = ws.max_row print('本 sheet 表一共有 %s 行(rows)' % ws_rows_len) ws_cols_len = ws.max_column print('本 sheet 表一共有 %s 列(columns)' % ws_cols_len) start_row = 4 # 定义起始行,EXCEL表中的数据列,从第4行开始 for col in range(2, ws_cols_len + 1): # 定位列 temp_cell_value = float(10000000.0000) temp_cell_pos = [] print('当前 是 第 %s 列.' % col) # col_str = get_column_letter(cols) # print(ws[col_str]) for row in range(start_row, ws_rows_len + 1): # 遍历方向是列,所以选择变更 值 为行的变化.进行循环 # cells_value = ws.cell(row=rows, column=cols).value cells_value = ws.cell(row=row, column=col).value ws.cell(row, col).fill = def_fille # 重置当前单元格的颜色,将以前着色的单元格恢复无底色 ws.cell(row, col).number_format = '0.0000' # 设置数据格式 ws.cell(row, col).alignment = Alignment(horizontal='right') # 设置居中对齐 if cells_value == '#N/A' or cells_value == None: # 判断单元格中的值 等于'#N/A ,无法使用,进行下一个循环 print('当前 单元格的值 为:%s ,此值不可用! 当前单元格的坐标, 列为: %s -- 行为: %s' % (cells_value, col, row)) continue elif cells_value == '#REF!' or cells_value == 0: print('当前 单元格的值 为:%s ,此值不可用! 当前单元格的坐标, 列为: %s -- 行为: %s' % (cells_value, col, row)) continue else: cells_value = float(cells_value) print('当前 单元格的值 为:%s , 当前单元格的坐标, 列为: %s -- 行为: %s' % (cells_value, col, row)) if temp_cell_value > cells_value: temp_cell_value = cells_value temp_cell_pos = [row, col] print( '进行数据比较,结果是当前单元格的值 比较小.符合要求,数据为:%s ,数据的坐标为行%s ,列 %s ' % (temp_cell_value, temp_cell_pos[0], temp_cell_pos[1])) # ws.cell(temp_cell_pos[0], temp_cell_pos[1]).fill = fille # ws.cell(row - 1, col).fill = def_fille elif temp_cell_value == cells_value: temp_cell_pos = [row, col] print( '进行数据比较,结果是当前单元格的值 相等.例外,数据为:%s ,数据的坐标为行%s ,列 %s ' % (temp_cell_value, temp_cell_pos[0], temp_cell_pos[1])) else: print('进行数据比较,结果是当前 单元格的值 比较大. 不符合要求,数据为:', cells_value) pass # ws.cell(temp_cell_pos[0],temp_cell_pos[1]).font=yahei_font_u ws.cell(temp_cell_pos[0], temp_cell_pos[1]).fill = fille print('比较大小着色完毕!进行保存') wb.save(path_excel)
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active ws.title = "StoreEnergyItem" # 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='Arial', size=15, bold=True) title_font = Font(name='Arial', size=15, bold=True) 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) # Img img = Image("excelexporters/myems.png") ws.add_image(img, 'A1') # Title ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'] = name ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'] = period_type ws['B4'].alignment = b_r_alignment ws['B4'] = 'Reporting Start Datetime:' ws['C4'].border = b_border ws['C4'].alignment = b_c_alignment ws['C4'] = reporting_start_datetime_local ws['D4'].alignment = b_r_alignment ws['D4'] = 'Reporting End Datetime:' ws['E4'].border = b_border ws['E4'].alignment = b_c_alignment ws['E4'] = reporting_end_datetime_local 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 + ' ' + 'Reporting Period Consumption' 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)] = 'Per Unit Area' 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_per_unit_area'][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)] = 'Increment Rate' 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]] + ') by Energy Item' 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]] + ') by Energy Item' 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 current_row_number += 1 chart_start_row_number = current_row_number if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] parameters_names_len = len(report['parameters']['names']) parameters_parameters_datas_len = 0 ws['B' + str(current_row_number - 1)].font = title_font ws['B' + str(current_row_number - 1)] = name + ' ' + 'Detailed Data' for i in range(0, parameters_names_len): if len(report['parameters']['timestamps'][i]) == 0: continue parameters_parameters_datas_len += 1 current_row_number = current_row_number + ( ca_len + parameters_parameters_datas_len) * 6 + 2 detail_data_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)] = 'Datetime' 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)] = 'Subtotal' 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 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 Consumption - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=detail_data_table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=detail_data_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 + 6 * i) ws.add_chart(line, chart_cell) #################################################################################################################### has_parameters_names_and_timestamps_and_values_data = True ca_len = len(report['reporting_period']['names']) current_sheet_parameters_row_number = chart_start_row_number + ca_len * 6 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']) file_name = "Store" + re.sub(r'[^A-Z]', '', ws.title.strip('S')) + "_" parameters_ws = wb.create_sheet(file_name + 'Parameters') 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") parameters_ws.add_image(img, 'A1') # Title 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'] = name 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'] = period_type parameters_ws['B4'].alignment = b_r_alignment parameters_ws['B4'] = 'Reporting Start Datetime:' parameters_ws['C4'].border = b_border parameters_ws['C4'].alignment = b_c_alignment parameters_ws['C4'] = reporting_start_datetime_local parameters_ws['D4'].alignment = b_r_alignment parameters_ws['D4'] = 'Reporting End Datetime:' parameters_ws['E4'].border = b_border parameters_ws['E4'].alignment = b_c_alignment parameters_ws['E4'] = reporting_end_datetime_local 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' 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 + ' ' + 'Parameters' 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 - ' + \ 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 _on_btn_export_users(self, event): default_dir = os.path.join(os.path.expanduser("~"), 'Desktop') default_file = u"导出用户" + time.strftime( '%Y-%m-%d', time.localtime(time.time())) + r".xlsx" file_dialog = wx.FileDialog(self, u"导出用户数据", defaultDir=default_dir, defaultFile=default_file, wildcard=u"Excel 文件(*.xlsx)|", style=wx.FD_SAVE) if file_dialog.ShowModal() == wx.ID_OK: # 导处操作进行时设置鼠标形状为箭头等待状态 self.SetCursor(wx.Cursor(wx.CURSOR_ARROWWAIT)) # 创建Excel文件 work_book = Workbook() work_sheet = work_book.active work_sheet.title = u"导出用户" # 写表头 row = ("", "ID", "姓名", "照片", "班级") work_sheet.append(row) for i in range(1, 6): cell = get_column_letter(i) + '1' work_sheet[cell].style = 'Headline 1' work_sheet[cell].fill = PatternFill("solid", fgColor="DDDDDD") work_sheet[cell].alignment = Alignment(horizontal="center", vertical="center") # 初始化 class_name = u"" start_row = 1 end_row = 1 # 连接数据库 conn = pyodbc.connect( 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s\\LMSdb.accdb' % os.getcwd()) cursor = conn.cursor() # 检索数据库中users表 cursor.execute( "SELECT user_id, user_name, user_photo, user_class FROM users ORDER BY user_class DESC" ) rows = cursor.fetchall() for row in rows: # 获取一行数据 user = [row[3], row[0], row[1], row[2], row[3]] if row[2] is None: # 用户照片为空时,该位置设置为空字符串 user[3] = "" # 输出一行 work_sheet.append(user) # 如果是新班级 if class_name != row[3]: # 合并上一班级单元格 work_sheet.merge_cells(start_row=start_row, start_column=1, end_row=end_row, end_column=1) class_cell = work_sheet["A" + str(start_row)] class_cell.value = class_name class_cell.alignment = Alignment(horizontal="center", vertical="center") # 记录新班级开始行 start_row = end_row + 1 # 记录新班级名称 class_name = row[3] # 下一行 end_row += 1 # 合并最后一个班级 work_sheet.merge_cells(start_row=start_row, start_column=1, end_row=end_row, end_column=1) class_cell = work_sheet["A" + str(start_row)] class_cell.value = class_name class_cell.alignment = Alignment(horizontal="center", vertical="center") # 关闭数据库 cursor.close() conn.close() # 保存Excel文件 work_book.save(filename=file_dialog.GetPath()) # 恢复鼠标形状 self.SetCursor(wx.Cursor(wx.CURSOR_ARROW)) # 弹出提示框 wx.MessageBox("导出完成!") event.Skip()
ws.insert_cols(col) #Change column width xl_cols_w = list('DFHJLN') for col in xl_cols_w: ws.column_dimensions[col].width = 15 #Change small columns width xl_cols_small = list('CEGIKM') for col in xl_cols_small: ws.column_dimensions[col].width = 1.5 #Make everything center for row_cells in ws.iter_rows(): for cell in row_cells: cell.alignment = Alignment(horizontal='center', vertical='center') #Delete the name in the Color column for row in range(9, 15): ws['P' + str(row)] = "" #Unmerge the cells start_row_range = list(range(2, 25, 4)) end_row_range = list(range(5, 26, 4)) for i in range(len(start_row_range)): ws.unmerge_cells('A' + str(start_row_range[i]) + ':A' + str(end_row_range[i])) #Insert empty rows rows_add = list(range(6, 30, 5))
def get (self, request, *args, **kwargs): query = TiempoMuertonDet.objects.all() wb = Workbook() ws = wb.active ws.tittle='Tiempos Muertos' #Establer el nombre del archivo nombre_archivo = "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']='Company' 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']='Department' 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.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=40 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'
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 # # for i in range(2, 37 + 1): # ws.row_dimensions[i].height = 30 # # for i in range(38, 90 + 1): # ws.row_dimensions[i].height = 30 # 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 ################################################# # First: 能耗分析 # 6: title # 7: table title # 8~10 table_data # Total: 5 rows # if has not energy data: set low height for rows ################################################# 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['B7'].border = f_border 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 = '' for i in range(0, ca_len): col = chr(ord('C') + i) row = '7' cell = col + row 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 ################################################# # Second: 分时电耗 # 12: title # 13: table title # 14~17 table_data # Total: 6 rows ################################################ 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.row_dimensions[13].height = 60 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 = 7.25 # cm 1.05*5 1.05cm = 30 pt pie.width = 9 # pie.title = "Pies sold by category" s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False # 标签显示 s1.dLbls.showVal = True # 数量显示 s1.dLbls.showPercent = True # 百分比显示 # s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100 ws.add_chart(pie, "D13") else: for i in range(12, 18 + 1): ws.row_dimensions[i].height = 0.1 # end_row 10 # start_row 12 ################################################ # Third: 子空间能耗 # 19: title # 20: table title # 21~24 table_data # Total: 6 rows ################################################ has_child_flag = True # Judge if the space has child space, if not, delete it. 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 current_row_number = 19 if has_child_flag: child = report['child_space'] child_spaces = child['child_space_names_array'][0] child_subtotals = child['subtotals_array'][0] ws['B19'].font = title_font ws['B19'] = name + ' 子空间能耗' ws.row_dimensions[20].height = 60 ws['B20'].fill = table_fill ws['B20'].border = f_border ca_len = len(child['energy_category_names']) table_start_row_number = 20 for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + '20'].fill = table_fill ws[row + '20'].font = title_font ws[row + '20'].alignment = c_c_alignment ws[row + '20'].border = f_border ws[row + '20'] = child['energy_category_names'][i] + ' (' + child[ 'units'][i] + ')' space_len = len(child['child_space_names_array'][0]) for i in range(0, space_len): row = str(i + 21) ws['B' + row].font = name_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = child['child_space_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 = 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 = 20 + space_len chart_start_row_number = 20 + space_len + 1 for i in range(0, ca_len): # pie # 25~30: pie pie = PieChart() pie.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) 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 # cm 1.05*5 1.05cm = 30 pt pie.width = 8 # pie.title = "Pies sold by category" s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False # 标签显示 s1.dLbls.showVal = True # 数量显示 s1.dLbls.showPercent = True # 百分比显示 # s1 = CharacterProperties(sz=1800) # 图表中字体大小 *100 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) # chart_col = chr(ord('B') + 2 * j) # chart_cell = chart_col + '25' 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 ################################################ # Fourth: 能耗详情 # current_row_number: title # current_row_number+1 ~ current_row_number+1+ca_len*6-1: line # current_row_number+1+ca_len*6: table title # current_row_number+1+ca_len*6~: table_data ################################################ 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 chart_start_row_number = 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: 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) print("max_row", max_row) if has_data: for i in range(0, len(time)): col = 'B' row = str(table_row + 1 + i) # col = chr(ord('B') + 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): # 38 title 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) # col = chr(ord('B') + i) 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 current_row_number = table_row + 1 + len(times[0]) 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)] = '小计' 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)].border = f_border ws[col + str(current_row_number )] = reporting_period_data['subtotals'][i] # line # 39~: line line = LineChart() line.title = '报告期消耗 - ' + ws.cell(column=3 + i, row=table_row).value 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) # openpyxl bug 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 # cm 1.05*5 1.05cm = 30 pt line.width = 24 # pie.title = "Pies sold by category" line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' # line.dLbls.showCatName = True # label show line.dLbls.showVal = True # val show line.dLbls.showPercent = True # percent show # s1 = CharacterProperties(sz=1800) # font size *100 chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number + 6 * i) ws.add_chart(line, chart_cell) filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def Informe(sheet, dic,lista_alumnos,cant_alumno): columnas_filas(sheet, 0, 'A', 20.00) columnas_filas(sheet, 0, 'B', 18.00) columnas_filas(sheet, 0, 'C', 40.00) columnas_filas(sheet, 0, 'D', 40.00) columnas_filas(sheet, 0, 'F', 40.00) columnas_filas(sheet, 0, 'E', 50.00) columnas_filas(sheet, 0, 'G', 20.00) columnas_filas(sheet, 0, 'H', 20.00) columnas_filas(sheet, 0, 'I', 15.00) alignment_title = Alignment(horizontal='center', vertical='center') fuente = Font(bold=False, size=11, name='arial') fila = 3 fila1 = 2 acum=1 cont=0 col=2 col1=4 fil=4 coli=2 colf=2 sheet.merge_cells('A2:I2') sheet['A2'].alignment = alignment_title.copy(wrapText=True,horizontal='center', vertical='top') sheet['A2'].font = fuente.copy(bold=True,size=15, name= "arial") sheet['A2']= 'DIRECTORIO DE ALUMNOS' sheet['G1'].alignment = alignment_title.copy(wrapText=True,horizontal='right', vertical='top') sheet['G1'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['G1']= 'Usuario' sheet['H1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H1'].font = fuente sheet['H1']= dic['usuario_id'] sheet['A1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A1'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A1']= 'Cia' sheet.merge_cells('B1:C1') sheet['B1'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B1'].font = fuente.copy(bold=False,size=12, name= "arial") sheet['B1']= dic['company_id'] sheet['A3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A3'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A3']= 'Fecha Emision:' #fecha_actual = datetime.strftime(datetime.now(), '%d-%m-%Y %H:%M:%S') fecha_actual = dic['fecha'] sheet.merge_cells('B3:C3') sheet['B3'].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B3'].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B3']= fecha_actual fila=6 for recorrer in lista_alumnos: sheet['A'+str(fila+1)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+1)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+1)]= 'Seccion:' sheet.merge_cells('B'+str(fila+1)+':C'+str(fila+1)) sheet['B'+str(fila+1)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+1)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['seccion_id']==False: sheet['B'+str(fila+1)]= '' else: sheet['B'+str(fila+1)]= recorrer['seccion_id'] sheet['A'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+2)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+2)]= 'Curso:' sheet.merge_cells('B'+str(fila+2)+':C'+str(fila+2)) sheet['B'+str(fila+2)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+2)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['curso_id']==False: sheet['B'+str(fila+2)]= '' else: sheet['B'+str(fila+2)]= recorrer['curso_id'] sheet['A'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+3)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+3)]= 'Codigo:' sheet.merge_cells('B'+str(fila+3)+':C'+str(fila+3)) sheet['B'+str(fila+3)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+3)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['codigo_curso']==False: sheet['B'+str(fila+3)]= '' else: sheet['B'+str(fila+3)]= recorrer['codigo_curso'] sheet['A'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+4)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+4)]= 'Paralelo:' sheet.merge_cells('B'+str(fila+4)+':C'+str(fila+4)) sheet['B'+str(fila+4)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+4)].font = fuente.copy(bold=False,size=12, name= "arial") if recorrer['paralelo_id']==False: sheet['B'+str(fila+4)]= '' else: sheet['B'+str(fila+4)]= recorrer['paralelo_id'] sheet['A'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila+5)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila+5)]= 'Total Alumnos:' sheet['B'+str(fila+5)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila+5)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B'+str(fila+5)]= len(recorrer['lista_detalle']) fila=fila+7 #poner_border(sheet,13,7,'medium','none','none','none') border_tabla(sheet,1,9,fila,fila,'none','none','none','medium') sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila)]= 'Orden' sheet['B'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['B'+str(fila)]= 'Codigo' sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['C'+str(fila)]= 'Alumno' sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['D'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['D'+str(fila)]= 'Representante' sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['E'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['E'+str(fila)]= 'Correo' sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['F'+str(fila)]= 'Direccion' sheet['G'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['G'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['G'+str(fila)]= 'Telefono' sheet['H'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['H'+str(fila)]= 'Cedula - RUC' sheet['I'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['I'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['I'+str(fila)]= 'Status' fila=fila+1 orden=1 for detalle in recorrer['lista_detalle']: sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['A'+str(fila)]= orden orden=orden+1 sheet['B'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['B'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['B'+str(fila)]= detalle['codigo'] sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['C'+str(fila)]= detalle['alumno'] sheet['D'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['D'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['D'+str(fila)]= detalle['representante'] sheet['E'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['E'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['correo']==False: sheet['E'+str(fila)]= '' else: sheet['E'+str(fila)]= detalle['correo'] sheet['F'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['F'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['direccion']==False: sheet['F'+str(fila)]= '' else: sheet['F'+str(fila)]= detalle['direccion'] sheet['G'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='justify', vertical='top') sheet['G'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") if detalle['telefono']==False: sheet['G'+str(fila)]= '' else: sheet['G'+str(fila)]= detalle['telefono'] sheet['H'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['H'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['H'+str(fila)]= detalle['cedula'] sheet['I'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['I'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['I'+str(fila)]= detalle['state'] fila=fila+1 border_tabla(sheet,1,9,fila,fila,'none','none','none','medium') fila=fila+2 fila=fila+2 sheet.merge_cells('A'+str(fila)+':B'+str(fila)) sheet['A'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['A'+str(fila)].font = fuente.copy(bold=True,size=12, name= "arial") sheet['A'+str(fila)]= 'TOTAL ALUMNOS REPORTE' sheet['C'+str(fila)].alignment = alignment_title.copy(wrapText=True,horizontal='left', vertical='top') sheet['C'+str(fila)].font = fuente.copy(bold=False,size=12, name= "arial") sheet['C'+str(fila)]= cant_alumno
def createFinalXlsFile(self, result_file_name, batch_obj): headers = ['Batch Name:', 'File Upload Time:', 'Number of Failed Records - Shodan:', 'Number of Failed Records - Censys:', 'Number of Failed Records - DomainIQ:', 'Number of Record Processed:', 'Number of Records with no data available - Shodan', 'Number of Records with no data available - Cenys', 'Number of Records with no data available - DomainIQ', 'Record Processing Time:', 'Uploader Name:'] rightAlignment = Alignment(horizontal='right', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) leftAlignment = Alignment(horizontal='left', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) centerAlignment = Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) fill = PatternFill("solid", bgColor="FFFFFF") ft = Font(color=colors.WHITE) wb = Workbook() ws = wb.active # colAlignment = ColumnDimension(ws, bestFit=True) ws.column_dimensions['A'].width = 50 ws.column_dimensions['B'].width = 35 ws.title = 'Batch Details' ws.merge_cells('A1:B1') headerCell = ws.cell(row=1, column=1, value="BATCH DETAILS") headerCell.fill = fill headerCell.font = Font(color=colors.WHITE, bold=True) headerCell.alignment = centerAlignment row_index = 2 now = datetime.datetime.now() time_diff = str(pytz.UTC.localize(now) - batch_obj.created_at).split(':') time_diff_format = '%s Hours and %s Minutes' % (time_diff[0], time_diff[1]) data = [batch_obj.batch_name, batch_obj.created_at.strftime("%b %d %Y at %I:%M %p"), str(batch_obj.shodan_failed_records), str(batch_obj.censys_failed_records), str(batch_obj.domainiq_failed_records), str(batch_obj.shodan_processed_records + batch_obj.domainiq_processed_records + batch_obj.censys_processed_records), "0", "0", "0", time_diff_format, str(batch_obj.user_created_by) ] for i in range(0, len(headers)): column_index = 1 cell = ws.cell(row=row_index, column=column_index, value=headers[i]) cell.alignment = rightAlignment cell.border = border cell.fill = fill cell.font = ft cell = ws.cell(row=row_index, column=(column_index + 1), value=data[i]) cell.alignment = leftAlignment cell.border = border row_index += 1 # wb.save(result_file_name) for topic in ('shodan', 'censys', 'domainiq'): file_name = '%s/result_files/batch_%s_%s.json' % (settings.MEDIA_ROOT, batch_obj.batch_id, topic) json_file = open(file_name, 'r') data1 = json.load(json_file, object_pairs_hook=OrderedDict) ws = wb.create_sheet(topic) try: for row in data1: del row['batch_id'] data = self.setDataAvail(data1) row_index = 1 for indx, col in enumerate(data[0].keys()): headerCell = ws.cell(row=row_index, column=(1 + indx), value=col) headerCell.fill = fill headerCell.font = Font(color=colors.WHITE, bold=True) headerCell.alignment = leftAlignment row_index = 2 for element in data: for indx,col in enumerate(element.keys()): cell = ws.cell(row=row_index, column=(1 + indx), value=element[col]) cell.alignment = leftAlignment cell.border = border row_index += 1 cols_array_align = self.getXlsxFieldsAlignmentByTopic(topic) if cols_array_align is not None: for key,value in cols_array_align.items(): ws.column_dimensions[key].width = value except FileNotFoundError: print("\"%s\" partial file not found" % topic) wb.save(result_file_name)
def get(self, request, *args, **kwargs): subscribers = Subscribers.objects.all() wb = Workbook() ws = wb.active ws['B1'] = 'REPORTE DE ABONADOS' ws.merge_cells('B1:K1') ws['B3'] = 'Item' ws['C3'] = 'Identificación' ws['D3'] = 'Apellidos' ws['E3'] = 'Nombres' ws['F3'] = 'Dirección' ws['G3'] = 'Email' ws['H3'] = 'Teléfono' ws['I3'] = 'Celular' ws['J3'] = 'Estado' ws['K3'] = 'Nació' ws['B1'].font = Font(bold=True, size=16) ws['B3'].font = Font(bold=True) ws['C3'].font = Font(bold=True) ws['D3'].font = Font(bold=True) ws['E3'].font = Font(bold=True) ws['F3'].font = Font(bold=True) ws['G3'].font = Font(bold=True) ws['H3'].font = Font(bold=True) ws['I3'].font = Font(bold=True) ws['J3'].font = Font(bold=True) ws['K3'].font = Font(bold=True) ws['B1'].alignment = Alignment(horizontal="center") ws['C3'].alignment = Alignment(horizontal="center") ws['D3'].alignment = Alignment(horizontal="center") ws['E3'].alignment = Alignment(horizontal="center") ws['F3'].alignment = Alignment(horizontal="center") ws['G3'].alignment = Alignment(horizontal="center") ws['H3'].alignment = Alignment(horizontal="center") ws['I3'].alignment = Alignment(horizontal="center") ws['J3'].alignment = Alignment(horizontal="center") ws['K3'].alignment = Alignment(horizontal="center") ws.column_dimensions['C'].width = 15 ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 15 ws.column_dimensions['F'].width = 30 ws.column_dimensions['G'].width = 20 ws.column_dimensions['H'].width = 10 ws.column_dimensions['I'].width = 10 ws.column_dimensions['J'].width = 10 ws.column_dimensions['K'].width = 15 counter = 4 for subscriber in subscribers: ws.cell(row=counter, column=2).value = subscriber.id ws.cell(row=counter, column=3).value = subscriber.identification ws.cell(row=counter, column=4).value = subscriber.surname ws.cell(row=counter, column=5).value = subscriber.name ws.cell(row=counter, column=6).value = subscriber.address ws.cell(row=counter, column=7).value = subscriber.email ws.cell(row=counter, column=8).value = subscriber.phone ws.cell(row=counter, column=9).value = subscriber.mobile ws.cell(row=counter, column=10).value = subscriber.state ws.cell(row=counter, column=11).value = subscriber.birth_date counter += 1 #define el nombre delñ archivo file_name = "RporteAbonadosExcel.xlsx" #Definir el tipo de respuesta que va a dar response = HttpResponse(content_type="application/ms_excel") content = "attachment; filename = {0}".format(file_name) response['Content-Disposition'] = content wb.save(response) return response
def start_Button_click(self): #复选框事件 self.hight_status = self.hight_checkBox.isChecked() self.middle_status = self.middle_checkBox.isChecked() self.low_status = self.low_checkBox.isChecked() self.port_status = self.port_checkBox.isChecked() self.web_status = self.web_checkBox.isChecked() try: folder_end = self.output_Button_cent folder_start = self.input_Button_cent except Exception as e: QtWidgets.QMessageBox.information(MainWindow, "提示", "要先设置文件夹!", QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No , QtWidgets.QMessageBox.Yes ) return e else: try: with open('set.ini') as set_ini: name_ini = set_ini.readlines()[:1][0].split(':')[1].strip(' \n') with open('set.ini') as set_ini: company_ini = set_ini.readlines()[1:2][0].split(':')[1].strip(' \n') except Exception as e: QtWidgets.QMessageBox.information(MainWindow, "提示", "找不到配置文件,请查看使用说明!", QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No , QtWidgets.QMessageBox.Yes ) return e try: shutil.rmtree('temp') except Exception as e: pass try: shutil.rmtree(folder_end+'/汇总-漏洞跟踪表') except Exception as e: pass try: shutil.rmtree(folder_end+'/汇总-端口对应关系表') except Exception as e: pass try: shutil.rmtree(folder_end+'/汇总-WEB网站') except Exception as e: pass if self.hight_status or self.middle_status or self.low_status == True: starttime = datetime.datetime.now() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('正在提取数据,这个过程可能很慢,请耐心等待!' + '\n') QtWidgets.QApplication.processEvents() os.mkdir('temp') with open('temp/database.mdb', 'w',encoding='gb18030') as content: content.write('') dirList = os.listdir(folder_start) for name in dirList: all_file_name = re.findall(File_re().file_re,name) for file_name in all_file_name: uzip = zipfile.ZipFile(folder_start+'/'+file_name) for uzip_content in uzip.namelist(): all_uzip_content = re.findall(File_re().uzip_re,uzip_content) for all_uzip in all_uzip_content: htmlcont_zip = uzip.open(all_uzip).read().decode('utf8') title = re.findall(File_re().all_title_re,htmlcont_zip,re.S|re.M) for title_content in title: with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('<python>title<python>') content.write(html.unescape(title_content)) content.write('<python>title</python>\n') with open('temp/database.mdb','a',encoding='gb18030') as content: content.write('temp/'+html.unescape(title_content)+'.mdb\n') host = re.findall(File_re().host_re,htmlcont_zip,re.S|re.M) for host_content in host: with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('<python>ip<python>\n') content.write('<python>host<python>\n') content.write(html.unescape(host_content)) content.write('\n<python>host</python>\n') vul_list = re.findall(File_re().vul_list_re,htmlcont_zip,re.S|re.M) for list_content in vul_list: with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('<python>vul_list<python>\n') content.write(html.unescape(list_content)) content.write('\n<python>vul_list</python>\n') vul_detail = re.findall(File_re().vul_detail_re,htmlcont_zip,re.S|re.M) for detail_content in vul_detail: with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('<python>vul_detail<python>\n') vul_details = re.findall(File_re().vul_details_re,detail_content,re.S|re.M) for list_details in vul_details: with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('<python>vul_details<python>\n') content.write(html.unescape(list_details)) content.write('\n<python>vul_details</python>\n') with open('temp/%s.mdb'%title_content, 'a',encoding='gb18030') as content: content.write('\n<python>vul_detail</python>\n') content.write('<python>ip</python>\n') self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('提取 %s'%file_name + ' 完成!\n') QtWidgets.QApplication.processEvents() vul_list = queue.Queue() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('数据提取完成,正在生成漏洞跟踪表...\n') QtWidgets.QApplication.processEvents() os.mkdir(folder_end+'/汇总-漏洞跟踪表') vul_re = Vul_re() with open('temp/database.mdb',encoding='gb18030') as content: for zip_content in content: vul_all_list = [] vul_all_detail = [] zip_cont = zip_content.strip('\n\r') content = open(zip_cont,'r',encoding='gb18030') global htmlcont htmlcont = content.read() content.close() sheet_name = re.findall(vul_re.title_re,htmlcont,re.S|re.M)[0] sheet_time = re.findall(vul_re.time_re,htmlcont,re.S|re.M)[0] self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('正在导出 %s\n' % sheet_name) QtWidgets.QApplication.processEvents() wb = openpyxl.Workbook() ws = wb.active ws.title = '漏洞数据' ws.column_dimensions['A'].width = 6 ws.column_dimensions['B'].width = 13 ws.column_dimensions['C'].width = 24 ws.column_dimensions['D'].width = 11 ws.column_dimensions['E'].width = 11 ws.column_dimensions['F'].width = 53 ws.column_dimensions['G'].width = 6 ws.column_dimensions['H'].width = 6 ws.column_dimensions['I'].width = 53 ws.column_dimensions['J'].width = 53 ws.column_dimensions['K'].width = 15 ws.column_dimensions['L'].width = 11 ws.column_dimensions['M'].width = 13 ws.column_dimensions['N'].width = 13 ws.column_dimensions['O'].width = 13 ws.column_dimensions['P'].width = 16 ws.column_dimensions['Q'].width = 13 ws.column_dimensions['S'].width = 9 ws.column_dimensions['T'].width = 9 ws.column_dimensions['U'].width = 9 ws.column_dimensions['V'].width = 9 ws.column_dimensions['W'].width = 9 ws.column_dimensions['X'].width = 9 ws.column_dimensions['Y'].width = 11 ws['A3'] = '序号' ws['B3'] = '主机名' ws['C3'] = 'IP地址' ws['D3'] = '漏洞对应端口' ws['E3'] = '漏洞对应服务' ws['F3'] = '漏洞名称' ws['G3'] = '风险分类' ws['H3'] = '风险等级' ws['I3'] = '整改建议' ws['J3'] = '漏洞描述' ws['K3'] = '漏洞CVE编号' ws['L3'] = '漏洞对应协议' ws['M3'] = '是否已备案' ws['N3'] = '是否已整改' ws['O3'] = '%s核实是否已经整改' % company_ini ws['P3'] = '漏洞复核时间' ws['Q3'] = '备注' ws['S1'] = '高风险总数' ws['T1'] = '中风险总数' ws['U1'] = '修补高风险数' ws['V1'] = '修补中风险数' ws['W1'] = '高风险修补率' ws['X1'] = '中风险修补率' ws['Y1'] = '总修补率' ws['S2'].value = '=COUNTIF(H:H,"高")' ws['T2'].value = '=COUNTIF(H:H,"中")' ws['U2'].value = '=COUNTIFS(H:H,"高",O:O,"已完成整改")' ws['V2'].value = '=COUNTIFS(H:H,"中",O:O,"已完成整改")' ws['W2'].value = '=IF(S2=0,"无高风险漏洞",TEXT(U2/S2,"0.00%"))' ws['X2'].value = '=IF(T2=0,"无中风险漏洞",TEXT(V2/T2,"0.00%"))' ws['Y2'].value = '=TEXT((U2+V2)/(S2+T2),"0.00%")' ws['A1'] = '高中风险漏洞整改情况跟踪表' ws.merge_cells('A1:Q1') ws['A2'] = '系统名称' ws.merge_cells('A2:B2') ws['C2'] = '' ws.merge_cells('C2:F2') ws['G2'] = '管理员' ws.merge_cells('G2:H2') ws['K2'] = '检查人员' ws['L2'] = '%s'% name_ini ws.merge_cells('L2:M2') ws['O2'] = '检查时间' ws['P2'] = '%s' % sheet_time ws.merge_cells('P2:Q2') # 样式 font = Font(size=10, name='宋体') thin = Side(border_style="thin") border = Border(left=thin, right=thin, top=thin, bottom=thin) # 对齐 alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) #设置第三行的格式 title_font = Font(size=12, bold=True, name='宋体', color= "ff0000") ws.row_dimensions[3].height = 45 for title_style in ws['A3:L3']: for title_cell in title_style: title_cell.font = title_font title_cell.border = border title_cell.alignment = alignment #设置第三行的格式 title_end_font = Font(size=12, bold=True, name='宋体', color= "0000FF") ws.row_dimensions[3].height = 45 for title_end_style in ws['M3:Q3']: for title_cell in title_end_style: title_cell.font = title_end_font title_cell.border = border title_cell.alignment = alignment #设置第一行的格式 one_font = Font(size=20, bold=True, name='宋体') ws.row_dimensions[1].height = 45 for one_style in ws['A1:L1']: for one_cell in one_style: one_cell.font = one_font one_cell.border = border one_cell.alignment = alignment one_cell.fill = openpyxl.styles.PatternFill(fill_type='solid',fgColor="008000") #设置第二行的格式 two_font = Font(size=14, bold=True, name='宋体') ws.row_dimensions[2].height = 20 for one_style in ws['A2:Q2']: for one_cell in one_style: one_cell.font = two_font one_cell.border = border one_cell.alignment = alignment one_cell.fill = openpyxl.styles.PatternFill(fill_type='solid',fgColor="008000") #设置统计的格式 count_font = Font(size=12, bold=True, name='宋体') for count_style in ws['S1:Y2']: for count_cell in count_style: count_cell.font = count_font count_cell.border = border count_cell.alignment = alignment count_red = openpyxl.styles.PatternFill(fill_type='solid',fgColor="FF5353") count_yellow = openpyxl.styles.PatternFill(fill_type='solid',fgColor="FCCC2C") ws['S1'].fill = count_red ws['U1'].fill = count_red ws['W1'].fill = count_red ws['T1'].fill = count_yellow ws['V1'].fill = count_yellow ws['X1'].fill = count_yellow ws['Y1'].fill = openpyxl.styles.PatternFill(fill_type='solid',fgColor="008000") vul_content = Vul_content(vul_re) for all_vul_ip in vul_content.vul_ip_content: vul_list_content = re.findall(vul_re.vul_list_re,all_vul_ip,re.S|re.M) for all_vul_list in vul_list_content: for other in Other(vul_re,all_vul_list[1]).all_other: for danger in Danger(vul_re,other[3]).danger_coneent: vul_all_list.append([danger[1],all_vul_list[0],danger[2],danger[0].replace('low','低').replace('middle','中').replace('high','高'),other[0],other[1],other[2]]) for all_vul_detail in vul_content.vul_detail_content: vul_details_content = re.findall(vul_re.vul_details_re,all_vul_detail,re.S|re.M) for all_vul_details in vul_details_content: vul_detail = Solve(Solve_re(),all_vul_details) for solve,describe in zip(vul_detail.solve_plumb,vul_detail.describe_plumb): cve = vul_detail.cve_plumb if cve: pass else: cve = ['漏洞暂无CVE编号'] vul_all_detail.append([describe[0],re.sub('\s{2,}','\n',html.unescape(re.sub('\s{2,}','',solve)).replace('<br/>','\n')),re.sub('\s{2,}','\n',html.unescape(re.sub('\s{2,}','',describe[1])).replace('<br/>','\n')),cve[0]]) for line_vul_list in vul_all_list: vul_list.put(line_vul_list) i = 1 while not vul_list.empty(): wait_list = vul_list.get() for wait_detail in vul_all_detail: if wait_list[0] == wait_detail[0] and self.hight_status == True and wait_list[3] == '高': ws.row_dimensions[i+3].height = 25 ws.append([i,'',wait_list[1],wait_list[4],wait_list[6],wait_list[2],'漏洞',wait_list[3],wait_detail[1].strip('\n'),wait_detail[2].strip('\n'),wait_detail[3],wait_list[5]]) for row in ws['A%s:Q%s'%(i+3,i+3)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment i += 1 break if wait_list[0] == wait_detail[0] and self.middle_status == True and wait_list[3] == '中': ws.row_dimensions[i+3].height = 25 ws.append([i,'',wait_list[1],wait_list[4],wait_list[6],wait_list[2],'漏洞',wait_list[3],wait_detail[1].strip('\n'),wait_detail[2].strip('\n'),wait_detail[3],wait_list[5]]) for row in ws['A%s:Q%s'%(i+3,i+3)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment i += 1 break if wait_list[0] == wait_detail[0] and self.low_status == True and wait_list[3] == '低': ws.row_dimensions[i+3].height = 25 ws.append([i,'',wait_list[1],wait_list[4],wait_list[6],wait_list[2],'漏洞',wait_list[3],wait_detail[1].strip('\n'),wait_detail[2].strip('\n'),wait_detail[3],wait_list[5]]) for row in ws['A%s:Q%s'%(i+3,i+3)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment i += 1 break wb.save(folder_end+'/汇总-漏洞跟踪表/高中风险漏洞跟踪表--%s.xlsx' % sheet_name) del vul_all_list[:] del vul_all_detail[:] self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('漏洞跟踪表导出完成,保存在 %s/汇总-漏洞跟踪表 目录下。\n'%folder_end) QtWidgets.QApplication.processEvents() shutil.rmtree('temp') endtime = datetime.datetime.now() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('导出花时:%s秒...\n\n\n'%(endtime - starttime).seconds) QtWidgets.QApplication.processEvents() if self.port_status: self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('正在导出端口,请稍后!\n') QtWidgets.QApplication.processEvents() starttime = datetime.datetime.now() os.mkdir(folder_end+'/汇总-端口对应关系表') dirList = os.listdir(folder_start) for name in dirList: all_file_name = re.findall(Port_File_re().file_re,name) for file_name in all_file_name: uzip = zipfile.ZipFile(folder_start+'/'+file_name) i = 1 wb = openpyxl.Workbook() ws = wb.active ws.column_dimensions['A'].width = 16.5 ws.column_dimensions['B'].width = 16 ws.column_dimensions['C'].width = 20 ws.column_dimensions['D'].width = 30 ws.column_dimensions['E'].width = 25 ws.column_dimensions['F'].width = 28 ws.column_dimensions['G'].width = 42 ws.column_dimensions['H'].width = 17 ws.title = '端口数据' ws['A1'] = '设备端口和服务信息表' ws.merge_cells('A1:H1') ws['A2'] = '收集时间' ws.merge_cells('A2:B2') ws.merge_cells('C2:D2') ws['E2'] = '所属系统' ws.merge_cells('F2:H2') ws['A3'] = '填表人' ws['C3'] = name_ini ws.merge_cells('A3:B3') ws.merge_cells('C3:D3') ws['E3'] = '系统责任人' ws.merge_cells('F3:H3') ws['A4'] = 'IP地址' ws['B4'] = '端口' ws['C4'] = '协议' ws['D4'] = '服务' ws['E4'] = '状态' ws['F4'] = '访问权限开放范围' ws['G4'] = '应用说明' ws['H4'] = '备注' # 样式 font = Font(size=12, name='宋体') thin = Side(border_style="thin") border = Border(left=thin, right=thin, top=thin, bottom=thin) # 对齐 alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) for excel_style in ws['A2:H4']: for excel_cell in excel_style: excel_cell.font = font excel_cell.border = border excel_cell.alignment = alignment #设置第一行的格式 one_font = Font(size=12, bold=True, name='宋体') for one_style in ws['A1:H1']: for one_cell in one_style: one_cell.font = one_font one_cell.border = border one_cell.alignment = alignment for uzip_content in uzip.namelist(): all_uzip_content = re.findall(Port_File_re().uzip_re,uzip_content) for all_uzip in all_uzip_content: htmlcont_zip = uzip.open(all_uzip).read().decode('utf8') vul_title = re.findall(Port_File_re().all_title_re,htmlcont_zip,re.S|re.M) for title_content in vul_title: pass vul_host = re.findall(Port_File_re().host_re,htmlcont_zip,re.S|re.M) for host_content in vul_host: for vul_port in re.findall(Port_File_re().port_re,host_content[2],re.S|re.M): ws['C2'] = '%s' % host_content[1] ws.append([host_content[0],vul_port[0].replace(' ','').strip('\n'),vul_port[1].replace(' ','').strip('\n'),vul_port[2].replace(' ','').strip('\n'),vul_port[3].replace(' ','').strip('\n')]) for row in ws['A%s:H%s'%(i+4,i+4)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment i += 1 wb.save(folder_end+'/汇总-端口对应关系表/端口服务对应关系表--%s.xlsx' % title_content) self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('导出 %s'%title_content+' 完成!\n') QtWidgets.QApplication.processEvents() endtime = datetime.datetime.now() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('所有端口导出完成,保存在 %s/汇总-端口对应关系表 目录下。\n'%folder_end) QtWidgets.QApplication.processEvents() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('导出花时:%s秒...\n\n\n'%(endtime - starttime).seconds) QtWidgets.QApplication.processEvents() if self.web_status: self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('正在WEB网站,请稍后!\n') QtWidgets.QApplication.processEvents() starttime = datetime.datetime.now() os.mkdir(folder_end+'/汇总-WEB网站') dirList = os.listdir(folder_start) for name in dirList: all_file_name = re.findall(Port_File_re().file_re,name) for file_name in all_file_name: uzip = zipfile.ZipFile(folder_start+'/'+file_name) x = 1 web = openpyxl.Workbook() wes = web.active wes.title = 'WEB网站' wes.column_dimensions['A'].width = 16.5 wes.column_dimensions['B'].width = 16 wes.column_dimensions['C'].width = 20 wes.column_dimensions['D'].width = 30 wes.column_dimensions['E'].width = 25 wes.column_dimensions['F'].width = 45 wes['A1'] = 'IP地址' wes['B1'] = '端口' wes['C1'] = '协议' wes['D1'] = '服务' wes['E1'] = '状态' wes['F1'] = 'WEB网站信息' # 样式 font = Font(size=12, name='宋体') thin = Side(border_style="thin") border = Border(left=thin, right=thin, top=thin, bottom=thin) # 对齐 alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) for excel_style in wes['A1:F1']: for excel_cell in excel_style: excel_cell.font = font excel_cell.border = border excel_cell.alignment = alignment for uzip_content in uzip.namelist(): all_uzip_content = re.findall(Port_File_re().uzip_re,uzip_content) for all_uzip in all_uzip_content: htmlcont_zip = uzip.open(all_uzip).read().decode('utf8') vul_title = re.findall(Port_File_re().all_title_re,htmlcont_zip,re.S|re.M) for title_content in vul_title: pass vul_host = re.findall(Port_File_re().host_re,htmlcont_zip,re.S|re.M) for host_content in vul_host: for vul_port in re.findall(Port_File_re().port_re,host_content[2],re.S|re.M): vul_web = re.findall(Port_File_re().http_re,vul_port[2].replace(' ','').strip('\n'),re.S|re.M) if vul_web: wes.append([host_content[0],vul_port[0].replace(' ','').strip('\n'),vul_port[1].replace(' ','').strip('\n'),vul_port[2].replace(' ','').strip('\n'),vul_port[3].replace(' ','').strip('\n'),'http://'+ host_content[0] + ':' + vul_port[0].replace(' ','').strip('\n')]) for row in wes['A%s:E%s'%(x+1,x+1)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment wes['F%s' % (x+1)].font = font wes['F%s' % (x+1)].border = border x += 1 vul_web = re.findall(Port_File_re().https_re,vul_port[2].replace(' ','').strip('\n'),re.S|re.M) if vul_web: wes.append([host_content[0],vul_port[0].replace(' ','').strip('\n'),vul_port[1].replace(' ','').strip('\n'),vul_port[2].replace(' ','').strip('\n'),vul_port[3].replace(' ','').strip('\n'),'https://'+ host_content[0] + ':' + vul_port[0].replace(' ','').strip('\n')]) for row in wes['A%s:E%s'%(x+1,x+1)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment wes['F%s' % (x+1)].font = font wes['F%s' % (x+1)].border = border x += 1 vul_web = re.findall(Port_File_re().www_re,vul_port[2].replace(' ','').strip('\n'),re.S|re.M) if vul_web: wes.append([host_content[0],vul_port[0].replace(' ','').strip('\n'),vul_port[1].replace(' ','').strip('\n'),vul_port[2].replace(' ','').strip('\n'),vul_port[3].replace(' ','').strip('\n'),'http://'+ host_content[0] + ':' + vul_port[0].replace(' ','').strip('\n')]) for row in wes['A%s:E%s'%(x+1,x+1)]: for cell in row: cell.font = font cell.border = border cell.alignment = alignment wes['F%s' % (x+1)].font = font wes['F%s' % (x+1)].border = border x += 1 web.save(folder_end+'/汇总-WEB网站/WEB网站--%s.xlsx' % title_content) self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('导出 %s'%title_content+' 完成!\n') QtWidgets.QApplication.processEvents() endtime = datetime.datetime.now() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('所有WEB网站导出导出完成,保存在 %s/汇总-WEB网站 目录下。\n'%folder_end) QtWidgets.QApplication.processEvents() self.log_textEdit.moveCursor(QtGui.QTextCursor.End) self.log_textEdit.insertPlainText('导出花时:%s秒...\n\n\n'%(endtime - starttime).seconds) QtWidgets.QApplication.processEvents()
import openpyxl from openpyxl.styles import Alignment from openpyxl.styles import Border, Side from openpyxl.styles import Color, PatternFill from collections import defaultdict align = Alignment(vertical='center', wrap_text=True) 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')) fill = PatternFill(patternType='solid', fill_type='solid', fgColor=Color('FFFF00')) instock_field_order = {'CATAGORY_NAME': 1, 'GOODS_NAME': 2, 'STOCK_DATE': 3, 'GOODS_NUM': 4, 'GOODS_UNIT': 5, 'GOODS_PRICE': 6, 'OP_AREA': 7, 'OP_PERSON': 8, 'TOTAL': 15} outstock_field_order = {'CATAGORY_NAME': 1, 'GOODS_NAME': 2, 'STOCK_DATE': 9, 'GOODS_NUM': 10, 'GOODS_UNIT': 11, 'GOODS_PRICE': 12, 'OP_AREA': 13, 'OP_PERSON': 14, 'TOTAL': 16} def downloadInStockDetail(filename, title, data): field_order = ['CATAGORY_NAME', 'GOODS_NAME', 'STOCK_DATE', 'GOODS_NUM', 'GOODS_UNIT', 'GOODS_PRICE', 'OP_AREA', 'OP_PERSON', 'TOTAL'] wb = openpyxl.load_workbook('template/InStockTemplate.xlsx') sheet = wb.active
def export_threat_modeling(request, appId): 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}-TM-report.xlsx'.format( date=datetime.now().strftime('%Y-%m-%d'), name=MACM.objects.get(appId=appId).application.replace( " ", "_")) workbook = Workbook() # Get active worksheet/tab worksheet = workbook.active worksheet.title = 'Threat_modeling_REPORT' columns = [ '#', 'Threat Agent', 'Asset name', 'Asset type', 'Threat', 'CIA', 'STRIDE', 'Behaviour' ] 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'), ) threats_list = threat_modeling(appId) ThreatAgents = (MACM_ThreatAgent.objects.all().filter( app=MACM.objects.get(appId=appId))) for ta in ThreatAgents: for threat in threats_list: row_num += 1 stride = "" cia = "" for index, single in enumerate(threat[1]): if not index == len(threat[1]) - 1: stride += single + ", " else: stride += single for index, single in enumerate(threat[2]): if not index == len(threat[1]) - 1: cia += single + ", " else: cia += single # columns = ['Asset name', 'Asset type', 'Threat', 'Description', 'CIA', 'STRIDE'] #print(threat[4].attribute_value) # Define the data for each cell in the row row = [ row_num, ta.category.category, threat[3], threat[4].attribute_value, threat[0].name, cia, stride, threat[0].description, ] # 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'), ) 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 = {} from openpyxl.styles import Alignment for row in worksheet.rows: for cell in row: cell.alignment = Alignment(wrap_text=True) if cell.value: dims[cell.column_letter] = max( (dims.get(cell.column_letter, 0), len(str(cell.value)))) + 0.05 for col, value in dims.items(): worksheet.column_dimensions[col].width = value workbook.save(response) return response
import openpyxl import pandas from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment from os import path thin = Side(border_style="thin", color="0089bb") titulo_border = Border(top=thin, left=thin, right=thin, bottom=thin) titulo_rect = PatternFill("solid", fgColor="0089bb") titulo_font_big = Font(name='Calibri', size=16, b=True, color="ffffff") titulo_font_little = Font(name='Calibri', size=12, b=True, color="ffffff") titulo_alignment = Alignment(horizontal="center", vertical="center") def load_workbook(wb_path): if path.exists(wb_path): return openpyxl.load_workbook(wb_path) return openpyxl.Workbook() def agregar_imagen(sheet): img = openpyxl.drawing.image.Image('../Data/image/see_webinar.png') sheet.add_image(img, 'A1') def agregar_titulo(ws): ws.merge_cells('A5:N5') top_left_cell = ws['A5'] top_left_cell.value = "Reporte de los Países" top_left_cell.border = titulo_border top_left_cell.fill = titulo_rect
URL = 'https://short-msg-ms.juejin.im/v1/pinList/recommend?uid=&device_id=&token=&src=web&before&limit=10' if __name__ == '__main__': wb = Workbook() filename = 'excel练习.xlsx' ws1 = wb.active ws1.title = "肥" dic = requests.get(URL).json() # 获取掘金十条数据 lis = dic['d']['list'] print(lis) # 设置单元格的对齐 alignment = Alignment( horizontal='general', # 水平:常规 vertical='bottom', # 垂直:底部对齐 text_rotation=0, # 文本方向:0度 wrap_text=False, # 自动换行 shrink_to_fit=False, # 缩小字体填充 indent=0 # 缩进0 ) # 调用单元格对齐方式 ws1['A1'].alignment = alignment font1 = Font(name=u'微软雅黑', size=20, strike=True, color='000000', vertAlign='subscript', bold=True, italic=False, underline='none') ws1['a1'].font = font1 ws1['b1'].font = font1 a = 1 ws1['A%s' % a] = '名字' ws1['B%s' % a] = '内容content' for i in lis:
if fill: for c in row: c.fill = fill ''' # imports from openpyxl import load_workbook wb = load_workbook(filename='Plymouth_Daily_Rounds.xlsx') from openpyxl.styles import Alignment, Border, Side, NamedStyle, Font, PatternFill, GradientFill sheet = wb['Page_01'] print('Active sheet is ', sheet) indexNumber = wb.worksheets.index(wb['Page_01']) wb.save('Plymouth_Daily_Rounds.xlsx') # Global Variables center = Alignment(horizontal='center', vertical='center', wrap_text=True) ctrdwn = Alignment(horizontal='center', vertical='bottom') right = Alignment(horizontal='right', vertical='bottom') left = Alignment(horizontal='left', vertical='center', wrap_text=True) leftTop = Alignment(horizontal='left', vertical='top') wrap = Alignment(wrap_text=True) thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) thick_border = Border(left=Side(style='thick'), right=Side(style='thick'), top=Side(style='thick'), bottom=Side(style='thick')) no_top_border = Border(left=Side(style='thin'), right=Side(style='thin'),
result = questionsCollectionExcel.__getitem__(r).testresult remarks = questionsCollectionExcel.__getitem__(r).remarks bold_itatic_24_font_fail = Font(name='Calibri', size=11, italic=False, color=colors.RED, bold=True) bold_itatic_24_font_pass = Font(name='Calibri', size=11, italic=False, color=colors.GREEN, bold=True) ws.append([testQuestion, stdQuestion, result, remarks]) alignment = Alignment(wrap_text=True) # 设置 备注单元格为自动换行 if result == 'fail': ws['C' + str(r + 2)].font = bold_itatic_24_font_fail ws['D' + str(r + 2)].alignment = alignment failed += 1 else: ws['C' + str(r + 2)].font = bold_itatic_24_font_pass ws['D' + str(r + 2)].alignment = alignment passed += 1 wb.save(testresult) print('\n\n>>>>>> 测试结束! 用时:', str(int(now() - start)) + ' 秒') print( '>>>>>> 共:',
#Para darle formato al archivo________________________________________________________ doc = openpyxl.load_workbook('Resumen_glosas.xlsx') H = doc.get_sheet_names( ) #la variable H guarda un listado con las hojas del excel hoja1 = doc.get_sheet_by_name(str( H[0])) #con H[0], le decimos que use la primera hoja del excel #Ancho de columna___________________________________________________________________________ hoja1.column_dimensions['B'].width = 60 #alineacion a la izquierda______________________________________________________________ for row in hoja1['B1':'B' + str(pos_tablas[-1] + espacio_de_tablas)]: for cell in row: cell.alignment = Alignment(horizontal="left") #alineacion vertical al centro______________________________________________________________ for row in hoja1['B1':'B' + str(pos_tablas[-1] + espacio_de_tablas)]: for cell in row: cell.alignment = Alignment(vertical="center") #cabeceras__________________________________________________________ hoja1["B1"] = "21 GASTOS EN PERSONAL" if "Honorarios asim. Ley 18.834" in clasificaciones: hoja1["B" + str(espacio_de_tablas * 1 + 1)] = "Honorarios asim. Ley 18.834 sin experimentales" if "Honorarios asim. Ley Médica" in clasificaciones:
def SetFormat(self,row,col,fmt): pyws = self.pyws font = None color = None align = None fill = None numFmt = None border = None c = pyws.cell(row=row,column=col) #------------------------------------------------------------------------- for i in fmt: if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] elif (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] elif (i == 'tAlign'): if (not align): align = Alignment() align.text_rotation = fmt[i] elif (i == 'wrap'): if (not align): align = Alignment() align.wrap_text = fmt[i] elif (i == 'font'): name = 'Calibri' bold = False size = 11 dict = fmt[i] if ('emph' in dict): if (dict['emph'] == 'B'): bold = True if ('size' in dict): size = dict['size'] if (not font): font = Font(name=name,size=size,bold=bold) elif (i == 'border'): dict = fmt[i] color = None style = None if ('Color' in dict): color = ColorTable[dict['Color']] else: color = ColorTable['Black'] if ('Style' in dict): color = dict['Style'] if (c.border.top.style == None): tSide = Side(color=color) else: tSide = c.border.top.copy() if (c.border.bottom.style == None): bSide = Side(color=color) else: bSide = c.border.bottom.copy() if (c.border.left.style == None): lSide = Side(color=color) else: lSide = c.border.left.copy() if (c.border.right.style == None): rSide = Side(color=color) else: rSide = c.border.right.copy() if ((len(dict) ==1) and ('A' in dict)): tSide.style = dict['A'] bSide.style = dict['A'] lSide.style = dict['A'] rSide.style = dict['A'] else: for j in dict: if (j == 'T'): tSide.style = dict[j] if (j == 'B'): bSide.style = dict[j] if (j == 'L'): lSide.style = dict[j] if (j == 'R'): rSide.style = dict[j] border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide) elif (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') elif (i == 'orient'): pass elif (i == 'numFmt'): numFmt = fmt[i] #------------------------------------------------------------------------- if (font): c.font = font.copy() if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt
def create_lady_supervisor_excel_file(excel_data, data_type, month, aggregation_level): export_info = excel_data[1][1] state = export_info[1][1] if aggregation_level > 0 else '' district = export_info[2][1] if aggregation_level > 1 else '' block = export_info[3][1] if aggregation_level > 2 else '' excel_data = [line[aggregation_level:] for line in excel_data[0][1]] thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) warp_text_alignment = Alignment(wrap_text=True) bold_font = Font(bold=True) blue_fill = PatternFill("solid", fgColor="B3C5E5") grey_fill = PatternFill("solid", fgColor="BFBFBF") workbook = Workbook() worksheet = workbook.active worksheet.title = "LS Performance Report" worksheet.sheet_view.showGridLines = False # sheet title amount_of_columns = 9 - aggregation_level last_column = string.ascii_uppercase[amount_of_columns] worksheet.merge_cells('B2:{0}2'.format(last_column)) title_cell = worksheet['B2'] title_cell.fill = PatternFill("solid", fgColor="4472C4") title_cell.value = "Lady Supervisor Performance Report for the {}".format( month) title_cell.font = Font(size=18, color="FFFFFF") title_cell.alignment = Alignment(horizontal="center") columns = [ string.ascii_uppercase[i] for i in range(1, amount_of_columns + 1) ] # sheet header header_cells = ['{0}3'.format(column) for column in columns] for cell in header_cells: worksheet[cell].fill = blue_fill worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment if state: worksheet['B3'].value = "State: {}".format(state) worksheet.merge_cells('B3:C3') if district: worksheet['D3'].value = "District: {}".format(district) if block: worksheet['E3'].value = "Block: {}".format(block) date_cell = '{0}3'.format(last_column) date_description_cell = '{0}3'.format( string.ascii_uppercase[amount_of_columns - 1]) worksheet[date_description_cell].value = "Date when downloaded:" worksheet[date_description_cell].alignment = Alignment(horizontal="right") utc_now = datetime.now(pytz.utc) now_in_india = utc_now.astimezone(india_timezone) worksheet[date_cell].value = custom_strftime('{S} %b %Y', now_in_india) worksheet[date_cell].alignment = Alignment(horizontal="right") # table header table_header_position_row = 5 header_data = excel_data[0] headers = ["S.No"] headers.extend(header_data) table_header = {} for col, header in zip(columns, headers): table_header[col] = header for column, value in table_header.items(): cell = "{}{}".format(column, table_header_position_row) worksheet[cell].fill = grey_fill worksheet[cell].border = thin_border worksheet[cell].font = bold_font worksheet[cell].alignment = warp_text_alignment worksheet[cell].value = value # table contents row_position = table_header_position_row + 1 for enum, row in enumerate(excel_data[1:], start=1): for column_index in range(len(columns)): column = columns[column_index] cell = "{}{}".format(column, row_position) worksheet[cell].border = thin_border if column_index == 0: worksheet[cell].value = enum else: worksheet[cell].value = row[column_index - 1] row_position += 1 # sheet dimensions title_row = worksheet.row_dimensions[2] title_row.height = 23 worksheet.row_dimensions[table_header_position_row].height = 46 widths = {} widths_columns = ['A'] widths_columns.extend(columns) standard_widths = [4, 7] standard_widths.extend([15] * (4 - aggregation_level)) standard_widths.extend([25, 15, 25, 15]) for col, width in zip(widths_columns, standard_widths): widths[col] = width widths['C'] = max(widths['C'], len(state) * 4 // 3 if state else 0) widths['D'] = 9 + (len(district) * 4 // 3 if district else 0) widths['E'] = 8 + (len(block) * 4 // 3 if district else 0) columns = columns[1:] # column widths based on table contents for column_index in range(len(columns)): widths[columns[column_index]] = max( widths[columns[column_index]], max( len(row[column_index].decode('utf-8') if isinstance( row[column_index], bytes) else six. text_type(row[column_index])) for row in excel_data[1:]) * 4 // 3 if len(excel_data) >= 2 else 0) for column, width in widths.items(): worksheet.column_dimensions[column].width = width # export info worksheet2 = workbook.create_sheet("Export Info") worksheet2.column_dimensions['A'].width = 14 for n, export_info_item in enumerate(export_info, start=1): worksheet2['A{0}'.format(n)].value = export_info_item[0] worksheet2['B{0}'.format(n)].value = export_info_item[1] # saving file file_hash = uuid.uuid4().hex export_file = BytesIO() icds_file = IcdsFile(blob_id=file_hash, data_type=data_type) workbook.save(export_file) export_file.seek(0) icds_file.store_file_in_blobdb(export_file, expired=60 * 60 * 24) icds_file.save() return file_hash