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))
