def write(ws, orders, created_on, update, settings):
row_offset = 0
max_row = 0
locations = sorted(orders.keys())
# set column widths
ws.column_dimensions["A"].width = 14.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["B"].width = 10.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["C"].width = 06.71 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["D"].width = 07.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["E"].width = 08.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["F"].width = 11.00 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["J"].width = 01.57 + MYSTERY_WIDTH_OFFSET
ws.column_dimensions["L"].width = 13.14 + MYSTERY_WIDTH_OFFSET
#format 'created / updated' and 'search' cells
ws.merge_cells("G1:I1")
ws["G1"].alignment = Alignment(horizontal="right",
vertical="top",
wrap_text=True)
ws["K2"] = "Search:"
ws["K2"].alignment = Alignment(horizontal="right")
ws["L2"].fill = style['recent']['fill']
#the big loop
for location in locations:
sz = len(orders[location])
if (sz > 0):
row = 1 + row_offset
ws.row_dimensions[row].height = 30
ws['A%s' % row].font = style['fonts']['header']
ws['A%s' % row] = location
# insert 'created on' date
if row == 1:
date_string = "Created On: {}".format(
created_on.strftime('%m-%d-%Y'))
if update:
date_string += "\nUpdated On: {}".format(
datetime.date.today().strftime('%m-%d-%Y'))
ws["G1"] = date_string
row_offset += 1
for excelRow in range(1, sz + 1):
#begin writing the business data to sheet
for excelCol in range(1, len(COLUMN_NAMES) + 1):
col_name = COLUMN_NAMES[excelCol - 1]
cell = ws.cell(row=excelRow + row_offset, column=excelCol)
value = orders[location][excelRow - 1]['info'][col_name]
status = orders[location][excelRow - 1]['status']
# right justify date
if col_name == "Need-By Date":
cell.alignment = Alignment(horizontal="right")
# generate 'balance due' formula
if col_name == "Balance Due":
value = "=if(C{row}-D{row}<0, 0, C{row}-D{row})".format(
row=excelRow + row_offset)
if col_name in ["Quantity Ordered", "Quantity Received"]:
try:
value = int(value)
except:
pass
# add horizontal gridlines
cell.border = style['borders']['underline']
cell.font = style[status]['font']
cell.fill = style[status]['fill']
cell.value = value
# add gridlines to the right of the page for handwritten notes on print out
ws.cell(row=excelRow + row_offset,
column=7).border = style['borders']['underline']
ws.cell(row=excelRow + row_offset,
column=8).border = style['borders']['mid']
ws.cell(row=excelRow + row_offset,
column=9).border = style['borders']['underline']
max_row = excelRow + row_offset
row_offset += sz + 1
ws.print_area = f'A1:I{max_row}'
#create conditional formatting for search results
dxf = DifferentialStyle(fill=style['found']['fill'])
r1 = Rule(type="expression", stopIfTrue=True)
r1.formula = ['isblank($L$2)']
ws.conditional_formatting.add(f"A1:F{max_row}".format(), r1)
r2 = Rule(type="expression", dxf=dxf, stopIfTrue=False)
r2.formula = ['isnumber(search($L$2, $A1))']
ws.conditional_formatting.add(f"A1:F{max_row}".format(), r2)
def ventas_cliente():
date_start = request.form['date_start'] + ' 00:00:00'
date_end = request.form['date_end'] + ' 23:59:59'
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.font = Font(color='FFFFFF')
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
#name_db = os.environ["DATABASE"]
#user_db = os.environ["USER"]
#pass_db = os.environ["PASS"]
#host_db = os.environ["HOST"]
#conn = psycopg2.connect(database=name_db,user=user_db,password=pass_db, host=host_db)
conn = psycopg2.connect(database='sicamex_test',
user='******',
password='******',
host='cuamoc-dev.sicamex.mx')
cur = conn.cursor()
query = """SELECT O.client_id as id, C.company_code as id_decsa, C.client_number_ant as num_cliente, C.client_high as cliente_mayorista, C.business_name as negocio, CE.name as cedis, R.name as route, CH.name as channel, CT.name as segmento, CD.gec_name as gec, CD.condition_name as condition
FROM orders O
LEFT JOIN clients C
ON O.client_id = C.id
LEFT JOIN routes R
ON O.route_id = R.id
LEFT JOIN centers CE
ON O.center_id = CE.id
LEFT JOIN channels CH
ON C.channel_id = CH.id
LEFT JOIN client_types CT
ON C.client_type_id = CT.id
LEFT JOIN client_details CD
ON CD.client_id = C.id
WHERE O.id IN
(
SELECT MAX(id) as id
FROM orders
WHERE active=true AND ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY client_id
ORDER BY client_id
)
ORDER BY O.client_id;"""
cur.execute(query)
data = cur.fetchall()
query = """SELECT O.client_id, EXTRACT(MONTH FROM O.ordered_at) as month, EXTRACT(YEAR FROM O.ordered_at) as year, SUM(O.boxes) as boxes, SUM(O.hlts) as hlts
FROM orders O
WHERE O.active=true AND O.ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY O.client_id, EXTRACT(MONTH FROM O.ordered_at), EXTRACT(YEAR FROM O.ordered_at)
ORDER BY O.client_id, EXTRACT(YEAR FROM O.ordered_at), EXTRACT(MONTH FROM O.ordered_at);"""
cur.execute(query)
data_sum = cur.fetchall()
wb = Workbook()
ws = wb.active
ws.title = "VTAS X CTE-13.1"
yearStart = int(date_start[0:4])
monthStart = int(date_start[5:7])
yearEnd = int(date_end[0:4])
monthEnd = int(date_end[5:7])
months = [
[1, "Ene"],
[2, "Feb"],
[3, "Mar"],
[4, "Abr"],
[5, "May"],
[6, "Jun"],
[7, "Jul"],
[8, "Ago"],
[9, "Sep"],
[10, "Oct"],
[11, "Nov"],
[12, "Dic"],
[100, "Total"],
]
qtyMonths = len(months)
ws['A1'] = 'VTAS x CLIENTE'
title = ws['A1']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells('A1:K1')
ws['A3'] = 'CEDIS'
ws['B3'] = 'LAGOS DE MORENO'
title = ws['A3']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
ws['A4'] = 'FECHA'
ws['B4'] = 'Desde ' + request.form['date_start'] + ' Al ' + request.form[
'date_end']
title = ws['A4']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
if len(data) > 0:
cell_hasta = len(data) + 6
# add column headings. NB. these must be strings
table_header = [
"Id", "Id Decsa", "Num. Cliente", "Cve Cte Mayorista", "Negocio",
"Cedis", "Ruta de Venta", "Canal Estrat.", "Segmento", "GEC",
"Condicion"
]
begin = yearStart
end = int(yearEnd) + 1
for y in range(begin, end):
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for mm in range(mStart, mEnd):
table_header.append(months[mm][1] + " " + str(y) + "\nHlts")
if mEnd < qtyMonths:
table_header.append(months[qtyMonths - 1][1] + " " + str(y) +
"\nHlts")
htls_end = len(table_header)
box_start = htls_end + 1
for y in range(begin, end):
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for mm in range(mStart, mEnd):
table_header.append(months[mm][1] + " " + str(y) + "\nCajas")
if mEnd < qtyMonths:
table_header.append(months[qtyMonths - 1][1] + " " + str(y) +
"\nCajas")
ws.append(table_header)
box_end = len(table_header)
row_ds = 0
for row in data:
row_list = list(row)
for y in range(begin, end):
total = 0
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for mm in range(mStart, mEnd):
valor = 0
for ind in range(row_ds, len(data_sum)):
if (data_sum[ind][0] == row[0]
and data_sum[ind][1] == months[mm][0]
and data_sum[ind][2] == y):
row_ds = ind + 1
valor = data_sum[ind][4]
total += valor
break
if int(months[mm][0]) == 100:
valor = total
row_list.append(valor)
if mEnd < qtyMonths:
row_list.append(total)
row_ds = 0
for y in range(begin, end):
total = 0
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for mm in range(mStart, mEnd):
valor = 0
for ind in range(row_ds, len(data_sum)):
if (data_sum[ind][0] == row[0]
and data_sum[ind][1] == months[mm][0]
and data_sum[ind][2] == y):
row_ds = ind + 1
valor = data_sum[ind][3]
total += valor
break
if int(months[mm][0]) == 100:
valor = total
row_list.append(valor)
if mEnd < qtyMonths:
row_list.append(total)
ws.append(row_list)
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = 15
for col, value in dims.items():
ws.column_dimensions[col].width = value
ws.insert_rows(5)
title = ws['A7']
ws.freeze_panes = title
for column in range(1, len(table_header) + 1):
column_letter = get_column_letter(column)
for rowD in range(5, len(data) + 6):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
for row in ws.iter_rows("L6:" + get_column_letter(htls_end) +
str(cell_hasta)):
for cell in row:
cell.number_format = '#,#0.0'
tab = Table(displayName="Table1",
ref="A6:" + get_column_letter(len(table_header)) +
str(cell_hasta))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
for row in ws.iter_rows('A6:O6'):
for cell in row:
cell.style = headOpe
title = ws['L7']
ws.freeze_panes = title
ws.merge_cells('L5:' + get_column_letter(htls_end) + '5')
ws['L5'] = 'HLTS'
hlts = ws['L5']
hlts.font = Font(color=colors.WHITE, name='Tahoma', size=14)
hlts.alignment = Alignment(horizontal='center')
hlts.fill = PatternFill("solid", fgColor="305496")
ws.merge_cells(
get_column_letter(box_start) + '5:' + get_column_letter(box_end) +
'5')
ws[get_column_letter(box_start) + '5'] = 'CAJAS'
hlts = ws[get_column_letter(box_start) + '5']
hlts.font = Font(color=colors.BLACK, name='Tahoma', size=14)
hlts.alignment = Alignment(horizontal='center')
hlts.fill = PatternFill("solid", fgColor="DDDDDD")
nombre_archivo = "RC-13-1-" + datetime.now().date().strftime(
'%Y%m%d') + ".xlsx"
wb.save(nombre_archivo)
return send_file(
nombre_archivo,
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
True, nombre_archivo)
else:
return "No hay registros"
def ventas_cobertura():
name_db = os.environ["DATABASE"]
user_db = os.environ["USER"]
pass_db = os.environ["PASS"]
host_db = os.environ["HOST"]
conn = psycopg2.connect(database=name_db,
user=user_db,
password=pass_db,
host=host_db)
cur = conn.cursor()
#Creamos el libro de trabajo
wb = Workbook()
#Definimos como nuestra hoja de trabajo, la hoja activa, por defecto la primera del libro
ws = wb.active
ws.title = "COBERTURA 13.3"
#month = datetime.now().date().strftime('%m')
#year = datetime.now().date().strftime('%Y')
date_start = request.form['date_start'] + ' 00:00:00'
date_end = request.form['date_end'] + ' 23:59:59'
yearStart = int(date_start[0:4])
monthStart = int(date_start[5:7])
yearEnd = int(date_end[0:4])
monthEnd = int(date_end[5:7])
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.font = Font(color='FFFFFF')
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
months = [[1, "Ene"], [2, "Feb"], [3, "Mar"], [4, "Abr"], [5, "May"],
[6, "Jun"], [7, "Jul"], [8, "Ago"], [9, "Sep"], [10, "Oct"],
[11, "Nov"], [12, "Dic"], [100, "Total"]]
qtyMonths = len(months)
ws.merge_cells('A1:N1')
ws['A1'] = 'COBERTURA'
title = ws['A1']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="center", vertical="center")
ws['A3'] = 'Cedis'
ws['B3'] = 'Lagos de Moreno'
title = ws['A3']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
ini = date_start.split(" ")
fin = date_end.split(" ")
ws['A4'] = 'Fecha'
ws['B4'] = 'Desde ' + ini[0] + ' al ' + fin[0]
title = ws['A4']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
query = """SELECT O.client_id as id_client, C.company_code as id_erp, C.client_number_ant as cliente_ceveceria, C.client_high as mayorista,
C.business_name as negocio, CI.name as poblacion, CD.channel_type_name as canal, CT.name as segmento, R.name as route,
U.name || ' ' || U.lastname as name, CD.margin_level, C.discount as bonificacion, C.client_status, ' ' as enfriador,
' ' as rice_entregado, ' ' as monto_rice, ' ' as fachada, ' ' as doc, ' ' as observ,
to_char(C.created_at,'YYYY/MM/DD') as creado, to_char(C.created_at, 'TMMonth')
FROM orders O
LEFT JOIN clients C ON O.client_id = C.id
LEFT JOIN addresses A ON C.address_id = A.id
LEFT JOIN cities CI ON A.city_id = CI.id
LEFT JOIN routes R ON R.id = C.route_conqueror_id
LEFT JOIN channels CH ON C.channel_id = CH.id
LEFT JOIN client_types CT ON C.client_type_id = CT.id
LEFT JOIN client_details CD ON CD.client_id = C.id
LEFT JOIN users U ON O.user_id = U.id
WHERE O.id IN
(
SELECT MAX(id) as id
FROM orders
WHERE active=true AND ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY client_id, route_id
ORDER BY client_id
)
ORDER BY O.client_id;"""
query1 = """SELECT O.client_id, EXTRACT(MONTH FROM O.ordered_at) as month, EXTRACT(YEAR FROM O.ordered_at) as year, SUM(O.hlts)
FROM orders O
LEFT JOIN clients C ON O.client_id = C.id
LEFT JOIN routes R ON R.id = C.route_conqueror_id
WHERE O.active=true
AND O.ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY O.client_id, EXTRACT(MONTH FROM O.ordered_at), EXTRACT(YEAR FROM O.ordered_at), R.id
ORDER BY O.client_id,EXTRACT(YEAR FROM O.ordered_at),EXTRACT(MONTH FROM O.ordered_at);"""
cur.execute(query)
data = cur.fetchall()
cur.execute(query1)
data_sum = cur.fetchall()
row_ds = 0
if len(data) > 0:
cell_hasta = len(data) + 7
table_header = [
"Id Cliente", "Id Decsa", "Num. Cliente", "Cve Cte Mayorista",
"Negocio", "Poblacion", "Canal", "Segmento", "Ruta Conq",
"Nombre Conquistador", "Gpo Nivel Margen", "% Bonificacion",
"CL / NUEVO", "Enfriador", "RICE Entregado", "Monto del RICE",
"Fachadas", "Docum. Completa", "Observaciones", "Fecha de alta",
"MES DE ALTA"
]
begin = yearStart
end = int(yearEnd) + 1
for y in range(begin, end):
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for m in range(mStart, mEnd):
table_header.append(months[m][1] + " " + str(y))
if mEnd < qtyMonths:
table_header.append(months[qtyMonths - 1][1] + " " + str(y))
ws.append(table_header)
for row in data:
row_list = list(row)
for y in range(begin, end):
total = 0
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for m in range(mStart, mEnd):
valor = 0
for ind in range(row_ds, len(data_sum)):
if (data_sum[ind][0] == row[0]
and data_sum[ind][1] == months[m][0]
and data_sum[ind][2] == y):
row_ds = ind + 1
valor = data_sum[ind][3]
total = total + valor
break
if int(months[m][0]) == 100:
valor = total
row_list.append((valor))
if mEnd < qtyMonths:
row_list.append((total))
ws.append(row_list)
for column in range(1, len(table_header) + 1):
column_letter = get_column_letter(column)
for rowD in range(5, len(data) + 6):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
if (column > 11):
ws[column_letter + str(rowD)].number_format = '#,#0.0'
#cambiar ancho de columns
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = 20
for col, value in dims.items():
ws.column_dimensions[col].width = value
ws.insert_rows(5)
ws.merge_cells("V5:" + get_column_letter(len(table_header)) + '5')
ws['V5'] = 'HTL'
title = ws['V5']
title.font = Font(size=14, bold=True)
title.alignment = Alignment(horizontal="center", vertical="center")
title.fill = PatternFill("solid", fgColor="2E9AFE")
#title = ws['D7']
#ws.freeze_panes = title
tab = Table(displayName="Table1",
ref="A6:" + get_column_letter(len(table_header)) +
str(cell_hasta))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
for row in ws.iter_rows('A6:' + get_column_letter(len(table_header)) +
'6'):
for cell in row:
cell.style = headOpe
nombre_archivo = "RC-13-3-" + datetime.now().date().strftime(
'%Y%m%d') + ".xlsx"
wb.save(nombre_archivo)
return send_file(
nombre_archivo,
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
True, nombre_archivo)
else:
return "No hay registros"
def run(self):
if self.isRun:
sd_data = ''
get_sd_url = f'http://jeil.jje.hs.kr/jeil-h/0208/board/16996/'
time_er = 0
try:
ua = UserAgent()
header = {'User-Agent': str(ua.chrome)}
req_html = requests.get(get_sd_url, headers=header)
html = req_html.text
soup = BeautifulSoup(html, 'html.parser')
for i in soup.find('tbody').find_all('a'):
if f'{dy.datetime.today().month}월{dy.datetime.today().day}일' in str(
i
) or f'{dy.datetime.today().month}/{dy.datetime.today().day}' in str(
i):
# if f'{7}/{31}' in str(i):
sd_data = i
data_url = str(sd_data.get(
'onclick'))[str(sd_data.get('onclick')).index('(') +
1:str(sd_data.get('onclick')).
index(')')].split(',')[1].replace("'", '')
url_sd = f'http://jeil.jje.hs.kr/jeil-h/0208/board/16996/{data_url}'
# https://jeil.jje.hs.kr/jeil-h/food/2021/03/2/lunch
print(url_sd)
req_html = requests.get(url_sd, headers=header)
html = req_html.text
soup = BeautifulSoup(html, 'html.parser')
pr = soup.find('dd').find_all('a')[1].get('href')
sh_name_day = soup.find('dd').find_all('a')[0].get_text(
)[soup.find('dd').find_all('a')[0].get_text().index("("):soup.
find('dd').find_all('a')[0].get_text().index(")") + 1]
sh_name = f'{dy.datetime.today().month}.{dy.datetime.today().day}{sh_name_day}'
# sh_name = '날짜'
# print(sh_name)
preview_url = f'http://jeil.jje.hs.kr{pr}'
urllib.request.urlretrieve(preview_url, 'sd.xlsx')
data = load_workbook("sd.xlsx", data_only=True)
ds = data.active
wb = Workbook()
ws = wb.active
self.st = 0
# print(ds.cell(row=10, column=29).value)
# print(len(ds.cell(row=10, column=29).value))
# print(type(ds.cell(row=10, column=29).value))
if ds.cell(row=10, column=29).value == None or ds.cell(
row=10, column=29).value == ' ' or ds.cell(
row=10, column=29) == ' ' or ds.cell(
row=10, column=29).value == '\n':
print(1)
self.st = 1
for i in range(2, 4):
for j in range(3, 11 - self.st):
ws.cell(row=j - 2,
column=i - 1).value = ds.cell(row=j,
column=i).value
ws.cell(row=j - 2,
column=i - 1).font = Font(name='맑은 고딕',
size=16,
bold=True)
ws.cell(row=j - 2, column=i - 1).alignment = Alignment(
horizontal='center',
vertical='center',
wrapText=True)
ws.column_dimensions['B'].width = 20
for i in range(29, 45):
for j in range(3, 11 - self.st):
if j != 10:
ws.row_dimensions[j].height = 74
if ds.cell(row=j, column=i).value == None:
ws.cell(row=j - 2, column=i - 26).value = str(
ws.cell(row=j - 2,
column=i - 27).value)[0:2] + str(
ws.cell(row=j - 2,
column=i - 27).value)[2:]
else:
ws.cell(row=j - 2, column=i - 26).value = str(
ds.cell(row=j, column=i).value)[0:2] + str(
ds.cell(row=j, column=i).value)[2:]
ws.cell(row=j - 2, column=i -
26).alignment = Alignment(horizontal='center',
vertical='center',
wrapText=True)
ws.cell(row=j - 2,
column=i - 26).font = Font(name='맑은 고딕',
size=14,
bold=True)
wb.save(f"시간표 {sh_name}.xlsx")
except AttributeError:
sh_name = ''
print("시간표 업데이트 안됨")
time_er = 1
time_table_data = [time_er, sh_name]
self.threadEvent.emit(time_table_data)
self.isRun = False
def flush_writer(self):
#pylint:disable=protected-access,too-many-statements
col_scale = 0.8
bold_font = Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign='baseline',
underline='none',
strike=False,
color='FF000000')
alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
subtitle_fill = PatternFill(fill_type=FILL_SOLID, fgColor='FFEEECE2')
subtitle_font = Font(name='Calibri',
size=10,
bold=False,
italic=False,
vertAlign='baseline',
underline='none',
strike=False,
color='FF000000')
subtitle_alignment = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
for col_num in range(1, self.depth + 1):
max_length = 0
for row in self.wsheet.iter_rows(min_row=3,
min_col=col_num,
max_col=col_num):
for cell in row:
if cell.value:
max_length = max(len(cell.value), max_length)
if max_length > 60:
max_length = 60
col_idx = chr(ord('A') + col_num - 1)
self.wsheet.column_dimensions[col_idx].width = \
max_length * col_scale
# Implementation Note: We style the cells here instead of the rows
# otherwise opening the file on Excel leads to weird background coloring
# (LibreOffice looks fine).
for col in [
chr(ord('A') + x)
for x in range(0, self.depth + len(self.headings))
]:
cell = self.wsheet['%s2' % col]
cell.fill = subtitle_fill
cell.font = subtitle_font
cell.border = self.border
cell.alignment = subtitle_alignment
# Write out the Excel file.
content = io.BytesIO()
self.wbook.save(content)
content.seek(0)
return content
excel_writer.save()
excel_writer.close()
# Для склееного файла почему-то не катит предыдущий вариант форматирования массива данных, поэтому делаем так ->
# Экспортируем выгруженные данные в xlsx
df_Four_x_stor_INT.to_excel(path_inter, index=False)
# Создаем шаблоны стилей
border = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
align_head = Alignment(horizontal='center',
vertical='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=True,
indent=0)
align_cell = Alignment(horizontal='right',
vertical='center',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
# Форматирование Excel
def exstyle(path):
# Открываем рабочую страницу основного отчета
wb = load_workbook(path)
ws = wb.active
def StartTableExtract(img_path):
####################################
############ main start ############
####################################
#print(cv2.__version__)
img_file = img_path
img = cv2.imread(img_file, cv2.IMREAD_GRAYSCALE)
rows, cols = img.shape # 이미지의 가로, 세로 길이
if rows > 2000 and cols > 2000:
img = cv2.resize(img, None, fx=0.4, fy=0.4, interpolation=cv2.INTER_AREA)
rows, cols = img.shape # 이미지의 가로, 세로 길이
gray = cv2.adaptiveThreshold(~img, 255, cv2.ADAPTIVE_THRESH_GAUSSIAN_C, cv2.THRESH_BINARY, 15, -2)
block_point = GetBlockList(gray, rows, cols) # <-- 완성
#print(block_point)
#########여기서부턴 엑셀에 넣는 거 ####### 함수 아직 안만들었음
######################################
wb = Workbook()
ws = wb.active
thin = Side(border_style="thin", color="000000")
# double = Side(border_style="double", color="ff0000")
border = Border(top=thin, left=thin, right=thin, bottom=thin)
# font = Font(b=True, color="FF0000")
al = Alignment(horizontal="center", vertical="center")
# wb.save("styled.xlsx")
matching_col = ['A', 'B', 'C', 'D', 'E', 'F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF','AG','AH','AI','AJ','AK']
img_list = []
#
# ws.merge_cells(start_row=2, start_column=4, end_row=8, end_column=6)
numb=0
for i in block_point:
ws.merge_cells(start_row=i[1]+1, start_column=i[0]+1, end_row=i[3], end_column=i[2])
# print(numb)
# numb = numb + 1
merge_string = matching_col[i[0]] + str(i[1] + 1) + ':' + matching_col[i[2] - 1] + str(i[3])
style_range(ws, merge_string, border=border, alignment=al)
cell = ws.cell(row=i[1] + 1, column=i[0] + 1)
cell.border = border
# # 자르는 이미지 범위 설정
crop_img = img[int(i[5]) - 2 : int(i[7]) - 1, int(i[4]) + 3:int(i[6]) - 3]
padding_crop_img = ImgtoPadding(crop_img, 10)
crop_position = str(int(i[0])) + "/" + str(int(i[1])) + "/" + str(int(i[2])) + "/" + str(int(i[3]))
StoreFormat = [padding_crop_img, crop_position]
img_list.append(StoreFormat)
# 이미지 저장
#crop_name = "./OpenCV/res/image" + str(int(i[0])) + str(int(i[1])) + str(int(i[2])) + str(int(i[3])) + '.png'
#
#img_result = cv2.resize(padding_crop_img, None, fx=2, fy=2, interpolation=cv2.INTER_CUBIC)
#cv2.imwrite(crop_name, img_result)
# # print(crop_name)
# cv2.imshow(crop_name, crop_img)
cv2.waitKey(0)
cv2.destroyAllWindows()
wb.save("result.xlsx")
return img_list
def exportar(form):
ruta = QFileDialog.getOpenFileName(form,
u"Seleccionar fichero de facturación",
"", "Ficheros Excel (*.xls *.xlsx)")
if ruta != "":
wb = openpyxl.load_workbook(unicode(ruta))
nhoja, ok = QInputDialog.getText(
form, "Nombre de la nueva hoja para LIQUIDADAS",
"Introduzca nombre:")
hj = wb.create_sheet(str(nhoja) + " LIQ")
bg = PatternFill(fill_type='solid',
start_color='FF8C00',
end_color='FF8C00')
ag = Alignment(horizontal='center', vertical='center')
borde = 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'))
fuente = Font(name='Calibri', size=10, bold=True, color='000000')
for c in range(1, form.tabla_2.columnCount(), 1):
d = hj.cell(row=1,
column=c,
value=str(form.tabla_2.horizontalHeaderItem(c).text()))
d.fill = bg
d.alignment = ag
d.border = borde
d.font = fuente
anchos = [('A', 9.28515625), ('B', 25.85546875), ('C', 23.28515625),
('D', 9.85546875), ('E', 40.42578125), ('F', 23.28515625),
('G', 13.85546875), ('H', 11.0), ('I', 11.28515625),
('J', 11.5703125), ('K', 9.42578125), ('L', 9.42578125),
('M', 15.7109375), ('N', 13.5703125), ('O', 19.42578125),
('P', 18.42578125), ('Q', 46.0), ('R', 11.140625),
('S', 17.5703125), ('T', 8.85546875), ('U', 20.85546875)]
for columna, ancho in anchos:
hj.column_dimensions[columna].width = ancho
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10)
contador = 1
for fila in range(form.tabla_2.rowCount()):
if form.tabla_2.cellWidget(fila, 0).idx() == 0:
contador += 1
fondoverde = PatternFill(fill_type='solid',
start_color='E2EFDA',
end_color='E2EFDA')
for columna in range(1, form.tabla_2.columnCount()):
if form.tabla_2.item(fila, columna) is not None:
print contador, columna, str(
form.tabla_2.item(fila, columna).text())
d = hj.cell(row=contador,
column=columna,
value=str(
form.tabla_2.item(fila,
columna).text()))
else:
d = hj.cell(row=contador, column=columna, value="")
d.border = borde
d.font = fuente
for col in hj.columns:
if col[0].column in ['I', 'J', 'L', 'M', 'N', 'O', 'P']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "#,##0.00"
except:
pass
elif col[0].column in ['K']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "0.0000"
except:
pass
totalfilas = contador
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10, bold=True)
bg = PatternFill(fill_type='solid',
start_color='FF8C00',
end_color='FF8C00')
a = hj.cell(row=totalfilas + 1, column=15, value="CON IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 1, column=16, value="SIN IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=17,
value="TOTAL FACTURADO LIQUIDADAS")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=15,
value="=SUM(O2:O" + str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
a = hj.cell(row=totalfilas + 2,
column=16,
value="=SUM(P2:P" + str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
# ------------------------------------NO LIQUIDADAS-------------------------------------------------------------
hj = wb.create_sheet(str(nhoja) + " NLQ")
bg = PatternFill(fill_type='solid',
start_color='FF8C00',
end_color='FF8C00')
ag = Alignment(horizontal='center', vertical='center')
borde = 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'))
fuente = Font(name='Calibri', size=10, bold=True, color='000000')
for c in range(1, form.tabla_2.columnCount(), 1):
d = hj.cell(row=1,
column=c,
value=str(form.tabla_2.horizontalHeaderItem(c).text()))
d.fill = bg
d.alignment = ag
d.border = borde
d.font = fuente
anchos = [('A', 9.28515625), ('B', 25.85546875), ('C', 23.28515625),
('D', 9.85546875), ('E', 40.42578125), ('F', 23.28515625),
('G', 13.85546875), ('H', 11.0), ('I', 11.28515625),
('J', 11.5703125), ('K', 9.42578125), ('L', 9.42578125),
('M', 15.7109375), ('N', 13.5703125), ('O', 19.42578125),
('P', 18.42578125), ('Q', 46.0), ('R', 11.140625),
('S', 17.5703125), ('T', 8.85546875), ('U', 20.85546875)]
for columna, ancho in anchos:
hj.column_dimensions[columna].width = ancho
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10)
contador = 1
for fila in range(form.tabla_2.rowCount()):
if form.tabla_2.cellWidget(
fila, 0).idx() == 1 or form.tabla_2.cellWidget(
fila, 0).idx() == 3:
contador += 1
fondoverde = PatternFill(fill_type='solid',
start_color='E2EFDA',
end_color='E2EFDA')
for columna in range(1, form.tabla_2.columnCount()):
if form.tabla_2.item(fila, columna) is not None:
print contador, columna, str(
form.tabla_2.item(fila, columna).text())
d = hj.cell(row=contador,
column=columna,
value=str(
form.tabla_2.item(fila,
columna).text()))
else:
d = hj.cell(row=contador, column=columna, value="")
d.border = borde
d.font = fuente
for col in hj.columns:
if col[0].column in ['I', 'J', 'L', 'M', 'N', 'O', 'P']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "#,##0.00"
except:
pass
elif col[0].column in ['K']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "0.0000"
except:
pass
totalfilas = contador
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10, bold=True)
bg = PatternFill(fill_type='solid',
start_color='FF8C00',
end_color='FF8C00')
a = hj.cell(row=totalfilas + 1, column=15, value="CON IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 1, column=16, value="SIN IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=17,
value="TOTAL FACTURADO NO LIQUIDADAS")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=15,
value="=SUM(O2:O" + str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
a = hj.cell(row=totalfilas + 2,
column=16,
value="=SUM(P2:P" + str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
#------------------------------------- INC. PADRON -------------------------------------------------------------
bg = PatternFill(fill_type='solid',
start_color='3CB371',
end_color='3CB371')
contador = totalfilas + 5
iniciocuenta = totalfilas + 6
for c in range(1, form.tabla_2.columnCount(), 1):
d = hj.cell(row=contador,
column=c,
value=str(form.tabla_2.horizontalHeaderItem(c).text()))
d.fill = bg
d.alignment = ag
d.border = borde
d.font = fuente
anchos = [('A', 9.28515625), ('B', 25.85546875), ('C', 23.28515625),
('D', 9.85546875), ('E', 40.42578125), ('F', 23.28515625),
('G', 13.85546875), ('H', 11.0), ('I', 11.28515625),
('J', 11.5703125), ('K', 9.42578125), ('L', 9.42578125),
('M', 15.7109375), ('N', 13.5703125), ('O', 19.42578125),
('P', 18.42578125), ('Q', 46.0), ('R', 11.140625),
('S', 17.5703125), ('T', 8.85546875), ('U', 20.85546875)]
for columna, ancho in anchos:
hj.column_dimensions[columna].width = ancho
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10)
for fila in range(form.tabla_2.rowCount()):
if form.tabla_2.cellWidget(fila, 0).idx() == 2:
contador += 1
fondoverde = PatternFill(fill_type='solid',
start_color='E2EFDA',
end_color='E2EFDA')
for columna in range(1, form.tabla_2.columnCount()):
if form.tabla_2.item(fila, columna) is not None:
print contador, columna, str(
form.tabla_2.item(fila, columna).text())
d = hj.cell(row=contador,
column=columna,
value=str(
form.tabla_2.item(fila,
columna).text()))
else:
d = hj.cell(row=contador, column=columna, value="")
d.border = borde
d.font = fuente
for col in hj.columns:
if col[0].column in ['I', 'J', 'L', 'M', 'N', 'O', 'P']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "#,##0.00"
except:
pass
elif col[0].column in ['K']:
for cell in col:
try:
a = float(cell.value)
cell.value = a
cell.number_format = "0.0000"
except:
pass
totalfilas = contador
borde = Border(left=Side(border_style='thin', color='00000000'),
right=Side(border_style='thin', color='00000000'),
top=Side(border_style='thin', color='00000000'),
bottom=Side(border_style='thin', color='00000000'))
fuente = Font(name='Calibri', size=10, bold=True)
bg = PatternFill(fill_type='solid',
start_color='FF8C00',
end_color='FF8C00')
a = hj.cell(row=totalfilas + 1, column=15, value="CON IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 1, column=16, value="SIN IVA")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=17,
value="TOTAL FACTURADO INCORPORACIÓN PADRÓN")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a = hj.cell(row=totalfilas + 2,
column=15,
value="=SUM(O" + str(iniciocuenta) + ":O" +
str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
a = hj.cell(row=totalfilas + 2,
column=16,
value="=SUM(P" + str(iniciocuenta) + ":P" +
str(totalfilas) + ")")
a.fill = bg
a.font = fuente
a.border = borde
a.alignment = Alignment(horizontal="center")
a.number_format = "#,##0.00"
try:
wb.save(unicode(ruta))
QMessageBox.information(form, "Guardado",
u"Guardado correctamente")
except IOError as e:
if e.errno == 13:
QMessageBox.critical(form, "Error",
u"El fichero de destino está en uso")
def add_style_nqe(nqe_dataframe, PATH, dict_t0):
wb = load_workbook(PATH)
ws = wb['NQE Biote']
nb_rows, nb_columns = nqe_dataframe.shape
header_row = '4'
header_columns = [get_column_letter(col_idx) for col_idx in list(range(2, nb_columns + 2))]
borders = Border(left=Side(border_style='thin', color='FFFFFF'),
right=Side(border_style='thin', color='FFFFFF'),
top=Side(border_style='thin', color='FFFFFF'),
bottom=Side(border_style='thin', color='FFFFFF'))
for letter in [get_column_letter(col_idx) for col_idx in range(1, nb_columns+5)]:
for number in range(1, nb_rows+7):
ws[letter+str(number)].border = borders
t0_threshold_list = QueryScript(f" SELECT sandre, concentration_t0_max FROM {env.DATABASE_TREATED}.r3 WHERE version= {env.CHOSEN_VERSION()}").execute()
t0_threshold = {}
for sandre, concentration in t0_threshold_list :
if concentration :
t0_threshold[sandre] = concentration
## UNIT
[unit_crustacean, sandre_crustacean, NQE_crustacean] = chemistry.get_unit_NQE(elements_crustacean.keys())
parameter_crustacean = [elements_crustacean[element] for element in elements_crustacean]
[unit_fish, sandre_fish, NQE_fish] = chemistry.get_unit_NQE(elements_fish.keys())
parameter_fish = [elements_fish[element] for element in elements_fish]
index = 0
sandre_checked = sandre_crustacean
unit_checked = unit_crustacean
for letter in header_columns[5:]:
index =None
if ws[letter + '4'].value and int(ws[letter + '4'].value) in sandre_crustacean:
index = sandre_crustacean.index(int(ws[letter + '4'].value))
if not index:
index = sandre_crustacean.index(ws[letter + '4'].value)
sandre_checked = sandre_crustacean
parameter_checked = parameter_crustacean
unit_checked = unit_crustacean
elif ws[letter + '4'].value and int(ws[letter + '4'].value) in sandre_fish:
index = sandre_fish.index(int(ws[letter + '4'].value))
if not index:
index = sandre_fish.index(ws[letter + '4'].value)
sandre_checked = sandre_fish
parameter_checked = parameter_fish
unit_checked = unit_fish
if index!=None:
ws[letter + '2'].value = unit_checked[index]
ws[letter + '3'].value = sandre_checked[index]
ws[letter + '4'].value = parameter_checked[index]
## HEADER STYLE ##
ws['B2'].value = 'Campagne'
ws['C2'].value = '#'
ws['D2'].value = 'Station de mesure'
ws['E2'].value = 'Code agence'
header_cells = [c+header_row for c in header_columns]
header_font = Font(size=8, bold=True, name='Arial')
header_alignment_rotate = Alignment(horizontal='center', vertical='bottom', text_rotation=90)
header_alignment_no_rotate = Alignment(horizontal='center', vertical='bottom')
borders = Border(left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
ws.column_dimensions['B'].width=3
ws.column_dimensions['C'].width=3
ws.column_dimensions['D'].width=30
ws.column_dimensions['E'].width=8
for letter in header_columns[:4]:
for number in range(2,5):
ws[letter+str(number)].border = borders
ws[letter+str(number)].font = header_font
ws['B2'].alignment = header_alignment_rotate
ws['C2'].alignment = header_alignment_no_rotate
ws['D2'].alignment = header_alignment_no_rotate
ws['E2'].alignment = header_alignment_rotate
header_font = Font(size=8, name='Arial')
for letter in header_columns[4:]:
if (ws[letter+'5'].value !=None and ws[letter+'5'].value !='') :
ws.column_dimensions[letter].width=6
ws[letter+'4'].alignment = header_alignment_rotate
ws[letter+'4'].font = header_font
ws[letter+'3'].alignment = header_alignment_no_rotate
ws[letter+'3'].font = header_font
ws[letter+'2'].alignment = header_alignment_no_rotate
ws[letter+'2'].font = header_font
else :
ws.column_dimensions[letter].width=2
## ADD T0
t0_mp = []
for mp in dict_t0:
if not dict_t0[mp]['code_t0_id'] in t0_mp and dict_t0[mp]['code_t0_id']:
t0_mp.append(dict_t0[mp]['code_t0_id'])
if len(t0_mp) > 1 or len(t0_mp) == 0:
query_tuple_t0 = tuple(t0_mp)
else:
query_tuple_t0 = f"({t0_mp[0]})"
reference_dict = {}
if len(t0_mp):
reference_result = QueryScript(f"SELECT reference, id FROM {env.DATABASE_RAW}.Measurepoint WHERE id IN {query_tuple_t0}").execute()
else:
reference_result = []
for reference in reference_result:
reference_dict.update({reference[1]:reference[0]})
t0_result=[]
if len(t0_mp):
t0_result = QueryScript(f"SELECT sandre, prefix, value, Pack.measurepoint_id, Measurepoint.reference FROM {env.DATABASE_RAW}.Analysis JOIN {env.DATABASE_RAW}.Pack ON Pack.id= Analysis.pack_id JOIN {env.DATABASE_RAW}.Measurepoint ON Pack.measurepoint_id=Measurepoint.id WHERE Pack.measurepoint_id IN {query_tuple_t0};").execute()
dict_t0_result= {}
for element in t0_result:
if not element[3] in dict_t0_result:
dict_t0_result.update({element[3]: {element[0]:element[1] + str(element[2]) if element[1] else str(element[2]), 'reference': element[4]}})
else :
dict_t0_result[element[3]][element[0]] = element[1] + str(element[2]) if element[1] else str(element[2])
t0_font = Font(size=6, name='Arial')
t0_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'))
t0_not_valid = Border(left=Side(border_style='medium', color='FF0000'),
right=Side(border_style='medium', color='FF0000'),
top=Side(border_style='medium', color='FF0000'),
bottom=Side(border_style='medium', color='FF0000'))
body_alignment = Alignment(horizontal='center', vertical='center')
for index, t0 in enumerate(t0_mp):
ws['B'+str(nb_rows+5+index)].font = t0_font
ws['B'+str(nb_rows+5+index)].border = t0_border
ws['C'+str(nb_rows+5+index)].font = t0_font
ws['C'+str(nb_rows+5+index)].border = t0_border
ws['D'+str(nb_rows+5+index)].font = t0_font
ws['D'+str(nb_rows+5+index)].border = t0_border
if t0 in dict_t0_result :
ws['D'+str(nb_rows+5+index)].value = dict_t0_result[t0]['reference']
else :
ws['D'+str(nb_rows+5+index)].value = reference_dict[t0]
ws['E'+str(nb_rows+5+index)].font = t0_font
ws['E'+str(nb_rows+5+index)].border = t0_border
for letter in header_columns[5:]:
sandre = ws[letter +'3'].value
if t0 in dict_t0_result and str(sandre) in dict_t0_result[t0]:
ws[letter+str(nb_rows+5+index)].value = dict_t0_result[t0][str(sandre)]
if str(sandre) in t0_threshold and str(dict_t0_result[t0][str(sandre)])[0]!='<' and float(dict_t0_result[t0][str(sandre)]) > t0_threshold[str(sandre)] :
ws[letter+str(nb_rows+5+index)].border = t0_not_valid
else :
ws[letter+str(nb_rows+5+index)].border = t0_border
ws[letter+str(nb_rows+5+index)].font = t0_font
ws[letter+str(nb_rows+5+index)].alignment = body_alignment
elif sandre != None :
ws[letter+str(nb_rows+5+index)].value = 'ND'
ws[letter+str(nb_rows+5+index)].font = t0_font
ws[letter+str(nb_rows+5+index)].border = t0_border
ws[letter+str(nb_rows+5+index)].alignment = body_alignment
## BODY STYLE ##
body_rows = [str(r) for r in list(range(5, nb_rows + 5 + len(t0_mp)))]
body_columns = header_columns[5:]
body_font = Font(size=6, name='Arial')
body_fill_ok = PatternFill(fill_type='solid', start_color='318CE7', end_color='318CE7')
body_fill_nd = PatternFill(fill_type='solid', start_color='FFFFFF', end_color='FFFFFF')
body_fill_not_ok = PatternFill(fill_type='solid', start_color='BB0B0B', end_color='BB0B0B')
body_alignment = Alignment(horizontal='center', vertical='center')
for column in header_columns:
for row in body_rows:
if column in ['B','C','D','E']:
ws[column+row].border = borders
ws[column+row].font = body_font
for column in body_columns:
sandre_checked = ws[column+'3'].value
if sandre_checked!='' and sandre_checked!=None:
try :
index = sandre_crustacean.index(sandre_checked)
threshold = NQE_crustacean[index]
except :
threshold = ''
for row in body_rows:
cell = ws[column+row]
value = cell.value
cell.font = body_font
if value!=None :
cell.alignment = body_alignment
if (value!="ND" and value!='0.0' and threshold!='') and ((value!='' and value[0]=='<') or float(value)<threshold):
cell.fill = body_fill_ok
elif (value!="ND" and value!='0.0' and threshold!='' and float(value)>=threshold):
cell.fill = body_fill_not_ok
for index,mp in enumerate(dict_t0):
try :
if ws[header_columns[-1] + str(index+5)].value and ws[header_columns[-1] + str(index+5)].value in dict_t0:
index_t0_associated = t0_mp.index(dict_t0[ws[header_columns[-1] + str(index+5)].value]['code_t0_id'])
for column in header_columns[5:]:
t0_ok = True if ws[column + str(5+nb_rows+index_t0_associated)].border != t0_not_valid else False
if ws[column + str(5+nb_rows+index_t0_associated)].value!= None and ws[column + str(5+nb_rows+index_t0_associated)].value!= '':
if not t0_ok :
ws[column + str(5+index)].border = t0_not_valid
ws[column + str(5+index)].font = body_font
else :
ws[column + str(5+index)].border = borders
ws[column + str(5+index)].font = body_font
except ValueError :
for column in header_columns[5:]:
ws[column + str(5+index)].border = borders
ws[column + str(5+index)].font = body_font
ws.delete_cols(len(header_columns)+1,1)
for letter in header_columns[5:]:
for number in range(5, nb_rows+21):
ws[letter + str(number)].value = str(ws[letter + str(number)].value).replace(".", ",") if ws[letter + str(number)].value else ''
ws.freeze_panes = ws["F6"]
wb.save(PATH)
wb.close()
wb = load_workbook(PATH)
ws = wb['NQE Biote']
## Add threshold table
ws.insert_rows(1)
ws.insert_rows(1)
ws["F2"].value = "Seuil NQE"
ws.column_dimensions['F'].width=10
for letter in header_columns[5:]:
if ws[letter + "5"].value :
try :
index_found = sandre_crustacean.index(ws[letter + "5"].value)
NQE_found = NQE_crustacean[index_found]
ws[letter+"1"].border = borders
ws[letter+"2"].value = NQE_found
ws[letter+"2"].border = borders
except ValueError :
try :
index_found = sandre_fish.index(ws[letter + "5"].value)
NQE_found = NQE_fish[index_found]
ws[letter+"1"].border = borders
ws[letter+"2"].value = NQE_found
ws[letter+"2"].border = borders
except ValueError :
continue
## Merge unit
current_unit = ws['G4'].value
first_letter = 'G'
last_letter = 'G'
index = 6
while index <len(header_columns):
while index <len(header_columns) and ws[header_columns[index] + '4'].value == current_unit :
last_letter = header_columns[index]
index +=1
ws.merge_cells(first_letter + '4:'+last_letter+'4')
if index<len(header_columns):
first_letter = last_letter = header_columns[index]
current_unit = ws[first_letter +'4'].value
index+=1
first_letter = 'G'
last_letter = 'G'
index = 6
while index <len(header_columns)-1:
while index <len(header_columns) and ws[header_columns[index] + '5'].value in elements_crustacean or ws[header_columns[index] + '5'].value in elements_fish :
last_letter = header_columns[index]
index +=1
if ws[header_columns[index-1] + '5'].value in elements_crustacean :
ws[first_letter+'1'].value = 'NQE Crustacé'
ws.merge_cells(first_letter + '1:'+last_letter+'1')
elif ws[header_columns[index-1] + '5'].value in elements_fish :
ws[first_letter+'1'].value = 'NQE Poisson'
ws.merge_cells(first_letter + '1:'+last_letter+'1')
if index<len(header_columns)-1:
index+=1
first_letter = last_letter = header_columns[index]
ws.merge_cells('B4:B6')
ws.merge_cells('C4:C6')
ws.merge_cells('D4:D6')
ws.merge_cells('E4:E6')
wb.save(PATH)
wb.close()
def dataWrite(self,cname,tables,savepath):
txtUnqualifiedlv=self.txtUnqualifiedlv.get()
txtVerifyTime=self.txtVerifyTime.get()
txtDealWay=self.txtDealWay.get()
txtExplanation=self.txtExplanation.get()
txtConfirmPerson=self.txtConfirmPerson.get()
txtTestPerson=self.txtTestPerson.get()
txtStated=self.txtStated.get()
wb=openpyxl.Workbook()#创建Excel文件对象
#UR
ws=wb.active #获取第一个sheet
ws.title='UR'
ws["A1"]='序号'
ws["B1"]='文件名'
ws["C1"]='Variable'
ws["D1"]='Undefine'
ws["E1"]='Reference'
ws["F1"]='详细'
ws["G1"]='不合格项等级'
ws["H1"]='验证时间'
ws["I1"]='处理方式'
ws["J1"]='研发说明内容'
ws["K1"]='研发确认'
ws["L1"]='测试者'
ws["M1"]='状态'
'''设置列宽'''
ws.column_dimensions['A'].width = 8
ws.column_dimensions['B'].width = 26
ws.column_dimensions['C'].width = 53
ws.column_dimensions['D'].width = 9
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 59
ws.column_dimensions['G'].width = 8
ws.column_dimensions['H'].width = 10
ws.column_dimensions['I'].width = 8
ws.column_dimensions['J'].width = 19
ws.column_dimensions['K'].width = 8
ws.column_dimensions['L'].width = 8
ws.column_dimensions['M'].width = 8
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+'1'].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+'1'].font=Font(name='宋体', size=11)
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'))
ws[nn+'1'].border=border
i=2
for item in tables[0]:
ws["A"+str(i)]=i-1
ws["B"+str(i)]=cname
ws["C"+str(i)]=item[0]
ws["D"+str(i)]=item[1]
ws["E"+str(i)]=item[2]
ws["F"+str(i)]=item[3]
ws["G"+str(i)]=txtUnqualifiedlv
ws["H"+str(i)]=txtVerifyTime
ws["I"+str(i)]=txtDealWay
ws["J"+str(i)]=txtExplanation
ws["K"+str(i)]=txtConfirmPerson
ws["L"+str(i)]=txtTestPerson
ws["M"+str(i)]=txtStated
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+str(i)].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+str(i)].font=Font(name='宋体', size=11)
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'))
ws[nn+str(i)].border=border
i+=1
#DU
ws=wb.create_sheet(title='DU')
ws["A1"]='序号'
ws["B1"]='文件名'
ws["C1"]='Variable'
ws["D1"]='Define'
ws["E1"]='Undefine'
ws["F1"]='详细'
ws["G1"]='不合格项等级'
ws["H1"]='验证时间'
ws["I1"]='处理方式'
ws["J1"]='研发说明内容'
ws["K1"]='研发确认'
ws["L1"]='测试者'
ws["M1"]='状态'
'''设置列宽'''
ws.column_dimensions['A'].width = 8
ws.column_dimensions['B'].width = 26
ws.column_dimensions['C'].width = 53
ws.column_dimensions['D'].width = 9
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 59
ws.column_dimensions['G'].width = 8
ws.column_dimensions['H'].width = 10
ws.column_dimensions['I'].width = 8
ws.column_dimensions['J'].width = 19
ws.column_dimensions['K'].width = 8
ws.column_dimensions['L'].width = 8
ws.column_dimensions['M'].width = 8
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+'1'].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+'1'].font=Font(name='宋体', size=11)
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'))
ws[nn+'1'].border=border
i=2
for item in tables[1]:
ws["A"+str(i)]=i-1
ws["B"+str(i)]=cname
ws["C"+str(i)]=item[0]
ws["D"+str(i)]=item[1]
ws["E"+str(i)]=item[2]
ws["F"+str(i)]=item[3]
ws["G"+str(i)]=txtUnqualifiedlv
ws["H"+str(i)]=txtVerifyTime
ws["I"+str(i)]=txtDealWay
ws["J"+str(i)]=txtExplanation
ws["K"+str(i)]=txtConfirmPerson
ws["L"+str(i)]=txtTestPerson
ws["M"+str(i)]=txtStated
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+str(i)].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+str(i)].font=Font(name='宋体', size=11)
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'))
ws[nn+str(i)].border=border
i+=1
#DD
ws=wb.create_sheet(title='DD')
ws["A1"]='序号'
ws["B1"]='文件名'
ws["C1"]='Variable'
ws["D1"]='Define'
ws["E1"]='Redefine'
ws["F1"]='详细'
ws["G1"]='不合格项等级'
ws["H1"]='验证时间'
ws["I1"]='处理方式'
ws["J1"]='研发说明内容'
ws["K1"]='研发确认'
ws["L1"]='测试者'
ws["M1"]='状态'
'''设置列宽'''
ws.column_dimensions['A'].width = 8
ws.column_dimensions['B'].width = 26
ws.column_dimensions['C'].width = 53
ws.column_dimensions['D'].width = 9
ws.column_dimensions['E'].width = 10
ws.column_dimensions['F'].width = 59
ws.column_dimensions['G'].width = 8
ws.column_dimensions['H'].width = 10
ws.column_dimensions['I'].width = 8
ws.column_dimensions['J'].width = 19
ws.column_dimensions['K'].width = 8
ws.column_dimensions['L'].width = 8
ws.column_dimensions['M'].width = 8
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+'1'].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+'1'].font=Font(name='宋体', size=11)
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'))
ws[nn+'1'].border=border
i=2
for item in tables[2]:
ws["A"+str(i)]=i-1
ws["B"+str(i)]=cname
ws["C"+str(i)]=item[0]
ws["D"+str(i)]=item[1]
ws["E"+str(i)]=item[2]
ws["F"+str(i)]=item[3]
ws["G"+str(i)]=txtUnqualifiedlv
ws["H"+str(i)]=txtVerifyTime
ws["I"+str(i)]=txtDealWay
ws["J"+str(i)]=txtExplanation
ws["K"+str(i)]=txtConfirmPerson
ws["L"+str(i)]=txtTestPerson
ws["M"+str(i)]=txtStated
'''设置单元格格式'''
for nn in ['A','B','C','D','E','F','G','H','I','J','K','L','M']:
ws[nn+str(i)].alignment = Alignment(horizontal='center', vertical='center',wrap_text=True) #水平、竖直对齐 自动换行
ws[nn+str(i)].font=Font(name='宋体', size=11)
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'))
ws[nn+str(i)].border=border
i+=1
wb.save(savepath+'\\'+cname+ "-静态分析数据流分析问题报告单.xlsx") #保存
def to_xls_xindian(to_xls_name, ws_data_M, ws_data_F, M_title, F_title):
wb = Workbook() # 实例化
ws = wb.active # 激活一个sheet 默认0 sheet
Table_Title_M = M_title
Table_Title_F = F_title
Title_2 = [
'动物ID', '动物编号', '检测时间', '心率(次/分)', 'P-R间期(s)', 'QRS间期(s)', 'Q-T间期(s)',
'P波(mV)', 'QRS波(mV)', 'T波(mV)', 'ST段(mV)', 'ST段(mV)绝对值'
]
Title_2_width = 10.50
Title_2_height = 30.00
Alig_center = Alignment(horizontal='center',
vertical='center',
wrapText=True) # 双剧中
'''单元格线框颜色设定'''
border_All = Border(
left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'),
)
font_A = Font(name='Times New Roman', size=11)
font_B = Font(name='Times New Roman', size=11, bold=True)
'''合并单元格,并填充数据Title'''
ws['A1'].font = Font(name='Times New Roman', size=22, bold=True)
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=12)
ws.cell(row=1, column=1, value=Table_Title_M)
ws['N1'].font = Font(name='Times New Roman', size=22, bold=True)
ws['N1'].alignment = Alignment(horizontal='center', vertical='center')
ws.merge_cells(start_row=1, start_column=14, end_row=1, end_column=25)
ws.cell(row=1, column=14, value=Table_Title_F)
'''设定行高列宽'''
for i in G_C_M + G_C_F:
ws.column_dimensions[i].width = Title_2_width
ws2_s = ws['{}2'.format(i)]
ws2_s.font = Font(name='Times New Roman', size=11, bold=True)
# ws2_s.font = Font(name=u'宋体')
ws2_s.alignment = Alignment(horizontal='center',
vertical='center',
wrapText=True) # 设定自动换行
for i in range(1, 3): # 第1,2行高
ws.row_dimensions[i].height = Title_2_height
'''填充第2行数据'''
for i in range(1, 13):
ws.cell(row=2, column=i, value=Title_2[i - 1])
ws.cell(
row=2,
column=i,
).border = border_All
ws.cell(row=2, column=i + 13, value=Title_2[i - 1])
ws.cell(row=2, column=i + 13).border = border_All
'''数据操作填充操作'''
# start_num = 3 # 开始行编号
to_len_xlsxM = int(len(ws_data_M) / 3 * 4) + 3 # 应该写入到地excel长度M
to_len_xlsxF = int(len(ws_data_F) / 3 * 4) + 3 # 应该写入到地excel长度F
slM = sorted([i for i in range(3, to_len_xlsxM, 4)] +
[i for i in range(4, to_len_xlsxM, 4)] +
[i for i in range(5, to_len_xlsxM, 4)]) # 生成一个数据列表
slF = sorted([i for i in range(3, to_len_xlsxF, 4)] +
[i for i in range(4, to_len_xlsxF, 4)] +
[i for i in range(5, to_len_xlsxF, 4)]) # 生成一个数据列表
tlM = [i for i in range(6, to_len_xlsxM, 4)] # 生成用于计算的列表列
tlF = [i for i in range(6, to_len_xlsxF, 4)] # 生成用于计算的列表列
for n, i in enumerate(slM):
for l, m in enumerate(range(1, 12)): # 代表1-12
ws.cell(row=i, column=m, value=ws_data_M[n][l])
ws.cell(row=i, column=m).alignment = Alig_center
ws.cell(row=i, column=m).number_format = '0.00'
ws.cell(row=i, column=1).number_format = '0'
ws['L{}'.format(i)] = "=ABS(K{})".format(i)
ws.cell(row=i, column=12).alignment = Alig_center
ws.cell(row=i, column=12).number_format = '0.00'
for n, i in enumerate(slF):
for l, f in enumerate(range(14, 25)): # 代表14-25
ws.cell(row=i, column=f, value=ws_data_F[n][l])
ws.cell(row=i, column=f).alignment = Alig_center
ws.cell(row=i, column=f).number_format = '0.00'
ws.cell(row=i, column=14).number_format = '0'
ws['Y{}'.format(i)] = "=ABS(X{})".format(i)
ws.cell(row=i, column=25).alignment = Alig_center
ws.cell(row=i, column=25).number_format = '0.00'
for i in slM:
for x in range(1, 26):
ws.cell(row=i, column=x).font = font_A
for i in slF:
for x in range(1, 26):
ws.cell(row=i, column=x).font = font_A
'''公式数据列'''
GC_MFM = ['A', 'B', 'D'] # 等于公式列
GC_MFF = ['N', 'O', 'Q']
mean_lM = ['C'] # mean列
mean_lF = ['P']
GC_M = ['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L'] # 均值公式列表
GC_F = ['R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y'] # 均值公式列表
for i in tlM: # 均值公式行
for mean in mean_lM: # mean列
ws['{}{}'.format(mean, i)] = "mean"
for z in GC_MFM: # 等值公式列
ws['{}{}'.format(z, i)] = "={}{}".format(z, i - 1)
for m in GC_M: # 均值公式列M
ws['{}{}'.format(m, i)] = "=AVERAGE({}{}:{}{})".format(
m, i - 3, m, i - 1)
for i in tlF: # 均值公式行
for mean in mean_lF: # mean列
ws['{}{}'.format(mean, i)] = "mean"
for z in GC_MFF: # 等值公式列
ws['{}{}'.format(z, i)] = "={}{}".format(z, i - 1)
for f in GC_F: # 均值公式列F
ws['{}{}'.format(f, i)] = "=AVERAGE({}{}:{}{})".format(
f, i - 3, f, i - 1)
'''加线框居中设定公式行加粗'''
for i in range(3, to_len_xlsxM):
for x in range(1, 26): # 所有行列
ws.cell(row=i, column=x).alignment = Alig_center
ws.cell(row=i, column=x).border = border_All
ws.cell(row=i, column=4).number_format = '0'
ws.cell(row=i, column=17).number_format = '0'
for i in tlM:
for x in range(1, 5):
ws.cell(row=i, column=x).font = font_B
for x in range(14, 18):
ws.cell(row=i, column=x).font = font_B
for x in range(5, 13): # M计算数值列
ws.cell(row=i, column=x).number_format = '0.00'
ws.cell(row=i, column=x).font = font_B
for x in range(18, 26): # F计算数值列
ws.cell(row=i, column=x).number_format = '0.00'
ws.cell(row=i, column=x).font = font_B
'''统计分析导出表格部分'''
'''性别M转换到一个文件'''
s_spss_M = []
s_spss_F = []
for x, row in enumerate(range(6, to_len_xlsxM, 4)): # 从第6行开始步进4获取数据形成列表
'''性别M获取数据'''
A_No_v1 = str(ws.cell(row=row - 3, column=2).value)
Col_3_V = str(ws.cell(row=row - 3, column=3).value)
Col_4_V = str(ws.cell(row=row - 3, column=4).value)
s_spss_M.append([A_No_v1, Col_3_V, Col_4_V])
for y in [5, 6, 7, 8, 9, 10, 11]:
'''E-K列均值 5-11'''
mean_l = np.mean([
ws.cell(row=row - 3, column=y).value,
ws.cell(row=row - 2, column=y).value,
ws.cell(row=row - 1, column=y).value
])
s_spss_M[x].append('{:.2f}'.format(mean_l))
'''l列绝对值 12 '''
abs_l = np.mean([
abs(ws.cell(row=row - 3, column=11).value),
abs(ws.cell(row=row - 2, column=11).value),
abs(ws.cell(row=row - 1, column=11).value)
])
s_spss_M[x].append('{:.2f}'.format(abs_l))
for x, row in enumerate(range(6, to_len_xlsxF, 4)): # 从第6行开始步进4获取数据形成列表
'''性别F获取数据'''
A_No_v2 = str(ws.cell(row=row - 3, column=15).value)
Col_16_V = str(ws.cell(row=row - 3, column=16).value)
Col_17_V = str(ws.cell(row=row - 3, column=17).value)
s_spss_F.append([A_No_v2, Col_16_V, Col_17_V])
for y in [18, 19, 20, 21, 22, 23, 24]:
'''R-X列均值 18-24'''
mean_l = np.mean([
ws.cell(row=row - 3, column=y).value,
ws.cell(row=row - 2, column=y).value,
ws.cell(row=row - 1, column=y).value
])
s_spss_F[x].append('{:.2f}'.format(mean_l))
'''Y列绝对值 25 '''
abs_l = np.mean([
abs(ws.cell(row=row - 3, column=24).value),
abs(ws.cell(row=row - 2, column=24).value),
abs(ws.cell(row=row - 1, column=24).value)
])
s_spss_F[x].append('{:.2f}'.format(abs_l))
df2 = spss_to_df(s_spss_F)
df1 = spss_to_df(s_spss_M)
ws1 = wb.create_sheet(title='心电spssF', index=3)
for r in dataframe_to_rows(df1, index=False, header=True):
ws1.append(r)
ws2 = wb.create_sheet(title='心电spssF', index=3)
for r in dataframe_to_rows(df2, index=False, header=True):
ws2.append(r)
wb.save(to_xls_name)
def excelPersonal(request):
#validar si existe usuario logeado
if 'nombreUsuario' not in request.session:
return render(request, 'seguridad/login.html', {})
else:
#Consultar las Bodegas
cursor = connection.cursor()
query = "SELECT personal_personal.id as id_personal, personal_personal.nombre AS nombre_persona, "
query = query + " personal_personal.telefono, personal_personal.celular, personal_personal.correo, personal_personal.estado, "
query = query + " personal_cargo.nombre AS cargo, personal_areapersonal.nombre AS area, "
#se agrega para mostrar los procesos vinculados
query = query + " areas_procesos.proceso "
query = query + " FROM personal_personal "
query = query + " LEFT JOIN personal_cargo ON personal_personal.id_cargo_id = personal_cargo.id "
#se agrega para mostrar los procesos vinculados
query = query + " LEFT JOIN areas_area_proceso ON areas_area_proceso.personal_id = personal_personal.id "
query = query + " LEFT JOIN areas_procesos ON areas_procesos.id = areas_area_proceso.proceso_id "
query = query + " LEFT JOIN personal_areapersonal ON personal_areapersonal.id = personal_personal.id_areapersonal_id "
query = query + " ORDER BY cargo "
cursor.execute(query)
rows = dictfetchall(cursor)
personal_list = list(rows)
#ESTABLECER BORDES
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
#Creamos el libro de trabajo
wb = Workbook()
#Definimos como nuestra hoja de trabajo, la hoja activa, por defecto la primera del libro
ws = wb.active
ws.title = "Personal"
#En la celda B1 ponemos el texto 'REPORTE DE PERSONAS'
ws.merge_cells('B2:H2')
ws['B2'] = 'Listado de Personal'
ws['B2'].alignment = Alignment(horizontal='center')
#ws.cell(row=2, column=2).border = thin_border
b2 = ws['B2']
b2.font = Font(bold=True, color=colors.DARKBLUE, size=12)
#Creamos los encabezados desde la celda B3 hasta la E3
ws['B3'] = '#'
ws.cell(row=3, column=2).border = thin_border
ws.cell(row=3, column=2).alignment = Alignment(horizontal='center')
b3 = ws['B3']
b3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['C3'] = 'Nombre'
ws.cell(row=3, column=3).border = thin_border
ws.cell(row=3, column=3).alignment = Alignment(horizontal='center')
c3 = ws['C3']
c3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['D3'] = 'Área'
ws.cell(row=3, column=4).border = thin_border
ws.cell(row=3, column=4).alignment = Alignment(horizontal='center')
d3 = ws['D3']
d3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['E3'] = 'Cargo'
ws.cell(row=3, column=5).border = thin_border
ws.cell(row=3, column=5).alignment = Alignment(horizontal='center')
e3 = ws['E3']
e3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['F3'] = 'Proceso'
ws.cell(row=3, column=6).border = thin_border
ws.cell(row=3, column=6).alignment = Alignment(horizontal='center')
f3 = ws['F3']
f3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['G3'] = 'Teléfono'
ws.cell(row=3, column=7).border = thin_border
ws.cell(row=3, column=7).alignment = Alignment(horizontal='center')
g3 = ws['G3']
g3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['H3'] = 'Celular'
ws.cell(row=3, column=8).border = thin_border
ws.cell(row=3, column=8).alignment = Alignment(horizontal='center')
h3 = ws['H3']
h3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['I3'] = 'Correo'
ws.cell(row=3, column=9).border = thin_border
ws.cell(row=3, column=9).alignment = Alignment(horizontal='center')
i3 = ws['I3']
i3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
cont = 4
indice = 1
for personal in personal_list:
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=2).value = indice
ws.cell(row=cont, column=2).border = thin_border
ws.cell(row=cont,
column=2).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=3).value = personal['nombre_persona']
ws.cell(row=cont, column=3).border = thin_border
ws.cell(row=cont,
column=3).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
if personal['area']:
ws.cell(row=cont, column=4).value = personal['area']
else:
ws.cell(row=cont, column=4).value = '--'
ws.cell(row=cont, column=4).border = thin_border
ws.cell(row=cont,
column=4).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=5).value = personal['cargo']
ws.cell(row=cont, column=5).border = thin_border
ws.cell(row=cont,
column=5).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
if personal['proceso']:
ws.cell(row=cont, column=6).value = personal['proceso']
else:
ws.cell(row=cont, column=6).value = '--'
ws.cell(row=cont, column=6).border = thin_border
ws.cell(row=cont,
column=6).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
if personal['telefono']:
ws.cell(row=cont, column=7).value = personal['telefono']
else:
ws.cell(row=cont, column=7).value = '--'
ws.cell(row=cont, column=7).border = thin_border
ws.cell(row=cont,
column=7).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
if personal['celular']:
ws.cell(row=cont, column=8).value = personal['celular']
else:
ws.cell(row=cont, column=8).value = '--'
ws.cell(row=cont, column=8).border = thin_border
ws.cell(row=cont,
column=8).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=9).value = personal['correo']
ws.cell(row=cont, column=9).border = thin_border
ws.cell(row=cont,
column=9).alignment = Alignment(horizontal='center')
if personal['estado'] == "0":
#obtengo celdas de estado inactivo
nombreCell = ws.cell(row=cont, column=3)
telefonoCell = ws.cell(row=cont, column=4)
correoCell = ws.cell(row=cont, column=5)
cargoCell = ws.cell(row=cont, column=6)
celularCell = ws.cell(row=cont, column=7)
areaCell = ws.cell(row=cont, column=8)
procesoCell = ws.cell(row=cont, column=9)
#color rojo a inactivos
nombreCell.font = Font(color=colors.RED)
telefonoCell.font = Font(color=colors.RED)
correoCell.font = Font(color=colors.RED)
cargoCell.font = Font(color=colors.RED)
celularCell.font = Font(color=colors.RED)
areaCell.font = Font(color=colors.RED)
procesoCell.font = Font(color=colors.RED)
cont = cont + 1
indice = indice + 1
#Establecemos el nombre del archivo
nombre_archivo = "Listado_Personal.xlsx"
#Definimos que el tipo de respuesta a devolver es un archivo de microsoft excel
response = HttpResponse(content_type="application/ms-excel")
contenido = "attachment; filename={0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
#ESTABLECER DIMENSIONES A COLUMNAS
ws.column_dimensions["C"].width = 30.0
ws.column_dimensions["D"].width = 20.0
ws.column_dimensions["E"].width = 20.0
ws.column_dimensions["F"].width = 20.0
ws.column_dimensions["G"].width = 20.0
ws.column_dimensions["H"].width = 20.0
ws.column_dimensions["I"].width = 40.0
wb.save(response)
#retorna el archivo excel
return response
def excelCargos(request):
#validar si existe usuario logeado
if 'nombreUsuario' not in request.session:
return render(request, 'seguridad/login.html', {})
else:
#Consultar las Bodegas
cargos = Cargo.objects.all().order_by('nombre')
#ESTABLECER BORDES
thin_border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
#Creamos el libro de trabajo
wb = Workbook()
#Definimos como nuestra hoja de trabajo, la hoja activa, por defecto la primera del libro
ws = wb.active
ws.title = "Cargos"
#En la celda B1 ponemos el texto 'REPORTE DE PERSONAS'
ws.merge_cells('B2:D2')
ws['B2'] = 'Listado de Cargos'
ws['B2'].alignment = Alignment(horizontal='center')
ws.cell(row=2, column=2).border = thin_border
b2 = ws['B2']
b2.font = Font(bold=True, color=colors.DARKBLUE, size=12)
#Creamos los encabezados desde la celda B3 hasta la E3
ws['B3'] = '#'
ws.cell(row=3, column=2).border = thin_border
ws.cell(row=3, column=2).alignment = Alignment(horizontal='center')
b3 = ws['B3']
b3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['C3'] = 'Cargo'
ws.cell(row=3, column=3).border = thin_border
ws.cell(row=3, column=3).alignment = Alignment(horizontal='center')
c3 = ws['C3']
c3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
ws['D3'] = 'Descripción'
ws.cell(row=3, column=4).border = thin_border
ws.cell(row=3, column=4).alignment = Alignment(horizontal='center')
d3 = ws['D3']
d3.font = Font(bold=True, color=colors.DARKBLUE, size=12)
cont = 4
indice = 1
for cargo in cargos:
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=2).value = indice
ws.cell(row=cont, column=2).border = thin_border
ws.cell(row=cont,
column=2).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=3).value = cargo.nombre
ws.cell(row=cont, column=3).border = thin_border
ws.cell(row=cont,
column=3).alignment = Alignment(horizontal='center')
#agregamos la data, borde, alineacion
ws.cell(row=cont, column=4).value = cargo.descripcion
ws.cell(row=cont, column=4).border = thin_border
ws.cell(row=cont,
column=4).alignment = Alignment(horizontal='center')
if cargo.estado == "0":
#obtengo celdas de estado inactivo
nombreCell = ws.cell(row=cont, column=3)
descripcionCell = ws.cell(row=cont, column=4)
#color rojo a inactivos
nombreCell.font = Font(color=colors.RED)
descripcionCell.font = Font(color=colors.RED)
cont = cont + 1
indice = indice + 1
#Establecemos el nombre del archivo
nombre_archivo = "Listado_Cargos.xlsx"
#Definimos que el tipo de respuesta a devolver es un archivo de microsoft excel
response = HttpResponse(content_type="application/ms-excel")
contenido = "attachment; filename={0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
#ESTABLECER DIMENSIONES A COLUMNAS
ws.column_dimensions["C"].width = 30.0
ws.column_dimensions["D"].width = 40.0
ws.column_dimensions["E"].width = 40.0
wb.save(response)
#retorna el archivo excel
return response
def generate_excel(report,
name,
reporting_start_datetime_local,
reporting_end_datetime_local,
period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000')
)
b_border = Border(
bottom=Side(border_style='medium', color='00000000'),
)
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期节约'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 75.0
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (基线-实际) (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = '吨标准煤 (基线-实际) (TCE)'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = '吨二氧化碳排放 (基线-实际) (TCO2E)'
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '节约'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['subtotals_saving'][i], 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgce_saving'] / 1000, 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgco2e_saving'] / 1000, 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '单位面积值'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['subtotals_per_unit_area_saving'][i], 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgco2e_per_unit_area_saving']
/ 1000, 2)
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['total_in_kgce_per_unit_area_saving'] / 1000, 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates_saving'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates_saving'][i] is not None else '-'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rate_in_kgce_saving'] * 100, 2)) + '%' \
if reporting_period_data['increment_rate_in_kgce_saving'] is not None else '-'
col = chr(ord(col) + 1)
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rate_in_kgco2e_saving'] * 100, 2)) + '%' \
if reporting_period_data['increment_rate_in_kgco2e_saving'] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 吨标准煤(TCE)占比'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '吨标准煤(TCE)占比'
current_row_number += 1
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgce_saving'][i] / 1000, 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' 吨标准煤(TCE)占比'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(chart_start_row_number))
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 吨二氧化碳排放(TCO2E)占比'
current_row_number += 1
table_start_row_number = current_row_number
chart_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '吨二氧化碳排放(TCO2E)占比'
current_row_number += 1
for i in range(0, ca_len):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = reporting_period_data['names'][i]
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(reporting_period_data['subtotals_in_kgco2e_saving'][i] / 1000, 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
if ca_len < 4:
current_row_number = current_row_number - ca_len + 4
current_row_number += 1
pie = PieChart()
pie.title = name + ' 吨二氧化碳排放(TCO2E)占比'
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
pie_data = Reference(ws, min_col=3, min_row=table_start_row_number, max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 7.25
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(chart_start_row_number))
#############################################
has_values_saving_data = True
has_timestamps_data = True
if 'values_saving' not in reporting_period_data.keys() or \
reporting_period_data['values_saving'] is None or \
len(reporting_period_data['values_saving']) == 0:
has_values_saving_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_saving_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values_saving'][j][i], 2) \
if reporting_period_data['values_saving'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['subtotals_saving'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期节约 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws, min_col=2, min_row=table_start_row_number + 1, max_row=table_end_row_number)
line_data = Reference(ws, min_col=3 + i, min_row=table_start_row_number, max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
wbs['B2'] = 'KEY PIANO' # 112 pt
wbs['C2'] = 'TELAIO' # 77
wbs['D2'] = 'MODELLO' # 77
wbs['E2'] = 'DT FINE COLLAUDO' # 100
wbs['F2'] = 'PTZ' # 100
wbs['G2'] = 'GRUPPO' # 100
wbs['H2'] = 'SOTTOGRUPPO' # 100
wbs['I2'] = 'DETTAGLIO NC' # 77
wbs['J2'] = 'SCORE' # 77
wbs['K2'] = 'NOTE' # 77
for sim in range(0, 100):
# read the line from error excel and copy to analysis excel 180225 add score as well
wbs['A' + str(sim + 3)].value = defects[sims[sim][0]]['NUM']
wbs['B' + str(sim + 3)].value = defects[sims[sim][0]]['KEY PIANO']
wbs['C' + str(sim + 3)].value = defects[sims[sim][0]]['TELAIO']
wbs['D' + str(sim + 3)].value = defects[sims[sim][0]]['MODELLO']
wbs['E' +
str(sim + 3)].value = defects[sims[sim][0]]['DT FINE COLLAUDO']
wbs['F' + str(sim + 3)].value = defects[sims[sim][0]]['PTZ']
wbs['G' + str(sim + 3)].value = defects[sims[sim][0]]['GRUPPO']
wbs['H' + str(sim + 3)].value = defects[sims[sim][0]]['SOTTOGRUPPO']
wbs['I' + str(sim + 3)].value = defects[sims[sim][0]]['DETTAGLIO NC']
wbs['J' + str(sim + 3)].value = sims[sim][1] # similarity score
wbs['K' + str(sim + 3)].alignment = Alignment(wrapText=True)
wbs['K' + str(sim + 3)].value = defects[sims[sim][0]]['NOTE']
print("Saving results excel")
wb.save("ErrorAnalysis.xlsx")
print("Done")
# ch19_30.py
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 建立空白的活頁簿
ws = wb.active # 獲得目前工作表
ws['A1'] = 'Ming-Chi Institute of Technology'
ws['A2'] = 'Ming-Chi Institute of Technology'
ws['A3'] = '明志科大'
ws['C3'] = '機械工程系'
ws.merge_cells('A1:E1') # 合併A1:E1儲存格
ws.merge_cells('A3:A5') # 合併A1:E1儲存格
ws.merge_cells('C3:E4') # 合併C3:E4儲存格
ws['A1'].alignment = Alignment(horizontal='center') # A1儲存格水平置中
ws['A3'].alignment = Alignment(vertical='center') # A3儲存格垂直置中
ws['C3'].alignment = Alignment(horizontal='center', vertical='center')
wb.save('out19_30.xlsx') # 將活頁簿儲存
from openpyxl.utils import *
import numpy as np
import pandas as pd
from openpyxl.styles import Font,Side
from openpyxl.styles import Border,colors,Alignment
wb = load_workbook('test.xlsx')
ws = wb[wb.sheetnames[0]]
row_length = len(ws['A'])#获取行长度
col_length = len(ws[1])#获取列长度
fontobj = Font(name=u'宋体', bold=False, italic=False, size=12)#字体设置
border = Border(left=Side(style='thin',color=colors.BLACK),
right=Side(style='thin',color=colors.BLACK),
top=Side(style='thin',color=colors.BLACK),
bottom=Side(style='thin',color=colors.BLACK))#边框设置
this_alignment = Alignment(horizontal='center', vertical='center')#居中设置
content=[] #获取列名
for i in range(1,(col_length+1)):
content.append(get_column_letter(i))
def my_get_col(col_num,sheet,start=0): #获得某一列，存储为list
result=[]
col_num = content[col_num]
temp = sheet[col_num]
for i in range(start,row_length):
result.append(temp[i].value)
return result
def my_get_row(row_num,sheet,start=0): #获得某一行，存储为list
result=[]
temp = sheet[row_num]
def get(self, request, *args, **kwargs):
today = datetime.datetime.now()
today = today.strftime('%Y-%m-%d')
query = TiempoMuertonDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Tiempos Muertos'
#Establer el nombre del archivo
nombre_archivo = str(today) + "Reporte Tiempos Muertosw.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Mar Bran S.A. de C.V.'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Tiempos Muertos'
ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
ws['G3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['G3'].font = Font(name='calibri', size=12, bold=True)
ws['G3'] = 'FECHA'
ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
ws['H3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['H3'].font = Font(name='calibri', size=12, bold=True)
ws['H3'] = today
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 60
ws.column_dimensions['H'].width = 60
ws.column_dimensions['G'].width = 20
ws.column_dimensions['J'].width = 60
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
ws['D6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['D6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['D6'].font = Font(name='calibri', size=11, bold=True)
ws['D6'] = 'Línea'
ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
ws['E6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['E6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['E6'].font = Font(name='calibri', size=11, bold=True)
ws['E6'] = 'Supervisor'
ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
ws['F6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['F6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['F6'].font = Font(name='calibri', size=11, bold=True)
ws['F6'] = 'Turno'
ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
ws['G6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['G6'].font = Font(name='calibri', size=11, bold=True)
ws['G6'] = 'Categoría'
ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
ws['H6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['H6'].font = Font(name='calibri', size=11, bold=True)
ws['H6'] = 'Causa'
ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
ws['I6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['I6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['I6'].font = Font(name='calibri', size=11, bold=True)
ws['I6'] = 'Tiempo (min)'
ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
ws['J6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['J6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['J6'].font = Font(name='calibri', size=11, bold=True)
ws['J6'] = 'Obs'
controlador = 7
for q in query:
causa = q.causa
query3 = CausaTM.objects.filter(descripcion=causa).first()
categoria = query3.categoriaTM
ws.cell(row=controlador,
column=7).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=7).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=7).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=7).value = str(categoria)
ws.cell(row=controlador,
column=8).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=8).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=8).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=8).value = str(q.causa)
ws.cell(row=controlador,
column=9).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=9).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=9).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=9).value = q.cantidad
ws.cell(row=controlador,
column=10).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=10).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=10).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=10).value = q.obs
id_enc = q.tiempo_muerto_id
query2 = TiempoMuertoEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,
column=2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=2).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=2).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=2).value = x.fecha_produccion
ws.cell(row=controlador,
column=3).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=3).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=3).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=3).value = str(x.planta)
ws.cell(row=controlador,
column=4).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=4).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=4).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=4).value = str(x.linea)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=5).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = str(x.supervisor)
ws.cell(row=controlador,
column=6).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=6).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=6).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=6).value = str(x.turno)
controlador += 1
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
def checkout(sale, url):
wb = openpyxl.load_workbook('报价.xlsx')
wb.guess_types = True
rows = wb['{}'.format(sale)].max_row
wsa = wb['{}'.format(sale)]['A2:A{}'.format(rows)]
wsd = wb['{}'.format(sale)]['D2:D{}'.format(rows)]
name = []
zhi = []
for each in wsa:
for each_cell in each:
name.append(each_cell.value)
for each1 in wsd:
for each_cell1 in each1:
zhi.append(each_cell1.value)
c = rows + 1
for each in find_url(url)[0]:
if each[0] in name:
num = name.index(each[0])
if int(each[3]) >= int(zhi[num]):
wb['{}'.format(sale)]['D{}'.format(num +
2)] = int(each[3]) + 10
else:
wb['{}'.format(sale)]['D{}'.format(num +
2)] = int(zhi[num]) + 10
'''
if zhi[num] != '0':
if each[3] != '0':
wb['{}'.format(sale)]['D{}'.format(num+2)] = (int(each[3])+int(zhi[num]))/2 + 2
else:
wb['{}'.format(sale)]['D{}'.format(num+2)] = zhi[num]
else:
if each[3] != '0':
wb['{}'.format(sale)]['D{}'.format(num+2)] = each[3]
else:
wb['{}'.format(sale)]['D{}'.format(num+2)] = 0
'''
else:
if each[0].count('--') == 2:
if each[0].find('耳机') == -1:
if each[0].find('移动') == -1:
if each[0].find('电信') == -1:
if each[0].find('手环') == -1:
wb[sale].append(each)
ws = wb[sale]['A{}'.format(c)]
ws1 = wb[sale]['D{}'.format(c)]
ws.alignment = Alignment(horizontal='center',
vertical='center')
ws1.alignment = Alignment(horizontal='center',
vertical='center')
c += 1
else:
continue
else:
continue
else:
continue
else:
continue
else:
continue
wb.save('报价.xlsx')
def get(self, request, *args, **kwargs):
today = datetime.datetime.now()
today = today.strftime('%Y-%m-%d')
query = ProduccionDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Producción Embolsados'
#Establer el nombre del archivo
nombre_archivo = str(today) + "Reporte Producción Embolslados.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Mar Bran S.A. de C.V.'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Innovación, Mejora Continua y Six Sigma'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Producción Embolsados'
ws['G3'].alignment = Alignment(horizontal='left', vertical='center')
ws['G3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['G3'].font = Font(name='calibri', size=12, bold=True)
ws['G3'] = 'FECHA'
ws['H3'].alignment = Alignment(horizontal='left', vertical='center')
ws['H3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['H3'].font = Font(name='calibri', size=12, bold=True)
ws['H3'] = today
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 30
ws.column_dimensions['F'].width = 20
ws.column_dimensions['G'].width = 60
ws.column_dimensions['H'].width = 60
ws.column_dimensions['G'].width = 20
ws.column_dimensions['J'].width = 60
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
ws['D6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['D6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['D6'].font = Font(name='calibri', size=11, bold=True)
ws['D6'] = 'Línea'
ws['E6'].alignment = Alignment(horizontal='center', vertical='center')
ws['E6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['E6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['E6'].font = Font(name='calibri', size=11, bold=True)
ws['E6'] = 'Supervisor'
ws['F6'].alignment = Alignment(horizontal='center', vertical='center')
ws['F6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['F6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['F6'].font = Font(name='calibri', size=11, bold=True)
ws['F6'] = 'Turno'
ws['G6'].alignment = Alignment(horizontal='center', vertical='center')
ws['G6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['G6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['G6'].font = Font(name='calibri', size=11, bold=True)
ws['G6'] = 'Plantilla'
ws['H6'].alignment = Alignment(horizontal='center', vertical='center')
ws['H6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['H6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['H6'].font = Font(name='calibri', size=11, bold=True)
ws['H6'] = 'Proc./term.'
ws['I6'].alignment = Alignment(horizontal='center', vertical='center')
ws['I6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['I6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['I6'].font = Font(name='calibri', size=11, bold=True)
ws['I6'] = 'Producto'
ws['J6'].alignment = Alignment(horizontal='center', vertical='center')
ws['J6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['J6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['J6'].font = Font(name='calibri', size=11, bold=True)
ws['J6'] = 'Peso (Lbs)'
ws['K6'].alignment = Alignment(horizontal='center', vertical='center')
ws['K6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['K6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['K6'].font = Font(name='calibri', size=11, bold=True)
ws['K6'] = 'Cantidad (cajas)'
ws['L6'].alignment = Alignment(horizontal='center', vertical='center')
ws['L6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['L6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['L6'].font = Font(name='calibri', size=11, bold=True)
ws['L6'] = 'Resto (lbs)'
ws['M6'].alignment = Alignment(horizontal='center', vertical='center')
ws['M6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['M6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['M6'].font = Font(name='calibri', size=11, bold=True)
ws['M6'] = 'Producción (lbs)'
ws['N6'].alignment = Alignment(horizontal='center', vertical='center')
ws['N6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['N6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['N6'].font = Font(name='calibri', size=11, bold=True)
ws['N6'] = 'Merma(%)'
controlador = 7
for q in query:
id_enc = q.produccion_id
query2 = ProduccionEnc.objects.filter(id=id_enc)
for x in query2:
ws.cell(row=controlador,
column=2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=2).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=2).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=2).value = x.fecha_produccion
ws.cell(row=controlador,
column=3).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador, column=3).border = Border(
left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador, column=3).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=3).value = str(x.planta)
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)
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
from openpyxl import load_workbook
from openpyxl.styles import (
Font,
Color,
colors,
Alignment,
Side,
Border,
)
from openpyxl.styles import NamedStyle
workbook = load_workbook(filename="sample.xlsx")
sheet = workbook.active
bold_font = Font(bold=True)
big_blue_text = Font(color=colors.BLUE, size=20)
center_aligned_text = Alignment(horizontal="center")
double_border_side = Side(border_style="double")
square_border = Border(top=double_border_side,
right=double_border_side,
bottom=double_border_side,
left=double_border_side)
sheet["A2"].font = bold_font
sheet["A3"].font = big_blue_text
sheet["A4"].alignment = center_aligned_text
sheet["A5"].border = square_border
header = NamedStyle(name="header")
header.font = Font(bold=True)
header.border = Border(bottom=Side(border_style="thin"))
header.alignment = Alignment(horizontal="center", vertical="center")
from openpyxl.styles import Font, colors, Alignment, Border, Side
header_font_style = Font(color='00FF0000', italic=False, bold=True)
header_font_alignment = Alignment(vertical="center",
text_rotation=180,
wrap_text=True,
indent=1,
shrink_to_fit=True)
header_main_title = Font(color=colors.BLACK, size=15, italic=True, bold=True)
indent = Alignment(indent=1)
def generate_datasets_report(self,
catalogs,
harvest='valid',
report=None,
export_path=None,
catalog_ids=None,
catalog_homepages=None,
catalog_orgs=None):
"""Genera un reporte sobre las condiciones de la metadata de los
datasets contenidos en uno o varios catálogos.
Args:
catalogs (str, dict o list): Uno (str o dict) o varios (list de
strs y/o dicts) catálogos.
harvest (str): Criterio a utilizar para determinar el valor del
campo "harvest" en el reporte generado ('all', 'none',
'valid', 'report' o 'good').
report (str): Path a un reporte/config especificando qué
datasets marcar con harvest=1 (sólo si harvest=='report').
export_path (str): Path donde exportar el reporte generado (en
formato XLSX o CSV). Si se especifica, el método no devolverá
nada.
catalog_id (str): Nombre identificador del catálogo para federación
catalog_homepage (str): URL del portal de datos donde está
implementado el catálogo. Sólo se pasa si el portal es un CKAN
o respeta la estructura:
https://datos.{organismo}.gob.ar/dataset/{dataset_identifier}
Returns:
list: Contiene tantos dicts como datasets estén presentes en
`catalogs`, con la data del reporte generado.
"""
assert isinstance(catalogs, string_types + (dict, list))
if isinstance(catalogs, list):
assert not catalog_ids or len(catalogs) == len(catalog_ids)
assert not catalog_orgs or len(catalogs) == len(catalog_orgs)
assert not catalog_homepages or len(catalogs) == len(
catalog_homepages)
# Si se pasa un único catálogo, genero una lista que lo contenga
if isinstance(catalogs, string_types + (dict, )):
catalogs = [catalogs]
if not catalog_ids or isinstance(catalog_ids, string_types + (dict, )):
catalog_ids = [catalog_ids] * len(catalogs)
if not catalog_orgs or isinstance(catalog_orgs, string_types +
(dict, )):
catalog_orgs = [catalog_orgs] * len(catalogs)
if not catalog_homepages or isinstance(catalog_homepages,
string_types + (dict, )):
catalog_homepages = [catalog_homepages] * len(catalogs)
catalogs_reports = [
self.catalog_report(catalog,
harvest,
report,
catalog_id=catalog_id,
catalog_homepage=catalog_homepage,
catalog_org=catalog_org)
for catalog, catalog_id, catalog_org, catalog_homepage in zip(
catalogs, catalog_ids, catalog_orgs, catalog_homepages)
]
full_report = []
for report in catalogs_reports:
full_report.extend(report)
if export_path:
# config styles para reportes en excel
alignment = Alignment(wrap_text=True,
shrink_to_fit=True,
vertical="center")
column_styles = {
"dataset_title": {
"width": 35
},
"dataset_description": {
"width": 35
},
"dataset_publisher_name": {
"width": 35
},
"dataset_issued": {
"width": 20
},
"dataset_modified": {
"width": 20
},
"distributions_formats": {
"width": 15
},
"distributions_list": {
"width": 90
},
"notas": {
"width": 50
},
}
cell_styles = [
{
"alignment": Alignment(vertical="center")
},
{
"row": 1,
"font": Font(bold=True)
},
{
"col": "dataset_title",
"alignment": alignment
},
{
"col": "dataset_description",
"alignment": alignment
},
{
"col": "dataset_publisher_name",
"alignment": alignment
},
{
"col": "distributions_formats",
"alignment": alignment
},
{
"col": "distributions_list",
"alignment": alignment
},
{
"col": "notas",
"alignment": alignment
},
]
# crea tabla
writers.write_table(table=full_report,
path=export_path,
column_styles=column_styles,
cell_styles=cell_styles)
else:
return full_report
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, 8):
ws.row_dimensions[i].height = 42
# for i in range(2, 6 + 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('V')):
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['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~ table_data
################################################
has_data_flag = True
reporting_period_data = report['reporting_period']
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_data_flag = False
if "timestamps" not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0:
has_data_flag = False
if "values" not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_data_flag = False
ca = reporting_period_data['names']
ca_len = len(ca)
temp_max_row = 0
times = reporting_period_data['timestamps']
category = report['meter']['energy_category_name']
parameters_names_len = len(report['parameters']['names'])
parameters_data = report['parameters']
parameters_parameters_datas_len = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
parameters_parameters_datas_len += 1
start_detail_data_row_num = 9 + (parameters_parameters_datas_len + ca_len) * 6
if has_data_flag:
time = times[0]
for time in times:
if len(time) > 0:
break
has_data = False
max_row = 0
current_sheet_parameters_row_number = 7
temp_max_row = max_row
for i in range(8, len(time) + 6 + ca_len * 6 + len(category) * 6 + 2):
ws.row_dimensions[i].height = 42
if len(time) > 0:
has_data = True
current_sheet_parameters_row_number = 7 + ca_len * 6
if has_data:
max_row = start_detail_data_row_num + len(time)
# print("max_row", max_row)
ws['B6'].font = title_font
ws['B6'] = name + ' 趋势'
ws.row_dimensions[start_detail_data_row_num - 1].height = 60
ws['B' + str(start_detail_data_row_num - 1)].fill = table_fill
ws['B' + str(start_detail_data_row_num - 1)].font = title_font
ws['B' + str(start_detail_data_row_num - 1)].border = f_border
ws['B' + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
ws['B' + str(start_detail_data_row_num - 1)] = '日期时间'
for i in range(0, len(time)):
col = 'B'
row = str(start_detail_data_row_num + 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 = format_cell.get_column_letter(3 + i)
ws[col + str(start_detail_data_row_num - 1)].fill = table_fill
ws[col + str(start_detail_data_row_num - 1)].font = title_font
ws[col + str(start_detail_data_row_num - 1)].alignment = c_c_alignment
ws[col + str(start_detail_data_row_num - 1)] = reporting_period_data['names'][i]
ws[col + str(start_detail_data_row_num - 1)].border = f_border
for j in range(0, len(time)):
row = str(start_detail_data_row_num + 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], 3) if \
len(reporting_period_data['values'][i]) > 0 and \
reporting_period_data['values'][i][j] is not None else " "
ws[col + row].border = f_border
# line
# 39~: line
line = LineChart()
line.title = '趋势值 - ' + reporting_period_data['names'][i]
labels = Reference(ws, min_col=2, min_row=start_detail_data_row_num, max_row=max_row-1)
line_data = Reference(ws, min_col=3 + i, min_row=start_detail_data_row_num+1, max_row=max_row-1)
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 = 36
# pie.title = "Pies sold by category"
line.dLbls = DataLabelList()
# line.dLbls.showCatName = True # label show
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False # val show
line.dLbls.showPercent = False # percent show
# s1 = CharacterProperties(sz=1800) # font size *100
chart_col = chr(ord('B'))
chart_cell = chart_col + str(7 + 6*i)
ws.add_chart(line, chart_cell)
else:
pass
##########################################
has_parameters_names_and_timestamps_and_values_data = True
# 12 is the starting line number of the last line chart in the report period
if 'parameters' not in report.keys() or \
report['parameters'] is None or \
'names' not in report['parameters'].keys() or \
report['parameters']['names'] is None or \
len(report['parameters']['names']) == 0 or \
'timestamps' not in report['parameters'].keys() or \
report['parameters']['timestamps'] is None or \
len(report['parameters']['timestamps']) == 0 or \
'values' not in report['parameters'].keys() or \
report['parameters']['values'] is None or \
len(report['parameters']['values']) == 0 or \
timestamps_data_all_equal_0(report['parameters']['timestamps']):
has_parameters_names_and_timestamps_and_values_data = False
if has_parameters_names_and_timestamps_and_values_data:
###############################
# new worksheet
###############################
parameters_data = report['parameters']
parameters_names_len = len(parameters_data['names'])
parameters_ws = wb.create_sheet('相关参数')
parameters_timestamps_data_max_len = \
get_parameters_timestamps_lists_max_len(list(parameters_data['timestamps']))
# Row height
parameters_ws.row_dimensions[1].height = 102
for i in range(2, 7 + 1):
parameters_ws.row_dimensions[i].height = 42
for i in range(8, parameters_timestamps_data_max_len + 10):
parameters_ws.row_dimensions[i].height = 60
# Col width
parameters_ws.column_dimensions['A'].width = 1.5
parameters_ws.column_dimensions['B'].width = 25.0
for i in range(3, 12 + parameters_names_len * 3):
parameters_ws.column_dimensions[format_cell.get_column_letter(i)].width = 15.0
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
parameters_ws.add_image(img, 'B1')
# Title
parameters_ws.row_dimensions[3].height = 60
parameters_ws['B3'].font = name_font
parameters_ws['B3'].alignment = b_r_alignment
parameters_ws['B3'] = 'Name:'
parameters_ws['C3'].border = b_border
parameters_ws['C3'].alignment = b_c_alignment
parameters_ws['C3'].font = name_font
parameters_ws['C3'] = name
parameters_ws['F3'].font = name_font
parameters_ws['F3'].alignment = b_r_alignment
parameters_ws['F3'] = 'Date:'
parameters_ws['G3'].border = b_border
parameters_ws['G3'].alignment = b_c_alignment
parameters_ws['G3'].font = name_font
parameters_ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
parameters_ws.merge_cells("G3:H3")
parameters_ws_current_row_number = 6
parameters_ws['B' + str(parameters_ws_current_row_number)].font = title_font
parameters_ws['B' + str(parameters_ws_current_row_number)] = name + ' 相关参数'
parameters_ws_current_row_number += 1
parameters_table_start_row_number = parameters_ws_current_row_number
parameters_ws.row_dimensions[parameters_ws_current_row_number].height = 80
parameters_ws_current_row_number += 1
table_current_col_number = 2
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(parameters_ws_current_row_number - 1)].fill = table_fill
parameters_ws[col + str(parameters_ws_current_row_number - 1)].border = f_border
parameters_ws[col + str(parameters_ws_current_row_number - 1)].font = name_font
parameters_ws[col + str(parameters_ws_current_row_number - 1)].alignment = c_c_alignment
parameters_ws[col + str(parameters_ws_current_row_number - 1)] = parameters_data['names'][i]
table_current_row_number = parameters_ws_current_row_number
for j, value in enumerate(list(parameters_data['timestamps'][i])):
col = format_cell.get_column_letter(table_current_col_number)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = value
col = format_cell.get_column_letter(table_current_col_number + 1)
parameters_ws[col + str(table_current_row_number)].border = f_border
parameters_ws[col + str(table_current_row_number)].font = title_font
parameters_ws[col + str(table_current_row_number)].alignment = c_c_alignment
parameters_ws[col + str(table_current_row_number)] = round(parameters_data['values'][i][j], 2)
table_current_row_number += 1
table_current_col_number = table_current_col_number + 3
########################################################
# parameters chart and parameters table
########################################################
ws['B' + str(current_sheet_parameters_row_number)].font = title_font
ws['B' + str(current_sheet_parameters_row_number)] = name + ' 相关参数'
current_sheet_parameters_row_number += 1
chart_start_row_number = current_sheet_parameters_row_number
col_index = 0
for i in range(0, parameters_names_len):
if len(parameters_data['timestamps'][i]) == 0:
continue
line = LineChart()
data_col = 3 + col_index * 3
labels_col = 2 + col_index * 3
col_index += 1
line.title = '相关参数 - ' + \
parameters_ws.cell(row=parameters_table_start_row_number, column=data_col).value
labels = Reference(parameters_ws, min_col=labels_col, min_row=parameters_table_start_row_number + 1,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line_data = Reference(parameters_ws, min_col=data_col, min_row=parameters_table_start_row_number,
max_row=(len(parameters_data['timestamps'][i]) + parameters_table_start_row_number))
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = False
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
current_sheet_parameters_row_number = chart_start_row_number
current_sheet_parameters_row_number += 1
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
#Code to create/name an excel file
def makeExcel():
global label
global outputSheet
label = Workbook()
outputSheet = label.active
zfFont = Font(size=16, bold=True)
lineFont = Font(size=14, bold=True)
zygFont = Font(size=10)
xFont = Font(size=10, bold=True)
normalFont = Font(size=8)
smallFont = Font(size=5)
centerAlign = Alignment(horizontal="center", vertical="top")
#Code collect data and fill out data with formatting
def collectAndFill(fileName):
inputExcel = load_workbook(fileName)
inputSheet = inputExcel.active
for row in range(startingRow, endingRow + 1):
makeExcel()
zfNumber = inputSheet.cell(row, 2).value
line = inputSheet.cell(row, 3).value
zygosity = inputSheet.cell(row, 4).value
xNumber = inputSheet.cell(row, 7).value
basicDOB = inputSheet.cell(row, 5).value
dob = str(basicDOB).split(" ")[0]
query_executed = time() - start
wb = Workbook()
ws = wb.active
m = -1
for t in q2:
actor1, actor2, count, name1, name2 = t
ws.cell(row=actor1 + 1 + 1, column=actor2 + 1 + 1, value=count)
ws.cell(row=actor2 + 1 + 1, column=actor1 + 1 + 1, value=count)
ws.cell(row=actor1 + 1 + 1, column=1, value=name1)
ws.cell(row=actor2 + 1 + 1, column=1, value=name2)
ws.cell(row=1, column=actor1 + 1 + 1,
value=name1).alignment = Alignment(text_rotation=85)
ws.cell(row=1, column=actor2 + 1 + 1,
value=name2).alignment = Alignment(text_rotation=85)
m = max(m, actor1, actor2)
for i in range(2, ws.max_row + 1):
if i - 1 <= m:
ws.cell(row=2, column=i + 1, value=i - 1)
ws.cell(row=i + 1, column=2, value=i - 1)
for j in range(3, ws.max_column + 1):
cell = ws.cell(row=i, column=j)
if cell.value is None:
cell.value = 0
wb.save("Query2.xlsx")
def ventas_marca():
date_start = request.form['date_start'] + ' 00:00:00'
date_end = request.form['date_end'] + ' 23:59:59'
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.font = Font(color='FFFFFF')
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
name_db = os.environ["DATABASE"]
user_db = os.environ["USER"]
pass_db = os.environ["PASS"]
host_db = os.environ["HOST"]
conn = psycopg2.connect(database=name_db,
user=user_db,
password=pass_db,
host=host_db)
cur = conn.cursor()
query = """SELECT O.client_id as id_client, C.company_code as id_erp, C.client_number_ant as cliente_ceveceria,
C.client_high as cliente_mayorista, C.business_name as negocio, P.code, P.name
FROM order_details OD
LEFT JOIN orders O ON OD.order_id = O.id
LEFT JOIN clients C ON O.client_id = C.id
LEFT JOIN products P ON OD.product_id=P.id
WHERE OD.is_devolution=false AND O.active=true AND O.ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY O.client_id, C.company_code, C.client_number_ant, C.client_high, C.business_name, P.code, P.name
ORDER BY O.client_id, P.code;"""
cur.execute(query)
data = cur.fetchall()
query1 = """SELECT O.client_id, P.code, CAST (EXTRACT(YEAR FROM O.ordered_at) AS INTEGER), CAST (EXTRACT(MONTH FROM O.ordered_at) AS INTEGER), SUM(OD.quantity_delivered * p.hlts) as hlts
FROM order_details OD
LEFT JOIN orders O ON OD.order_id = O.id
LEFT JOIN products P ON OD.product_id=P.id
WHERE OD.is_devolution=false AND O.active=true AND O.ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY O.client_id, P.code, EXTRACT(YEAR FROM O.ordered_at), EXTRACT(MONTH FROM O.ordered_at)
ORDER BY O.client_id, P.code, EXTRACT(YEAR FROM O.ordered_at), EXTRACT(MONTH FROM O.ordered_at);"""
cur.execute(query1)
data_sum = cur.fetchall()
wb = Workbook()
ws = wb.active
ws.title = "VTAS X MARCA 13.2"
yearStart = int(date_start[0:4])
monthStart = int(date_start[5:7])
yearEnd = int(date_end[0:4])
monthEnd = int(date_end[5:7])
months = [
[1, "Ene"],
[2, "Feb"],
[3, "Mar"],
[4, "Abr"],
[5, "May"],
[6, "Jun"],
[7, "Jul"],
[8, "Ago"],
[9, "Sep"],
[10, "Oct"],
[11, "Nov"],
[12, "Dic"],
[100, "Total"],
]
qtyMonths = len(months)
ws['A1'] = 'VENTAS POR MARCA POR CLIENTE'
title = ws['A1']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells('A1:G1')
ws['A3'] = 'CEDIS'
ws['B3'] = 'LAGOS DE MORENO'
title = ws['A3']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
ws['A4'] = 'FECHA'
ws['B4'] = 'Desde ' + request.form['date_start'] + ' Al ' + request.form[
'date_end']
title = ws['A4']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
if len(data) > 0:
cell_hasta = len(data) + 6
# add column headings. NB. these must be strings
table_header = [
"Id Cliente", "Id ERP", "Num. Cliente", "Cve Cte Mayorista",
"Negocio", "Clave", "Producto"
]
begin = yearStart
end = int(yearEnd) + 1
for y in range(begin, end):
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
for mm in range(mStart, mEnd):
table_header.append(months[mm][1] + " " + str(y) + "\nHlts")
if mEnd < qtyMonths:
table_header.append(months[qtyMonths - 1][1] + " " + str(y) +
"\nHlts")
ws.append(table_header)
hlts_end = len(table_header)
row_ds = 0
for row in data:
row_list = list(row)
cliente = int(row[0])
producto = int(row[5])
details = []
for ind in range(row_ds, len(data_sum)):
if cliente == int(data_sum[row_ds][0]) and producto == int(
data_sum[row_ds][1]):
details.append([
data_sum[row_ds][2], data_sum[row_ds][3],
data_sum[row_ds][4]
])
row_ds += 1
else:
break
for y in range(begin, end):
if y == yearStart and y == yearEnd:
mStart = monthStart - 1
mEnd = monthEnd
elif y == yearStart:
mStart = monthStart - 1
mEnd = qtyMonths
elif y == yearEnd:
mStart = 0
mEnd = monthEnd
else:
mStart == 0
mEnd = qtyMonths
total = 0
for mm in range(mStart, mEnd):
valor = 0
if (months[mm][0] == 100):
valor = total
else:
if len(details) > 0:
for d in details:
if y == int(d[0]) and months[mm][0] == int(
d[1]):
valor = d[2]
total += valor
if int(months[mm][0]) == 100:
valor = total
row_list.append(valor)
if mEnd < qtyMonths:
row_list.append(total)
ws.append(row_list)
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = 15
for col, value in dims.items():
ws.column_dimensions[col].width = value
ws.insert_rows(5)
title = ws['A7']
ws.freeze_panes = title
for column in range(1, len(table_header) + 1):
column_letter = get_column_letter(column)
for rowD in range(5, len(data) + 6):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
if (column_letter == 'H' or column_letter == 'I'):
ws[column_letter + str(rowD)].number_format = '#,#0.0'
for row in ws.iter_rows("L6:" + get_column_letter(hlts_end) +
str(cell_hasta)):
for cell in row:
cell.number_format = '#,#0.0'
tab = Table(displayName="Table1",
ref="A6:" + get_column_letter(len(table_header)) +
str(cell_hasta))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
for row in ws.iter_rows('A6:' + get_column_letter(len(table_header)) +
'6'):
for cell in row:
cell.style = headOpe
title = ws['H7']
ws.freeze_panes = title
ws.merge_cells('H5:' + get_column_letter(hlts_end) + '5')
ws['H5'] = 'HLTS'
hlts = ws['H5']
hlts.font = Font(color=colors.WHITE, name='Tahoma', size=14)
hlts.alignment = Alignment(horizontal='center')
hlts.fill = PatternFill("solid", fgColor="305496")
nombre_archivo = "RC-13-2-" + datetime.now().date().strftime(
'%Y%m%d') + ".xlsx"
wb.save(nombre_archivo)
return send_file(
nombre_archivo,
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
True, nombre_archivo)
else:
return "No hay registros"
"""
主要功能：
1、将需求的字段生成对应表格；2、将金额为正的（S）的行项目标记为黄色；
3、只导入特定物料；4、对数据源取名进行约束；
5、增加程序运行完毕的消息提醒；6、增加padas的排序、筛选功能
"""
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, GradientFill, Color, colors
thin = Side(border_style="thin", color="000000") #边框样式，定义为对象
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
wb = load_workbook(
filename=u"C:\\Users\\wbliang\\Desktop\\deptbudget1203.xlsx")
ws = wb["ExportExcel"]
for col in ["I", "J"]:
ws.column_dimensions[col].width = 25 #设置固定列宽
for col in range(1, 11):
ws.cell(1, col).font = Font(name='Microsoft YaHei',
size=13,
bold=True,
color=colors.RED) #设置标题行的字体样式
ws.cell(col, 10).border = Border(top=thin,
left=thin,
right=thin,
bottom=thin) #设置标题行的边框样式
ws.cell(col, 10).alignment = align
wb.save("C:\\Users\\wbliang\\Desktop\\deptbudget1203.xlsx")
def ventas_operaciones():
name_db = os.environ["DATABASE"]
user_db = os.environ["USER"]
pass_db = os.environ["PASS"]
host_db = os.environ["HOST"]
conn = psycopg2.connect(database=name_db,
user=user_db,
password=pass_db,
host=host_db)
cur = conn.cursor()
#Creamos el libro de trabajo
wb = Workbook()
#Definimos como nuestra hoja de trabajo, la hoja activa, por defecto la primera del libro
ws = wb.active
ws.title = "OPERACIONES 13.4"
date_start = request.form['date_start'] + ' 00:00:00'
date_end = request.form['date_end'] + ' 23:59:59'
headOpe = NamedStyle(name="headOpe")
headOpe.alignment = Alignment(horizontal='center')
headOpe.font = Font(color='FFFFFF')
rowPar = NamedStyle(name="rowPar")
rowPar.fill = PatternFill("solid", fgColor="E0ECF8")
ws['A1'] = 'OPERACIONES'
title = ws['A1']
title.font = Font(size=14, bold=True)
title.alignment = Alignment(horizontal="center", vertical="center")
ws.merge_cells('A1:G1')
ws['A3'] = 'Cedis'
ws['B3'] = 'Lagos de Moreno'
title = ws['A3']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
ws['A4'] = 'Fecha'
ws['B4'] = 'Desde ' + request.form['date_start'] + ' Al ' + request.form[
'date_end']
title = ws['A4']
title.font = Font(size=12, bold=True)
title.alignment = Alignment(horizontal="left", vertical="center")
query = """SELECT CT.name as cedis, EXTRACT(YEAR FROM O.ordered_at) as year,
EXTRACT(DOW FROM O.ordered_at) + 1 as week,
CI.name as poblacion, R.name as ruta, RT.name as modalidad,
SUM(quantity) as cajas_pedido, SUM(quantity_delivered) as cajas_entregadas,
SUM(P.hlts::numeric * quantity) as htl_pedido,
SUM(P.hlts::numeric * quantity_delivered) as htl_entregado,
(SUM(quantity_delivered::numeric) / SUM(quantity::numeric)) as eficiencia
FROM orders O
LEFT JOIN order_details OD ON O.id = OD.order_id
LEFT JOIN clients C ON O.client_id = C.id
LEFT JOIN centers CT ON C.center_id = CT.id
LEFT JOIN addresses A ON C.address_id = A.id
LEFT JOIN cities CI ON A.city_id = CI.id
LEFT JOIN routes R ON O.route_id = R.id
LEFT JOIN route_types RT ON R.route_type_id = RT.id
LEFT JOIN client_details CD ON CD.client_id = C.id
LEFT JOIN products P ON OD.product_id = P.id
WHERE O.active = true AND OD.is_devolution=false AND CT.id = 1 AND OD.quantity > 0
AND ordered_at BETWEEN ('""" + date_start + """') AND ('""" + date_end + """')
GROUP BY CT.name, RT.name, EXTRACT(YEAR FROM O.ordered_at), EXTRACT(DOW FROM O.ordered_at), CI.name, R.name
ORDER BY EXTRACT(YEAR FROM O.ordered_at), EXTRACT(DOW FROM O.ordered_at);"""
cur.execute(query)
data = cur.fetchall()
if len(data) > 0:
cell_hasta = len(data) + 7
# add column headings. NB. these must be strings
header = [
"Zona", "ANO", "SEM", "Localidad", "Ruta", "Modalidad de venta",
"Cajas - pedidas", "Cajas - entregadas", "HTL - Pedidos",
"HTL - Entregados", "% de eficiencia"
]
ws.append(header)
for row in data:
ws.append(row)
for column in range(1, len(header) + 1):
column_letter = get_column_letter(column)
for rowD in range(5, len(data) + 6):
if (rowD % 2 == 0):
ws[column_letter + str(rowD)].style = rowPar
if (column_letter == 'I' or column_letter == 'J'):
ws[column_letter + str(rowD)].number_format = '#,#0.0'
if (column_letter == 'K'):
ws[column_letter + str(rowD)].number_format = '0.00%'
#cambiar ancho de columns
dims = {}
for row in ws.rows:
for cell in row:
if cell.value:
dims[cell.column] = 20
for col, value in dims.items():
ws.column_dimensions[col].width = value
ws.insert_rows(5)
title = ws['B7']
ws.freeze_panes = title
tab = Table(displayName="Table1", ref="A6:K" + str(cell_hasta))
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleLight9",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
for row in ws.iter_rows('A6:K6'):
for cell in row:
cell.style = headOpe
nombre_archivo = "RC-13-4-" + datetime.now().date().strftime(
'%Y%m%d') + ".xlsx"
wb.save(nombre_archivo)
return send_file(
nombre_archivo,
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
True, nombre_archivo)
else:
return "No hay registros"
# print len(default_ws.columns)
# 写入数据
cell01 = default_ws.cell(row=taskRow, column=1).value = tempFileData[1]
cell02 = default_ws.cell(row=taskRow, column=2).value = tempFileData[2]
cell03 = default_ws.cell(row=taskRow,
column=3).value = round(float(tempFileData[3]), 1)
cell04 = default_ws.cell(row=taskRow, column=4).value = tempFileData[4]
cell05 = default_ws.cell(row=taskRow,
column=5).value = round(float(tempFileData[5]), 8)
cell06 = default_ws.cell(row=taskRow,
column=6).value = round(float(tempFileData[6]), 8)
# cell01.value = tempFileData[1]
# cell02.value = tempFileData[2]
# cell03.value = tempFileData[3]
# cell04.value = tempFileData[4]
# cell05.value = tempFileData[5]
# cell06.value = tempFileData[6]
# 表格样式
# 遍历单元格文本居中
for row in default_ws.iter_rows('A1:F%d' % taskRow):
for cell in row:
# print cell
cell.alignment = Alignment(horizontal='center', vertical='center')
# 保存数据
wb.save(targetFilePath)
print "the task is successful~~~"
