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