def format_cell(self,cell,type = 'normal'): # my_font = Font(size = 8) my_font = Font(name='Calibri', size=8, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000' # color='FFBB00' ) if type == "error": my_font.color = 'F60' my_font.size = 11 cell.font = my_font
def parse_font(self, font_node): """Read individual font""" font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') bold = font_node.find('{%s}b' % SHEET_MAIN_NS) if bold is not None: font.bold = bool(bold.get('val', True)) italic = font_node.find('{%s}i' % SHEET_MAIN_NS) if italic is not None: font.italic = bool(italic.get('val', True)) underline = font_node.find('{%s}u' % SHEET_MAIN_NS) if underline is not None: font.underline = underline.get('val', 'single') strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS) if strikethrough is not None: font.strikethrough = True color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: font.color.index = self._get_relevant_color(color) return font
def test_conditional_formatting_addDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK dxfId = cf.addDxfStyle(self.workbook, font, borders, fill) assert dxfId == 0 dxfId = cf.addDxfStyle(self.workbook, None, None, fill) assert dxfId == 1 assert len(self.workbook.style_properties['dxf_list']) == 2
def parse_fonts(root, color_index): """Read in the fonts""" font_list = [] fonts = root.find('{%s}fonts' % SHEET_MAIN_NS) if fonts is not None: font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS) for font_node in font_nodes: font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)): underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val') font.underline = underline if underline else 'single' color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index): font.color.index = color_index[int(color.get('indexed'))] elif color.get('theme') is not None: if color.get('tint') is not None: font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): font.color.index = color.get('rgb') font_list.append(font) return font_list
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def format_worksheet(worksheet, scenario_note, imei, scenario_start_time, rssi_power_list, rssi_average_list): # FUNCTION FOR SETTING UP THE EXCEL WORKSHEET LAYOUT, FORMAT AND CHART line_chart = LineChart() line_chart.title = "RSSI Power Graph" line_chart.y_axis.title = 'RSSI Level' line_chart.x_axis.title = 'Data Count' rssi_power_data = Reference(worksheet, min_col=9, min_row=8, max_col=9, max_row=len(rssi_power_list) + 7) power_series = Series(rssi_power_data, title="Actual RSSI Power") line_chart.append(power_series) rssi_average_data = Reference(worksheet, min_col=15, min_row=8, max_col=15, max_row=len(rssi_average_list) + 7) average_series = Series(rssi_average_data, title="Averaged RSSI Power") line_chart.append(average_series) worksheet.add_chart(line_chart, "Q6") worksheet.merge_cells('A1:O1') worksheet.merge_cells('A3:O3') worksheet.merge_cells('A4:O4') worksheet.merge_cells('A6:C6') worksheet.merge_cells('E6:I6') worksheet.merge_cells('K6:O6') worksheet['A3'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['B7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['C7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['E6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['E7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['F7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['G7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['H7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['I7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['K6'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['K7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['L7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['M7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['N7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['O7'].fill = PatternFill(start_color="1e90ff", end_color="1e90ff", fill_type="solid") worksheet['A1'].alignment = Alignment(horizontal='center') worksheet['A3'].alignment = Alignment(horizontal='center') worksheet['A4'].alignment = Alignment(horizontal='center') worksheet['A6'].alignment = Alignment(horizontal='center') worksheet['D6'].alignment = Alignment(horizontal='center') worksheet['E6'].alignment = Alignment(horizontal='center') worksheet['J6'].alignment = Alignment(horizontal='center') worksheet['K6'].alignment = Alignment(horizontal='center') worksheet['A3'].font = Font(bold=True) worksheet['A6'].font = Font(bold=True) worksheet['K6'].font = Font(bold=True) worksheet['A1'].font = Font(bold=True) worksheet['E6'].font = Font(bold=True) worksheet['A1'] = 'RSSI PARSE RESULTS' worksheet['A3'] = 'Scenario notes' worksheet['A4'] = scenario_note worksheet['A6'] = 'Test details' worksheet['A7'] = 'Target IMEI' worksheet['A8'] = imei worksheet['B7'] = 'Start time' worksheet['B8'] = scenario_start_time worksheet['C7'] = 'Duration' worksheet['E6'] = 'Actual RSSI Power' worksheet['E7'] = 'Data count' worksheet['E8'] = '=COUNT(I8:I1048576)' worksheet['F7'] = 'Average' worksheet['F8'] = '=AVERAGE(I8:I1048576)' worksheet['G7'] = 'Max' worksheet['G8'] = '=MAX(I8:I1048576)' worksheet['H7'] = 'Min' worksheet['H8'] = '=MIN(I8:I1048576)' worksheet['I7'] = 'Data' worksheet['K6'] = 'Averaged RSSI Power' worksheet['K7'] = 'Data count' worksheet['K8'] = '=COUNT(O8:O1048576)' worksheet['L7'] = 'Average' worksheet['L8'] = '=AVERAGE(O8:O1048576)' worksheet['M7'] = 'Max' worksheet['M8'] = '=MAX(O8:O1048576)' worksheet['N7'] = 'Min' worksheet['N8'] = '=MIN(O8:O1048576)' worksheet['O7'] = 'Data'
# Master Sheet (Sheet0) if found == 1: if 'Sheet0' not in wb.sheetnames: # head = [] #head = ['Name', 'PS Number', 'Email', 'Phone Number', 'Batch', 'Location', 'BU', 'XYZ'] ws = wb.create_sheet('Sheet0') print("CREATING") s = ws.max_row # variable to store max rows for sl num for i in range(1, len(head)+1): ws.cell(row=1, column=i).value = head[i - 1] for i in range(1, len(head)+1): clr = ws.cell(row=1, column=i) clr.font = Font(bold=True) for i in range(1, len(head)+1): ws.cell(row=s + 1, column=i).value = data[i - 1] wb.save(path) else: # ws = wb.get_sheet_by_name('Sheet0') ws = wb['Sheet0'] s = ws.max_row for i in range(1, len(head)+1): ws.cell(row=s + 1, column=i).value = data[i - 1] wb.save(path) if found == 0: print("DATA NOT FOUND") from openpyxl.chart import BarChart3D, Series, Reference
def cabecera(listubicacion, lista, wb, tipo, empadronadores): idemp = lista[1] if tipo == 1: scr = lista[2] else: scr = lista[-3:] ccdd = listubicacion[0] ccpp = listubicacion[1] ccdi = listubicacion[2] departamento = listubicacion[3] provincia = listubicacion[4] distrito = listubicacion[5] if tipo == 1: ws = wb.get_sheet_by_name(idemp) else: ws = wb.get_sheet_by_name(lista) ws["D1"] = u'CENSOS NACIONALES: XII DE LA POBLACION, VII DE VIVIENDA Y III DE COMUNIDADES INDIGENAS' ws['D1'].alignment = Alignment(horizontal="center", vertical="center") ws["D1"].font = Font(bold=True) ws.merge_cells('D1:N1') ws["D2"] = u'III Censo de Comunidades Nativas y I Censo de Comunidades Campesinas' ws['D2'].alignment = Alignment(horizontal="center", vertical="center") ws["D2"].font = Font(bold=True) ws.merge_cells('D2:N2') ws["D4"] = u'PROGRAMACION DE RUTAS DE TRABAJO RURAL' ws['D4'].alignment = Alignment(horizontal="center", vertical="center") ws["D4"].font = Font(bold=True, size=12) ws.merge_cells('D4:N4') ws["A6"] = u'DEPARTAMENTO' ws["A7"] = u'PROVINCIA' ws["A8"] = u'DISTRITO' ws["M6"] = u'EMPADRONADOR' ws["M8"] = u'JEFE DE SECCION' ws["A10"] = u'DIA' ws["A11"] = u'AER' ws["A12"] = u'ACTIVIDAD' ws["A23"] = u'G.OPER.' ws["A24"] = u'PASAJES' ws["A25"] = u'V: VIAJE' ws["A26"] = u'E: EMPADRONAMIENTO' ws["A27"] = u'T: TRASLADO' ws["A28"] = u'S: SUPERVISION' ws["Q5"] = u'Doc.CPV.03.105' ws["A25"].font = Font(bold=True) ws["A26"].font = Font(bold=True) ws["A27"].font = Font(bold=True) ws["A28"].font = Font(bold=True) ws["Q5"].font = Font(bold=True) ws["Q5"].alignment = Alignment(horizontal="right", vertical="bottom") ws["Q10"] = u'TOTAL' diasCuerpo(ws) for x in ['A10', 'A11', 'A12', 'A21', 'A22', 'Q8', 'Q10']: ws[x].alignment = Alignment(horizontal="center", vertical="center") colorCelda(ws) formatocampos(ws) infoubicacion = [ccdd, ccpp, ccdi, departamento, provincia, distrito] for x in range(6, 9): ws["C{}".format(x)] = infoubicacion[x - 6] ws["D{}".format(x)] = infoubicacion[x - 3] if tipo == 1: emp = lista[0] ws['O6'] = emp ws.cell(row=6, column=17).border = thin_border elif tipo == 2: listaEmp = [] for i in empadronadores: if i[0] == lista: listaEmp.append(i[1]) else: pass ws["O6"] = '-'.join(listaEmp) ws.cell(row=6, column=17).border = thin_border del listaEmp ws['O8'] = scr ws.cell(row=8, column=17).border = thin_border for x in range(13, 23): ws["A{}".format(x)] = u'CC.PP.' ws["A{}".format(x)].alignment = Alignment(horizontal="center", vertical="center") ws.merge_cells('Q10:Q12') listaceldas = ['A6:B6', 'A7:B7', 'A8:B8', 'D6:H6', 'D7:H7', 'D8:H8'] # 'N6:O6', 'N8:O8', 'P6:Q6', 'P8:Q8' # for x in ['M6', 'M8', 'O6', 'O8', 'C6', 'C7', 'C8']: # ws[x].alignment = Alignment(horizontal="center", vertical="center") for cells in listaceldas: ws.merge_cells(cells) set_border(cells, ws) for cells in [6, 7, 8]: ws.cell(row=cells, column=3).border = thin_border for cells in ['D6:H6', 'D7:H7', 'D8:H8']: ws.merge_cells(cells) set_border(cells, ws) cuerpo_border('A10:Q24', ws)
Author: <David Emenike> ''' import argparse import sys import openpyxl from openpyxl.utils import get_column_letter from openpyxl.styles import Font number = int(sys.argv[1]) cells = number + 1 wb = openpyxl.Workbook() sheet = wb.active make_bold = Font(bold=True) while cells > 1: # Write outer cels values and make them bold sheet.cell(row=cells, column=1).value = cells - 1 sheet.cell(row=1, column=cells).value = cells - 1 sheet.cell(row=cells, column=1).font = make_bold sheet.cell(row=1, column=cells).font = make_bold cells -= 1 col_length = number + 1 count = 0 while count < number:
def load_weights(self, weightfile, layer_num, channel_bins_pow, \ vis=None, featureLayers=None, show=3, view="flat", \ dn_ref=None, cmp_precision=4, log=None): #Open the weights file fp = open(weightfile, "rb") #The first 4 values are header information # 1. Major version number # 2. Minor Version Number # 3. Subversion number # 4. IMages seen version = np.fromfile(fp, count=3, dtype=np.int32) version = [int(i) for i in version] if version[0] * 10 + version[1] >= 2 and version[0] < 1000 and version[ 1] < 1000: seen = np.fromfile(fp, count=1, dtype=np.int64) else: seen = np.fromfile(fp, count=1, dtype=np.int32) self.header = torch.from_numpy(np.concatenate((version, seen), axis=0)) self.seen = int(seen) #The rest of the values are the weights # Let's load them up ann_version = False ANN_FORMAT = 1 << 12 if (ANN_FORMAT == version): ann_version = True if ann_version is True: n_layers = np.fromfile(fp, dtype=np.int32, count=1) fp.seek(4 * n_layers[0], 1) else: weights = np.fromfile(fp, dtype=np.float32) show_weights = False if vis and dn_ref is None: show_weights = True cmp_weights = False if dn_ref is not None: cmp_weights = True nLayerCmp = 0 nErr = 0 nSkip = 0 ptr = 0 conv_num = 0 for i in range(len(self.module_list)): module_type = self.blocks[i + 1]["type"] if module_type == "convolutional" or module_type == "dilated_convolutional": #dilated_convolutional for deeplab if (ann_version is True): fp.seek(4 * 2, 1) #print(str(i)+" "+str(self.blocks[i + 1])) s_layer = "layer {} {}: ".format(i, module_type) show_layer_weights = False if show_weights and (featureLayers == None or len(featureLayers) == 0 or featureLayers.split().count(str(i)) > 0): show_layer_weights = True cmp_layer_weights = False if cmp_weights and (featureLayers == None or len(featureLayers) == 0 or featureLayers.split().count(str(i)) > 0): cmp_layer_weights = True model = self.module_list[i] #compare weights model_ref = None if cmp_layer_weights: nLayerCmp += 1 model_ref = dn_ref.module_list[i] try: batch_normalize = int(self.blocks[i + 1]["batch_normalize"]) except: batch_normalize = 0 conv = model[0] #compare weights conv_ref = None if model_ref is not None: conv_ref = model_ref[0] if (batch_normalize > 0): bn = model[1] #compare weights bn_ref = None if model_ref is not None: bn_ref = model_ref[1] #Get the number of weights of Batch Norm Layer num_bn_biases = bn.bias.numel() #Load the weights if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_bn_biases) ptr = 0 bn_biases = torch.from_numpy(weights[ptr:ptr + num_bn_biases]) ptr += num_bn_biases if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_bn_biases) ptr = 0 bn_weights = torch.from_numpy(weights[ptr:ptr + num_bn_biases]) ptr += num_bn_biases if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_bn_biases) ptr = 0 bn_running_mean = torch.from_numpy(weights[ptr:ptr + num_bn_biases]) ptr += num_bn_biases if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_bn_biases) ptr = 0 bn_running_var = torch.from_numpy(weights[ptr:ptr + num_bn_biases]) ptr += num_bn_biases #Cast the loaded weights into dims of model weights. bn_biases = bn_biases.view_as(bn.bias.data) bn_weights = bn_weights.view_as(bn.weight.data) bn_running_mean = bn_running_mean.view_as(bn.running_mean) bn_running_var = bn_running_var.view_as(bn.running_var) #Copy the data to model bn.bias.data.copy_(bn_biases) bn.weight.data.copy_(bn_weights) bn.running_mean.copy_(bn_running_mean) bn.running_var.copy_(bn_running_var) #compare weights if bn_ref: no_diff, is_skip = tensor_diff(vis, bn.bias, bn_ref.bias, cmp_precision, s_layer + "bn bias", log, 0) if no_diff == False: nErr += 1 if is_skip == True: nSkip += 1 no_diff, is_skip = tensor_diff(vis, bn.weight, bn_ref.weight, cmp_precision, s_layer + "bn weight", log, 0) if no_diff == False: nErr += 1 if is_skip == True: nSkip += 1 no_diff, is_skip = tensor_diff(vis, bn.running_mean, bn_ref.running_mean, cmp_precision, s_layer + "bn mean", log, 0) if no_diff == False: nErr += 1 if is_skip == True: nSkip += 1 no_diff, is_skip = tensor_diff(vis, bn.running_var, bn_ref.running_var, cmp_precision, s_layer + "bn var", log, 0) if no_diff == False: nErr += 1 if is_skip == True: nSkip += 1 #if tensor_diff(vis, bn.bias, bn_ref.bias, cmp_precision, s_layer+"bn bias", log, 0) == False: # nErr += 1 #if tensor_diff(vis, bn.weight, bn_ref.weight, cmp_precision, s_layer+"bn weight", log, 0) == False: # nErr += 1 #if tensor_diff(vis, bn.running_mean, bn_ref.running_mean, cmp_precision, s_layer+"bn mean", log, 0) == False: # nErr += 1 #if tensor_diff(vis, bn.running_var, bn_ref.running_var, cmp_precision, s_layer+"bn var", log, 0) == False: # nErr += 1 if show_layer_weights == True: self.visualizeWeights(vis, i, bn_biases, show, view, s_layer + "bn bias", log) self.visualizeWeights(vis, i, bn_weights, show, view, s_layer + "bn weight", log) self.visualizeWeights(vis, i, bn_running_mean, show, view, s_layer + "bn mean", log) self.visualizeWeights(vis, i, bn_running_var, show, view, s_layer + "bn var", log) else: #Number of biases num_biases = conv.bias.numel() #Load the weights if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_biases) ptr = 0 conv_biases = torch.from_numpy(weights[ptr:ptr + num_biases]) ptr = ptr + num_biases #reshape the loaded weights according to the dims of the model weights conv_biases = conv_biases.view_as(conv.bias.data) #Finally copy the data conv.bias.data.copy_(conv_biases) #compare weights if conv_ref: no_diff, is_skip = tensor_diff(vis, conv.bias, conv_ref.bias, cmp_precision, s_layer + "conv bias", log, 0) if no_diff == False: nErr += 1 if is_skip == True: nSkip += 1 #if tensor_diff(vis, conv.bias, conv_ref.bias, cmp_precision, s_layer+"conv bias", log, 0) == False: # nErr += 1 if show_layer_weights: self.visualizeWeights(vis, i, conv_biases, show, view, s_layer + "conv bias", log) #Let us load the weights for the Convolutional layers num_weights = conv.weight.numel() #Do the same as above for weights if (ann_version is True): fp.seek(4, 1) weights = np.fromfile(fp, dtype=np.float32, count=num_weights) ptr = 0 conv_weights = torch.from_numpy(weights[ptr:ptr + num_weights]) ptr = ptr + num_weights conv_weights = conv_weights.view_as(conv.weight.data) conv_num += 1 # print("*****************************************************************") # print(conv_num) # print(i) # layer num 0~160, 161 is yolo # print(conv_weights.shape[0]) # e.g. layer = 157, conv No.107 convweights.shape = [1024, 512, 3, 3] if i == layer_num: print( "*****************************************************************" ) print('conv_weights.shape: {}'.format(conv_weights.shape)) conv_batches = conv_weights.shape[0] # 1024 conv_channels = conv_weights.shape[1] # 512 conv_heights = conv_weights.shape[2] # 3 conv_widthes = conv_weights.shape[3] # 3 print("valid:", conv_batches, conv_channels, conv_heights, conv_widthes) channel_bins = pow( 2, channel_bins_pow ) # value is 16, means group_num = 1024/16 = 64 channel_bins_num = conv_batches / channel_bins # 64 print('channel_bins: {}'.format(channel_bins)) # create excel 157 filefullpath = 'output/layer' + str(i) + '.xlsx' if os.path.exists(filefullpath): os.remove(filefullpath) wb = Workbook() # ws = wb.create_sheet('layer' + str(i), i) ws = wb.active ws.freeze_panes = 'B2' alignment_set = Alignment(horizontal='center', vertical='center') fill_set = PatternFill("solid", fgColor="CCCCCC") border_set = Border(left=Side(style='thin', color=colors.BLACK), right=Side(style='thin', color=colors.BLACK), top=Side(style='thin', color=colors.BLACK), bottom=Side(style='thin', color=colors.BLACK)) # write 'Channel\Batch' in (1,1) ws.cell(row=1, column=1, value='Channel\Batch') ws.cell(row=1, column=1).font = Font(bold=True) ws.cell(row=1, column=1).alignment = alignment_set ws.cell(row=1, column=1).fill = fill_set ws.cell(row=1, column=1).border = border_set # write 'Channel_Max' in (1, int(channel_bins_num)+2) ws.cell(row=1, column=int(channel_bins_num) + 2, value='Channel_Max') ws.cell(row=1, column=int(channel_bins_num) + 2).font = Font(bold=True) ws.cell(row=1, column=int(channel_bins_num) + 2).alignment = Alignment(horizontal='center', vertical='center') ws.cell(row=1, column=int(channel_bins_num) + 2).fill = fill_set ws.cell(row=1, column=int(channel_bins_num) + 2).border = border_set # write 'Total_Max' in (conv_channels+2, 1) ws.cell(row=conv_channels + 2, column=1, value='Total_Max') ws.cell(row=conv_channels + 2, column=1).font = Font(bold=True) ws.cell(row=conv_channels + 2, column=1).alignment = Alignment( horizontal='center', vertical='center') ws.cell(row=conv_channels + 2, column=1).fill = fill_set ws.cell(row=conv_channels + 2, column=1).border = border_set # write 'Sparsity' in (conv_channels+3, 1) ws.cell(row=conv_channels + 3, column=1, value='Sparsity') ws.cell(row=conv_channels + 3, column=1).font = Font(bold=True) ws.cell(row=conv_channels + 3, column=1).alignment = Alignment( horizontal='center', vertical='center') ws.cell(row=conv_channels + 3, column=1).fill = fill_set ws.cell(row=conv_channels + 3, column=1).border = border_set # write groups = 64 in row for j in range(1, int(channel_bins_num) + 1): ws.cell(row=1, column=j + 1, value=j) ws.cell(row=1, column=j + 1).font = Font(bold=True) ws.cell(row=1, column=j + 1).alignment = Alignment( horizontal='center', vertical='center') ws.cell(row=1, column=j + 1).fill = fill_set ws.cell(row=1, column=j + 1).border = border_set # write channels = 512 in column for k in range(1, conv_channels + 1): ws.cell(row=k + 1, column=1, value=k) ws.cell(row=k + 1, column=1).font = Font(bold=True) ws.cell(row=k + 1, column=1).alignment = Alignment( horizontal='center', vertical='center') ws.cell(row=k + 1, column=1).fill = fill_set ws.cell(row=k + 1, column=1).border = border_set # slice channel_bin_nozero = [] channel_bins_max = [] total_max = [] nozero_len_total = 0 for conv_channel in range(conv_channels): # conv_channels for conv_batch in range(conv_batches): for conv_height in range(conv_heights): for conv_width in range(conv_widthes): conv_value = conv_weights[conv_batch, conv_channel, conv_height, conv_width] if conv_value != 0: channel_bin_nozero.append(conv_value) if (conv_batch + 1) % channel_bins == 0: channel_bin_nozero_len = len( channel_bin_nozero ) # channel bin nozero number channel_bins_max.append( channel_bin_nozero_len) # Channel_Max list channel_bins_max_value = max( channel_bins_max) # Channel_Max value total_max.append( channel_bins_max_value) # Total_Max list nozero_len_total += channel_bin_nozero_len ws.cell( row=conv_channel + 2, column=((conv_batch + 1) / channel_bins + 1), value=channel_bin_nozero_len) ws.cell( row=conv_channel + 2, column=((conv_batch + 1) / channel_bins + 1)).alignment = Alignment( horizontal='center', vertical='center') ws.cell(row=conv_channel + 2, column=channel_bins_num + 2, value=channel_bins_max_value) ws.cell(row=conv_channel + 2, column=channel_bins_num + 2).alignment = Alignment( horizontal='center', vertical='center') channel_bin_nozero.clear() channel_bins_max.clear() total_max_value = max(total_max) # Total_Max value ws.cell(row=conv_channels + 2, column=2, value=total_max_value) ws.cell(row=conv_channels + 2, column=2).alignment = Alignment( horizontal='center', vertical='center') sparsity = (conv_batches * conv_channels * conv_widthes * conv_heights - nozero_len_total) / ( conv_batches * conv_channels * conv_widthes * conv_heights) sparsity = "%.2f%%" % (sparsity * 100) ws.cell(row=conv_channels + 3, column=2, value=sparsity) ws.cell(row=conv_channels + 3, column=2).alignment = Alignment( horizontal='center', vertical='center') print('nozero_len_total: ', nozero_len_total) # print('sparsity: %.2f%%'%(sparsity*100)) print('sparsity: ', sparsity) print('total_max: ', total_max_value) # #create chart # bar_chart = BarChart() # # bar_chart.type = 'col' # bar_chart.title = 'Weights Nozero Distribution' # # bar_chart.style = 12 # bar_chart.x_axis.title = 'Channels' # bar_chart.x_axis.delete = False # bar_chart.y_axis.title = 'Channel bins max' # bar_chart.y_axis.delete = False # lables = Reference(ws, min_row=2, max_row=10+1, min_col=1) # data = Reference(ws, min_row=1, max_row=10+1, min_col=channel_bins_num+2) # bar_chart.add_data(data, titles_from_data=True) # bar_chart.set_categories(lables) # # bar_chart.shape = 0 # bar_chart.height = 7.5 # bar_chart.width = 15 # bar_chart.dLbls = label.DataLabelList() # bar_chart.dLbls.showVal = True # bar_chart.showVal = True # ws.add_chart(bar_chart,'B13') wb.save(filefullpath)
def writeexcel(box, cutoffDate): wb = Workbook() ws = wb.active bld = Font(bold=True) ws.title = "Lag Report" # '"Boat","Fab Start","Fab End","Canvas Start","Lag 1","Canvas End","Paint Start","Lag 2","Paint End","Outfit Start","Lag 3","Outfit End"' props = [ ['A', 'Boat', 10.2602040816327], ['B', 'Firstday\nFab', 12.6887755102041], ['C', 'Lastday\nFab', 12.6887755102041], \ ['D', 'Firstday\nCanvas', 12.6887755102041], ['E', 'Lag', 10.2602040816327], ['F', 'Lastday\nCanvas', 12.6887755102041], \ ['G', 'Firstday\nPaint', 12.6887755102041], ['H', 'Lag', 10.2602040816327], ['I', 'Lastday\nPaint', 12.6887755102041], \ ['J', 'Firstday\nOutfitting', 12.6887755102041], ['K', 'Lag', 10.2602040816327], ['L', 'Lastday\nOutfitting', 12.6887755102041] ] for col, text, width in props: ws['%s1' % col] = text ws.column_dimensions[col].width = width currentCell = ws.cell(1, ord(col) - 64) currentCell.alignment = Alignment(horizontal='center') currentCell.font = bld # set header row height ws.row_dimensions[1].height = 35.05 row_index = 2 for boat in sorted(box, key=lambda k: k['outfitStart']): if boat["outfitEnd"] != "": column_index = 1 if datetime.datetime.strptime(boat["outfitStart"], "%Y-%m-%d").date() > cutoffDate: if boat["canvasStart"] == "": # no canvas stage only output for paint and outfitting lag1 = "" lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \ datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0) else: lag1 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \ datetime.datetime.strptime(boat["canvasStart"],"%Y-%m-%d").date(), holidays) -2, 0) lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["canvasEnd"],"%Y-%m-%d").date(), \ datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0) lag3 = max(workdays.networkdays(datetime.datetime.strptime(boat["paintEnd"],"%Y-%m-%d").date(), \ datetime.datetime.strptime(boat["outfitStart"],"%Y-%m-%d").date(), holidays) -2, 0) ws["A%s" % (row_index)] = boat["job"] ws["B%s" % (row_index)] = boat["fabStart"] ws["C%s" % (row_index)] = boat["fabEnd"] ws["D%s" % (row_index)] = boat["canvasStart"] ws["E%s" % (row_index)] = lag1 ws["F%s" % (row_index)] = boat["canvasEnd"] ws["G%s" % (row_index)] = boat["paintStart"] ws["H%s" % (row_index)] = lag2 ws["I%s" % (row_index)] = boat["paintEnd"] ws["J%s" % (row_index)] = boat["outfitStart"] ws["K%s" % (row_index)] = lag3 ws["L%s" % (row_index)] = boat["outfitEnd"] row_index += 1 greyFill = PatternFill(start_color='FFCCCCCC', end_color='FFCCCCCC', fill_type='solid') ws.conditional_formatting.add( 'A2:L%s' % (row_index - 1), FormulaRule(formula=['ISEVEN(ROW())'], stopIfTrue=True, fill=greyFill)) wb.save('/tmp/LagReport-%s.xlsx' % (datetime.date.today()))
if last_weekday in [5, 6]: celula_fds(header_letter[str(last_weekday)], row, '') else: celula_vazia(header_letter[str(last_weekday)], row) last_weekday += 1 mes = {} mes['1'] = 'Janeiro' mes['2'] = 'FEvereiro' mes['3'] = 'Março' mes['4'] = 'Abril' mes['5'] = 'Maio' mes['6'] = 'Junho' mes['7'] = 'Julho' mes['8'] = 'Agosto' mes['9'] = 'Setembro' mes['10'] = 'Outubro' mes['11'] = 'Novembro' mes['12'] = 'Dezembro' fonte_def = Font(name='calibri', size=16, bold=True) sheet['b2'].value = mes[str(month.dt.month)] + ' ' + str(month.dt.year) sheet['b2'].font = fonte_def excel_file = 'David Jorge - {}-{:02} {}.xlsx'.format(month.dt.year, month.dt.month, mes[str(month.dt.month)]) print('excel_file:', excel_file) wb.save(excel_file) console.open_in(excel_file)
def __call__(self): lectures = self.request.db.query(models.Lecture) lectures = lectures.filter(models.Lecture.is_visible) w = self.w # sheet Tutorials worksheet_tutorials = w.active worksheet_tutorials.title = 'Tutorials' header = [ 'Tutor FirstName', 'Tutor LastName', 'Tutor Email', 'Tutorial Information', 'Student Count', 'Tutorial Room', 'Time', 'Comments' ] worksheet_tutorials.append(header) worksheet_tutorials.row_dimensions[1].font = Font(bold=True) row_index = 2 for lecture in lectures.all(): tutorial_list = [] lecture_name = lecture.name for tutorial in lecture.tutorials: vtutor = tutorial.tutor.name( ) if tutorial.tutor is not None else 'None' vtutor_first_name = tutorial.tutor.first_name if tutorial.tutor is not None else 'None' vtutor_last_name = tutorial.tutor.last_name if tutorial.tutor is not None else 'None' vemail = tutorial.tutor.email if tutorial.tutor is not None else 'None' vplace = tutorial.place vtime = tutorial.time.__html__() vcomment = tutorial.comment vstudent = len(tutorial.students.all()) tutorial_item = (vtutor_first_name, vtutor_last_name, vemail, lecture_name, vstudent, vplace, vtime, vcomment) tutorial_list.append(tutorial_item) # add summary lecture new_list = [] lecture_item = ('', lecture.lecturer, '', lecture_name, lecture.lecture_students.count(), '', lecture.term.__html__(), '') new_list.append(lecture_item) # sort by tutor fistName tutorial_list = sorted(tutorial_list) tutorial_index = 1 for item in tutorial_list: new_item = (item[0], item[1], item[2], item[3] + ' Uebungsgruppe: ' + str(tutorial_index), item[4], item[5], item[6], item[7]) new_list.append(new_item) tutorial_index = tutorial_index + 1 # add to sheet for item in new_list: for col, d in enumerate(item, 1): worksheet_tutorials.cell(row=row_index, column=col, value=d) row_index = row_index + 1 # set column width for column_cells in worksheet_tutorials.columns: max_length = max(len(str(cell.value)) for cell in column_cells) worksheet_tutorials.column_dimensions[ column_cells[0].column].width = max_length * 1.2 return self.createResponse()
def fix_formatting(filename, patient_name='', case_name=''): ''' Helper function ''' def border_add(border, top=None, right=None, left=None, bottom=None): if top is None: top = border.top if left is None: left = border.left if right is None: right = border.right if bottom is None: bottom = border.bottom return openpyxl.styles.Border(top=top, left=left, right=right, bottom=bottom) # print(filename) assert filename.endswith('.xlsx') thin = Side(border_style='thin') medium = Side(border_style='medium') df = pd.read_excel(filename) wb = openpyxl.load_workbook(filename) ws = wb.worksheets[0] ''' Insert header ''' ws.oddHeader.center.text = patient_name ''' By default make all cells horizontal='center', except for the first and last two columns ''' cells = [ ws[c + str(r)] for c in ascii_uppercase[1:ws.max_column - 1] for r in range(1, ws.max_row + 1) ] for cell in cells: cell.alignment = Alignment(horizontal='center') ''' Make first column bold and right aligned ''' cells = [ws['A' + str(r)] for r in range(3, ws.max_row - 1)] for cell in cells: # cell.font = Font(bold=True) cell.alignment = Alignment(horizontal='right') cell.border = border_add(cell.border, right=medium) ''' Make first two rows bold and left aligned ''' cells = [ ws[c + str(i)] for i in range(1, 3) for c in ascii_uppercase[0:ws.max_column] ] for cell in cells: cell.font = Font(bold=True) cell.alignment = Alignment(horizontal='left') ''' Get locations of 'Allele' ''' allele_ij = [] for i in df.index: for j, v in enumerate(df.iloc[i]): if v == 'Allele': allele_ij.append([i, j]) ''' Apply medium thickness based on which cells have the word 'Allele' ''' for i, j in allele_ij: for k in range(0, ws.max_column): cell = ws[ascii_uppercase[k] + str(i + 3)] cell.border = border_add(cell.border, bottom=medium) for k in range(0, j, 2): cell = ws[ascii_uppercase[k] + str(i + 2)] cell.border = border_add(cell.border, right=medium) cell = ws[ascii_uppercase[k] + str(i + 3)] cell.border = border_add(cell.border, right=medium) cell = ws[ascii_uppercase[k + j] + str(i + 2)] cell.border = border_add(cell.border, right=medium) cell = ws[ascii_uppercase[k + j] + str(i + 3)] cell.border = border_add(cell.border, right=medium) cell = ws[ascii_uppercase[2 * j] + str(i + 2)] cell.border = border_add(cell.border, right=medium) cell = ws[ascii_uppercase[2 * j] + str(i + 3)] cell.border = border_add(cell.border, right=medium) ws.sheet_view.view = 'pageLayout' openpyxl.worksheet.worksheet.Worksheet.set_printer_settings( ws, paper_size=1, orientation='landscape') ws.page_margins.bottom = 0.5 ws.page_margins.top = 0.5 ws.page_margins.left = 0.5 ws.page_margins.right = 0.5 ws.page_margins.header = 0.1 ws.sheet_properties.pageSetUpPr.fitToPage = True ws.page_setup.fitToHeight = False ws.page_setup.fitToWidth = 1 wb.save(filename) wb.close() # FileFormat = 56 is for .xls extension
def generate_excel(report, name, reporting_start_datetime_local, reporting_end_datetime_local, period_type): wb = Workbook() ws = wb.active # Row height ws.row_dimensions[1].height = 102 for i in range(2, 2000 + 1): ws.row_dimensions[i].height = 42 # Col width ws.column_dimensions['A'].width = 1.5 ws.column_dimensions['B'].width = 25.0 for i in range(ord('C'), ord('L')): ws.column_dimensions[chr(i)].width = 15.0 # Font name_font = Font(name='Constantia', size=15, bold=True) title_font = Font(name='宋体', size=15, bold=True) data_font = Font(name='Franklin Gothic Book', size=11) table_fill = PatternFill(fill_type='solid', fgColor='1F497D') f_border = Border(left=Side(border_style='medium', color='00000000'), right=Side(border_style='medium', color='00000000'), bottom=Side(border_style='medium', color='00000000'), top=Side(border_style='medium', color='00000000')) b_border = Border(bottom=Side(border_style='medium', color='00000000'), ) b_c_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_c_alignment = Alignment(vertical='center', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) b_r_alignment = Alignment(vertical='bottom', horizontal='right', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) c_r_alignment = Alignment(vertical='bottom', horizontal='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) # Img img = Image("excelexporters/myems.png") img.width = img.width * 0.85 img.height = img.height * 0.85 # img = Image("myems.png") ws.add_image(img, 'B1') # Title ws.row_dimensions[3].height = 60 ws['B3'].font = name_font ws['B3'].alignment = b_r_alignment ws['B3'] = 'Name:' ws['C3'].border = b_border ws['C3'].alignment = b_c_alignment ws['C3'].font = name_font ws['C3'] = name ws['D3'].font = name_font ws['D3'].alignment = b_r_alignment ws['D3'] = 'Period:' ws['E3'].border = b_border ws['E3'].alignment = b_c_alignment ws['E3'].font = name_font ws['E3'] = period_type ws['F3'].font = name_font ws['F3'].alignment = b_r_alignment ws['F3'] = 'Date:' ws['G3'].border = b_border ws['G3'].alignment = b_c_alignment ws['G3'].font = name_font ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local ws.merge_cells("G3:H3") if "reporting_period" not in report.keys() or \ "names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0: filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename ################################## current_row_number = 6 reporting_period_data = report['reporting_period'] has_names_data_flag = True if "names" not in reporting_period_data.keys() or \ reporting_period_data['names'] is None or \ len(reporting_period_data['names']) == 0: has_names_data_flag = False if has_names_data_flag: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 报告期消耗' current_row_number += 1 category = reporting_period_data['names'] ca_len = len(category) ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \ " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '消耗' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '环比' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = name_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = str( round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \ if reporting_period_data['increment_rates'][i] is not None else '-' col = chr(ord(col) + 1) current_row_number += 2 category_dict = group_by_category( reporting_period_data['energy_category_names']) for category_dict_name, category_dict_values in category_dict.items(): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' current_row_number += 1 table_start_row_number = current_row_number ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].fill = table_fill ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = '消耗' current_row_number += 1 for i in category_dict_values: ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" ws['C' + str(current_row_number)].font = name_font ws['C' + str(current_row_number)].alignment = c_c_alignment ws['C' + str(current_row_number)].border = f_border ws['C' + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 3) current_row_number += 1 table_end_row_number = current_row_number - 1 pie = PieChart() pie.title = \ name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \ ') 分项消耗占比' labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number) pie.add_data(pie_data, titles_from_data=True) pie.set_categories(labels) pie.height = 6.6 pie.width = 9 s1 = pie.series[0] s1.dLbls = DataLabelList() s1.dLbls.showCatName = False s1.dLbls.showVal = True s1.dLbls.showPercent = True ws.add_chart(pie, 'D' + str(table_start_row_number)) if len(category_dict_values) < 4: current_row_number = current_row_number - len( category_dict_values) + 4 current_row_number += 1 ##################################### has_values_data = True has_timestamps_data = True if 'values' not in reporting_period_data.keys() or \ reporting_period_data['values'] is None or \ len(reporting_period_data['values']) == 0: has_values_data = False if 'timestamps' not in reporting_period_data.keys() or \ reporting_period_data['timestamps'] is None or \ len(reporting_period_data['timestamps']) == 0 or \ len(reporting_period_data['timestamps'][0]) == 0: has_timestamps_data = False if has_values_data and has_timestamps_data: ca_len = len(reporting_period_data['names']) time = reporting_period_data['timestamps'][0] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 详细数据' current_row_number += 1 chart_start_row_number = current_row_number current_row_number += ca_len * 6 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '日期时间' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].fill = table_fill ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" col = chr(ord(col) + 1) current_row_number += 1 for i in range(0, len(time)): ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = time[i] col = 'C' for j in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \ if reporting_period_data['values'][j][i] is not None else 0.00 col = chr(ord(col) + 1) current_row_number += 1 table_end_row_number = current_row_number - 1 ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '小计' col = 'C' for i in range(0, ca_len): ws[col + str(current_row_number)].font = title_font ws[col + str(current_row_number)].alignment = c_c_alignment ws[col + str(current_row_number)].border = f_border ws[col + str(current_row_number)] = round( reporting_period_data['subtotals'][i], 2) col = chr(ord(col) + 1) current_row_number += 2 format_time_width_number = 1.0 min_len_number = 1.0 min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0 if period_type == 'hourly': format_time_width_number = 4.0 min_len_number = 2 min_width_number = 12.0 elif period_type == 'daily': format_time_width_number = 2.5 min_len_number = 4 min_width_number = 14.0 elif period_type == 'monthly': format_time_width_number = 2.1 min_len_number = 4 min_width_number = 12.4 elif period_type == 'yearly': format_time_width_number = 1.5 min_len_number = 5 min_width_number = 11.5 for i in range(0, ca_len): line = LineChart() line.title = '报告期消耗 - ' + \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number) line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number) line.add_data(line_data, titles_from_data=True) line.set_categories(labels) line_data = line.series[0] line_data.marker.symbol = "circle" line_data.smooth = True line.x_axis.crosses = 'min' line.height = 8.25 line.width = format_time_width_number * len(time) if len( time) > min_len_number else min_width_number if line.width > 24: line.width = 24 line.dLbls = DataLabelList() line.dLbls.dLblPos = 't' line.dLbls.showVal = True line.dLbls.showPercent = False chart_col = 'B' chart_cell = chart_col + str(chart_start_row_number) chart_start_row_number += 6 ws.add_chart(line, chart_cell) ##################################### has_associated_equipment_flag = True if "associated_equipment" not in report.keys() or \ "energy_item_names" not in report['associated_equipment'].keys() or \ len(report['associated_equipment']["energy_item_names"]) == 0 \ or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \ or report['associated_equipment']['associated_equipment_names_array'] is None \ or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \ or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0: has_associated_equipment_flag = False if has_associated_equipment_flag: associated_equipment = report['associated_equipment'] ws['B' + str(current_row_number)].font = title_font ws['B' + str(current_row_number)] = name + ' 相关设备数据' current_row_number += 1 table_start_row_number = current_row_number ws.row_dimensions[current_row_number].height = 60 ws['B' + str(current_row_number)].fill = table_fill ws['B' + str(current_row_number)].font = name_font ws['B' + str(current_row_number)].alignment = c_c_alignment ws['B' + str(current_row_number)].border = f_border ws['B' + str(current_row_number)] = '相关设备' ca_len = len(associated_equipment['energy_item_names']) for i in range(0, ca_len): row = chr(ord('C') + i) ws[row + str(current_row_number)].fill = table_fill ws[row + str(current_row_number)].font = name_font ws[row + str(current_row_number)].alignment = c_c_alignment ws[row + str(current_row_number)].border = f_border ws[row + str(current_row_number)] = \ reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")" associated_equipment_len = len( associated_equipment['associated_equipment_names_array'][0]) for i in range(0, associated_equipment_len): current_row_number += 1 row = str(current_row_number) ws['B' + row].font = title_font ws['B' + row].alignment = c_c_alignment ws['B' + row] = associated_equipment[ 'associated_equipment_names_array'][0][i] ws['B' + row].border = f_border for j in range(0, ca_len): col = chr(ord('C') + j) ws[col + row].font = title_font ws[col + row].alignment = c_c_alignment ws[col + row] = round( associated_equipment['subtotals_array'][j][i], 2) ws[col + row].border = f_border filename = str(uuid.uuid4()) + '.xlsx' wb.save(filename) return filename
def infer_schema(data, fname, output_root='', sample_size=1.0, type_threshold=0.5, n_jobs=1, base_schema=None): """ Infer data types for all columns for the input table Parameters ---------- data: pandas DataFrame data table to infer fname: string the output file name output_root: string, default='' the root directory for the output file sample_size: int or float(<= 1.0), default=1.0 int: number of sample rows to infer the data type (useful for large tables) float: sample size in percentage type_threshold: float(<= 1.0), default=0.5 threshold for inferring data type n_jobs: int, default=1 the number of jobs to run in parallel base_schema: pandas DataFrame, default=None data schema to base on """ # check sample_size if sample_size > 1: if int(sample_size) != sample_size: raise ValueError( 'sample_size: only accept integer when it is > 1.0') if sample_size > data.shape[0]: print("sample_size: %d is larger than the data size: %d" % (sample_size, data.shape[0])) # check output_root if output_root != '': if not os.path.isdir(output_root): raise ValueError('output_root: root not exists') # check type_threshold if (type_threshold <= 0) or (type_threshold > 1): raise ValueError('type_threshold: should be in (0, 1]') # check base_schema if base_schema is not None: if type(base_schema) != pd.core.frame.DataFrame: raise ValueError('base_schema: only accept pandas DataFrame') # open a new workbook to store all result wb = openpyxl.Workbook() ws = wb['Sheet'] ws.title = 'schema' # calculate sample size if sample_size <= 1.0: sample_size = int(data.shape[0] * sample_size) # dictionary to store dropna sample data values data_dropna_sample_values = {} for col in data.columns.values: if len(data[col].dropna()) <= sample_size: data_dropna_sample_values[col] = data[col].dropna().values else: data = data.sample(sample_size).reset_index(drop=True) data_dropna_sample_values[col] = data[col].dropna().values # use data_dropna_sample_values to infer data type for each column _n_jobs = np.min([n_jobs, len(data.columns.values)]) type_infos = Parallel(n_jobs=_n_jobs)(delayed(_infer_dtype)( data_dropna_sample_values[col], col, type_threshold) for col in data.columns.values) type_infos_df = pd.DataFrame(type_infos)[['column', 'type']] # dtype mapping for basic stat calculation data_types = {} for col in data.columns.values: data_types[col] = type_infos_df.loc[type_infos_df['column'] == col, 'type'].values[0] # get basic statistic information for all columns stat_infos = Parallel(n_jobs=_n_jobs)(delayed(_cal_column_stat)( data_dropna_sample_values[col], col, data_types[col]) for col in data.columns.values) stat_infos_df = pd.DataFrame(stat_infos) # merge dtype infomation with stat information full_infos_df = type_infos_df.merge(stat_infos_df, on='column', how='left') # add include column full_infos_df['include'] = 1 full_infos_df = full_infos_df[[ 'column', 'type', 'include', 'sample_value', 'sample_num_uni', 'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max', 'sample_std' ]] # if base_schema is provided, we can compare with base schema if base_schema is not None: base_schema = base_schema[['column', 'type', 'include']] base_schema.columns = [ 'base_%s' % (col) for col in base_schema.columns.values ] full_infos_df = full_infos_df.merge(base_schema, left_on='column', right_on='base_column', how='outer') # compare with the base schema full_infos_df['base_column'] = full_infos_df['base_column'].apply( lambda x: 'column not in base table' if pd.isnull(x) else x) full_infos_df['column'] = full_infos_df['column'].apply( lambda x: 'column not in current table' if pd.isnull(x) else x) # reorder the column full_infos_df['include'] = base_schema['base_include'] full_infos_df = full_infos_df[[ 'column', 'base_column', 'type', 'base_type', 'include', 'sample_value', 'sample_num_uni', 'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max', 'sample_std' ]] # add data validation for type column val_type = DataValidation(type="list", formula1='"key,numeric,str,date"', allow_blank=False) ws.add_data_validation(val_type) # add data validation for include column val_include = DataValidation(type="list", formula1='"0,1"', allow_blank=False) ws.add_data_validation(val_include) # get col_name, excel column mapping column_mapping = {} for i, col in enumerate(full_infos_df.columns): column_mapping[col] = xlsxwriter.utility.xl_col_to_name(i) # write everything into the worksheet for r_idx, r in enumerate( dataframe_to_rows(full_infos_df, index=False, header=True)): ws.append(r) for cell_idx, cell in enumerate( ws.iter_cols(max_col=ws.max_column, min_row=ws.max_row, max_row=ws.max_row)): cell = cell[0] if r_idx != 0: val_type.add(ws['%s%d' % (column_mapping['type'], ws.max_row)]) val_include.add(ws['%s%d' % (column_mapping['include'], ws.max_row)]) if cell_idx == 0: cell.font = Font(bold=True) else: cell.style = 'Accent5' # add conditional formating red_fill = PatternFill(bgColor="FFC7CE") red_font = Font(color="9C0006") green_fill = PatternFill(bgColor="C6EFCE") green_font = Font(color="006100") blue_fill = PatternFill(bgColor="9ECAE1") blue_font = Font(color="08306B") orange_fill = PatternFill(bgColor="FDD0A2") orange_font = Font(color="A63603") purple_fill = PatternFill(bgColor="DADAEB") purple_font = Font(color="3F007D") # red highlight if there is any inconsistent between base and the target if base_schema is not None: col1 = column_mapping['column'] col2 = column_mapping['base_column'] ws.conditional_formatting.add( '%s2:%s%d' % (col1, col1, ws.max_row), FormulaRule(formula=['%s2<>%s2' % (col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col2, col2, ws.max_row), FormulaRule(formula=['%s2<>%s2' % (col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font)) col1 = column_mapping['type'] col2 = column_mapping['base_type'] ws.conditional_formatting.add( '%s2:%s%d' % (col1, col1, ws.max_row), FormulaRule(formula=['%s2<>%s2' % (col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col2, col2, ws.max_row), FormulaRule(formula=['%s2<>%s2' % (col1, col2)], stopIfTrue=True, fill=red_fill, font=red_font)) # yellow highlight column type and include (which need to be modified) ws['%s1' % (column_mapping['type'])].style = 'Neutral' ws['%s1' % (column_mapping['include'])].style = 'Neutral' # green highlight for the mkey type and red highlight for the error type type_cols = [column_mapping['type']] if 'base_type' in column_mapping.keys(): type_cols.append(column_mapping['base_type']) for col in type_cols: ws.conditional_formatting.add( '%s2:%s%d' % (col, col, ws.max_row), FormulaRule(formula=['%s2="error"' % (col)], stopIfTrue=True, fill=red_fill, font=red_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col, col, ws.max_row), FormulaRule(formula=['%s2="key"' % (col)], stopIfTrue=True, fill=green_fill, font=green_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col, col, ws.max_row), FormulaRule(formula=['%s2="numeric"' % (col)], stopIfTrue=True, fill=blue_fill, font=blue_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col, col, ws.max_row), FormulaRule(formula=['%s2="str"' % (col)], stopIfTrue=True, fill=orange_fill, font=orange_font)) ws.conditional_formatting.add( '%s2:%s%d' % (col, col, ws.max_row), FormulaRule(formula=['%s2="date"' % (col)], stopIfTrue=True, fill=purple_fill, font=purple_font)) # red highlight for include = 0 ws.conditional_formatting.add( '%s2:%s%d' % (column_mapping['include'], column_mapping['include'], ws.max_row), FormulaRule(formula=['%s2=0' % (column_mapping['include'])], stopIfTrue=True, fill=red_fill, font=red_font)) # red highlight for sample_num_uni = 0 or 1, only one unique value ws.conditional_formatting.add( '%s2:%s%d' % (column_mapping['sample_num_uni'], column_mapping['sample_num_uni'], ws.max_row), FormulaRule(formula=['%s2=0' % (column_mapping['sample_num_uni'])], stopIfTrue=True, fill=red_fill, font=red_font)) ws.conditional_formatting.add( '%s2:%s%d' % (column_mapping['sample_num_uni'], column_mapping['sample_num_uni'], ws.max_row), FormulaRule(formula=['%s2=1' % (column_mapping['sample_num_uni'])], stopIfTrue=True, fill=red_fill, font=red_font)) # adjust the column format for the worksheet _adjust_ws(ws=ws, row_height=20) wb.save(filename=os.path.join(output_root, 'data_schema_%s.xlsx' % (fname)))
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment wb = load_workbook("sample.xlsx") ws = wb.active a1 = ws["A1"] b1 = ws["B1"] c1 = ws["C1"] # 열의 너비 설정 ws.column_dimensions["A"].width = 5 # 행의 높이 설정 ws.row_dimensions[1].height = 50 # 폰트 스타일 설정 a1.font = Font(color="FF0000", italic=True, bold=True) b1.font = Font(color="CC33FF", name="Arial", strike=True) c1.font = Font(color="0000FF", size=20, underline="single") # 테두리 적용 thin_border = Border(left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin")) a1.border = thin_border b1.border = thin_border c1.border = thin_border # 90점 넘는 셀에 대해서 초록색으로 적용 for row in ws.rows: for cell in row:
cellCheckBoundsXLeft = 40 cellCheckBoundsXRight = 40 cellCheckBoundsY = 48 #Holds given cell positions - if given - otherwise automatic detecting is used. cellUniversalArray = [] #Used to create imaginary bounds for checking nucleus nucleusCheckBoundsX = 5 nucleusCheckBoundsY = 8 #Spreadsheet initializtaion spreadsheet = workbook.active spreadsheet.title = "Cell Results" BOLD_FONT = Font(bold=True) scaler = setKind * 9 spreadsheet.cell(row=scaler + 1, column=1, value="Cell Image:").font = BOLD_FONT spreadsheet.cell(row=scaler + 4, column=1, value="Cell Number:").font = BOLD_FONT spreadsheet.cell(row=scaler + 5, column=1, value="Cell Area:").font = BOLD_FONT spreadsheet.cell(row=scaler + 6, column=1, value="Nuclei Count:").font = BOLD_FONT spreadsheet.cell(row=scaler + 7, column=1, value="Cell Width:").font = BOLD_FONT spreadsheet.cell(row=scaler + 8, column=1, value="Cell Height:").font = BOLD_FONT scaler = 2 * 9 spreadsheet.cell(row=scaler + 1, column=1, value="Cell Image:").font = BOLD_FONT
def get_xls(self): day_set = self.get_day_set() deals = self.get_deals() filename = '{branch} {service} {day}.xlsx'.format( # city=self.service.branch.city.name, branch=self.service.branch.name, service=self.service.name, day=self.day_start.strftime('%Y%m%d')) wb = Workbook() ws = wb.active ws.title = '%s %s' % (self.service.name, self.day_start.strftime('%Y%m%d')) title_text = '{day} {branch} {service}'.format( # city=self.service.branch.city.name, branch=self.service.branch.name, service=self.service.name, day=self.day_start.strftime('%d.%m.%Y')) title = ws.cell(row=1, column=1, value=title_text) title.alignment = Alignment(horizontal='left') title.font = Font(name='Calibri', size=11, bold=True) ws.merge_cells('A1:C1') labels = [ 'Время', 'Клиент', 'День рождения', 'Телефон', 'Email', 'Статус', 'Стоимость', 'Предоплата', 'Безнал', 'Правщик', 'Администратор', 'Комментарий' ] # names = [i[1] for i in data['tabs'][tab_key]['labels']] columns = [] for index, label in enumerate(labels): cell = ws.cell(row=2, column=index + 1, value=label) cell.font = Font(name='Calibri', size=11, bold=True) columns.append(cell.column) row = 3 # import ipdb; ipdb.set_trace() for time_key in sorted(day_set['timing'].keys()): item = day_set['timing'][time_key] # time_key = time['key'] time_key_str = '%s%s' % (day_set['key'], time_key) if time_key in day_set['groups']: row += 1 _ = ws.cell(row=row, column=1, value=day_set['groups'][time_key]['name']) _ = ws.cell(row=row, column=2, value=day_set['groups'][time_key]['persons']) # has deals if time_key in day_set['timing'] and 'deals' in item: # print('item', item) if item.get('empty'): row += 1 _ = ws.cell(row=row, column=1, value=item['label']) for deal_key in item['deals']: try: deal = deals[deal_key] except KeyError: continue # row += 1 # deal_text = '{step} ({cost} / {paid}), правщик: {master}'.format( # step=deal['step_label'], # cost=deal['cost'], # paid=deal['paid'], # master=deal['master__full_name'], # ) # _ = ws.cell(row=row, column=1, value='%s (%s)' % (deal['start_string'], deal['minutes'])) # _ = ws.cell(row=row, column=2, value=deal_text) # ws.merge_cells('B{row}:C{row}'.format(row=row)) # if deal['comment']: # row += 1 # _ = ws.cell(row=row, column=2, value=deal['comment']) # ws.merge_cells('B{row}:C{row}'.format(row=row)) for index, person in enumerate(deal['persons']): row += 1 person_text = '%s' % (person['full_name']) if person['control']: person_text = '(Контроль) ' + person_text if index == 0: _ = ws.cell( row=row, column=1, value='%s (%s)' % (deal['start_string'], deal['minutes'])) _ = ws.cell(row=row, column=6, value=deal['step_label']) _ = ws.cell(row=row, column=7, value=deal['cost']) _ = ws.cell(row=row, column=8, value=deal['paid']) _ = ws.cell(row=row, column=9, value=deal['paid_non_cash']) _ = ws.cell(row=row, column=10, value=deal['master__full_name']) _ = ws.cell(row=row, column=11, value=deal['manager__full_name']) _ = ws.cell(row=row, column=12, value=deal['comment']) _ = ws.cell(row=row, column=2, value=person_text) _ = ws.cell(row=row, column=3, value=person['age']) _ = ws.cell(row=row, column=4, value=person['phone']) # _ = ws.cell(row=row, column=5, value=person['emails']) if 'empty_finish' in item: row += 1 _ = ws.cell(row=row, column=1, value=item['empty_finish']['label']) border = Border(bottom=Side(border_style='thin', color='000000')) ws.conditional_formatting.add( 'A1:K%s' % row, FormulaRule(formula=['E1=0'], border=border)) ws.column_dimensions['A'].width = 13 ws.column_dimensions['B'].width = 30 ws.column_dimensions['C'].width = 10 # row += 2 # for cellObj in ws['%s2:%s%s' % (columns[0], columns[-1], items_count)]: # for cell in cellObj: # import ipdb; ipdb.set_trace() # print(cell.coordinate, cell.column) # ws.column_dimensions[cell.column].bestFit = True # ws[cell.coordinate].alignment = Alignment(horizontal='left') response = HttpResponse(save_virtual_workbook(wb), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'inline; filename=%s' % urlquote( filename).lower() return response
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 = 20 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' controlador = 7 for q in query: 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).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=4).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=4).value = str(q.causa) 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).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=5).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=5).value = q.cantidad 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).fill = PatternFill(start_color='66CFCC', end_color='66CFCC', fill_type='solid') ws.cell(row=controlador, column=6).font = Font(name='calibri', size=11, bold=True) ws.cell(row=controlador, column=6).value = q.observaciones 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
startdate = datetime.datetime.strptime(r['start'], '%Y-%m-%d') finishdate = datetime.datetime.strptime(r['finish'], '%Y-%m-%d') print(startdate.weekday()) les = requests.get("http://localhost/var/www/html/Raspisator/API/getLessons.php?project_id="+sys.argv[1]) les = json.loads(les.text) les = {i['id'] : i for i in les} print(sinfo) grades = sinfo['grades'] teachers = sinfo['teachers'] subjects = sinfo['subjects'] wb = openpyxl.workbook.Workbook() ws = wb.active ws.merge_cells('A'+str(koef-1)+':'+str(unichr(ord('A')+len(grades)+1))+str(koef-1)) ws['A'+str(koef-1)] = "Расписание на " + str(startdate.date().day)+" "+months[(startdate).date().month-1] + " - " + str(finishdate.date().day)+" "+months[finishdate.date().month-1] ws['A'+str(koef-1)].font = Font(color=colors.BLACK, bold=True) ws['A'+str(koef-1)].alignment = Alignment(wrap_text = True, horizontal = 'center') for i in range(len(a['grades'])): #классы ws[str(unichr(ord('A')+i+2))+str(koef)] = grades[a['grades'][i]]['grade_number'] + grades[a['grades'][i]]['grade_name'] t = ws[str(unichr(ord('A')+i+2))+str(koef)] t.font = Font(color=colors.BLACK, bold=True) ws.column_dimensions[str(unichr(ord('A')+i+2))].width=defwidth #t.fill = PatternFill(bgColor=colors.RED, fill_type = "solid") ws.column_dimensions['A'].width = 9 koef+=1 for i in range(len(a['table']['table'])): for j in range(len(a['table']['table'][i])):
fields1 = list(fielddic) fields2 = list(fielddic.values()) # print(fields1) ws.append(fields1) ws.append(fields2) # 导出表内容 for rows in obj.objects.all().values_list( ): # 未排除自动增长的字段内容导出,结构没有,内容有(小BUG) ws.append(list(rows)) ws.row_dimensions.group(1, 1, hidden=True) i = i + 1 ws = wb.active ws.title = 'Cover' ws['A4'] = '填报单位:' ws['A4'].font = Font(name='Arial', size=14, color=colors.RED, italic=True) ws['A5'] = '填报期间:' ws['A6'] = '币种:' ws['A7'] = '版本号' application_window = tk.Tk() application_window.withdraw() # 隐藏消息框 # Build a list of tuples for each file type the file dialog should display my_filetypes = [('xlsx files', '.xlsx')] # localpath = filedialog.asksaveasfile() # localpath = filedialog.askdirectory() localpath = filedialog.asksaveasfilename(parent=application_window, initialdir=os.getcwd(), title="Please select a location:", filetypes=my_filetypes)
# ch19_25.py import openpyxl from openpyxl.styles import Font wb = openpyxl.Workbook() # 建立空白的活頁簿 ws = wb.get_active_sheet() # 獲得目前工作表 fontTitle1 = Font(name='微軟正黑體', size=24) ws['A1'].font = fontTitle1 ws['A1'] = '明志科技大學' fontTitle2 = Font(name='Old English Text MT', size=24, bold=True) ws['A2'].font = fontTitle2 ws['A2'] = 'Ming-Chi Institute of Technology' fontTitle3 = Font(size=24, bold=True, italic=True) ws['A3'].font = fontTitle3 ws['A3'] = 'Ming-Chi Institute of Technology' wb.save('out19_25.xlsx') # 將活頁簿儲存
def export_analysis_xls(title, event_pk=None, information_pks=None, request_data=None): # Create a spreadsheet and get active workbook ew = ExcelWriter() ws = ew.get_active() ws.title = "Split Entries" wsg = ew.wb.create_sheet("Grouped Entries") # Create title row titles = [ "Date of Lead Publication", "Imported By", "Date Imported", "URL", "Lead Title", "Source", "Excerpt" ] event = entry_models.Event.objects.get(pk=event_pk) elements = json.loads(event.entry_template.elements) sTypes = [ 'date-input', 'scale', 'number-input', 'multiselect', 'organigram' ] element_ids = [] geo_elements = [] for element in elements: eType = element['type'] if eType in sTypes: titles.append(element['label']) elif eType == 'matrix1d': titles.append([element['title'], 'Dimension', 'Sub-Dimension']) elif eType == 'matrix2d': titles.append([ element['title'], 'Dimension', 'Sub-Dimension', 'Sector', 'Subsector' ]) elif eType == 'number2d': for row in element['rows']: for column in element['columns']: titles.append('{} - {}'.format(row['title'], column['title'])) for row in element['rows']: titles.append('{} matches'.format(row['title'])) elif eType == 'geolocations': geo_elements.append(element['id']) else: continue element_ids.append([element['id'], eType]) if event_pk: countries = entry_models.Event.objects.get(pk=event_pk).countries.\ all().distinct() else: countries = entry_models.Country.objects.all().distinct() for country in countries: admin_levels = country.adminlevel_set.all() for admin_level in admin_levels: titles.append(admin_level.name) titles.append('{} P-Code'.format(admin_level.name)) index = 0 for t in titles: if isinstance(t, list): for wswsg in [ws, wsg]: wswsg.cell(row=1, column=index + 1).value = t[0] wswsg.cell(row=1, column=index + 1).font = Font(bold=True) wswsg.merge_cells(start_row=1, end_row=1, start_column=index + 1, end_column=index + len(t) - 1) for ele in t[1:]: for wswsg in [ws, wsg]: wswsg.cell(row=2, column=index + 1).value = ele wswsg.cell(row=2, column=index + 1).font = Font(bold=True) index = index + 1 else: for wswsg in [ws, wsg]: wswsg.cell(row=1, column=index + 1).value = t wswsg.cell(row=1, column=index + 1).font = Font(bold=True) index = index + 1 ew.auto_fit_cells_in_row(1, ws) ew.auto_fit_cells_in_row(1, wsg) # Add each information in each entry belonging to this event informations = entry_models.EntryInformation.objects.filter( ~Q(entry__template=None), entry__lead__event__pk=event_pk).distinct() if information_pks: informations = informations.filter(pk__in=information_pks) informations = analysis_filter(informations, request_data, elements) grouped_rows = [] for i, info in enumerate(informations): try: rows = RowCollection(1) lead_url = info.entry.lead.url if Attachment.objects.filter(lead=info.entry.lead).count() > 0: lead_url = info.entry.lead.attachment.upload.url rows.add_values([ format_date(info.entry.lead.published_at), info.entry.created_by, format_date(info.entry.created_at.date()), lead_url, info.entry.lead.name, info.entry.lead.source_name, xstr(info.excerpt) ]) infoE = json.loads(info.elements) for element_id, element_type in element_ids: element = list_filter(infoE, 'id', element_id) get_analysis_data(elements, element_id, element, element_type, rows) for country in countries: admin_levels = country.adminlevel_set.all() for admin_level in admin_levels: selections = [] for map_selections in [ geoE for geoE in infoE if geoE.get('id') in geo_elements ]: for map_selection in map_selections.get('value', []): map_selection_list = map_selection.split(':') if len(map_selection_list) == 3: map_selection_list.append('') if len(map_selection_list) == 4: m_iso3, m_admin, m_name, pcode = \ map_selection_list if admin_level.level == int(m_admin): selections.append([m_name, pcode]) if len(selections) == 0: selections = [['', '']] rows.permute_and_add_list(selections) ew.append(rows.rows, ws) grouped_rows.append(rows.group_rows) except: pass ew.append(grouped_rows, wsg) # ew.save_to('/tmp/text.xls') # REMOVE THIS return ew.get_http_response(title)
def excel_helper(sheet, table_id, table, option): """ Create excel sheet. :param option: 'value' or 'percent' """ # Write table id and title on the first row sheet['A1'] = table_id sheet['B1'] = table['title'] sheet['A1'].font = Font(bold=True) sheet['B1'].font = Font(bold=True) header = [] max_indent = 0 # get column headers for column_id, column_info in table['columns'].items(): column_name_utf8 = column_info['name'].encode('utf-8') indent = column_info['indent'] header.append((column_name_utf8, indent)) if indent > max_indent: max_indent = indent # Populate first column with headers for i, col_tuple in enumerate(header): header = col_tuple[0] current_row = i + 4 # 1-based index, account for geographic headers current_cell = sheet.cell(row=current_row, column=1) current_cell.value = header indent = col_tuple[1] if indent is None: logger.warn("Null indent for {} {}".format(table_id, header)) indent = 0 current_cell.alignment = Alignment(indent=indent, wrap_text=True) # Resize column width sheet.column_dimensions['A'].width = 50 # this SQL echoed in OGR export but no geom so copying instead of factoring out # plus different binding when using SQLAlchemy result = session(sql_url).execute( """SELECT full_geoid,display_name FROM tiger2018.census_name_lookup WHERE full_geoid IN :geoids ORDER BY full_geoid""", {'geoids': tuple(valid_geo_ids)} ) geo_headers = [] any_zero_denominators = False has_denominator_column = False for i, (geoid, name) in enumerate(result): geo_headers.append(name) col_values = [] col_errors = [] for table_id, table in table_metadata.items(): table_estimates = data.get(geoid, {}).get(table_id, {}).get('estimate') table_errors = data.get(geoid, {}).get(table_id, {}).get('error') if option == 'value': for column_id, column_info in table['columns'].items(): col_values.append(table_estimates.get(column_id, '')) col_errors.append(table_errors.get(column_id, '')) elif option == 'percent': denominator_column_id = table.get('denominator_column_id') if denominator_column_id: has_denominator_column = True base_estimate = data[geoid][table_id]['estimate'][denominator_column_id] for column_id, column_info in table['columns'].items(): if base_estimate and column_id in table_estimates and column_id in table_errors: col_values.append(table_estimates[column_id] / base_estimate) col_errors.append(table_errors[column_id] / base_estimate) else: any_zero_denominators = True col_values.append('*') col_errors.append('') else: col_values.append('*') col_errors.append('') for j, value in enumerate(col_values): col_num = (i + 1) * 2 row_num = j + 4 sheet.cell(row=row_num, column=col_num).value = value sheet.cell(row=row_num, column=col_num + 1).value = col_errors[j] if option == 'percent': sheet.cell(row=row_num, column=col_num).number_format = '0.00%' sheet.cell(row=row_num, column=col_num + 1).number_format = '0.00%' if option == 'percent' and (any_zero_denominators or not has_denominator_column): annotation_cell = sheet.cell(row=(row_num + 1), column=1) annotation_cell.font = Font(italic=True) if any_zero_denominators: annotation_cell.value = "* Base value of zero; no percentage available" elif not has_denominator_column: annotation_cell.value = "* Percentage values not appropriate for this table" else: annotation_cell.value = "* Unexpected error. Please contact Census Reporter at https://censusreporter.uservoice.com/ and let us know the page from where you downloaded this data." # Write geo headers for i in range(len(geo_headers)): current_col = (i + 1) * 2 current_cell = sheet.cell(row=2, column=current_col) current_cell.value = geo_headers[i] current_cell.alignment = Alignment(horizontal='center') sheet.merge_cells(start_row=2, end_row=2, start_column=current_col, end_column=current_col + 1) sheet.cell(row=3, column=current_col).value = "Value" sheet.cell(row=3, column=current_col + 1).value = "Error"
def export_and_save(event_pk, filename): # Create a spreadsheet and get active workbook ew = ExcelWriter() ws = ew.get_active() ws.title = "Split Entries" wsg = ew.wb.create_sheet("Grouped Entries") # Create title row titles = [ "Country", "Date of Lead Publication", "Date of Information", "Created By", "Date Imported", "Lead Title", "Source", "Excerpt", "Reliability", "Severity", "Demographic Groups", "Specific Needs Groups", "Affected Groups", "Pillar", "Subpillar", "Sector", "Subsector", ] countries = entry_models.Event.objects.get(pk=event_pk).countries\ .all().distinct() for country in countries: admin_levels = country.adminlevel_set.all() for i, admin_level in enumerate(admin_levels): titles.append('Admin {}'.format(i)) for i, t in enumerate(titles): ws.cell(row=1, column=i + 1).value = t ws.cell(row=1, column=i + 1).font = Font(bold=True) wsg.cell(row=1, column=i + 1).value = t wsg.cell(row=1, column=i + 1).font = Font(bold=True) ew.auto_fit_cells_in_row(1, ws) ew.auto_fit_cells_in_row(1, wsg) # Add each information in each entry belonging to this event informations = entry_models.EntryInformation.objects.filter( entry__lead__event__pk=event_pk, entry__template=None).distinct() grouped_rows = [] for i, info in enumerate(informations): try: rows = RowCollection(1) rows.permute_and_add(info.entry.lead.event.countries.all()) rows.add_values([ format_date(info.entry.lead.published_at), format_date(info.date), info.entry.created_by, format_date(info.entry.created_at.date()), info.entry.lead.name, info.entry.lead.source_name, xstr(info.excerpt), info.reliability.name, info.severity.name ]) # Column Name `Demographic Groups` Renamed to # `Vulnerable Group` as specified in Issue #280 rows.permute_and_add(info.vulnerable_groups.all()) rows.permute_and_add(info.specific_needs_groups.all()) rows.permute_and_add(info.affected_groups.all()) attributes = [] if info.informationattribute_set.count() > 0: for attr in info.informationattribute_set.all(): attr_data = [ attr.subpillar.pillar.name, attr.subpillar.name ] if attr.sector: attr_data.append(attr.sector.name) if attr.subsectors.count() > 0: for ss in attr.subsectors.all(): attributes.append(attr_data + [ss.name]) else: attributes.append(attr_data + ['']) else: attributes.append(attr_data + ['', '']) else: attributes.append(['', '', '', '']) rows.permute_and_add_list(attributes) for country in countries: admin_levels = country.adminlevel_set.all() for admin_level in admin_levels: selections = [] for map_selection in info.map_selections.all(): if admin_level == map_selection.admin_level: selections.append(map_selection.name) rows.permute_and_add(selections) ew.append(rows.rows, ws) grouped_rows.append(rows.group_rows) except: pass ew.append(grouped_rows, wsg) ew.save_to(filename)
wb = Workbook() ws = wb.active # for troubleshooting from collections import Counter if sys.version[0] == '2': reload(sys) sys.setdefaultencoding("utf-8") # sys.path.insert(0, "/var/www/cgi-bin/bank_recon.py") os.chdir(".") # bound path to the current directory # satisfy openpyxl requirements for highlighting cells highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=12) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) highlight.fill = PatternFill(fill_type='lightUp', start_color='fff000', end_color='6efdfd') # satisfy openpyxl requirements for highlighting cells2 highlight2 = NamedStyle(name="highlight2") highlight2.font = Font(bold=True, size=12) bd = Side(style='thick', color="000000") highlight2.border = Border(left=bd, top=bd, right=bd, bottom=bd) highlight2.fill = PatternFill(fill_type='lightUp', start_color='fff000', end_color='fff000')
def generate_comparison_data_xlsx(op_name, mi100_trace_events, v100_trace_events, scope_to_config_map, xlsx_file): mi100_data = extract_op_data(mi100_trace_events, op_name) v100_data = extract_op_data(v100_trace_events, op_name) mi100_scopes = sorted(mi100_data.keys()) v100_scopes = sorted(v100_data.keys()) if mi100_scopes != v100_scopes: print("Data collection error : scopes do not match") workbook = Workbook() sheet = workbook.active dark_green_font = Font(bold=True, color='008800') light_green_fill = PatternFill('solid', fgColor='DDFFDD') dark_red_font = Font(bold=True, color='880000') light_red_fill = PatternFill('solid', fgColor='FFDDDD') right_align = Alignment(horizontal="right") header = ["Model Scope and Config", "MI100(us)", "V100(us)", "V100/MI100", "MI100 Kernel Name", "V100 Kernel Name"] sheet.append(header) for i in range(len(header)): sheet.cell(row=sheet._current_row, column=i+1).font = Font(bold=True) sheet.append([]) for scope in mi100_scopes: config = scope_to_config_map[scope] mi100_kernels = mi100_data[scope] v100_kernels = v100_data[scope] num_mi100_kernels = len(mi100_kernels) num_v100_kernels = len(v100_kernels) num_kernels = max(2, num_mi100_kernels, num_v100_kernels) mi100_duration = 0 v100_duration = 0 for i in range(num_kernels): mi100_kernel_name, mi100_kernel_duration = mi100_kernels[i] if i < num_mi100_kernels else (" ","") v100_kernel_name, v100_kernel_duration = v100_kernels[i] if i < num_v100_kernels else (" ","") line = [""] if i == 0: line = [scope] if i == 1: line = [config] line.append(mi100_kernel_duration) line.append(v100_kernel_duration) line.append("") line.append(mi100_kernel_name) line.append(v100_kernel_name) sheet.append(line) mi100_duration += mi100_kernel_duration if mi100_kernel_duration else 0 v100_duration += v100_kernel_duration if v100_kernel_duration else 0 v100_mi100_ratio = v100_duration / mi100_duration line = ["layer total"] line.append(mi100_duration) line.append(v100_duration) line.append(v100_mi100_ratio) sheet.append(line) sheet.cell(row=sheet._current_row, column=1).alignment = right_align sheet.cell(row=sheet._current_row, column=4).font = dark_red_font if v100_mi100_ratio < 1.0 else dark_green_font sheet.cell(row=sheet._current_row, column=4).fill = light_red_fill if v100_mi100_ratio < 1.0 else light_green_fill sheet.append([]) sheet.append([]) line = [] line.append("Total Kernel Time (us)") line.append("=SUM(B2:B{})/2".format(sheet._current_row - 1)) line.append("=SUM(C2:C{})/2".format(sheet._current_row - 1)) sheet.append(line) sheet.column_dimensions['A'].width = 90 sheet.column_dimensions['B'].width = 15 sheet.column_dimensions['C'].width = 15 sheet.column_dimensions['D'].width = 15 sheet.column_dimensions['E'].width = 50 sheet.column_dimensions['F'].width = 50 workbook.save(filename=xlsx_file)
def format_ft1(lista): ft1 = Font(name='Calibri', size=9, italic=True) for komorka in lista: komorka.font = ft1
for j in range(1, 6): sheet.cell(row=i, column=j).border = thin_border wb.save(Name[0]) else: print 'New Sheet Created ' + OS SheetIndex = len(sheets) wb.create_sheet(index=SheetIndex - 1, title=OS) sheet = wb.get_sheet_by_name(OS) ListFormat = [ 'KB No.', 'Description', 'WDM Package Name', 'Affected software', 'Dependency' ] sheet['A1'] = 'Security Patches JAN 2016-' + OS + '' #------------------------Fonts and Styling------------------------------------------# font1 = Font(bold=True) BackColor = PatternFill(patternType='solid', fgColor=Color('FFFF00')) BackColor2 = PatternFill(patternType='solid', fgColor=Color('528ED5')) align = Alignment(horizontal='center') sheet.merge_cells('A1:E1') for i in range(1, 6): sheet.cell(row=1, column=i).style = Style(alignment=align, fill=BackColor2, font=font1) sheet.cell(row=2, column=i).style = Style(fill=BackColor, font=font1) sheet.cell(row=2, column=i).value = ListFormat[i - 1] #-----------------------------Adding Data to new sheet-------------------------------# TotalKB = len(KBNos) for j in range(3, TotalKB + 3): sheet.cell(row=j, column=1).value = KBNos[j - 3]
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml(xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def exportTable(matrix, fname, ext, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save ext: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = QApplication.translate("pychemqt", "Table") if fname.split(".")[-1] != ext: fname += ".%s" % ext # Format title header = [] if title: for ttl in title: line = str(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) if ext == "csv": import csv with open(fname, 'w') as csvfile: writer = csv.DictWriter(csvfile, fieldnames=header) writer.writeheader() for row in matrix: kw = {} for ttl, value in zip(header, row): kw[ttl] = value writer.writerow(kw) elif ext == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet = ezodf.Table(sheetTitle) sheets += sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif ext == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif ext == "xlsx": import openpyxl from openpyxl.styles import Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i" % (spreadsheetColumn(i), j+2)] = data sheet["%s%i" % (spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + ext)
#Create new sheet in Excel with template name if does not already exist wb.create_sheet(list_template_index) # Now that we have an Excel workbook with a sheet per template, we need to populate it. Try putting in just headers first # Put column_headers into Excel and style. Only really want to do this once though... and probably have as separate function for column_count, header in enumerate(header_list, 1): #print "\nThe list index here is: %s\n" % list_template_index sheet_selected = wb[list_template_index] cell_pos = sheet_selected.cell(row=1, column=column_count) cell_pos.value = header # Ensures data is centered within cell cell_pos.alignment = Alignment(horizontal="center", wrapText=True) cell_pos.font = Font(bold=True) #Now that headers are filled out in each template...need to add in data for row_index, row in enumerate(table.rows): single_row = [ table.cell(row_index, column).text_frame.text for column in range(0, number_of_columns) ] #print "\nRow %s in table is: %s\n" % (row_index, single_row) #print "\nThe length of a single row is: %s\n" % len(single_row) #Zip up row with header template only if same length if row_index != 0 and len(single_row) == len( header_list):
def export(self) -> str: def cm_to_inch(cm: float) -> float: return cm * 0.393701 def prepare(ws: openpyxl.worksheet.Worksheet) -> None: ws.header_footer.setHeader( '&L&"Calibri,Regular"&K000000&G&C&"Lao UI,Bold"&8Total Project Construction Pty. Ltd.&"Lao UI,Regular"&K000000_x000D_ACN 117 578 560 ABN 84 117 578 560_x000D_PO Box 313 HALL ACT_x000D_P: 02-6230 2455 F:02-6230 2488_x000D_E: [email protected]' ) ws.header_footer.setFooter( f'&L&"Arial,Italic"&9&K000000App. A - Contract Variations&R&"Arial,Italic"&9&K000000{self.name}' ) ws.page_margins.top = cm_to_inch(3.4) ws.page_margins.bottom = cm_to_inch(2) ws.page_margins.left = cm_to_inch(1.2) ws.page_margins.right = cm_to_inch(1.1) fill = PatternFill(patternType='solid', fgColor=Color('D8E4BC')) wb: openpyxl.Workbook = openpyxl.Workbook() ws: openpyxl.worksheet.Worksheet = wb.active ws.title = 'Claim - TOTAL' prepare(ws) if self.client is None: client_name = '' client_first_line_address = '' client_second_line_address = '' else: client_name = self.client.name client_first_line_address = self.client.first_line_address client_second_line_address = self.client.second_line_address if self.superintendent is None: superintendent_name = '' superintendent_first_line_address = '' superintendent_second_line_address = '' else: superintendent_name = self.superintendent.name superintendent_first_line_address = self.superintendent.first_line_address superintendent_second_line_address = self.superintendent.second_line_address ws['A1'].value = 'Client: ' ws['A1'].value += ' '.join([client_name, superintendent_name]) ws['A1'].font = Font(name='Lao UI', size=10, bold=True) ws['A2'].value = ' ' ws['A2'].value += ' '.join( [client_first_line_address, superintendent_first_line_address]) ws['A3'].value = ' ' ws['A3'].value += ' '.join( [client_second_line_address, superintendent_second_line_address]) ws['C1'].value = f'Reference #: {arrow.now("Australia/Canberra").format("MM")}-{self.reference_number}' ws['C1'].font = Font(name='Lao UI', size=10, bold=True) ws['C3'].value = f'Date: {arrow.now("Australia/Canberra").format("DD/MM/YY")}' ws['C3'].font = Font(name='Lao UI', size=10) ws['A4'].value = 'PROGRESS CLAIM No.' ws['A4'].font = Font(name='Lao UI', size=14) ws['A4'].fill = fill ws['B4'].value = f'Project No: {self.reference_number}' ws['B4'].font = Font(name='Lao UI', size=10) ws['C4'].value = f'{arrow.now("Australia/Canberra").format("MMMM")}' ws['C4'].font = Font(name='Lao UI', size=10) ws['B5'].value = 'Approval terms: ' ws['B5'].font = Font(name='Lao UI', size=10) ws['A6'].value = f'Project: {self.name}' ws['A6'].font = Font(name='Lao UI', size=11, bold=True) ws['B6'].value = 'Contract' ws['B6'].font = Font(name='Lao UI', size=10, bold=True) ws['B6'].alignment = Alignment(horizontal='center') ws['C6'].value = 'Completed' ws['C6'].font = Font(name='Lao UI', size=10, bold=True) ws['C6'].alignment = Alignment(horizontal='center') ws['A7'].value = '' ws['B7'].value = 'Value' ws['B7'].font = Font(name='Lao UI', size=10, bold=True) ws['B7'].alignment = Alignment(horizontal='center') ws['C7'].value = 'To Date' ws['C7'].font = Font(name='Lao UI', size=10, bold=True) ws['C7'].alignment = Alignment(horizontal='center') ws.merge_cells('D6:D7') ws['D6'].value = '%' ws['D6'].font = Font(name='Arial', size=12, bold=True) ws['D6'].alignment = Alignment(horizontal='center', vertical='center') for row in [6, 7]: for column in 'ABCD': cell = ws[f'{column}{row}'] cell.fill = fill ws['A1'].border = Border( top=Side(border_style='medium', color='FF000000')) ws['B1'].border = Border( top=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['C1'].border = Border( top=Side(border_style='medium', color='FF000000')) ws['D1'].border = Border( top=Side(border_style='medium', color='FF000000')) ws['B2'].border = Border( right=Side(border_style='medium', color='FF000000')) ws['A3'].border = Border( bottom=Side(border_style='medium', color='FF000000')) ws['B3'].border = Border( bottom=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['A4'].border = Border( top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), ) ws['A6'].border = Border( top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['B6'].border = Border( top=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['C6'].border = Border( top=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['D6'].border = Border( top=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['A7'].border = Border( top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['B7'].border = Border( bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['C7'].border = Border( bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) ws['D7'].border = Border( bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), ) row = 8 def _comp(p): return p.id self.progress_items.sort(key=_comp) for progress_item in self.progress_items: ws[f'A{row}'].value = progress_item.name ws[f'B{row}'].value = progress_item.contract_value ws[f'C{row}'].value = progress_item.completed_value ws[f'D{row}'].value = f'= C{row}/B{row}' # print(ws['B{}'.format(row)].number_format) # print(ws['C{}'.format(row)].number_format) row += 1 for irow in range(8, row): for column in 'ABCD': cell = ws[f'{column}{irow}'] cell.font = Font(name='Lao UI', size=9) cell.border = Border( left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), ) if column == 'D': cell.alignment = Alignment(vertical='center', horizontal='center') ws[f'A{row}'].value = 'TOTAL OF CONTRACT' ws[f'B{row}'].value = f'=SUM(B{8}:B{row - 1})' ws[f'C{row}'].value = f'=SUM(C{8}:C{row - 1})' ws[f'D{row}'].value = f'=C{row}/B{row}' for column in 'ABCD': cell = ws[f'{column}{row}'] cell.font = Font(name='Lao UI', size=9, bold=True) row += 1 ws[f'A{row}'].value = 'Variations - See Appendix A attached' ws[f'B{row}'].value = r"='Appendix A'!D34" ws[f'C{row}'].value = r"='Appendix A'!E34" ws[f'D{row}'].value = f'=C{row}/B{row}' for row in [row - 1, row]: for column in 'ABCD': cell = ws[f'{column}{row}'] cell.fill = fill cell.border = Border(top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) row += 1 ws[f'A{row}'].value = 'Totals Excluding GST' ws[f'B{row}'].value = f'=B{row - 1} + B{row - 2}' ws[f'C{row}'].value = f'=C{row - 1} + C{row - 2}' ws[f'D{row}'].value = f'=C{row}/B{row}' for column in 'BCD': cell = ws[f'{column}{row}'] cell.fill = fill cell.border = Border(top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000')) row += 1 ws[f'A{row}'].value = 'Less paid to date' ws[f'C{row}'].border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) row += 1 ws[f'A{row}'].value = 'Value of work completed this period' ws[f'C{row}'].value = f'=C{row - 2} - C{row - 1}' ws[f'C{row}'].border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) row += 1 ws[f'A{row}'].value = 'GST this period' ws[f'C{row}'].value = f'=C{row - 1} * 10%' ws[f'C{row}'].border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) for irow in range(row - 3, row + 1): for column in 'ABCD': cell = ws[f'{column}{irow}'] cell.font = Font(name='Lao UI', size=9) row += 1 ws[f'A{row}'].value = 'TOTAL PAYABLE THIS CLAIM' ws[f'A{row}'].font = Font(name='Lao UI', size=9, bold=True) ws[f'C{row}'].value = f'=C{row - 2} + C{row - 1}' ws[f'C{row}'].font = Font(name='Lao UI', size=9, bold=True) ws[f'C{row}'].border = Border(top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000')) for irow in range(8, row + 1): ws[f'B{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' ws[f'C{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' ws[f'D{irow}'].number_format = r'0.00%' ws.column_dimensions['A'].width = 40 ws.column_dimensions['B'].width = 15 ws.column_dimensions['C'].width = 15 ws.column_dimensions['D'].width = 8 ws.sheet_view.view = 'pageLayout' ws: openpyxl.worksheet.Worksheet = wb.create_sheet() ws.title = 'Appendix A' prepare(ws) ws.merge_cells('A1:D1') ws['A1'].style.alignment.wrap_text = True ws['A1'].value = f'{self.name}\nJOB #: {self.reference_number}' ws['A1'].fill = fill ws['A1'].font = Font(name='Lao UI', size=11, bold=True) ws.merge_cells('A3:B3') ws['A3'].value = "Appendix 'A' - Contract variations" ws['A3'].font = Font(name='Lao UI', size=10, bold=True) # ws['E3'].value = '=TODAY()' ws['E3'].value = datetime.today() ws['E3'].number_format = 'mm-dd-yy' ws['E3'].font = Font(name='Lao UI', size=9) ws['A5'].value = 'NO.' ws['B5'].value = 'ITEM' ws['C5'].value = 'PENDING' ws['D5'].value = 'APPROVED' ws['E5'].value = 'COMPLETED' for cell in ['A5', 'B5', 'C5', 'D5', 'E5']: ws[cell].font = Font(name='Lao UI', size=10, bold=True) ws[cell].alignment = Alignment(horizontal='center') ws[cell].fill = fill ws[cell].border = Border(top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000')) row = 6 self.variations.sort(key=lambda v: v.vid) for variation in self.variations: ws['A' + str(row)].value = row - 5 ws['B' + str(row)].value = variation.description if variation.pending or variation.approved: if variation.pending: column = 'C' else: column = 'D' ws[column + str(row)].value = variation.amount elif variation.declined: ws['B' + str( row )].value = f"{variation.description} (declined {variation.amount})" ws['C' + str(row)].value = 0.0 if variation.completed: ws['E' + str(row)].value = variation.amount row += 1 while row < 34: for column in ['A', 'B', 'C', 'D', 'E']: cell = ws[column + str(row)] cell.border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) row += 1 for index in range(6, row): for column in ['A', 'B', 'C', 'D', 'E']: cell = ws[f'{column}{index}'] cell.border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000')) if column == 'A': cell.font = Font(name='Lao UI', size=10, bold=True) cell.alignment = Alignment(vertical='center', horizontal='center') else: cell.font = Font(name='Lao UI', size=9) if column == 'B': cell.alignment = Alignment(vertical='center', horizontal='left') else: cell.alignment = Alignment(vertical='center') cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' ws.merge_cells(f'A{row}:B{row}') ws[f'A{row}'].value = 'TOTALS' ws[f'C{row}'].value = f'=SUM(C6:C{row - 1})' ws[f'D{row}'].value = f'=SUM(D6:D{row - 1})' ws[f'E{row}'].value = f'=SUM(E6:E{row - 1})' for column in 'ABCDE': cell = ws[f'{column}{row}'] cell.alignment = Alignment(vertical='center', horizontal='center') cell.fill = fill cell.border = Border(left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000'), top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000')) if column != 'A': cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' ws.row_dimensions[1].height = 30 for row in range(len(self.variations)): ws.row_dimensions[6 + row].height = 30 ws.column_dimensions['A'].width = 5 ws.column_dimensions['B'].width = 40 ws.column_dimensions['C'].width = 13 ws.column_dimensions['D'].width = 13 ws.column_dimensions['E'].width = 13 ws.cell('A1').alignment = Alignment(wrapText=True) ws.sheet_view.view = 'pageLayout' for index, variation in enumerate(self.variations): new_ws: openpyxl.worksheet.Worksheet = wb.create_sheet() prepare(new_ws) new_ws.title = f'V{index + 1}' new_ws.merge_cells('A1:H1') new_ws['A1'].value = 'CONTRACT VARIATION' new_ws['A1'].fill = fill new_ws['A1'].font = Font(name='Lao UI', size=16, bold=True) new_ws['A1'].alignment = Alignment(vertical='center', horizontal='center') for column in 'ABCDEFGH': new_ws[column + '1'].border = Border( top=Side(border_style='medium', color='FF000000'), bottom=Side(border_style='medium', color='FF000000'), left=Side(border_style='medium', color='FF000000'), right=Side(border_style='medium', color='FF000000')) new_ws.merge_cells('B3:H3') new_ws['B3'].value = f'PROJECT: {self.name}' new_ws['B3'].fill = fill new_ws['B3'].font = Font(name='Lao UI', size=12, bold=True) for column in 'BCDEFGH': new_ws[f'{column}3'].border = Border( top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000')) new_ws['B5'].value = 'VARIATION NO:' new_ws['B5'].fill = fill new_ws['C5'].fill = fill new_ws['B5'].font = Font(name='Lao UI', size=12, bold=True) new_ws.merge_cells('D5:E5') new_ws['D5'].value = index + 1 new_ws['D5'].fill = fill new_ws['D5'].font = Font(name='Lao UI', size=14, bold=True) new_ws.merge_cells('G5:H5') new_ws['G5'].value = f'TPC REF: {self.reference_number}' new_ws['G5'].font = Font(name='Lao UI', size=14, bold=True) new_ws['G5'].alignment = Alignment(vertical='center', horizontal='left') for column in 'BCDE': cell = new_ws[column + str(5)] cell.border = Border(top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000')) for column in 'BCDEFGH': cell = new_ws[column + str(6)] cell.border = Border( bottom=Side(border_style='medium', color='FF000000')) row = 7 if len(variation.items) > 1: new_ws.merge_cells(f'B{row}:G{row}') new_ws[f'B{row}'].value = variation.description new_ws[f'B{row}'].font = Font(name='Lao UI', size=11, bold=True) new_ws[f'B{row}'].alignment = Alignment(vertical='center') row += 1 for item in variation.items: new_ws.merge_cells(f'B{row}:G{row}') new_ws[f'B{row}'].value = item.description new_ws[f'B{row}'].font = Font(name='Lao UI', size=11, bold=True) new_ws[f'B{row}'].alignment = Alignment(vertical='center') new_ws[f'H{row}'].value = item.amount new_ws[f'H{row}'].font = Font(name='Lao UI', size=11, bold=True) new_ws[f'H{row}'].alignment = Alignment(vertical='center') new_ws[ f'H{row}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' new_ws.row_dimensions[row].height = 40 row += 1 while row < 13: new_ws[f'B{row}'].font = Font(name='Lao UI', size=11) new_ws[f'H{row}'].font = Font(name='Lao UI', size=11) row += 1 for column in 'BCDEFGH': cell = new_ws[column + str(row - 1)] cell.border = Border( bottom=Side(border_style='thin', color='FF000000')) new_ws['B' + str(row)].value = 'Value of work' new_ws['H' + str(row)].value = f'=SUM(H7:H{row - 1})' new_ws['B' + str(row)].font = Font(name='Lao UI', size=11) new_ws['H' + str(row)].font = Font(name='Lao UI', size=11) row += 1 new_ws['B' + str(row)].value = f'Add OH/Profit {self.margin * 100}%' new_ws['H' + str(row)].value = f'=H{row - 1} * {self.margin * 100}%' new_ws['B' + str(row)].font = Font(name='Lao UI', size=11) new_ws['H' + str(row)].font = Font(name='Lao UI', size=11) if self.admin_fee is not None and self.admin_fee != 0: row += 1 new_ws['B' + str(row)].value = 'Fixed administration fee' new_ws['H' + str(row)].value = self.admin_fee new_ws['B' + str(row)].font = Font(name='Lao UI', size=11) new_ws['H' + str(row)].font = Font(name='Lao UI', size=11) for column in 'BCDEFGH': new_ws[column + str(row)].border = Border( bottom=Side(border_style='thin', color='FF000000')) row += 1 new_ws['B' + str(row)].value = 'Subtotal' if self.admin_fee is None: new_ws['H' + str(row)] = f'=H{row - 2} + H{row - 1}' else: new_ws['H' + str(row)] = f'=H{row - 3} + H{row - 2} + H{row - 1}' new_ws['H' + str(row)].font = Font(name='Lao UI', size=11, bold=True) row += 1 new_ws['B' + str(row)] = 'Add GST' new_ws['H' + str(row)].value = f'=H{row - 1} * 0.1' new_ws['H' + str(row)].font = Font(name='Lao UI', size=11, underline='singleAccounting') for column in 'BCDEFGH': new_ws[column + str(row)].border = Border( bottom=Side(border_style='medium', color='FF000000')) row += 1 new_ws.merge_cells(f'B{row}:C{row}') for column in 'BCDEFGH': new_ws[f'{column}{row}'].fill = fill new_ws['B' + str(row)] = 'TOTAL' new_ws['H' + str(row)].value = f'=H{row - 1} + H{row - 2}' new_ws['H' + str(row)].font = Font(name='Lao UI', size=11, bold=True) for idx in range(7, row + 1): new_ws['H' + str( idx )].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-' row += 4 new_ws[f'B{row}'].value = 'Variation Prepared By:' new_ws[f'G{row}'].value = 'Variation Prepared For:' new_ws[f'B{row}'].font = Font(name='Lao UI', size=11) new_ws[f'G{row}'].font = Font(name='Lao UI', size=11) row += 5 new_ws[f'B{row}'].value = 'FOR' new_ws[f'B{row}'].font = Font(name='Lao UI', size=11) new_ws[f'B{row + 1}'].value = 'Total Project Construction Pty Ltd' new_ws[f'B{row + 1}'].font = Font(name='Lao UI', size=11) new_ws[f'G{row}'].value = 'FOR' new_ws[f'G{row}'].font = Font(name='Lao UI', size=11) new_ws[f'G{row + 1}'].value = variation.prepared_for new_ws[f'G{row + 1}'].font = Font(name='Lao UI', size=11) row += 3 new_ws[f'B{row}'].value = 'Date:' # new_ws['C{}'.format(row)].value = '=TODAY()' new_ws[f'C{row}'].value = datetime.today() new_ws[f'C{row}'].number_format = 'mm-dd-yy' new_ws[f'G{row}'].value = 'Date:' new_ws[f'B{row}'].font = Font(name='Lao UI', size=11) new_ws[f'C{row}'].font = Font(name='Lao UI', size=11) new_ws[f'G{row}'].font = Font(name='Lao UI', size=11) new_ws.row_dimensions[1].height = 60 new_ws.row_dimensions[3].height = 40 new_ws.column_dimensions['A'].width = 3 new_ws.column_dimensions['D'].width = 6 new_ws.column_dimensions['F'].width = 4 new_ws.column_dimensions['H'].width = 12 new_ws.sheet_view.view = 'pageLayout' fn = self.name + '.xlsx' wb.save('generated/' + fn) return fn
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 InsertCellValues(r,col,v,ws): if(len(v)==5): d=r[0] e=r[1] f=r[2] ws[d+col[0]]=float(v[0]) ws[d+col[0]].font=Font(color='0000FF') ws[d+col[0]].alignment=Alignment(horizontal='left') ws[e+col[0]]=float(v[1]) ws[f+col[0]]=float(v[2]) ws[e+col[1]]=float(v[3]) ws[e+col[1]].font=Font(color='FF0000') ws[f+col[1]]=float(v[4]) ws[f+col[1]].font=Font(color='FF0000') totalTrips=float(v[1])+float(v[3]) totalAmount=float(v[2])+float(v[4]) elif(len(v)==4): c=r[0] d=r[1] e=r[2] f=r[3] ws[c+col[0]]=float(v[0]) ws[d+col[0]]=float(v[1]) ws[e+col[0]]=float(v[2]) ws[f+col[0]]=float(v[3]) ws[c+col[0]].font=Font(color='0000FF') ws[d+col[0]].font=Font(color='0000FF') ws[e+col[0]].font=Font(color='0000FF') ws[f+col[0]].font=Font(color='0000FF') ws[d+col[0]].alignment=Alignment(horizontal='left') totalTrips=float(v[2]) totalAmount=float(v[3]) elif(len(v)==7): c=r[0] d=r[1] e=r[2] f=r[3] ws[c+col[0]]=v[0] ws[c+col[0]].alignment=Alignment(horizontal='left') ws[c+col[0]].font=Font(color='000000',bold=True,size=11) ws[e+col[1]]=v[1] ws[f+col[1]]=v[2] ws[e+col[1]].alignment=Alignment(horizontal='center') ws[e+col[1]].font=Font(color='000000',size=11) ws[f+col[1]].alignment=Alignment(horizontal='center') ws[f+col[1]].font=Font(color='000000',size=11) ws[c+col[2]]=float(v[3]) ws[d+col[2]]=float(v[4]) ws[d+col[2]].font=Font(color='0000FF') ws[d+col[2]].alignment=Alignment(horizontal='left') ws[c+col[2]].font=Font(color='0000FF') ws[e+col[2]]=float(v[5]) ws[f+col[2]]=float(v[6]) ws[e+col[2]].font=Font(color='0000FF') ws[f+col[2]].font=Font(color='0000FF') totalTrips=float(v[5]) totalAmount=float(v[6]) elif(len(v)==6): c=r[0] d=r[1] e=r[2] f=r[3] ws[c+col[0]]=float(v[0]) ws[c+col[0]].font=Font(color='0000FF') ws[d+col[0]]=float(v[1]) ws[d+col[0]].font=Font(color='0000FF') ws[d+col[0]].alignment=Alignment(horizontal='left') ws[e+col[0]]=float(v[2]) ws[e+col[0]].font=Font(color='0000FF') ws[f+col[0]]=float(v[3]) ws[f+col[0]].font=Font(color='0000FF') ws[e+col[1]]=float(v[4]) ws[e+col[1]].font=Font(color='FF0000') ws[f+col[1]]=float(v[5]) ws[f+col[1]].font=Font(color='FF0000') totalTrips=float(v[2])+float(v[4]) totalAmount=float(v[3])+float(v[5]) elif(len(v)==9): c=r[0] d=r[1] e=r[2] f=r[3] ws[c+col[0]]=v[0] ws[c+col[0]].alignment=Alignment(horizontal='left') ws[c+col[0]].font=Font(color='000000',bold=True,size=11) ws[e+col[1]]=v[1] ws[f+col[1]]=v[2] ws[e+col[1]].alignment=Alignment(horizontal='center') ws[e+col[1]].font=Font(color='000000',size=11) ws[f+col[1]].alignment=Alignment(horizontal='center') ws[f+col[1]].font=Font(color='000000',size=11) ws[c+col[2]]=float(v[3]) ws[c+col[2]].font=Font(color='0000FF') ws[d+col[2]]=float(v[4]) ws[d+col[2]].font=Font(color='0000FF') ws[d+col[2]].alignment=Alignment(horizontal='left') ws[e+col[2]]=float(v[5]) ws[f+col[2]]=float(v[6]) ws[e+col[3]]=float(v[7]) ws[e+col[3]].font=Font(color='FF0000') ws[f+col[3]]=float(v[8]) ws[f+col[3]].font=Font(color='FF0000') totalTrips=float(v[5])+float(v[7]) totalAmount=float(v[6])+float(v[8]) return (totalTrips,totalAmount)
def exportTable(matrix, fname, format, title=None): """Save data to a file Inputs matrix: array with data to save fname: name of file to save format: name of format to save csv | ods | xls | xlsx title: column title array, optional """ sheetTitle = unicode(QApplication.translate("pychemqt", "Table")) if fname.split(".")[-1] != format: fname+=".%s" % format # Format title if title: header = [] for ttl in title: line = unicode(ttl).split(os.linesep) if line[-1] != "[-]": line[-1] = "["+line[-1]+"]" header.append(" ".join(line)) c_newline=maketrans(os.linesep, " ") if format == "csv": import csv with open(fname, "w") as archivo: writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE) # Add Data if title: writer.writerow([ttl.translate(c_newline) for ttl in header]) c_float=maketrans(".", ",") for row in matrix: writer.writerow([str(data).translate(c_float) for data in row]) elif format == "ods": import ezodf spreadsheet = ezodf.newdoc("ods", fname) sheets = spreadsheet.sheets sheet=ezodf.Table(sheetTitle) sheets+=sheet sheet.reset(size=(len(matrix)+1, len(matrix[0]))) # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)].set_value(ttl) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)].set_value(data) spreadsheet.save() elif format == "xls": import xlwt spreadsheet = xlwt.Workbook() sheet = spreadsheet.add_sheet(sheetTitle) font = xlwt.Font() font.bold = True style = xlwt.XFStyle() style.font = font # Add Data if title: for i, ttl in enumerate(header): sheet.write(0, i, ttl, style) for j, row in enumerate(matrix): for i, data in enumerate(row): sheet.write(j+1, i, data) spreadsheet.save(fname) elif format == "xlsx": import openpyxl from openpyxl.styles import Style, Font spreadsheet = openpyxl.Workbook() sheet = spreadsheet.active sheet.title = sheetTitle font1 = Font() font1.size = 9 font1.bold = True font2 = Font() font2.size = 9 # Add Data if title: for i, ttl in enumerate(header): sheet["%s%i"%(spreadsheetColumn(i), 1)] = ttl sheet["%s%i"%(spreadsheetColumn(i), 1)].style.font= font1 for j, row in enumerate(matrix): for i, data in enumerate(row): sheet["%s%i"%(spreadsheetColumn(i), j+2)] = data sheet["%s%i"%(spreadsheetColumn(i), j+2)].style.font = font2 spreadsheet.save(filename=fname) else: raise ValueError(QApplication.translate( "pychemqt", "Unsopported format") + " " + format)
if args["group"]: grupos = get_group_ids(args["group"]) else: grupos = get_group_ids() for g in zapi.hostgroup.get(output="extend", groupids=grupos): print("--> Grupo selecionado: " + g["name"]) # CRIANDO PLANILHA wb = Workbook() sheet = wb.active sheet.title = "Eventos" sheet["A1"] = "INICIO" sheet["A1"].font = Font(sz=12, bold=True) sheet["B1"] = "FIM" sheet["B1"].font = Font(sz=12, bold=True) sheet["C1"] = "HOST" sheet["C1"].font = Font(sz=12, bold=True) sheet["D1"] = "TRIGGER" sheet["D1"].font = Font(sz=12, bold=True) sheet["E1"] = "SEVERIDADE" sheet["E1"].font = Font(sz=12, bold=True) sheet["F1"] = "STATUS" sheet["F1"].font = Font(sz=12, bold=True) sheet["G1"] = "GRUPO" sheet["G1"].font = Font(sz=12, bold=True) sheet["H1"] = "APP" sheet["H1"].font = Font(sz=12, bold=True) sheet["I1"] = "ACK"
def parse_fonts(self): """Read in the fonts""" fonts = self.root.findall('{%s}fonts/{%s}font' % (SHEET_MAIN_NS, SHEET_MAIN_NS)) for node in fonts: yield Font.from_tree(node)
from openpyxl.styles import Font from openpyxl.styles import Alignment A1 = Font( name="MS UI Gothic", size=19.0, extend=None, underline=None, vertAlign=None, scheme=None, charset=128, family=3.0, bold=False, italic=False, strike=None, outline=None, shadow=None, condense=None, ) A2 = Font( name="MS PGothic", size=10.0, extend=None, underline=None, vertAlign=None, scheme=None, charset=128, family=3.0, bold=True, italic=False,