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