def write_sumif_formula(self, worksheet, first_formula_range, condition_cell, second_formula_range, formula_cell, cells_format=None): formula_first_first_cell_row, formula_first_first_cell_column, formula_first_second_cell_row, \ formula_first_second_cell_column = first_formula_range.get_range_cells_coordinates() formula_second_first_cell_row, formula_second_first_cell_column, formula_second_second_cell_row, \ formula_second_second_cell_column = second_formula_range.get_range_cells_coordinates() condition_cell_row, condition_cell_column = condition_cell.get_cell_coordinates( ) formula_cell_row, formula_cell_column = formula_cell.get_cell_coordinates( ) first_range = xl_range(formula_first_first_cell_row, formula_first_first_cell_column, formula_first_second_cell_row, formula_first_second_cell_column) second_range = xl_range(formula_second_first_cell_row, formula_second_first_cell_column, formula_second_second_cell_row, formula_second_second_cell_column) formula_condition_cell = xl_rowcol_to_cell(condition_cell_row, condition_cell_column) formula = self._sumif_formula_template.format(first_range, formula_condition_cell, second_range) worksheet.write_formula(formula_cell_row, formula_cell_column, formula, cells_format)
def to_xlsx(csv): """Convert typeperf's CSV to xlsx charts.""" if not csv.endswith('.csv'): click.echo("please select typeperf's csv") return xlsx = re.sub(r"\.csv$", ".xlsx", csv) if os.path.exists(xlsx): click.echo("please remove {0}".format(xlsx)) return click.echo("convert {0} to {1}".format(csv, xlsx)) df = pd.read_csv(csv, index_col=[0], parse_dates=[0], na_values=[" "]) df.index.name = None writer = pd.ExcelWriter(xlsx, engine="xlsxwriter", datetime_format="yyyy/MM/dd-hh:mm:ss") df.to_excel(writer, sheet_name="data") workbook = writer.book for idx in range(df.shape[1]): name = "Chart" + xl_col_to_name(idx + 1) chartname = df.columns[idx] print "idx:{0} name:{1} df:{2}".format(idx, name, chartname) chart = workbook.add_chart({'type': 'scatter', 'subtype': 'straight'}) chart.set_legend({'none': True}) chart.set_title({'name': chartname, 'overlay': True, 'none': False}) categories = '=data!' + xl_range(1, 0, df.shape[0], 0) values = '=data!' + xl_range(1, idx + 1, df.shape[0], idx + 1) chart.add_series({'categories': categories, 'values': values}) cs = workbook.add_chartsheet(name) cs.set_chart(chart) workbook.close()
def exportar_total_ubicados_organismos(request): organismos=Organismo.objects.all() total=len(organismos) if request.method == "POST": anno=int(request.POST["anno"]) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = "attachment; filename=Totales_ubicados_organismos_%s.xlsx"%anno book = Workbook(response, {'in_memory': True}) bold = book.add_format({'bold': True, 'border': 1}) format = book.add_format({'border': 1}) sheet = book.add_worksheet("Ubicados") sheet.set_column('A:A', 60) sheet.set_column('B:B', 20) sheet.set_column('C:C', 20) sheet.write(0, 0, "Organismos",bold) sheet.write(0, 1, "Total Ubicados",bold) sheet.write(0, 2, "Presentados",bold) for i,organismo in enumerate(organismos): total_ubicados=UbicacionLaboral.objects.filter(organismo=organismo,anno_graduado=int(anno)).count() presentados=UbicacionLaboral.objects.filter(organismo=organismo,anno_graduado=int(anno),presentado=True).count() sheet.write(i+1, 0, organismo.nombre,format) sheet.write(i+1, 1, total_ubicados,format) sheet.write(i+1, 2, presentados,format) formula_rango_total = '=SUM(%s)' % xl_range(1, 1,total, 1) formula_rango_presentados = '=SUM(%s)' % xl_range(1, 2,total, 2) sheet.write(total+1, 0, "Total",bold) sheet.write(total+1, 1, formula_rango_total,bold) sheet.write(total+1, 2, formula_rango_presentados,bold) book.close() return response else: return Http404
def exportar_organismos_expedientes(request): organismos=Organismo.objects.all() total=len(organismos) if request.method == "POST": perfil=request.user.perfil_usuario anno=int(request.POST["anno"]) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = "attachment; filename=resumen_exp_aprobados_organismos_%s.xlsx"%anno book = Workbook(response, {'in_memory': True}) bold = book.add_format({'bold': True, 'border': 1}) format = book.add_format({'border': 1}) sheet = book.add_worksheet("Resumen") sheet.set_column('A:A', 60) sheet.set_column('B:B', 10) sheet.set_column('B:B', 10) sheet.set_column('C:C', 10) sheet.write(0, 0, "Organismos",bold) sheet.write(0, 1, "Liberados",bold) sheet.write(0, 2, "Aceptados",bold) sheet.write(0, 3, "Internos",bold) if perfil.categoria.nombre == "organismo" and perfil.organismo.hijode == None: organismos=list(Organismo.objects.filter(hijode=perfil.organismo)) organismos.insert(0,perfil.organismo) total=len(organismos) for i,organismo in enumerate(organismos): liberados=Expediente_aprobado.objects.filter(expediente__organismo_liberacion=organismo,fecha_aprobado__year=anno).count() aceptados=Expediente_aprobado.objects.filter(expediente__organismo_aceptacion=organismo,fecha_aprobado__year=anno).count() internos=Expediente_movimiento_interno.objects.filter(organismo=organismo,fecha_registro__year=anno).count() sheet.write(i+1, 0, organismo.nombre,format) sheet.write(i+1, 1, liberados,format) sheet.write(i+1, 2, aceptados,format) sheet.write(i+1, 3, internos,format) else: for i,organismo in enumerate(organismos): liberados=Expediente_aprobado.objects.filter(expediente__organismo_liberacion=organismo,fecha_aprobado__year=anno).count() aceptados=Expediente_aprobado.objects.filter(expediente__organismo_aceptacion=organismo,fecha_aprobado__year=anno).count() internos=Expediente_movimiento_interno.objects.filter(organismo=organismo,fecha_registro__year=anno).count() sheet.write(i+1, 0, organismo.nombre,format) sheet.write(i+1, 1, liberados,format) sheet.write(i+1, 2, aceptados,format) sheet.write(i+1, 3, internos,format) formula_rango_total_liberados = '=SUM(%s)' % xl_range(1, 1,total, 1) formula_rango_rango_total_aceptados = '=SUM(%s)' % xl_range(1, 2,total, 2) formula_rango_rango_total_internos = '=SUM(%s)' % xl_range(1, 3,total, 3) sheet.write(total+1, 0, "Total",bold) sheet.write(total+1, 1, formula_rango_total_liberados,bold) sheet.write(total+1, 2, formula_rango_rango_total_aceptados,bold) sheet.write(total+1, 3, formula_rango_rango_total_internos,bold) book.close() return response else: return Http404
def exportar_total_ubicados_provincias(request, opcion): provincias = Provincia.objects.all() total = len(provincias) total_ubicados = 0 if request.method == "POST": anno = int(request.POST["anno"]) response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response[ 'Content-Disposition'] = "attachment; filename=Totales_ubicados_provincia_%s_%s.xlsx" % ( opcion, anno) book = Workbook(response, {'in_memory': True}) bold = book.add_format({'bold': True, 'border': 1}) format = book.add_format({'border': 1}) sheet = book.add_worksheet("Ubicados") sheet.write(0, 0, "Provincias", bold) sheet.write(0, 1, "Total Ubicados", bold) sheet.write(0, 2, "Presentados", bold) sheet.set_column('A:A', 30) sheet.set_column('B:B', 20) sheet.set_column('C:C', 20) for i, provincia in enumerate(provincias): if opcion == "ubicacion": total_ubicados = UbicacionLaboral.objects.filter( provincia_ubicacion=provincia, anno_graduado=int(anno)).count() presentados = UbicacionLaboral.objects.filter( presentado=True, provincia_ubicacion=provincia, anno_graduado=int(anno)).count() elif opcion == "residencia": total_ubicados = UbicacionLaboral.objects.filter( municipio_residencia__provincia=provincia, anno_graduado=int(anno)).count() presentados = UbicacionLaboral.objects.filter( presentado=True, municipio_residencia__provincia=provincia, anno_graduado=int(anno)).count() sheet.write(i + 1, 0, provincia.siglas, format) sheet.write(i + 1, 1, total_ubicados, format) sheet.write(i + 1, 2, presentados, format) formula_rango_total = '=SUM(%s)' % xl_range(1, 1, total, 1) formula_rango_presentados = '=SUM(%s)' % xl_range(1, 2, total, 2) sheet.write(total + 1, 0, "Total", bold) sheet.write(total + 1, 1, formula_rango_total, bold) sheet.write(total + 1, 2, formula_rango_presentados, bold) book.close() return response else: return Http404
def write_data_tables(writer, data_tables, formats): # todo Придумать как перенести время в комменты к данным for table_key in data_tables: sheet_name = RECORDS_COMPREHENSION[table_key] table = data_tables[table_key] # table.reset_index(inplace=True) table.to_excel(writer, sheet_name=sheet_name) last_row = len(table.index) + 2 last_col = len(table.columns) sheet = writer.sheets[sheet_name] # for row in range(3, last_row, 2): # sheet.set_column(':'.join([xl_range(row, 0 , row, last_col)]), cell_format=blue) sheet.set_column('A:A', 28) # На индексные ячейки формат не применяется =( full_datas = [] for col in range(1, last_col, 2): dates = ':'.join([xl_range(3, col, last_row, col)]) datas = ':'.join([xl_range(3, col + 1, last_row, col + 1)]) sheet.set_column(dates, 18, None, {'align': 'left'}) full_datas.append(datas) # if datas: # sheet.conditional_format(datas, {'type': '3_color_scale'}) # sheet.conditional_format(datas, {'type': 'cell', # 'criteria': '>', # 'value': '5000', # 'format': formats['red']}) # sheet.conditional_format(datas, {'type': 'cell', # 'criteria': '==', # 'value': '0', # 'format': formats['grey']}) sheet.conditional_format( full_datas[0], { 'type': 'cell', 'criteria': '>', 'value': '5000', 'format': formats['red'], 'multi_range': ' '.join(full_datas) }) sheet.conditional_format( full_datas[0], { 'type': 'cell', 'criteria': '==', 'value': '0', 'format': formats['grey'], 'multi_range': ' '.join(full_datas) })
def apply_format_table(self): # Apply format to dataframe cells for index, row in self.df.iterrows(): rowIndex = self.df.index.get_loc(index) iRow, worksheetRow = self.convert_to_row_index(index) for iCol in range(self.nCols): iCol, worksheetCol = self.convert_to_col_index(iCol) x = row.iloc[iCol] cell = xl_rowcol_to_cell(worksheetRow, worksheetCol) formatDic = self.formatTable[rowIndex][iCol] cellFormat = self.workbook.add_format(formatDic) self.worksheet.write(cell, x, cellFormat) # Apply format to header cells for i in range(self.nColLevels): formatDic = self.formatTableHeader[i] cellFormat = self.workbook.add_format(formatDic) self.worksheet.set_row(i, self.headerRowsHeight[i], cellFormat) # Apply format to index cells for j in range(self.nIndexLevels): formatDic = self.formatTableIndex[i] cellFormat = self.workbook.add_format(formatDic) self.worksheet.set_column(xl_range(1, j, 1, j), self.indexColWidth[j], cellFormat)
def write_line(row, head, acc_list): worksheet.write(row, 0, head) if len(acc_list) > 0: max_cell = xl_rowcol_to_cell(row, 1) max_range = xl_range(row, 2, row, N+1) worksheet.write_formula(max_cell, '=MAX(%s)' % (max_range,)) for i, acc in enumerate(acc_list): worksheet.write(row, i+2, acc)
def exportar_total_procesos(request): if request.method == "POST": anno = int(request.POST['anno']) response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ) response[ 'Content-Disposition'] = "attachment; filename=Totales_procesos_%s.xlsx" % anno book = Workbook(response, {'in_memory': True}) bold = book.add_format({'bold': True, 'border': 1}) format = book.add_format({'border': 1}) sheet = book.add_worksheet("Procesos") sheet.set_column('A:A', 20) sheet.set_column('B:B', 15) sheet.set_column('C:C', 15) sheet.set_column('D:D', 15) sheet.write(0, 0, "Meses", bold) sheet.write(0, 1, "Inhabilitaciones", bold) sheet.write(0, 2, "Suspensiones", bold) sheet.write(0, 3, "Total", bold) for i in range(1, 13): where = '%(year)s = EXTRACT(YEAR FROM fecha) AND %(month)s = EXTRACT(MONTH FROM fecha)' % { 'year': anno, 'month': i } procesos = ProcesoInhabilitacion.objects.filter().extra( where=[where]) total_inhabilitaciones = procesos.filter(proceso='i').count() total_suspensiones = procesos.filter(proceso='s').count() total = total_inhabilitaciones + total_suspensiones sheet.write(i, 0, MESES[i], format) sheet.write(i, 1, total_inhabilitaciones, format) sheet.write(i, 2, total_suspensiones, format) sheet.write(i, 3, total, bold) formula_1 = '=SUM(%s)' % xl_range(1, 1, 12, 1) formula_2 = '=SUM(%s)' % xl_range(1, 2, 12, 2) formula_3 = '=SUM(%s)' % xl_range(1, 3, 12, 3) sheet.write(13, 0, "Total", bold) sheet.write(13, 1, formula_1, bold) sheet.write(13, 2, formula_2, bold) sheet.write(13, 3, formula_3, bold) book.close() return response else: return Http404
def writetotal(ws,fmt,r,row_1st,row_last): ws.write(r,0,"Total",fmt) ws.write(r,1,"", fmt) ws.write(r,2,"", fmt) ws.write(r,3,"", fmt) formula = "=SUBTOTAL(109,{:s})".format(xl_range(row_1st,4,row_last,4)) ws.write_formula(xl_rowcol_to_cell(r,4), formula, fmt) return
def calcMedian(self): avgFrmt = self.workBook.add_format({'bold': True, 'font_color': 'red'}) self.activeSheet.write(self.numRows + 4, 0, "Медиана", avgFrmt) for itr in range(self.numCols - 1): tstRange = xl_range(1, itr + 1, self.numRows - 1, itr + 1) tstCell = xl_rowcol_to_cell(self.numRows + 4, itr + 1) self.activeSheet.write_formula( tstCell, '=ROUND(MEDIAN(' + tstRange + '),2)', avgFrmt)
def make_range(col_run, row_run): if isinstance(col_run, int) and isinstance(row_run, int): return xl_rowcol_to_cell(row_run, col_run) else: if isinstance(col_run, int): left = right = col_run else: left, right = col_run if isinstance(row_run, int): top = bottom = row_run else: top, bottom = row_run return xl_range(top, left, bottom, right)
def get_value(self): return "=SUM({})".format(",".join([ xl_range( i[0] + self.first_data_row_num, i[1], i[2] + self.first_data_row_num, i[3], ) for i in self.target_ranges ]))
def _write_data_column(self, entity_name: str, entity_schema: dict): self.worksheet.write(self.row, self.col, entity_name.capitalize(), self.HEADER_THEME) self._write_comment(self.row, self.col, entity_schema) # Write validation to data cells below header cell data_range = xl_range(self.row + 1, self.col, self.row + self.DATA_ROWS, self.col) self._write_validation(data_range, entity_name, entity_schema)
def format_col(self, col, colWidth=None, colFormat=None): iCol, worksheetCol = self.convert_to_col_index(col) if colWidth is not None: self.worksheet.set_column(xl_range(1, worksheetCol, 1, worksheetCol), colWidth) if colFormat is not None: for rowIndex in range(self.nRows): self.formatTable[rowIndex][iCol].update(colFormat)
def create_summary_table(col): # 'Points' worksheet.write(1, col, 'Punkty') # Column with student's score in points temp_row = 2 # This also calculates the max point num for i in range(student_num + 1): temp_range_row = xl_range(temp_row, 1, temp_row, col - 1) worksheet.write(temp_row, col, '=SUM(' + temp_range_row + ')') temp_row += 1 temp_row = student_num + 2 temp_range_col = xl_range(2, col, student_num + 1, col) worksheet.write(temp_row + 2, col, '=AVERAGE(' + temp_range_col + ')') # Column with student's score in % worksheet.write(1, col + 1, '%') temp_row = 2 max_p = xl_rowcol_to_cell(2 + student_num, col) for i in range(student_num): temp_range_row = xl_range(temp_row, 1, temp_row, col - 1) worksheet.write(temp_row, col + 1, '=SUM(' + temp_range_row + ')/' + max_p, merge_per_format) temp_row += 1 temp_row = student_num + 2 temp_range_col = xl_range(2, col + 1, student_num + 1, col + 1) worksheet.write(temp_row + 2, col + 1, '=AVERAGE(' + temp_range_col + ')', merge_per_format) temp_col = col + 2 # For every element create a table that shows a student's score in that area (element) temp_start_col = 1 for t, col_n in column_data: worksheet.write(1, temp_col, t) temp_row = 2 temp_end_col = temp_start_col + col_n - 1 max_p_range = xl_range(2 + student_num, temp_start_col, 2 + student_num, temp_end_col) # Get the average of every student for i in range(student_num): temp_range_row = xl_range(temp_row, temp_start_col, temp_row, temp_end_col) worksheet.write( temp_row, temp_col, '=SUM(' + temp_range_row + ')/' + 'SUM(' + max_p_range + ')', merge_per_format) temp_row += 1 temp_row = student_num + 2 temp_range_col = xl_range(2, temp_col, student_num + 1, temp_col) worksheet.write(temp_row + 5, temp_col, '=AVERAGE(' + temp_range_col + ')', merge_per_format) temp_col += 1 temp_start_col += col_n return col
def exportar_causales_expedientes(request): causales=Causal_movimiento.objects.filter(activo=True,tipo='ml') total_causales=len(causales) if request.method == "POST": anno=int(request.POST["anno"]) response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = "attachment; filename=resumen_exp_aprobados_causales_%s.xlsx"%anno book = Workbook(response, {'in_memory': True}) bold = book.add_format({'bold': True, 'border': 1}) format = book.add_format({'border': 1}) sheet = book.add_worksheet("Resumen") sheet.set_column('A:A', 60) sheet.write(0, 0, "Causales",bold) sheet.write(0, 1, "Externos",bold) sheet.write(0, 2, "Internos",bold) perfil=request.user.perfil_usuario if perfil.categoria.nombre == "organismo": for i,causal in enumerate(causales): cant=Expediente_aprobado.objects.filter(expediente__organismo_liberacion=perfil.organismo,expediente__causal_movimiento=causal,fecha_aprobado__year=anno).count() cant+=Expediente_aprobado.objects.filter(expediente__organismo_aceptacion=perfil.organismo,expediente__causal_movimiento=causal,fecha_aprobado__year=anno).count() cant_internos=Expediente_movimiento_interno.objects.filter(organismo=perfil.organismo,causal_movimiento=causal,fecha_registro__year=anno).count() sheet.write(i+1, 0, causal.nombre,format) sheet.write(i+1, 1, cant,format) sheet.write(i+1, 2, cant_internos,format) else: for i,causal in enumerate(causales): cant=Expediente_aprobado.objects.filter(expediente__causal_movimiento=causal,fecha_aprobado__year=anno).count() cant_internos=Expediente_movimiento_interno.objects.filter(causal_movimiento=causal,fecha_registro__year=anno).count() sheet.write(i+1, 0, causal.nombre,format) sheet.write(i+1, 1, cant,format) sheet.write(i+1, 2, cant_internos,format) formula_rango_total_externos = '=SUM(%s)' % xl_range(1, 1,total_causales, 1) formula_rango_total_internos = '=SUM(%s)' % xl_range(1, 2,total_causales, 2) sheet.write(total_causales+1, 0, "Total",bold) sheet.write(total_causales+1, 1, formula_rango_total_externos,bold) sheet.write(total_causales+1, 2, formula_rango_total_internos,bold) book.close() return response else: return Http404
def write_table_data(self, entry): for i, field in enumerate(entry.data_list): if i == 0: self.worksheet.merge_range( xl_range(self.line, min(5, self.group_level), self.line, 6), field, self.format_table_cell) else: self.worksheet.write(self.line, i + 6, field, self.format_table_cell) self.line += 1
def writesubtotal(ws, groupname, fmt, r, row_1st, row_last): ws.write(r, 0, "Subtotal - " + str(groupname), fmt) ws.write(r, 1, "", fmt) ws.write(r, 2, "", fmt) ws.write(r, 3, "", fmt) ws.write(r, 4, "", fmt) ws.write(r, 5, "", fmt) formula = "=SUBTOTAL(109,{:s})".format(xl_range(row_1st, 6, row_last, 6)) ws.write_formula(xl_rowcol_to_cell(r, 6), formula, fmt) return
def create_table(title, ex_num, col): # Check if I'm not merging a single cell if col != col + ex_num - 1: worksheet.merge_range(xl_range(0, col, 0, col + ex_num - 1), title, merge_format) else: worksheet.write(0, col, title) temp_col = col temp_row = student_num + 2 # Go through every column (exercise) in that element column for i in range(ex_num): # 'Exercise number' worksheet.write(1, temp_col, 'zad n') temp_range = xl_range(2, temp_col, student_num + 1, temp_col) temp_max_p = xl_range(temp_row, temp_col, student_num + 2, temp_col) worksheet.write(temp_row + 1, temp_col, '=MODE(' + temp_range + ')') worksheet.write(temp_row + 2, temp_col, '=AVERAGE(' + temp_range + ')') worksheet.write(temp_row + 3, temp_col, '=MEDIAN(' + temp_range + ')') worksheet.write( temp_row + 4, temp_col, '=SUM(' + temp_range + ')/' + str(student_num) + '/' + temp_max_p, merge_per_format) temp_col += 1 # Check if I'm not merging a single cell if col != col + ex_num - 1: el_avg_range = xl_range(temp_row + 5, col, temp_row + 5, col + ex_num - 1) el_avg_data_range = xl_range(temp_row + 4, col, temp_row + 4, col + ex_num - 1) worksheet.merge_range(el_avg_range, '=AVERAGE(' + el_avg_data_range + ')', merge_per_format) else: worksheet.write( temp_row + 5, col, '=AVERAGE(' + xl_rowcol_to_cell(temp_row + 4, col) + ')', merge_per_format) col += ex_num return col
def _xls_row(self, row, fromtype, absolute=True): rowi = self._convert_rowindex(row, fromtype, self.indextypes.xlscell) head = xlsxutils.xl_rowcol_to_cell(rowi, 0, True, True) if absolute: ran = xlsxutils.xl_range_abs(rowi, self._xls_coloff, rowi, self._xls_ncol + self._xls_coloff - 1) else: ran = xlsxutils.xl_range(rowi, self._xls_coloff, rowi, self._xls_ncol + self._xls_coloff - 1) return head, ran
def calcAVG(self): avgFrmt = self.workBook.add_format({ 'bold': True, 'font_color': 'blue' }) self.activeSheet.write(self.numRows + 2, 0, "Среднее", avgFrmt) for itr in range(self.numCols - 1): tstRange = xl_range(1, itr + 1, self.numRows - 1, itr + 1) tstCell = xl_rowcol_to_cell(self.numRows + 2, itr + 1) self.activeSheet.write_formula( tstCell, '=ROUND(AVERAGEA(' + tstRange + '),2)', avgFrmt)
def write_xlsx(serializer, headers, title_text): output = StringIO.StringIO() workbook = Workbook(output) worksheet_s = workbook.add_worksheet('Summary') # Here we will adding the code to add data title = workbook.add_format({ 'bold': True, 'font_size': 14, 'align': 'center', 'valign': 'vcenter' }) header = workbook.add_format({ 'bg_color': '#92d050', 'color': 'black', 'align': 'center', 'valign': 'top', 'border': 1 }) cell = workbook.add_format({ 'align': 'left', 'valign': 'top', 'text_wrap': False, 'border': 1 }) hearder_len = len(headers) header_range = xl_range(0, 0, 0, hearder_len - 1) worksheet_s.merge_range(header_range, title_text, title) # column header for col, field in enumerate(headers): worksheet_s.write(1, col, field[1], header) worksheet_s.set_column(col, col, width=field[2]) # column content for row, line in enumerate(serializer.data, start=2): # Write a line for each record for col, field in enumerate(headers): keys = field[0].split('.') value = line for key in keys[:-1]: value = value[key] if "|" in keys[-1]: key, flt = keys[-1].split("|") value = field_display(value[key], flt) else: value = value[keys[-1]] worksheet_s.write(row, col, value, cell) workbook.close() xlsx_data = output.getvalue() # xlsx_data contains the Excel file return xlsx_data
def _xls_column(self, col, fromtype, absolute=True): coli = self._convert_colindex(col, fromtype, self.indextypes.xlscell) head = xlsxutils.xl_rowcol_to_cell(0, coli, True, True) if absolute: ran = xlsxutils.xl_range_abs(self._xls_rowoff, coli, self._xls_nrow + self._xls_rowoff - 1, coli) else: ran = xlsxutils.xl_range(self._xls_rowoff, coli, self._xls_nrow + self._xls_rowoff - 1, coli) return head, ran
def write_sum_formula(self, worksheet, formula_cells_range, formula_cell, cells_format=None): first_cell_row, first_cell_column, second_cell_row, second_cell_column = \ formula_cells_range.get_range_cells_coordinates() formula_cell_row, formula_cell_column = formula_cell.get_cell_coordinates( ) formula_range = xl_range(first_cell_row, first_cell_column, second_cell_row, second_cell_column) formula = self._sum_formula_template.format(formula_range) worksheet.write_formula(formula_cell_row, formula_cell_column, formula, cells_format)
def insert_text_rows(self, objeto): """insert_text_rows: Inserta filas con texto.""" self.info("Objetos text_rows") row, col = xl_cell_to_rowcol(objeto.get("at")) textos = objeto.get("text", None) format = self.formatos.get(objeto.get("format")) for i, t in enumerate([self.get_string_from_template(t) for t in textos], 0): at = xl_range(row, col + i, row, col + i) if t: self.active_worksheet.write(at, t, format) else: self.active_worksheet.write_blank(at, '', format) self._setup_boundaries_rc(row=row, col=col + i)
def exportExcelFormatVertical(mongoClient, output, listDate ,result): workbook = xlsxwriter.Workbook(output, {'in_memory': True}) worksheet = workbook.add_worksheet() bold = workbook.add_format({'bold': True}) yellow_format = workbook.add_format({'bg_color': '#ffdb47'}) worksheet.set_column(0, 20, 15) worksheet.write('A1', 'No', yellow_format) worksheet.write('B1', 'Nama', yellow_format) worksheet.write('C1', 'Tanggal', yellow_format) worksheet.write('D1', 'Scan Masuk', yellow_format) worksheet.write('E1', 'Scan Keluar', yellow_format) worksheet.write('F1', 'Jumlah Jam Kerja', yellow_format) worksheet.write('G1', 'Keterangan', yellow_format) worksheet.write('H1', 'Total Jumlah Jam Kerja', yellow_format) worksheet.write('I1', 'TTD', yellow_format) lengthDate = len(listDate) indexPegawai = 0 indexContents = 0 indexLengthDate = 1 for i in result: indexPegawai += 1 for d in listDate: indexContents += 1 presence = getTimePresence(mongoClient, i[0], d) hour = getHours(mongoClient, i[0], d) information = getInformation(mongoClient, i[0], d) worksheet.write(indexContents, 0, indexPegawai) worksheet.write(indexContents, 1, i[1]) worksheet.write(indexContents, 2, d) worksheet.write(indexContents, 3, presence[0]) worksheet.write(indexContents, 4, presence[1]) worksheet.write(indexContents, 5, hour) worksheet.write(indexContents, 6, information) if indexPegawai > 1: indexLengthDate += lengthDate cell_range = xl_range(indexLengthDate, 5, indexPegawai*lengthDate, 5) formula = '=SUM(%s)' % cell_range worksheet.merge_range(indexLengthDate, 7, indexPegawai*lengthDate, 7, formula) worksheet.merge_range(indexLengthDate, 8, indexPegawai*lengthDate, 8, '') workbook.close() return output
def paste_excel_range(self, xl_sheet, ppt_slide, col1, row1, col2, row2, width=None, height=None, left=0, top=TOP_INSERT): """ Pastes the cells with the given range (as a picture) from the given Excel sheet to the given Powerpoint slide at the given coordinates. @param xl_sheet: The Excel sheet object on which the Copy() is being performed. @param ppt_slide: The Powerpoint slide object on which the Paste() is being performed. @param col1: The first column of Copy within the Excel sheet (0-indexed). @param row1: The first row of Copy within the Excel sheet (0-indexed). @param col2: The last column of Copy within the Excel sheet (0-indexed). @param row2: The last row of Copy within the Excel sheet (0-indexed). @param width: The width of the picture within the Powerpoint. @param height: The height of the picture within the Powerpoint. @param left: The left coordinate from which the picture is pasted. @param top: The top coordinate from which the picture is pasted. @return: Object reference to the table that was pasted into the slide. """ # Copies the given range within Excel sheet cells = xl_sheet.Range(xl_range(row1, col1, row2, col2)) cells.CopyPicture() # Pastes Copied content from Excel sheet into Powerpoint slide shaperange = ppt_slide.Shapes.Paste() # Modifies pasted item's location and size shape = shaperange.Item(1) if (width): shape.Width = width if (height): shape.Height = height shape.Left = left shape.Top = top return shape
def write_man_days_counting_formula(self, worksheet, formula_cells_range, formula_cell, man_days_hours, cells_format=None): formula_first_cell_row, formula_first_cell_column, formula_second_cell_row, formula_second_cell_column = \ formula_cells_range.get_range_cells_coordinates() converted_formula_cells_range = xl_range(formula_first_cell_row, formula_first_cell_column, formula_second_cell_row, formula_second_cell_column) formula_cell_row, formula_cell_column = formula_cell.get_cell_coordinates( ) formula = self._man_days_counting_formula_template.format( converted_formula_cells_range, man_days_hours) worksheet.write_formula(formula_cell_row, formula_cell_column, formula, cells_format)
def gererate_footer(self): # content for footer content = u"Tổng số ngày nghỉ trong tháng" # add on sheet self.row = self.row + 1 self.sheet.merge_range( self.row, 0, self.row, self.col_range + 1, content, self.header_format ) for idx in range(2, 5): self.sheet.write_formula( self.row, self.col_range + idx, "= SUM({0})".format(utility.xl_range( 2, self.col_range + idx, self.row - 1, self.col_range + idx )), self.content_number_format_w_bg )
def print_head_env(wb, sheet, env, **kargs):#显示测试的环境变量 """ print test environment information paramters: env: test driver and cmodel information start_row: the first row index to write start_col: the first column index to write """ row = kargs.get('start_row', 1) col = kargs.get('start_col', 0) rg = xl_range(row, col, row, col+1) sheet.merge_range(rg, 'Test Environment', fmts.get('head_tag')) row += 1 for key in env.hierachy if key != gcdefine else: sheet.write(row, col, key, fmts.get('head_item')) sheet.write(row, col+1, env.env.get(key, ''), fmts.get('com')) row += 1
def write2xlsx_v0(self, fliename, rel_angle_err, rel_trans_err, exp_angle, the_angle, exp_trans, the_trans): # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook(fliename) worksheet = workbook.add_worksheet() # format title_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1}) ev_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#F0F0F0', 'num_format': '0.000'}) tv_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEEED1', 'num_format': '0.000'}) re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'num_format': 10, 'bold' : 1}) highlight_re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'font_color': '#FF0000', 'num_format': 10, 'bold' : 1}) # write first row worksheet.write('A1','No',title_format) worksheet.write('B1','Type',title_format) worksheet.merge_range('C1:E1','Euler Angle (degree)',title_format) worksheet.merge_range('F1:H1','Translation (m)',title_format) for i in range(0,rel_angle_err.shape[0]): first_row = i*3+1 last_row = i*3+3 # write the number r = xl_range(first_row,0,last_row,0) worksheet.merge_range(r,i+1,title_format) # write Experimental value cur_row = first_row worksheet.write(cur_row, 1,'Experimental value',ev_format) worksheet.write_row(cur_row, 2, exp_angle[i], ev_format) worksheet.write_row(cur_row, 5, exp_trans[i], ev_format) # write Theoretical value cur_row = cur_row + 1 worksheet.write(cur_row, 1,'Theoretical value',tv_format) worksheet.write_row(cur_row, 2, the_angle[i], tv_format) worksheet.write_row(cur_row, 5, the_trans[i], tv_format) # write Relative Error cur_row = cur_row + 1 threshold = 0.3 worksheet.write(cur_row, 1,'Relative Error',re_format) angle_err = rel_angle_err[i] r = xl_range(cur_row,5,cur_row,7) if (angle_err > threshold).sum() or (rel_trans_err[i] > threshold).sum(): worksheet.write_row(cur_row, 2, angle_err, highlight_re_format) worksheet.merge_range(r,rel_trans_err[i],highlight_re_format) else: worksheet.write_row(cur_row, 2, angle_err, re_format) worksheet.merge_range(r,rel_trans_err[i],re_format) # set col weight worksheet.set_column(0,0,2.5) worksheet.set_column(1,1,18) worksheet.set_column(2,7,6.8) # compute avg and write to xlsx threshold = 0.30 total_view_no = rel_trans_err.shape[0] ind = (rel_angle_err > threshold).sum(axis=1) + (rel_trans_err > threshold).reshape(total_view_no) ind = (ind < 1).nonzero() in_thres_view_no = ind[0].shape[0] in_thres_angle_err = rel_angle_err[ind] in_thres_trans_err = rel_trans_err[ind] avg_angle_err = in_thres_angle_err.sum(axis = 0)/in_thres_view_no avg_trans_err = in_thres_trans_err.sum()/in_thres_view_no worksheet.write_row(0, 8, avg_angle_err, highlight_re_format) worksheet.write(0, 11, avg_angle_err.sum()/3.0, highlight_re_format) worksheet.write(1, 8, avg_trans_err, highlight_re_format) workbook.close()
def calculateBenefitCosts(self, BC_detail_workbook, all_projects_dir): """ Compares the run results with those from the base results (if they exist), calculating the daily difference, annual difference, and annual benefits. Writes a readable version into `BC_detail_workbook`, and flat csv series into a csv in `all_projects_dir` named [Project ID].csv. """ self.base_results = None try: self.base_dir = self.config.loc['base_dir'] except: # this is ok -- no base_dir specified self.base_dir = None if self.base_dir: # these are not ok - let exceptions raise print("") print("BASE:") self.base_dir = os.path.realpath(self.base_dir) # pass the parking config for overwrite base_overwrite_config = {} total_parking_cost_allocation = 0.0 for county in RunResults.PARKING_COST_PER_TRIP_WORK.keys(): key = 'percent parking cost incurred in %s' % county # convert to floats self.config.loc[key] = float(self.config.loc[key]) base_overwrite_config[key] = self.config.loc[key] total_parking_cost_allocation += self.config.loc[key] assert(total_parking_cost_allocation == 1.0) # pass the project mode for overwrite to base base_overwrite_config['Project Mode'] = self.config.loc['Project Mode'] # and the ovtt adjustment base_overwrite_config['ovtt_adjustment'] = self.ovtt_adjustment print self.base_dir print base_overwrite_config self.base_results = RunResults(rundir = self.base_dir, overwrite_config=base_overwrite_config) self.base_results.calculateDailyMetrics() print "base results----" print self.base_results.config print self.base_results.daily_category_results # these will be the daily and annual diffs, and monetized diffs # key = (category1, category2, variable name) bc_metrics = collections.OrderedDict() # put config in here first for key,val in self.config.iteritems(): bc_metrics[(key,"","")] = val workbook = xlsxwriter.Workbook(BC_detail_workbook) worksheet = workbook.add_worksheet('project') worksheet.protect() # Notice row format_red = workbook.add_format({'font_color':'red', 'bold':True}) worksheet.write(0,0, "This workbook is written by the script %s. " % os.path.realpath(__file__) + "DO NOT CHANGE THE WORKBOOK, CHANGE THE SCRIPT.", format_red) # Info rows format_label = workbook.add_format({'align':'right','indent':1}) format_highlight= workbook.add_format({'bg_color':'yellow'}) format_highlight_money = workbook.add_format({'bg_color':'yellow', 'num_format':'_($* #,##0.0_);_($* (#,##0.0);_($* "-"_);_(@_)'}) worksheet.write(1,0, "Project Run Dir", format_label) worksheet.write(1,1, os.path.realpath(self.rundir), format_highlight) for col in range(2,7): worksheet.write(1,col,"",format_highlight) # Config-based rows row = 2 for key in RunResults.REQUIRED_KEYS: worksheet.write(row,0, key, format_label) unit = None if key in RunResults.UNITS: unit = RunResults.UNITS[key] config_key = '%s (%s)' % (key,unit) if unit else key try: val = float(self.config.loc[config_key]) except: val = self.config.loc[config_key] worksheet.write(row,1, val, format_highlight_money if string.find(key,'Costs') >= 0 else format_highlight) for col in range(2,7): worksheet.write(row,col,"",format_highlight) if unit: worksheet.write(row,2, '(%s)' % unit,format_highlight) row += 1 # Run directory if self.base_dir: worksheet.write(row,0, "Base Run Dir", format_label) worksheet.write(row,1, self.base_dir, format_highlight) for col in range(2,7): worksheet.write(row,col,"",format_highlight) row += 1 # Calculated from config format_highlight= workbook.add_format({'bg_color':'#FFFFC0'}) format_highlight_money = workbook.add_format({'bg_color':'#FFFFC0', 'num_format':'_($* #,##0.0_);_($* (#,##0.0);_($* "-"_);_(@_)'}) # Annual Capital Costs worksheet.write(row,0, 'Annual Capital Costs', format_label) worksheet.write(row,1, '=%s/%s' % (xl_rowcol_to_cell(RunResults.REQUIRED_KEYS.index('Capital Costs')+2,1), xl_rowcol_to_cell(RunResults.REQUIRED_KEYS.index('Life of Project')+2,1)), format_highlight_money) worksheet.write(row,2, '(%s)' % RunResults.UNITS['Annual Capital Costs'], format_highlight) for col in range(3,7): worksheet.write(row,col,"",format_highlight) bc_metrics[('Annual Capital Costs (%s)' % RunResults.UNITS['Annual Capital Costs'],"","")] = \ float(self.config.loc['Capital Costs (%s)' % RunResults.UNITS['Capital Costs']]) / \ float(self.config.loc['Life of Project (%s)' % RunResults.UNITS['Life of Project']]) row += 1 # Annual O&M Costs not recovered worksheet.write(row,0, 'Annual O&M Costs not recovered', format_label) worksheet.write(row,1, '=%s*(1-%s)' % (xl_rowcol_to_cell(RunResults.REQUIRED_KEYS.index('Annual O&M Costs')+2,1), xl_rowcol_to_cell(RunResults.REQUIRED_KEYS.index('Farebox Recovery Ratio')+2,1)), format_highlight_money) worksheet.write(row,2, '(%s)' % RunResults.UNITS['Annual O&M Costs not recovered'], format_highlight) for col in range(3,7): worksheet.write(row,col,"",format_highlight) bc_metrics[('Annual O&M Costs not recovered (%s)' % RunResults.UNITS['Annual O&M Costs not recovered'],"","")] = \ float(self.config.loc['Annual O&M Costs (%s)' % RunResults.UNITS['Annual O&M Costs']])* \ (1.0-float(self.config.loc['Farebox Recovery Ratio'])) row += 1 # Net Annual Costs worksheet.write(row,0, 'Net Annual Costs', format_label) worksheet.write(row,1, '=%s+%s' % (xl_rowcol_to_cell(row-2,1), xl_rowcol_to_cell(row-1,1)), format_highlight_money) worksheet.write(row,2, '(%s)' % RunResults.UNITS['Net Annual Costs'], format_highlight) for col in range(3,7): worksheet.write(row,col,"",format_highlight) bc_metrics[('Net Annual Costs (%s)' % RunResults.UNITS['Net Annual Costs'],"","")] = \ bc_metrics[('Annual Capital Costs (%s)' % RunResults.UNITS['Annual Capital Costs'],"","")] + \ bc_metrics[('Annual O&M Costs not recovered (%s)' % RunResults.UNITS['Annual O&M Costs not recovered'],"","")] row += 1 # Header row row += 1 # space format_header = workbook.add_format({'bg_color':'#1F497D', 'font_color':'white', 'bold':True, 'text_wrap':True, 'align':'center'}) worksheet.write(row,0,"Benefit/Cost",format_header) worksheet.write(row,1,"Daily\nWith Project" if self.base_dir else "Daily",format_header) if self.base_dir: worksheet.write(row,2,"Daily\nNo Build",format_header) worksheet.write(row,3,"Daily\nDifference",format_header) worksheet.write(row,4,"Annual\nDifference",format_header) worksheet.write(row,6,"Benefit Valuation\n(per unit)",format_header) worksheet.write(row,8,"Annual\nBenefit ($2013)",format_header) # Data rows row += 1 cat1 = None cat2 = None format_cat1 = workbook.add_format({'bold':True, 'bg_color':'#C5D9F1'}) format_cat2 = workbook.add_format({'bold':True, 'indent':1, 'bg_color':'#D9D9D9'}) format_cat2_big = workbook.add_format({'bg_color':'#92D050', 'num_format':'#,##0'}) format_cat2_lil = workbook.add_format({'bg_color':'#92D050', 'num_format':'#,##0.000'}) format_var = workbook.add_format({'indent':2}) format_val_big = workbook.add_format({'num_format':'#,##0'}) format_val_lil = workbook.add_format({'num_format':'#,##0.000'}) format_cat2b_big = workbook.add_format({'bg_color':'#FCD5B4', 'num_format':'#,##0'}) format_cat2b_lil = workbook.add_format({'bg_color':'#FCD5B4', 'num_format':'#,##0.000'}) format_cat2d_big = workbook.add_format({'bg_color':'#D9D9D9', 'num_format':'#,##0'}) format_cat2d_lil = workbook.add_format({'bg_color':'#D9D9D9', 'num_format':'#,##0.000'}) format_benval_lil = workbook.add_format({'num_format':'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)', 'font_color':'#808080'}) format_benval_big = workbook.add_format({'num_format':'_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)', 'font_color':'#808080'}) format_ann_ben = workbook.add_format({'num_format':'_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)'}) format_cat2_ben = workbook.add_format({'num_format':'_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)', 'bg_color':'#D9D9D9'}) for key,value in self.daily_results.iteritems(): # What's the valuation of this metric? valuation = None if (key[0],key[1],key[2]) in RunResults.BENEFIT_VALUATION: valuation = RunResults.BENEFIT_VALUATION[(key[0],key[1],key[2])] elif (key[0],key[1]) in RunResults.BENEFIT_VALUATION: valuation = RunResults.BENEFIT_VALUATION[(key[0],key[1])] else: # these are purely informational print("Could not lookup benefit valuation for %s" % str(key)) # is this already annual? (e.g. no daily -> annual transformation) already_annual = False if (key[0],key[1],key[2]) in RunResults.ALREADY_ANNUAL: already_annual = RunResults.ALREADY_ANNUAL[(key[0],key[1],key[2])] elif (key[0],key[1]) in RunResults.ALREADY_ANNUAL: already_annual = RunResults.ALREADY_ANNUAL[(key[0],key[1])] # category one header if cat1 != key[0]: cat1 = key[0] worksheet.write(row,0,cat1,format_cat1) worksheet.write(row,1,"",format_cat1) if self.base_dir: worksheet.write(row,2,"",format_cat1) worksheet.write(row,3,"",format_cat1) worksheet.write(row,4,"",format_cat1) worksheet.write(row,5,"",format_cat1) worksheet.write(row,6,"",format_cat1) worksheet.write(row,7,"",format_cat1) worksheet.write(row,8,"",format_cat1) row += 1 # category two header if cat2 != key[1]: cat2 = key[1] worksheet.write(row,0,cat2,format_cat2) # Sum it worksheet.write(row,1, '=SUM(%s)' % xl_range(row+1,1,row+len(self.daily_results[cat1][cat2]),1), format_cat2_lil if (cat1,cat2) in self.lil_cats else format_cat2_big) if self.base_dir: worksheet.write(row,2, # base '=SUM(%s)' % xl_range(row+1,2,row+len(self.daily_results[cat1][cat2]),2), format_cat2b_lil if (cat1,cat2) in self.lil_cats else format_cat2b_big) if already_annual: worksheet.write(row,3, # diff daily "", format_cat2d_lil if (cat1,cat2) in self.lil_cats else format_cat2d_big) worksheet.write(row,4, # diff annual '=%s-%s' % (xl_rowcol_to_cell(row,1), xl_rowcol_to_cell(row,2)), format_cat2d_lil if (cat1,cat2) in self.lil_cats else format_cat2d_big) bc_metrics[(cat1,cat2,'Difference')] = self.daily_category_results[(cat1,cat2)] - \ self.base_results.daily_category_results[(cat1,cat2)] else: worksheet.write(row,3, # diff daily '=%s-%s' % (xl_rowcol_to_cell(row,1), xl_rowcol_to_cell(row,2)), format_cat2d_lil if (cat1,cat2) in self.lil_cats else format_cat2d_big) worksheet.write(row,4, # diff annual '=%d*%s' % (RunResults.ANNUALIZATION, xl_rowcol_to_cell(row,3)), format_cat2d_lil if (cat1,cat2) in self.lil_cats else format_cat2d_big) try: bc_metrics[(cat1,cat2,'Daily Difference')] = self.daily_category_results[(cat1,cat2)] - \ self.base_results.daily_category_results[(cat1,cat2)] bc_metrics[(cat1,cat2,'Annual Difference')] = RunResults.ANNUALIZATION * \ bc_metrics[(cat1,cat2,'Daily Difference')] except Exception as e: print cat1, cat2 print e # print self.daily_category_results[(cat1,cat2)] # print self.base_results.daily_category_results[(cat1,cat2)] sys.exit() # worksheet.write(row,6, "", format_cat2d_lil) if valuation != None: worksheet.write(row,8, '=SUM(%s)' % xl_range(row+1,8,row+len(self.daily_results[cat1][cat2]),8), format_cat2_ben) row += 1 # details worksheet.write(row,0,key[2],format_var) worksheet.write(row,1,value, format_val_lil if (cat1,cat2) in self.lil_cats else format_val_big) if self.base_dir: worksheet.write(row,2, # base self.base_results.daily_results[cat1][cat2][key[2]], format_val_lil if (cat1,cat2) in self.lil_cats else format_val_big) nominal_diff = 0 if already_annual: worksheet.write(row,4, # diff annual '=%s-%s' % (xl_rowcol_to_cell(row,1), xl_rowcol_to_cell(row,2)), format_val_lil if (cat1,cat2) in self.lil_cats else format_val_big) nominal_diff = self.daily_results[cat1][cat2][key[2]] - \ self.base_results.daily_results[cat1][cat2][key[2]] else: worksheet.write(row,3, # diff daily '=%s-%s' % (xl_rowcol_to_cell(row,1), xl_rowcol_to_cell(row,2)), format_val_lil if (cat1,cat2) in self.lil_cats else format_val_big) worksheet.write(row,4, # diff annual '=%d*%s' % (RunResults.ANNUALIZATION, xl_rowcol_to_cell(row,3)), format_val_lil if (cat1,cat2) in self.lil_cats else format_val_big) nominal_diff = RunResults.ANNUALIZATION * (self.daily_results[cat1][cat2][key[2]] - \ self.base_results.daily_results[cat1][cat2][key[2]]) if valuation != None: worksheet.write(row,6, # diff annual valuation, format_benval_lil if abs(valuation)<1000 else format_benval_big) worksheet.write(row,8, # annual benefit '=%s*%s' % (xl_rowcol_to_cell(row,4), xl_rowcol_to_cell(row,6)), format_ann_ben) if (cat1,cat2,'Annual Benefit ($2013)') not in bc_metrics: bc_metrics[(cat1,cat2,'Annual Benefit ($2013)')] = 0 bc_metrics[(cat1,cat2,'Annual Benefit ($2013)')] += valuation*nominal_diff row += 1 worksheet.set_column(0,0,40.0) worksheet.set_column(1,8,13.0) worksheet.set_column(5,5,2.0) worksheet.set_column(7,7,2.0) worksheet.set_column(8,8,15.0) worksheet.set_column(9,9,2.0) # THIS IS COBRA format_red = workbook.add_format({'font_color':'white','bg_color':'#C0504D','align':'right','bold':True}) for row in range(1,9): for col in range(10,13): worksheet.write(row,col,"",format_red) worksheet.write(1,10,"co",format_red) worksheet.write(2,10,"b" ,format_red) worksheet.write(3,10,"r" ,format_red) worksheet.write(4,10,"a" ,format_red) format_red = workbook.add_format({'font_color':'white','bg_color':'#C0504D'}) worksheet.write(1,11,"st",format_red) worksheet.write(2,11,"enefit" ,format_red) worksheet.write(3,11,"results" ,format_red) worksheet.write(4,11,"nalyzer" ,format_red) worksheet.set_column(11,11,8.0) worksheet.set_column(12,12,15.0) worksheet.insert_image(2, 12, os.path.join(os.path.dirname(os.path.abspath(__file__)), "King-cobra.png"), {'x_scale':0.1, 'y_scale':0.1}) workbook.close() print("Wrote %s" % BC_detail_workbook) if self.base_dir: idx = pd.MultiIndex.from_tuples(bc_metrics.keys(), names=['category1','category2','variable_name']) self.bc_metrics = pd.Series(bc_metrics, index=idx) self.bc_metrics.name = 'values' all_proj_filename = os.path.join(all_projects_dir, "%s.csv" % self.config.loc['Project ID']) self.bc_metrics.to_csv(all_proj_filename, header=True, float_format='%.5f') print("Wrote %s" % all_proj_filename)
def write2xlsx_v2(self, fliename, exp_acc_angle, the_acc_angle, exp_acc_trans, the_acc_trans): the_init_position = the_acc_trans[0] exp_init_position = exp_acc_trans[0] init_err = np.sqrt(((the_init_position-exp_init_position)**2).sum())/np.sqrt((the_init_position*2).sum()) the_other_position = the_acc_trans[1:] exp_other_position = exp_acc_trans[1:] err_other_position = (the_other_position- exp_other_position) ** 2 err_other_position = np.sqrt(err_other_position.sum(axis=1))/np.sqrt((the_other_position**2).sum(axis=1)) inf_ind = (err_other_position > 1).nonzero()[0] err_other_position[inf_ind] = 1 norm_ind = (~(err_other_position > 0.3)).nonzero()[0] in_sample = err_other_position[norm_ind] avg_trans_err = in_sample.sum()/ in_sample.shape[0] # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook(fliename) worksheet = workbook.add_worksheet() # format title_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1}) ev_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#F0F0F0', 'num_format': '0.000'}) tv_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEEED1', 'num_format': '0.000'}) re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'num_format': 10, 'bold' : 1}) highlight_re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'font_color': '#FF0000', 'num_format': 10, 'bold' : 1}) # write first row worksheet.write('A1','No',title_format) worksheet.write('B1','Type',title_format) worksheet.merge_range('C1:E1','Acc Euler Angle(degree)',title_format) worksheet.merge_range('F1:H1','Acc Translation(m)',title_format) r = xl_range(1,0,3,0) worksheet.merge_range(r,0,title_format) worksheet.write(1, 1,'Exp init value',ev_format) worksheet.write_row(1, 2, exp_acc_angle[0], ev_format) worksheet.write_row(1, 5, exp_acc_trans[0], ev_format) worksheet.write(2, 1,'The init value',tv_format) worksheet.write_row(2, 2, the_acc_angle[0], tv_format) worksheet.write_row(2, 5, the_acc_trans[0], tv_format) worksheet.write(3, 1,'Relative Error',re_format) worksheet.write_row(3, 2, [0,0,0], re_format) r = xl_range(3,5,3,7) worksheet.merge_range(r,init_err, re_format) for i in range(1,exp_acc_angle.shape[0]): first_row = i*3+1 last_row = i*3+3 # write the number r = xl_range(first_row,0,last_row,0) worksheet.merge_range(r,i,title_format) # write Experimental value cur_row = first_row worksheet.write(cur_row, 1,'Experimental value',ev_format) worksheet.write_row(cur_row, 2, exp_acc_angle[i], ev_format) worksheet.write_row(cur_row, 5, exp_acc_trans[i], ev_format) # write Theoretical value cur_row = cur_row + 1 worksheet.write(cur_row, 1,'Theoretical value',tv_format) worksheet.write_row(cur_row, 2, the_acc_angle[i], tv_format) worksheet.write_row(cur_row, 5, the_acc_trans[i], tv_format) # write Relative Error cur_row = cur_row + 1 worksheet.write(cur_row, 1,'Relative Error',re_format) r = xl_range(cur_row,2,cur_row,4) r1 = xl_range(first_row,2,first_row,4) r2 = xl_range(first_row+1,2,first_row+1,4) worksheet.write_array_formula(r,'ABS(('+r2+'-'+r1+')/'+r2+')',cell_format = re_format) r = xl_range(cur_row,5,cur_row,7) worksheet.merge_range(r,err_other_position[i-1],re_format) # set col weight worksheet.set_column(0,0,2.5) worksheet.set_column(1,1,18) worksheet.set_column(2,7,6.8) # compute avg err and write to xlsx _in = the_acc_angle == 0.0 _in = _in.sum(axis = 1).nonzero()[0] no = the_acc_angle.shape[0] no_set = set(range(0,no)) no_set = no_set - set(_in) no_in = list(no_set) sample_the_acc_angle = the_acc_angle[no_in] sample_exp_acc_angle = exp_acc_angle[no_in] angle_err = np.abs((sample_the_acc_angle - sample_exp_acc_angle)/sample_the_acc_angle) in_thres_view_no = len(no_in) avg_angle_err = angle_err.sum(axis = 0)/in_thres_view_no worksheet.write_row(0, 8, avg_angle_err, highlight_re_format) worksheet.write(0, 11, avg_angle_err.sum()/3.0, highlight_re_format) worksheet.write(1, 8, avg_trans_err, highlight_re_format) workbook.close()
def generate_xlsx(path, tests): """ Generates an excel spreadsheet containing the results of all tests Args: path (str): Path to save the spreadsheet tests (list of Tester): All completed tests """ debug('Generating excel spreadsheet...') # Fix filename if not path.endswith('.xlsx'): path += '.xlsx' wb = xl.Workbook(path) ws = wb.add_worksheet() # Some formats used in the sheet fmt_name = wb.add_format({'font_color': 'green', 'bg_color': 'black'}) fmt_perfect = wb.add_format({'bg_color': '#b6d7a8', 'border': 1, 'border_color': '#cccccc'}) fmt_bad = wb.add_format({'bg_color': '#ea9999', 'border': 1, 'border_color': '#cccccc'}) fmt_none = wb.add_format({'bg_color': '#ffe599', 'border': 1, 'border_color': '#cccccc'}) fmt_default = wb.add_format({'bg_color': 'white', 'border': 1, 'border_color': '#cccccc'}) # Write headers row = 0 ws.write_row(row, 0, ['CB_NAME', 'POVs Total', 'POVs Passed', 'POVs Failed', 'POLLs Total', 'POLLs Passed', 'POLLs Failed', 'Total Tests', 'Total Passed', 'Total Failed', '% Passed', 'Notes']) # Add all test data for test in tests: row += 1 # Pick the format for this row if test.total == 0: fmt = fmt_none elif test.total == test.passed: fmt = fmt_perfect elif test.passed == 0: fmt = fmt_bad else: fmt = fmt_default # Write some fields we already know ws.write(row, 0, test.name, fmt_name) ws.write_row(row, 1, [test.povs.total, test.povs.passed, '', test.polls.total, test.polls.passed], fmt) # NOTE: Leaving all of these to be calculated in excel in case you want to manually edit it later # POVs failed ws.write_formula(row, 3, '={}-{}'.format(xlutil.xl_rowcol_to_cell(row, 1), xlutil.xl_rowcol_to_cell(row, 2)), fmt) # POLLs failed ws.write_formula(row, 6, '={}-{}'.format(xlutil.xl_rowcol_to_cell(row, 4), xlutil.xl_rowcol_to_cell(row, 5)), fmt) # Total tests ws.write_formula(row, 7, '={}+{}'.format(xlutil.xl_rowcol_to_cell(row, 1), xlutil.xl_rowcol_to_cell(row, 4)), fmt) # Total passed ws.write_formula(row, 8, '={}+{}'.format(xlutil.xl_rowcol_to_cell(row, 2), xlutil.xl_rowcol_to_cell(row, 5)), fmt) # Total failed ws.write_formula(row, 9, '={}-{}'.format(xlutil.xl_rowcol_to_cell(row, 7), xlutil.xl_rowcol_to_cell(row, 8)), fmt) # % Passed ws.write_formula(row, 10, '=100*{}/MAX(1, {})'.format(xlutil.xl_rowcol_to_cell(row, 8), xlutil.xl_rowcol_to_cell(row, 7)), fmt) # Totals at bottom row += 1 ws.write(row, 0, 'TOTAL') for col in range(1, 10): ws.write_formula(row, col, '=SUM({})'.format(xlutil.xl_range(1, col, len(tests), col))) # Total % passed ws.write_formula(row, 10, '=100*{}/MAX(1, {})'.format(xlutil.xl_rowcol_to_cell(row, 8), xlutil.xl_rowcol_to_cell(row, 7))) # Averages at bottom row += 1 ws.write(row, 0, 'AVERAGE') for col in range(1, 11): ws.write_formula(row, col, '=AVERAGE({})'.format(xlutil.xl_range(1, col, len(tests), col))) wb.close() debug('Done, saved to {}\n'.format(path))
# Format 'Name' column worksheet.write('A1', 'Name') worksheet.set_column(0, 0, 20) i = 1 # lines counter maxLength = 1 while str: # empty line is 'falsy' tokens = str.split(' ') maxLength = max(maxLength, len(tokens)) # find max line length worksheet.write(i,0, tokens[0]) # writing a name tokens.remove(tokens[0]) # remove name from array worksheet.write_row(i, 1, map(int, tokens)) # write all marks as ints str = input() # read next line i += 1 # formatting 'marks'-block worksheet.merge_range(xl_range(0, 1, 0, maxLength-1), 'Marks', workbook.add_format({'align': 'center'})) worksheet.set_column(1, maxLength-1, 4) # 'Average' column worksheet.write(0, maxLength, 'Average') for n in range(1, i): # create formula for each line worksheet.write_formula(n, maxLength, '{=AVERAGE(' + xl_range(n, 1, n, maxLength-1)+')}') workbook.close() else: print('No data') # dima 5 4 7 8 9 6 5 4 5 7 # pasha 4 5 7 # vlad 5 5 5 5 4 78 6 5 # lena 2 2 2 5 6 8 7 7 9 9
def export(self, filename=None): """ Does three things:ä - Exports the data into a xlsx file - backups the count.txt - clear all entries from the counts.txt That are larger than minimumCoffees :param filename: The filename of the xlsx file. DEFAULT: Abrechnung_<timestamp>.xlsx :return: void :raise Exception: Multiple exceptions for file-io and consistensy errors """ # ################################ # Create the excel sheet ################################# # Create a workbook and add a worksheet. if filename is None: filename = 'Abrechnung_'+time.strftime("%Y-%m-%d_%H-%M-%S") if not filename.endswith(".xlsx"): filename += '.xlsx' workbook = xlsxwriter.Workbook(filename ) worksheet = workbook.add_worksheet('Kaffee-Abrechnung') # Add a number format for cells with money. moneyFormat = workbook.add_format({'num_format': u'0.00€'}) resultFormat = workbook.add_format({'bold': True, 'top': 1}) # Start from the first cell. Rows and columns are zero indexed. row = 1 col = 0 worksheet.write(0, 0, 'Name') worksheet.set_column(0, 0, 50) worksheet.write(0, 1, 'Anzahl') worksheet.write(0, 2, 'Betrag') worksheet.set_column(2, 2, 10, moneyFormat) worksheet.write(0, 3, '€/Kaffe:') worksheet.write(0, 4, self.mPrice, moneyFormat) # Iterate over the data and write it out row by row. # OLD Version without sorting for key, item in counts.items(): for key, item in self.mData.items(): worksheet.write(row, col, item['name']) worksheet.write(row, col + 1, item['count']) worksheet.write(row, col + 2, '=PRODUCT(E1,' + xl_rowcol_to_cell(row, col + 1) + ')') row += 1 # Write a total using a formula. worksheet.write(row, 0, 'Total', resultFormat) worksheet.write(row, 1, '=SUM(' + xl_range(1, 1, row - 1, 1) + ')', resultFormat) worksheet.write(row, 2, '=SUM(' + xl_range(1, 2, row - 1, 2) + ')', resultFormat) try: workbook.close() except Exception as e: self.mMessages.append(str(e)) raise Exception("EXCEL workbook could not be created") ######################################### ## Remove the old Count File and back it up ######################################### backupFilename = 'COUNT_'+os.path.basename(filename).replace('.xlsx', '') + ".TXT" try: zip = zipfile.ZipFile(self.mSdPath+"count.zip", "a") i=0 while backupFilename in zip.namelist(): i += 1 backupFilename = 'COUNT_'+str(i)+"_"+os.path.basename(filename).replace('.xlsx', '') + ".TXT" zip.write(self.mSdPath+"COUNT.TXT", backupFilename) zip.close() except Exception as e: self.mMessages.append(str(e)) raise Exception("FATAL ERROR: Backup of C**T.TXT could not be saved int count.zip" "\nSTOPPING EXECUTION: CONTACT ROMAN") return # Everthing is fine now, so delete the old Backup and write back the Counts of the users with # few coffes this month try: os.remove(self.mSdPath+'~COUNT.TXT') except (OSError, IOError) as e: pass os.rename(self.mSdPath+'COUNT.TXT', self.mSdPath+'~COUNT.TXT') #Now create a new File that contains all people with less than the min Amount try: with open(self.mSdPath+'COUNT.TXT', 'w') as f: for key, item in self.mData.items(): if item['count'] < self.mMinimumCoffees: f.write(str(key).upper()+"\t"+str(item['count']).zfill(4)+"\n") except Exception as e: self.mMessages.append(str(e)) raise Exception("FATAL ERROR: Remaining counts could not be written back onto SD-CARD" "\nSTOPPING EXECUTION: CONTACT ROMAN") return
def write2xlsx_v1(self, fliename, exp_acc_angle, the_acc_angle, exp_dist, the_dist): # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook(fliename) worksheet = workbook.add_worksheet() # format title_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1}) ev_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#F0F0F0', 'num_format': '0.000'}) tv_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEEED1', 'num_format': '0.000'}) re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'num_format': 10, 'bold' : 1}) highlight_re_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1, 'bottom': 1, 'top': 1, 'left': 1, 'right': 1, 'bg_color': '#EEE8CD', 'font_color': '#FF0000', 'num_format': 10, 'bold' : 1}) # write first row worksheet.write('A1','No',title_format) worksheet.write('B1','Type',title_format) worksheet.merge_range('C1:E1','Accumulated Euler Angle(degree)',title_format) worksheet.write('F1','Avrg Dist(m)',title_format) for i in range(0,exp_acc_angle.shape[0]): first_row = i*3+1 last_row = i*3+3 # write the number r = xl_range(first_row,0,last_row,0) worksheet.merge_range(r,i,title_format) # write Experimental value cur_row = first_row worksheet.write(cur_row, 1,'Experimental value',ev_format) worksheet.write_row(cur_row, 2, exp_acc_angle[i], ev_format) worksheet.write(cur_row, 5, exp_dist[i], ev_format) # write Theoretical value cur_row = cur_row + 1 worksheet.write(cur_row, 1,'Theoretical value',tv_format) worksheet.write_row(cur_row, 2, the_acc_angle[i], tv_format) worksheet.write(cur_row, 5, the_dist[i], tv_format) # write Relative Error cur_row = cur_row + 1 worksheet.write(cur_row, 1,'Relative Error',re_format) r = xl_range(cur_row,2,cur_row,5) r1 = xl_range(first_row,2,first_row,5) r2 = xl_range(first_row+1,2,first_row+1,5) worksheet.write_array_formula(r,'ABS(('+r2+'-'+r1+')/'+r2+')',cell_format = re_format) # set col weight worksheet.set_column(0,0,2.5) worksheet.set_column(1,1,18) worksheet.set_column(2,4,9.5) worksheet.set_column(5,5,12) workbook.close()
def generate_xlsx(path, tests): """ Generates an excel spreadsheet containing the results of all tests Args: path (str): Path to save the spreadsheet tests (list of Tester): All completed tests """ debug('Generating excel spreadsheet...') # Fix filename if not path.endswith('.xlsx'): path += '.xlsx' wb = xl.Workbook(path) ws = wb.add_worksheet() # Some cell formats used in the sheet fmt_name = wb.add_format({'font_color': '#00ff00', 'bg_color': 'black', 'border': 1, 'border_color': '#005500'}) fmt_perfect = wb.add_format({'bg_color': '#b6d7a8', 'border': 1, 'border_color': '#cccccc'}) fmt_bad = wb.add_format({'bg_color': '#ea9999', 'border': 1, 'border_color': '#cccccc'}) fmt_none = wb.add_format({'bg_color': '#ffe599', 'border': 1, 'border_color': '#cccccc'}) fmt_default = wb.add_format({'bg_color': 'white', 'border': 1, 'border_color': '#cccccc'}) # Some common format strings subtract = '={}-{}' add = '={}+{}' percent = '=100*{}/MAX(1, {})' # Write headers cols = ['CB_NAME', 'POVs Total', 'POVs Passed', 'POVs Failed', '% POVs Passed', '', 'POLLs Total', 'POLLs Passed', 'POLLs Failed', '% POLLs Passed', '', 'Total Tests', 'Total Passed', 'Total Failed', 'Total % Passed', 'Notes'] row = 0 ws.write_row(row, 0, cols) # Helper map for getting column indices col_to_idx = {val: i for i, val in enumerate(cols)} # Helper for writing formulas that use two cells def write_formula(row, col_name, formula, formula_col1, formula_col2, fmt=fmt_default): # type: (int, str, str, str, str, xl.format.Format) -> None ws.write_formula(row, col_to_idx[col_name], formula.format(xlutil.xl_rowcol_to_cell(row, col_to_idx[formula_col1]), xlutil.xl_rowcol_to_cell(row, col_to_idx[formula_col2])), fmt) # Helper for choosing the right format for a cell def select_fmt(total, passed): # type: (int, int) -> xl.format.Format if total == 0: return fmt_none elif total == passed: return fmt_perfect elif passed == 0: return fmt_bad return fmt_default # Add all test data for test in tests: row += 1 # Write the challenge name ws.write(row, 0, test.name, fmt_name) # NOTE: Leaving all of these to be calculated in excel in case you want to manually edit it later # POVs fmt = select_fmt(test.povs.total, test.povs.passed) ws.write_row(row, col_to_idx['POVs Total'], [test.povs.total, test.povs.passed], fmt) write_formula(row, 'POVs Failed', subtract, 'POVs Total', 'POVs Passed', fmt) write_formula(row, '% POVs Passed', percent, 'POVs Passed', 'POVs Total', fmt) # POLLs fmt = select_fmt(test.polls.total, test.polls.passed) ws.write_row(row, col_to_idx['POLLs Total'], [test.polls.total, test.polls.passed], fmt) write_formula(row, 'POLLs Failed', subtract, 'POLLs Total', 'POLLs Passed', fmt) write_formula(row, '% POLLs Passed', percent, 'POLLs Passed', 'POLLs Total', fmt) # Totals fmt = select_fmt(test.total, test.passed) write_formula(row, 'Total Tests', add, 'POVs Total', 'POLLs Total', fmt) write_formula(row, 'Total Passed', add, 'POVs Passed', 'POLLs Passed', fmt) write_formula(row, 'Total Failed', subtract, 'Total Tests', 'Total Passed', fmt) write_formula(row, 'Total % Passed', percent, 'Total Passed', 'Total Tests', fmt) # These columns are ignored in totals skip_cols = ['', 'CB_NAME', '% POVs Passed', '% POLLs Passed', 'Total % Passed', 'Notes'] # Totals at bottom row += 1 ws.write(row, 0, 'TOTAL') for col_name in cols: if col_name not in skip_cols: col = col_to_idx[col_name] ws.write_formula(row, col, '=SUM({})'.format(xlutil.xl_range(1, col, len(tests), col))) # Calculate total %'s write_formula(row, '% POVs Passed', percent, 'POVs Passed', 'POVs Total') write_formula(row, '% POLLs Passed', percent, 'POLLs Passed', 'POLLs Total') write_formula(row, 'Total % Passed', percent, 'Total Passed', 'Total Tests') # These columns are ignored in averages skip_cols = ['', 'CB_NAME', 'Notes'] # Averages at bottom row += 1 ws.write(row, 0, 'AVERAGE') for col_name in cols: if col_name not in skip_cols: col = col_to_idx[col_name] ws.write_formula(row, col, '=AVERAGE({})'.format(xlutil.xl_range(1, col, len(tests), col))) # Done, save the spreadsheet wb.close() debug('Done, saved to {}\n'.format(path))