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 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)
#TODO: Refactor function names and variables (low-hyphen instead of camelcase)
import yaml
import openpyxl
from openpyxl.utils.cell import coordinate_from_string, column_index_from_string
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
import time
import sys
from tqdm import tqdm
YELLOW_HIGHLIGHT = PatternFill(start_color='ffff00',
end_color='ffff00',
fill_type='solid')
GREEN_HIGHLIGHT = PatternFill(start_color='90ee90',
end_color='90ee90',
fill_type='solid')
ORANGE_HIGHLIGHT = PatternFill(start_color='ffa500',
end_color='ffa500',
fill_type='solid')
#Retrieves data from the YAML file and returns a dictionary with the vals.
def get_config_variables():
with open('config.yaml') as f:
data = yaml.load(f, Loader=yaml.FullLoader)
return data
def add_text(worksheet, title, overall_text):
worksheet['A1'] = title
cnt = 0
name_array= []
def fill_cvant_2 (flag, obj1, obj2, new_flag):
if new_flag == 1:
if flag == 0:
temp = obj1.time_w8
for j in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj2)
temp = obj2.time_w8
for i in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if i< obj2.time_start:
continue
while temp:
sheet.cell(row =6, column=i+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
i += 1
if data[j] != 0:
continue
sheet.cell(row =6, column=i+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
elif flag == 1:
temp = obj2.time_w8
for j in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if j< obj2.time_start:
continue
while temp:
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj1)
temp = obj1.time_w8
for i in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if i< obj1.time_start:
continue
while temp:
sheet.cell(row =5, column=i+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
i += 1
if data[j] != 0:
continue
sheet.cell(row =5, column=i+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[i] += 1
elif new_flag == 2:
if flag == 0:
temp = obj1.time_w8
for j in range(int(obj1.time_start + obj1.time_w8 + obj1.time)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj2)
temp = obj2.time_w8
for i in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if i< obj2.time_start:
continue
while temp:
sheet.cell(row =6, column=i+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
i += 1
if data[i] != 0:
continue
sheet.cell(row =6, column=i+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[i] += 1
elif flag == 1:
temp = obj2.time_w8
for j in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if j< obj2.time_start:
continue
while temp:
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj1)
temp = obj1.time_w8
for j in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
elif new_flag == 3:
if flag == 0:
temp = obj1.time_w8
for j in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj2)
temp = obj2.time_w8
for j in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if j< obj2.time_start:
continue
while temp:
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
elif flag == 1:
temp = obj2.time_w8
for j in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if j< obj2.time_start:
continue
while temp:
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj1)
temp = obj1.time_w8
for j in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
if min_start > secound.time_start:
min_start = secound.time_start
flag = 1
if min_start > third.time_start:
min_start = third.time_start
flag = 2
if min_start > fourth.time_start:
min_start = fourth.time_start
flag = 3
#Заполнение квантов процессов. После заполнения одного процесса, идёт перерасчёт штрафного времени
if flag == 0:
for j in range(int(first.time + first.time_start)):
if j < first.time_start:
continue
sheet.cell(row =3, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j]+=1
expecttion (data, secound)
expecttion (data, third)
expecttion (data, fourth)
max_pen = penalty_against(secound)
if max_pen < penalty_against(third):
max_pen = penalty_against(third)
flag = 1
if max_pen < penalty_against(fourth):
max_pen = penalty_against(fourth)
flag = 2
fill_cvant_1(flag, secound, third, fourth)
elif flag == 1:
ws1 = wb.create_sheet('COVER', 0)
ws3 = wb.create_sheet('environment', 1)
ws4 = wb.create_sheet('Data', 4)
ws1.sheet_properties.tabColor = "1072BA" #设定sheet的标签的背景颜色
ws1.column_dimensions['B'].width = 80.0
ws1.row_dimensions[7].height = 70.0
cover_font = Font(name='Arial Unicode MS',
size=11,
bold=True,
italic=True,
vertAlign=None,
underline='none',
strike=True,
color='000000',
shadow=False)
data_fill = PatternFill('solid', fgColor='FFCC66')
title = [
'Channel', 'Path_Loss(dB)', 'Angle', 'DS_Throughput', 'DS_Throughput_avg',
'US_Throughput', 'US_Throughput_avg', 'Sta_Rssi', 'Sta_Rssi_avg',
'AP_Rssi', 'AP_Rssi_avg', 'DS_Rate', 'DS_Rate_avg', 'US_Rate',
'US_Rate_avg', 'Time', 'MCS(DS)', 'MCS(US)', 'NSS(DS)', 'NSS(US)',
'BW(DS)', 'BW(US)', 'STA RSSI(per chain)', 'AP POWER',
'AP RSSI(per chain)', 'STA POWER'
]
ws1.append(title)
for comlumn in range(len(title)):
comlumn += 1
ws1.cell(1, comlumn).font = cover_font
ws1.cell(1, comlumn).fill = data_fill
posX = ord('A')
posY = 2
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 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 main(_argv):
physical_devices = tf.config.experimental.list_physical_devices('GPU')
if len(physical_devices) > 0:
tf.config.experimental.set_memory_growth(physical_devices[0], True)
yolo = YoloV3(classes=total_number_of_logos) # number of classes/logos, needs to be updated if another logo is added
yolo.load_weights('./weights/yolov3-custom.tf').expect_partial() # file path to weights
class_names = [c.strip() for c in open('./data/labels/custom.names').readlines()] # file path to classes list, needs to be updated if another logo is added
if FLAGS.count:
count = FLAGS.count
excel = []
images = []
for i in range(count):
con = convert_from_path('data/pdf/test (' + str(i+1) + ').pdf', output_folder='data/images', fmt="jpg", single_file=True, output_file='test (' + str(i+1) + ')')
excel.append('data/excel/test (' + str(i+1) + ').xlsx')
images.append('data/images/test (' + str(i+1) + ').jpg')
raw_images = []
for image in images:
img_raw = tf.image.decode_image(
open(image, 'rb').read(), channels=3)
raw_images.append(img_raw)
i = 0 # index number for main loop
logos = [] # list of detected logos for each image
approvals = [] # list of excel data for each image
for raw_img in raw_images:
img = tf.expand_dims(raw_img, 0)
img = transform_images(img, 416) # image size
t1 = time.time()
boxes, scores, classes, nums = yolo(img)
t2 = time.time()
logging.info('time: {}'.format(t2 - t1))
img = cv2.cvtColor(raw_img.numpy(), cv2.COLOR_RGB2BGR)
img = draw_outputs(img, (boxes, scores, classes, nums), class_names)
cv2.imwrite('./detections/detection (' + str(i+1) + ').jpg', img) # image output
# LABEL EXTRACTION
temp_names = [] # temporary list for each image's logo detections
for j in range(nums[0]):
repeat = True
temp_pair = [] # temporary list for each logo and its status
if (j > 0):
for k in range(len(temp_names)):
if (class_names[int(classes[0][j])] == temp_names[k][0]):
repeat = False
break
if (repeat): # if not a repeated logo, update main logo list
temp_pair.append(class_names[int(classes[0][j])]) # append logo
temp_pair.append(False) # append status
temp_names.append(temp_pair) # append pair
logos.append(temp_names) # append names list to main logo list
# EXCEL EXTRACTION
wb = load_workbook(excel[i])
sheet = wb.active
rows = sheet.max_row
temp_sheet = [] # temporary list for each image's excel data
for j in range(rows-1):
temp_rows = [] # temporary list for each row's excel data
temp_rows.append(str(sheet.cell(row=j+2, column=4).value).upper().strip())
temp_rows.append(str(sheet.cell(row=j+2, column=5).value).upper().strip())
temp_rows.append("00FF0000") # Red by default
temp_sheet.append(temp_rows)
approvals.append(temp_sheet) # append sheet list to main approvals list
# EXCEL TRANSLATION
for j in range(len(approvals[i])):
if (approvals[i][j][0] in extola):
temp_trans = extola[approvals[i][j][0]]
else:
temp_trans = ["NAL"] # No Associated Logo
approvals[i][j][0] = temp_trans
# EXCEL COMPARED TO LABEL
# "APPROVAL STATUS" "On label" "Not on label"
# "APPROVED" "Green" "Red"
# "NO REQUIREMENTS" "Red" "Green"
# "APPROVAL NOT APPLICABLE" "Red" "Green"
# "APPROVAL NOT REQUIRED" "Red" "Green"
# "CONTACT CISCO PARTNER/IOR" "Red" "Green"
# "NOT APPROVED" "Red" "Green"
# "PENDING" "Red" "Green"
# "RENEWAL IN PROGESS" "Red" "Green"
# "NONE"/"UNKNOWN" "Red" "Red"
#
# "00FF0000" (Red) needs attention
# "0000FF00" (Green) good to go
# "000000FF" (Blue) missing logo
#
for j in range(len(approvals[i])):
flag = True
k = 0
temp_count = 0
while (flag):
if (k == len(logos[i])): # logo not on label
flag = False
if (approvals[i][j][1] == "APPROVED"):
approvals[i][j][2] = "00FF0000" # Red
elif ((approvals[i][j][1] == "APPROVAL NOT APPLICABLE")or(approvals[i][j][1] == "APPROVAL NOT REQUIRED")or(approvals[i][j][1] == "CONTACT CISCO PARTNER/IOR")or
(approvals[i][j][1] == "NOT APPROVED")or(approvals[i][j][1] == "PENDING")or(approvals[i][j][1] == "RENEWAL IN PROGESS")or(approvals[i][j][1] == "NO REQUIREMENTS")):
approvals[i][j][2] = "0000FF00" # Green
elif ((approvals[i][j][1] == "NONE")or(approvals[i][j][1] == "UNKNOWN")):
approvals[i][j][2] = "00FF0000" # Red
sheet.cell(row=j+2, column=5).value = "Unknown"
elif (approvals[i][j][0][0] == "NAL"): # no logo to detect
flag = False
if ((approvals[i][j][1] == "APPROVAL NOT APPLICABLE")or(approvals[i][j][1] == "APPROVAL NOT REQUIRED")or(approvals[i][j][1] == "CONTACT CISCO PARTNER/IOR")or
(approvals[i][j][1] == "NOT APPROVED")or(approvals[i][j][1] == "PENDING")or(approvals[i][j][1] == "RENEWAL IN PROGESS")or(approvals[i][j][1] == "APPROVED")or(approvals[i][j][1] == "NO REQUIREMENTS")):
approvals[i][j][2] = "0000FF00" # Green
elif ((approvals[i][j][1] == "NONE")or(approvals[i][j][1] == "UNKNOWN")):
approvals[i][j][2] = "00FF0000" # Red
sheet.cell(row=j+2, column=5).value = "Unknown"
else: # continue or logo on label
for X in range(len(approvals[i][j][0])):
if (approvals[i][j][0][X] == logos[i][k][0]): # logo on label
logos[i][k][1] = True
temp_count+=1
if (temp_count == len(approvals[i][j][0])):
flag = False
if ((approvals[i][j][1] == "APPROVAL NOT APPLICABLE")or(approvals[i][j][1] == "APPROVAL NOT REQUIRED")or(approvals[i][j][1] == "CONTACT CISCO PARTNER/IOR")or
(approvals[i][j][1] == "NOT APPROVED")or(approvals[i][j][1] == "PENDING")or(approvals[i][j][1] == "RENEWAL IN PROGESS")):
approvals[i][j][2] = "00FF0000" # Red
elif ((temp_count == len(approvals[i][j][0]))and(approvals[i][j][1] == "APPROVED")or(approvals[i][j][1] == "NO REQUIREMENTS")):
approvals[i][j][2] = "0000FF00" # Green
elif ((approvals[i][j][1] == "NONE")or(approvals[i][j][1] == "UNKNOWN")):
approvals[i][j][2] = "00FF0000" # Red
sheet.cell(row=j+2, column=5).value = "Unknown"
k+=1
sheet.cell(row=j+2, column=5).fill = PatternFill(start_color=approvals[i][j][2], end_color=approvals[i][j][2], fill_type='solid')
# LABEL COMPARED TO EXCEL
new_row=1
for j in range(len(logos[i])):
if (logos[i][j][1] == False): # not on excel so add it in a new row
sheet.cell(row=new_row+rows, column=1).value = str(sheet.cell(row=rows, column=1).value) #1 Product Name
sheet.cell(row=new_row+rows, column=3).value = str(sheet.cell(row=rows, column=3).value) #3 Desc
sheet.cell(row=new_row+rows, column=4).value = logos[i][j][0] #4 Country
sheet.cell(row=new_row+rows, column=5).value = "Unknown" #5 Approval Status
sheet.cell(row=new_row+rows, column=5).fill = PatternFill(start_color="000000FF", end_color="000000FF", fill_type='solid') #5 Blue
for k in range(5):
sheet.cell(row=new_row+rows, column=k+6).value = str(sheet.cell(row=rows, column=k+6).value) #6-10
new_row+=1
wb.save(excel[i])
i+=1
# DISPLAY
for j in range(i):
print("\nL" + str(j+1) + ": ", end="")
temp_print = []
for k in range(len(logos[j])):
temp_print.append(logos[j][k][0])
print(temp_print, "\nE" + str(j+1) + ": ", end="")
temp_print = []
for k in range(len(approvals[j])):
temp_print.append(approvals[j][k][0])
print(temp_print)
print("")
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()
def saveLoadableExcel(dts, excelFile):
from arelle import ModelDocument, XmlUtil
from openpyxl import Workbook, cell
from openpyxl.styles import Font, PatternFill, Border, Alignment, Color, fills, Side
from openpyxl.worksheet.dimensions import ColumnDimension
from openpyxl.utils import get_column_letter
workbook = Workbook()
# remove pre-existing worksheets
while len(workbook.worksheets) > 0:
workbook.remove_sheet(workbook.worksheets[0])
conceptsWs = workbook.create_sheet(title="Concepts")
dtsWs = workbook.create_sheet(title="DTS")
# identify type of taxonomy
conceptsWsHeaders = None
cellFontArgs = None
for doc in dts.urlDocs.values():
if doc.type == ModelDocument.Type.SCHEMA and doc.inDTS:
for i in range(len(headersStyles)):
if re.match(headersStyles[i][0], doc.targetNamespace):
cellFontArgs = headersStyles[i][
1] # use as arguments to Font()
conceptsWsHeaders = headersStyles[i][2]
break
if conceptsWsHeaders is None:
dts.info(
"error:saveLoadableExcel",
_("Referenced taxonomy style not identified, assuming general pattern."
),
modelObject=dts)
cellFontArgs = headersStyles[-1][1] # use as arguments to Font()
conceptsWsHeaders = headersStyles[-1][2]
hdrCellFont = Font(**cellFontArgs)
hdrCellFill = PatternFill(
patternType=fills.FILL_SOLID,
fgColor=Color("00FFBF5F")) # Excel's light orange fill color = 00FF990
cellFont = Font(**cellFontArgs)
def writeCell(ws,
row,
col,
value,
fontBold=False,
borders=True,
indent=0,
hAlign=None,
vAlign=None,
hdr=False):
cell = ws.cell(row=row, column=col)
cell.value = value
if hdr:
cell.font = hdrCellFont
cell.fill = hdrCellFill
if not hAlign: hAlign = "center"
if not vAlign: vAlign = "center"
else:
cell.font = cellFont
if not hAlign: hAlign = "left"
if not vAlign: vAlign = "top"
if borders:
cell.border = Border(top=Side(border_style="thin"),
left=Side(border_style="thin"),
right=Side(border_style="thin"),
bottom=Side(border_style="thin"))
cell.alignment = Alignment(horizontal=hAlign,
vertical=vAlign,
wrap_text=True,
indent=indent)
# sheet 1 col widths
for i, hdr in enumerate(conceptsWsHeaders):
colLetter = get_column_letter(i + 1)
conceptsWs.column_dimensions[colLetter] = ColumnDimension(
conceptsWs, customWidth=True)
conceptsWs.column_dimensions[colLetter].width = headerWidths.get(
hdr[1], 40)
# sheet 2 headers
for i, hdr in enumerate(dtsWsHeaders):
colLetter = get_column_letter(i + 1)
dtsWs.column_dimensions[colLetter] = ColumnDimension(conceptsWs,
customWidth=True)
dtsWs.column_dimensions[colLetter].width = hdr[1]
writeCell(dtsWs, 1, i + 1, hdr[0], hdr=True)
# referenced taxonomies
conceptsRow = 1
dtsRow = 3
# identify extension schema
extensionSchemaDoc = None
if dts.modelDocument.type == ModelDocument.Type.SCHEMA:
extensionSchemaDoc = dts.modelDocument
elif dts.modelDocument.type == ModelDocument.Type.INSTANCE:
for doc, docReference in dts.modelDocument.referencesDocument.items():
if docReference.referenceType == "href":
extensionSchemaDoc = doc
break
if extensionSchemaDoc is None:
dts.info("error:saveLoadableExcel",
_("Unable to identify extension taxonomy."),
modelObject=dts)
return
for doc, docReference in extensionSchemaDoc.referencesDocument.items():
if docReference.referenceType == "import" and doc.targetNamespace != XbrlConst.xbrli:
writeCell(dtsWs, dtsRow, 1, "import")
writeCell(dtsWs, dtsRow, 2, "schema")
writeCell(
dtsWs, dtsRow, 3,
XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace))
writeCell(dtsWs, dtsRow, 4, doc.uri)
writeCell(dtsWs, dtsRow, 5, doc.targetNamespace)
dtsRow += 1
dtsRow += 1
doc = extensionSchemaDoc
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "schema")
writeCell(dtsWs, dtsRow, 3,
XmlUtil.xmlnsprefix(doc.xmlRootElement, doc.targetNamespace))
writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri))
writeCell(dtsWs, dtsRow, 5, doc.targetNamespace)
dtsRow += 1
for doc, docReference in extensionSchemaDoc.referencesDocument.items():
if docReference.referenceType == "href" and doc.type == ModelDocument.Type.LINKBASE:
linkbaseType = ""
role = docReference.referringModelObject.get(
"{http://www.w3.org/1999/xlink}role") or ""
if role.startswith("http://www.xbrl.org/2003/role/"
) and role.endswith("LinkbaseRef"):
linkbaseType = os.path.basename(role)[0:-11]
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "linkbase")
writeCell(dtsWs, dtsRow, 3, linkbaseType)
writeCell(dtsWs, dtsRow, 4, os.path.basename(doc.uri))
writeCell(dtsWs, dtsRow, 5, "")
dtsRow += 1
dtsRow += 1
# extended link roles defined in this document
for roleURI, roleTypes in sorted(
dts.roleTypes.items(),
# sort on definition if any else URI
key=lambda item:
(item[1][0].definition
if len(item[1]) and item[1][0].definition else item[0])):
for roleType in roleTypes:
if roleType.modelDocument == extensionSchemaDoc:
writeCell(dtsWs, dtsRow, 1, "extension")
writeCell(dtsWs, dtsRow, 2, "role")
writeCell(dtsWs, dtsRow, 3, "")
writeCell(dtsWs, dtsRow, 4, roleType.definition)
writeCell(dtsWs, dtsRow, 5, roleURI)
dtsRow += 1
# tree walk recursive function
def treeWalk(row, depth, concept, preferredLabel, arcrole, preRelSet,
visited):
if concept is not None:
# calc parents
calcRelSet = dts.relationshipSet(XbrlConst.summationItem,
preRelSet.linkrole)
calcRel = None
for modelRel in calcRelSet.toModelObject(concept):
calcRel = modelRel
break
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
value = ""
if colType == "name":
value = str(concept.name)
elif colType == "prefix" and concept.qname is not None:
value = concept.qname.prefix
elif colType == "type" and concept.type is not None:
value = str(concept.type.qname)
elif colType == "substitutionGroup":
value = str(concept.substitutionGroupQname)
elif colType == "abstract":
value = "true" if concept.isAbstract else "false"
elif colType == "nillable":
if concept.isNillable:
value = "true"
elif colType == "periodType":
value = concept.periodType
elif colType == "balance":
value = concept.balance
elif colType == "label":
role = hdr[2]
lang = hdr[3]
if role == XbrlConst.standardLabel:
if "indented" in hdr:
roleUri = preferredLabel
elif "overridePreferred" in hdr:
if preferredLabel and preferredLabel != XbrlConst.standardLabel:
roleUri = role
else:
roleUri = "**no value**" # skip putting a value in this column
else:
roleUri = role
else:
roleUri = role
if roleUri != "**no value**":
value = concept.label(
roleUri,
linkroleHint=preRelSet.linkrole,
lang=lang,
fallbackToQname=(role == XbrlConst.standardLabel))
elif colType == "preferredLabel" and preferredLabel:
if preferredLabel.startswith(
"http://www.xbrl.org/2003/role/"):
value = os.path.basename(preferredLabel)
else:
value = preferredLabel
elif colType == "calculationParent" and calcRel is not None:
calcParent = calcRel.fromModelObject
if calcParent is not None:
value = str(calcParent.qname)
elif colType == "calculationWeight" and calcRel is not None:
value = calcRel.weight
elif colType == "depth":
value = depth
if "indented" in hdr:
indent = min(depth, MAXINDENT)
else:
indent = 0
writeCell(conceptsWs, row, i + 1, value, indent=indent)
row += 1
if concept not in visited:
visited.add(concept)
for modelRel in preRelSet.fromModelObject(concept):
if modelRel.toModelObject is not None:
row = treeWalk(row, depth + 1, modelRel.toModelObject,
modelRel.preferredLabel, arcrole,
preRelSet, visited)
visited.remove(concept)
return row
# use presentation relationships for conceptsWs
arcrole = XbrlConst.parentChild
# sort URIs by definition
linkroleUris = []
relationshipSet = dts.relationshipSet(arcrole)
if relationshipSet:
for linkroleUri in relationshipSet.linkRoleUris:
modelRoleTypes = dts.roleTypes.get(linkroleUri)
if modelRoleTypes:
roledefinition = (modelRoleTypes[0].genLabel(strip=True)
or modelRoleTypes[0].definition
or linkroleUri)
else:
roledefinition = linkroleUri
linkroleUris.append((roledefinition, linkroleUri))
linkroleUris.sort()
# for each URI in definition order
for roledefinition, linkroleUri in linkroleUris:
# write linkrole
writeCell(
conceptsWs,
conceptsRow,
1, (roledefinition or linkroleUri),
borders=False) # ELR has no boarders, just font specified
conceptsRow += 1
# write header row
for i, hdr in enumerate(conceptsWsHeaders):
writeCell(conceptsWs, conceptsRow, i + 1, hdr[0], hdr=True)
conceptsRow += 1
# elr relationships for tree walk
linkRelationshipSet = dts.relationshipSet(arcrole, linkroleUri)
for rootConcept in linkRelationshipSet.rootConcepts:
conceptsRow = treeWalk(conceptsRow, 0, rootConcept, None,
arcrole, linkRelationshipSet, set())
conceptsRow += 1 # double space rows between tables
else:
# write header row
for i, hdr in enumerate(conceptsWsHeaders):
writeCell(conceptsWs, conceptsRow, i, hdr[0], hdr=True)
conceptsRow += 1
# get lang
lang = None
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
if colType == "label":
lang = hdr[3]
if colType == "label":
role = hdr[2]
lang = hdr[3]
lbls = defaultdict(list)
for concept in set(dts.qnameConcepts.values(
)): # may be twice if unqualified, with and without namespace
lbls[concept.label(role, lang=lang)].append(concept.objectId())
srtLbls = sorted(lbls.keys())
excludedNamespaces = XbrlConst.ixbrlAll.union(
(XbrlConst.xbrli, XbrlConst.link, XbrlConst.xlink, XbrlConst.xl,
XbrlConst.xbrldt, XbrlConst.xhtml))
for label in srtLbls:
for objectId in lbls[label]:
concept = dts.modelObject(objectId)
if concept.modelDocument.targetNamespace not in excludedNamespaces:
for i, hdr in enumerate(conceptsWsHeaders):
colType = hdr[1]
value = ""
if colType == "name":
value = str(concept.qname.localName)
elif colType == "prefix":
value = concept.qname.prefix
elif colType == "type":
value = str(concept.type.qname)
elif colType == "substitutionGroup":
value = str(concept.substitutionGroupQname)
elif colType == "abstract":
value = "true" if concept.isAbstract else "false"
elif colType == "periodType":
value = concept.periodType
elif colType == "balance":
value = concept.balance
elif colType == "label":
role = hdr[2]
lang = hdr[3]
value = concept.label(role, lang=lang)
elif colType == "depth":
value = 0
if "indented" in hdr:
indent = min(0, MAXINDENT)
else:
indent = 0
writeCell(conceptsWs,
conceptsRow,
i,
value,
indent=indent)
conceptsRow += 1
try:
workbook.save(excelFile)
dts.info("info:saveLoadableExcel",
_("Saved Excel file: %(excelFile)s"),
excelFile=os.path.basename(excelFile),
modelXbrl=dts)
except Exception as ex:
dts.error("exception:saveLoadableExcel",
_("File saving exception: %(error)s"),
error=ex,
modelXbrl=dts)
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)
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.formatting import CellIsRule
import sys, time, pprint
wb1 = load_workbook('reddit_scraper_test.xlsx')
wb2 = load_workbook('reddit_scraper_test2.xlsx')
wb_change = load_workbook('change_in_test_rank.xlsx')
ws1 = wb1.active
ws2 = wb2.active
ws_change = wb_change.active
blackFill = PatternFill(start_color='00000000',
end_color='00000000',
fill_type='solid')
whiteFont = Font(color='FFFFFFFF')
rows2 = ws2.rows
rows_list = []
for r2 in rows2:
for cell in r2:
rows_list.append(cell)
rows1 = ws1.rows
rows1_list = []
for r1 in rows1:
rows1_list.append(r1)
start = time.time()
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
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)
sheet.column_dimensions[get_column_letter(k3)].width = 12
num += 1
import os
import re
import openpyxl
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
asc = (ls[0] < ls[-1])
col = [l[i] for l in lines]
for j in range(len(col)-1):
#print(type(lines[j+1][i]), ls[j+1][i], ls[j][i], (ls[j+1][i] <= ls[j][i]) == asc, asc)
if type(col[j+1]) is float and type(col[j]) is float and (col[j+1] <= col[j]) == asc:
warns.append((i, j))
# Save everything to excel
wb = Workbook()
ws1 = wb.active
ws1.title = "Data"
for line in lines:
ws1.append(line)
letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
orangefill = PatternFill("solid", fgColor="FF7F00")
for att in attentions:
cell = ws1[letters[att[0]]+str(att[1]+1)]
#print(cell)
cell.fill = orangefill
redfill = PatternFill("solid", fgColor="FF0000")
for warn in warns:
cell = ws1[letters[warn[0]]+str(warn[1]+1)]
#print(cell)
cell.fill = redfill
wb.save("p33_h1_silver.xlsx")
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)
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 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
for x in range(-move_ability, move_ability + 1):
if x not in x_move_range:
x_move_range.append(x)
for y in range(-move_ability, move_ability + 1):
if y not in y_move_range:
y_move_range.append(y)
print(x_move_range, y_move_range)
for x in x_move_range:
for y in y_move_range:
if abs(y) <= abs(move_ability - abs(x)):
# As fill the cell of Excel sheet, neither x nor y could be less than 1
if unit_x + x >= x_init and unit_y + y >= y_init:
cells_to_draw.append([unit_x + x, unit_y + y])
print(cells_to_draw)
print(len(cells_to_draw))
# Fill the cells
for cell_to_draw in cells_to_draw:
j = cell_to_draw[0]
i = cell_to_draw[1]
sheet.cell(j, i).fill = PatternFill(fgColor=draw_color,
bgColor=draw_color,
fill_type="solid")
wb.save('move.xlsx')
wb.close()
def fill_cvant_1 (flag, obj1, obj2, obj3):
if flag == 0:
temp = obj1.time_w8
for j in range(int(obj1.time + obj1.time_start + obj1.time_w8)):
if j< obj1.time_start:
continue
while temp:
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =4, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj2)
expecttion (data, obj3)
max_pen = penalty_against(obj2)
if max_pen < penalty_against(obj3):
flag = 1
fill_cvant_2(flag, obj2, obj3, 1)
elif flag == 1:
temp = obj2.time_w8
for j in range(int(obj2.time + obj2.time_start + obj2.time_w8)):
if j< obj2.time_start:
continue
while temp:
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =5, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj1)
expecttion (data, obj3)
flag = 0
max_pen = penalty_against(obj1)
if max_pen < penalty_against(obj3):
flag = 1
fill_cvant_2(flag, obj1, obj3, 2)
elif flag == 2:
temp = obj3.time_w8
for j in range(int(obj3.time + obj3.time_start + obj3.time_w8)):
if j< obj3.time_start:
continue
while temp:
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
temp -=1
j += 1
if data[j] != 0:
continue
sheet.cell(row =6, column=j+4).fill = PatternFill(start_color='008000', end_color='008000', fill_type='solid')
data[j] += 1
expecttion (data, obj1)
expecttion (data, obj2)
flag = 0
max_pen = penalty_against(obj1)
if max_pen < penalty_against(obj2):
flag = 1
fill_cvant_2(flag, obj1, obj2, 3)
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
working_list = list(existing_list)
unmatched_latest_list = list(latest_list)
# compare each line of latest list to each line of working list
for item in latest_list:
# pprint.pprint(item)
for line in existing_list:
# pprint.pprint(line)
# if names match,
if item[0].lower() == line[0].lower():
if line[1] == green:
# copy existing list entry and add rank as a 3rd entry
# in new list "updated_list"
updated_list.append((item[0],
PatternFill(start_color=green,
end_color=green,
fill_type="solid"), item[1]))
elif line[1] == red:
# copy existing list entry and add rank as a 3rd entry
# in new list "updated_list"
updated_list.append((item[0],
PatternFill(start_color=red,
end_color=red,
fill_type="solid"), item[1]))
# remove match from working list and unmatched_latest_list
working_list.remove(line)
unmatched_latest_list.remove(item)
# append non-matches to updated list with red formatting
for item in unmatched_latest_list:
updated_list.append(
def job():
now = datetime.now()
date = "%s-%s-%s" % (now.month, now.day, now.year)
wb = Workbook()
del wb['Sheet']
f = open('ip_list.txt')
for ip in f.read().splitlines():
try:
port_numbers_9332 = []
forward_drop_9332 = []
LB_drop_9332 = []
error_drop_9332 = []
port_numbers_9396 = []
forward_drop_9396 = []
LB_drop_9396 = []
error_drop_9396 = []
port_numbers_9372 = []
forward_drop_9372 = []
LB_drop_9372 = []
error_drop_9372 = []
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname=ip,
username='******',
password='******',
look_for_keys=False)
print("Successfully connect to ", ip)
command = ssh_client.invoke_shell()
command.send('show hostname\n')
time.sleep(2)
output = command.recv(65535).decode('ascii')
hostname = re.search(r'LF-\w+-\w+-\w+-\d{3}', output).group()
ws = wb.create_sheet(hostname)
ws['A1'] = 'Interface'
ws['B1'] = 'LB Drop (Cosmetic)'
ws['C1'] = 'Forward Drop (Cosmetic)'
ws['D1'] = 'Error Drop (Real)'
yellowFill = PatternFill(start_color='FFFF00',
end_color='FFFF00',
fill_type='solid')
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
ws['A1'].fill = yellowFill
ws['B1'].fill = yellowFill
ws['C1'].fill = yellowFill
ws['D1'].fill = yellowFill
command.send("vsh_lc\n")
time.sleep(5)
if '9332' in hostname:
for i in range(1, 33):
command.send(
f"show platform internal counters port {str(i)} | in LB|Forward|Error\n"
)
time.sleep(1)
output = command.recv(65535).decode('ascii')
#print(output)
forward_drop = re.search(r'Forward\s+(\d+)', output)
LB_drop = re.search(r'LB\s+(\d+)', output)
error_drop = re.search(r'Error\s+(\d+)', output)
if forward_drop:
port_number = f'Eth1/{i}'
port_numbers_9332.append(port_number)
forward_drop_9332.append(forward_drop.group(1))
LB_drop_9332.append(LB_drop.group(1))
error_drop_9332.append(error_drop.group(1))
rows = len(port_numbers_9332)
for port_number, lb_drop, forward_drop, error_drop, row in zip(
port_numbers_9332, LB_drop_9332,
forward_drop_9332, error_drop_9332,
range(2, rows + 2)):
ws.cell(row=row, column=1, value=port_number)
ws.cell(row=row, column=2, value=int(lb_drop))
ws.cell(row=row, column=3, value=int(forward_drop))
ws.cell(row=row, column=4, value=int(error_drop))
elif '9396' in hostname:
for i in range(1, 61):
command.send(
f"show platform internal counters port {str(i)} | in LB|Forward|Error\n"
)
time.sleep(1)
output = command.recv(65535).decode('ascii')
#print(output)
forward_drop = re.search(r'Forward\s+(\d+)', output)
LB_drop = re.search(r'LB\s+(\d+)', output)
error_drop = re.search(r'Error\s+(\d+)', output)
if forward_drop:
port_number = f'Eth1/{i}'
port_numbers_9396.append(port_number)
forward_drop_9396.append(forward_drop.group(1))
LB_drop_9396.append(LB_drop.group(1))
error_drop_9396.append(error_drop.group(1))
rows = len(port_numbers_9396)
for port_number, lb_drop, forward_drop, error_drop, row in zip(
port_numbers_9396, LB_drop_9396,
forward_drop_9396, error_drop_9396,
range(2, rows + 2)):
ws.cell(row=row, column=1, value=port_number)
ws.cell(row=row, column=2, value=int(lb_drop))
ws.cell(row=row, column=3, value=int(forward_drop))
ws.cell(row=row, column=4, value=int(error_drop))
elif '9372' in hostname:
for i in range(1, 55):
command.send(
f"show platform internal counters port {str(i)} | in LB|Forward|Error\n"
)
time.sleep(1)
output = command.recv(65535).decode('ascii')
#print(output)
forward_drop = re.search(r'Forward\s+(\d+)', output)
LB_drop = re.search(r'LB\s+(\d+)', output)
error_drop = re.search(r'Error\s+(\d+)', output)
if forward_drop:
port_number = f'Eth1/{i}'
port_numbers_9372.append(port_number)
forward_drop_9372.append(forward_drop.group(1))
LB_drop_9372.append(LB_drop.group(1))
error_drop_9372.append(error_drop.group(1))
rows = len(port_numbers_9372)
for port_number, lb_drop, forward_drop, error_drop, row in zip(
port_numbers_9372, LB_drop_9372,
forward_drop_9372, error_drop_9372,
range(2, rows + 2)):
ws.cell(row=row, column=1, value=port_number)
ws.cell(row=row, column=2, value=int(lb_drop))
ws.cell(row=row, column=3, value=int(forward_drop))
dims = {}
for row in ws.rows:
for cell in row:
cell.border = thin_border
if cell.value:
dims[cell.column_letter] = max(
(dims.get(cell.column_letter,
0), len(str(cell.value))))
for col, value in dims.items():
ws.column_dimensions[col].width = value + 1
except paramiko.ssh_exception.AuthenticationException:
print("User authentication failed for " + ip + ".")
switch_with_authentication_issue.append(ip)
except socket.error:
print(ip + " is not reachable.")
switch_not_reachable.append(ip)
wb.save(f'ACI Input Drop {date}.xlsx')
fromaddr = 'Yin Wang'
toaddr = '[email protected],[email protected], [email protected]'
msg = MIMEMultipart()
msg['From'] = fromaddr
msg['To'] = toaddr
msg['Subject'] = f'ACI Input Drop {date}'
email_content = f'''Attachment is the ACI Input Drop report for {date}.
This is a Python generated email, please do not reply.'''
msg.attach(MIMEText(email_content, 'plain'))
attachment = open(f'ACI Input Drop {date}.xlsx', 'rb')
part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header("Content-Disposition",
f"attachment; filename= ACI Input Drop {date}.xlsx")
msg.attach(part)
text = msg.as_string()
server = smtplib.SMTP('smtp.gmail.com', 587)
server.ehlo()
server.starttls()
server.ehlo()
server.login('*****@*****.**', 'xxxx')
server.sendmail(fromaddr, toaddr.split(","), text)
def _convert_to_fill(cls, fill_dict):
"""
Convert ``fill_dict`` to an openpyxl v2 Fill object.
Parameters
----------
fill_dict : dict
A dict with one or more of the following keys (or their synonyms),
'fill_type' ('patternType', 'patterntype')
'start_color' ('fgColor', 'fgcolor')
'end_color' ('bgColor', 'bgcolor')
or one or more of the following keys (or their synonyms).
'type' ('fill_type')
'degree'
'left'
'right'
'top'
'bottom'
'stop'
Returns
-------
fill : openpyxl.styles.Fill
"""
from openpyxl.styles import PatternFill, GradientFill
_pattern_fill_key_map = {
"patternType": "fill_type",
"patterntype": "fill_type",
"fgColor": "start_color",
"fgcolor": "start_color",
"bgColor": "end_color",
"bgcolor": "end_color",
}
_gradient_fill_key_map = {"fill_type": "type"}
pfill_kwargs = {}
gfill_kwargs = {}
for k, v in fill_dict.items():
pk = gk = None
if k in _pattern_fill_key_map:
pk = _pattern_fill_key_map[k]
if k in _gradient_fill_key_map:
gk = _gradient_fill_key_map[k]
if pk in ["start_color", "end_color"]:
v = cls._convert_to_color(v)
if gk == "stop":
v = cls._convert_to_stop(v)
if pk:
pfill_kwargs[pk] = v
elif gk:
gfill_kwargs[gk] = v
else:
pfill_kwargs[k] = v
gfill_kwargs[k] = v
try:
return PatternFill(**pfill_kwargs)
except TypeError:
return GradientFill(**gfill_kwargs)
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
def __call__(self):
self.request.response.setHeader('Content-Type',
'application/vnd.ms-excel')
self.request.response.setHeader('Content-disposition',
'attachment;filename=Atividades.xls')
catalog = getToolByName(self, 'portal_catalog')
path_demandas = '/'.join(self.context.getPhysicalPath())
ordemServico = self.request.get('ordemServico', None)
start = self.request.get('start', None)
end = self.request.get('end', None)
out = StringIO()
wb = Workbook()
# grab the active worksheet
ws1 = wb.active
ws1.title = "Rotineiras"
# Data can be assigned directly to cells
ws1['A1'] = 'Sistema'
ws1['B1'] = 'Ordem de Serviço'
ws1['C1'] = 'Número da RA'
ws1['D1'] = 'Status da RA'
ws1['E1'] = 'Atividade'
ws1['F1'] = 'Quantidade HST'
# Style
a1 = ws1['A1']
b1 = ws1['B1']
c1 = ws1['C1']
d1 = ws1['D1']
e1 = ws1['E1']
f1 = ws1['F1']
ft = Font(bold=True, color="FFFFFF")
fill = PatternFill(patternType=fills.FILL_SOLID)
a1.font = ft
a1.fill = fill
b1.font = ft
b1.fill = fill
c1.font = ft
c1.fill = fill
d1.font = ft
d1.fill = fill
e1.font = ft
e1.fill = fill
f1.font = ft
f1.fill = fill
results = catalog(
object_provides=IDemanda.__identifier__,
path=path_demandas,
ordem_servico=ordemServico,
sort_on='chamado',
sort_order='reverse',
)
for i in results:
atividade = []
if i.Title:
atividade.append(i.Title)
else:
atividade.append('')
if i.ordem_servico:
atividade.append(i.ordem_servico)
else:
atividade.append('')
if i.chamado:
atividade.append(i.chamado)
else:
atividade.append('')
if i.status_ra:
atividade.append(i.status_ra)
else:
atividade.append('')
if i.atividade:
atividade.append(i.atividade)
else:
atividade.append('')
if i.quantHST:
atividade.append(i.quantHST)
else:
atividade.append('')
ws1.append(atividade)
# Rows can also be appended
# ws1.append([1, 2, 3])
# ws1.append([4, 5, 6])
if start and end:
ws2 = wb.create_sheet(title='Projetizadas')
ws2['A1'] = 'Atividade'
ws2['B1'] = 'Projeto'
ws2['C1'] = 'Data início'
ws2['D1'] = 'Data fim'
ws2['E1'] = 'Duração'
ws2['F1'] = 'Quantidade HST'
# Style
a1 = ws2['A1']
b1 = ws2['B1']
c1 = ws2['C1']
d1 = ws2['D1']
e1 = ws2['E1']
f1 = ws2['F1']
ft = Font(bold=True, color="FFFFFF")
fill = PatternFill(patternType=fills.FILL_SOLID)
a1.font = ft
a1.fill = fill
b1.font = ft
b1.fill = fill
c1.font = ft
c1.fill = fill
d1.font = ft
d1.fill = fill
e1.font = ft
e1.fill = fill
f1.font = ft
f1.fill = fill
first_date = DateTime(start, datefmt='international')
last_date = DateTime(end + ' 23:59:59', datefmt='international')
results = catalog(object_provides=IAtividade.__identifier__,
path=path_demandas,
data_inicio={
'query': first_date,
'range': 'min'
},
data_fim={
'query': last_date,
'range': 'max'
},
sort_on='data_inicio')
for i in results:
atividade = []
if i.Title:
atividade.append(i.Title)
else:
atividade.append('')
if i.projeto:
atividade.append(i.projeto)
else:
atividade.append('')
if i.data_inicio:
data_inicio = i.data_inicio.strftime('%d/%m/%Y %H:%M')
atividade.append(data_inicio)
else:
atividade.append('')
if i.data_fim:
data_fim = i.data_fim.strftime('%d/%m/%Y %H:%M')
atividade.append(data_fim)
else:
atividade.append('')
if i.duracao:
atividade.append(i.duracao)
else:
atividade.append('')
if i.quantHST:
atividade.append(i.quantHST)
else:
atividade.append('')
ws2.append(atividade)
# Save the file
wb.save(out)
return out.getvalue()
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 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
i = 0
for z in range(ColStart, ColEnd):
file = worksheet[f'{Col_lett}{z}'].value
NewFileName = f'{FilesDir}{file}_between'
#Check if file already exist
if os.path.isfile(f'{NewFileName}.xlsx'):
print(f'{file}_between.xlsx already exist')
NewFile = openpyxl.load_workbook(f'{NewFileName}.xlsx')
NewWorksheet = NewFile[TemplateWorkSheetName]
#Fullfil created files with data from list
for x, y in Map.items():
if NewWorksheet[x].value != worksheet[f'{y}{z}'].value:
comment = Comment(f'Previous value = {NewWorksheet[x].value}',
'automatic inspect')
NewWorksheet[x].value = worksheet[f'{y}{z}'].value
NewWorksheet[x].fill = PatternFill(fgColor=MyColor,
fill_type="solid")
NewWorksheet[x].comment = comment
NewFile.save(f'{NewFileName}.xlsx')
WbPrint = excel.Workbooks.Open(f'{NewFileName}.xlsx')
WsPrint = WbPrint.Worksheets[TemplateWorkSheetName]
WbPrint.SaveAs(f"{NewFileName}.pdf", FileFormat=57)
WbPrint.Close()
excel.Quit()
NewFile.close()
else:
#If no so create one
source.save(f'{NewFileName}.xlsx')
source.close()
#Fullfil created files with data from list
NewFile = openpyxl.load_workbook(f'{NewFileName}.xlsx')
NewWorksheet = NewFile[TemplateWorkSheetName]
