def set_page_break(self):
# 40 rows per page
get_max_row = self.excel_sheet.max_row
get_max_column = self.excel_sheet.max_column
if get_max_row >= 32:
while get_max_row >= 32:
self.excel_sheet.sheet_properties.pageSetUpPr.fitToPage = True
openpyxl.worksheet.pagebreak.PageBreak.tagname = 'rowBreaks'
page_break_row = Break((get_max_row + 1) - 31)
self.excel_sheet.page_breaks.append(page_break_row)
openpyxl.worksheet.pagebreak.PageBreak.tagname = 'colBreaks'
page_break_column = Break(get_max_column + 1)
self.excel_sheet.page_breaks.append(page_break_column)
get_max_row -= 32
else:
self.excel_sheet.sheet_properties.pageSetUpPr.fitToPage = True
openpyxl.worksheet.pagebreak.PageBreak.tagname = 'rowBreaks'
page_break_row = Break(get_max_row + 1)
self.excel_sheet.page_breaks.append(page_break_row)
openpyxl.worksheet.pagebreak.PageBreak.tagname = 'colBreaks'
page_break_column = Break(get_max_column + 1)
self.excel_sheet.page_breaks.append(page_break_column)
# Landscape orientation
self.excel_sheet.page_setup.orientation = self.excel_sheet.ORIENTATION_LANDSCAPE
def generate(datas, sheets_name):
wb = Workbook(write_only=False)
ws = [wb.create_sheet(sheet_name) for sheet_name in sheets_name]
count = [0 for i in sheets_name]
for row in datas:
sheet_num = row['grade'] - 1
if is_page_divide(count[sheet_num]):
ws[sheet_num].row_breaks.append(
Break(id=int(count[sheet_num] / 2) * 6 + 1))
organization = ws[sheet_num].cell(column=count[sheet_num] % 2 * 5 + 3,
row=int(count[sheet_num] / 2) * 6 +
2,
value=row['organization'])
locker = ws[sheet_num].cell(column=count[sheet_num] % 2 * 5 + 3,
row=int(count[sheet_num] / 2) * 6 + 4,
value='[ ' + row['locker'] + ' ]')
user = ws[sheet_num].cell(column=count[sheet_num] % 2 * 5 + 3,
row=int(count[sheet_num] / 2) * 6 + 5,
value=row['user_id'] + ' ' + row['name'])
count[sheet_num] += 1
organization.font = Font(size=24, bold=True)
organization.alignment = Alignment(horizontal='center')
locker.font = Font(size=24, bold=True)
locker.alignment = Alignment(horizontal='center')
user.font = Font(size=24, bold=True)
user.alignment = Alignment(horizontal='center')
for i in range(4):
for c in "ABCDEFGHIJKLMNOPQ":
ws[i].column_dimensions[c].width = 14.2857142857
ws[i].page_setup.orientation = ws[i].ORIENTATION_PORTRAIT
ws[i].page_setup.paperSize = ws[i].PAPERSIZE_A4
ws[i].sheet_properties.pageSetUpPr.fitToPage = True
ws[i].page_setup.fitToHeight = False
ws[i].page_setup.fitToWidth = True
ws[i].page_margins.left = 0.7
ws[i].page_margins.top = 0.75
ws[i].page_margins.right = 0.7
ws[i].page_margins.bottom = 0.8
ws[i].sheet_view.showGridLines = False
# wb.save('./new_test_file.xlsx')
return wb
def test_page_break(WorkSheetParser):
src = """
<sheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<rowBreaks count="1" manualBreakCount="1">
<brk id="15" man="1" max="16383" min="0"/>
</rowBreaks>
</sheet>
"""
expected_pagebreak = PageBreak()
expected_pagebreak.append(Break(id=15))
parser = WorkSheetParser
parser.source = src
parser.parse()
ws = parser.ws
assert ws.page_breaks == expected_pagebreak
def _insert_subtotal_row(self, row_idx, page_break):
r = []
for i in range(len(self.headers[-1])):
r.append("")
for idx, val in self._sub_total_data.iteritems():
if idx == self._sub_total_idx:
r[idx] = get_str(val) + ' Total'
else:
if type(val) == list:
# r[idx] = len(val)
# pprint(val)
r[idx] = str(len(val))+ ' Total' + "\n (" + ", ".join(str_grouping([int(v) for v in val])) + ")"
else:
r[idx] = val
self._insert_row(r, row_idx, 'HEADER')
if page_break:
# self._ws.page_breaks.append(Break(id=row_idx))
self.breaks.append(Break(id=row_idx))
self._sub_total_data = {}
def page_break_print(fp):
# wb = Workbook()
# ws = wb.active
wb = load_workbook(fp)
table = pd.read_excel(fp)
ws = wb.active
# print(type(table))
count = 0
row_number = 1
flag = True
# header = ['指标', '12月', '01月', '02月', '03月', '04月', '05月', '06月', '07月', '08月', '09月', '10月', '11月', '合计']
for index,row in table.iterrows():
if flag == True:
flag = False
else:
# print(row['指标'], type(row['指标']))
if len(str(row['指标']).split(' ')[0]) != 21:
count = count + 1
continue
row_number += count # the row that you want to insert page break
row_number += 1
# print(count,row_number)
page_break = Break(id=row_number) # create Break obj
ws.page_breaks.append(page_break) # insert page break
count = 0
index += 2
rows = ws['A1':'N{}'.format(index)]
rows = list(rows)
# set_border(wb)
thin_border = Border(left=Side(style='dashed'),
right=Side(style='dashed'),
top=Side(style='dashed'),
bottom=Side(style='dashed'))
for x,cells in enumerate(rows):
for y,cell in enumerate(cells):
ws.cell(row=x+1,column=y+1).border = thin_border
wb.save(fp)
def Informe_financiero(sheet,
dic,
lista_alumnos,
cant_alumno,
v_saldo,
v_total,
tutor=False):
columnas_filas(sheet, 0, 'A', 5.00)
columnas_filas(sheet, 0, 'B', 8.00)
columnas_filas(sheet, 0, 'C', 35.00)
columnas_filas(sheet, 0, 'D', 10.00)
columnas_filas(sheet, 0, 'E', 10.00)
columnas_filas(sheet, 0, 'F', 10.00)
columnas_filas(sheet, 0, 'G', 8.00)
if not tutor:
if v_saldo and v_total:
columnas_filas(sheet, 0, 'L', 29.00)
elif v_saldo or v_total:
columnas_filas(sheet, 0, 'K', 29.00)
else:
columnas_filas(sheet, 0, 'J', 29.00)
else:
columnas_filas(sheet, 0, 'F', 35.00)
fuente = Font(bold=False, size=7, name='arial')
fuente3 = Font(bold=True, size=10, name='arial')
fuente2 = Font(bold=True, size=7, name='arial')
formato_numero = '#,##0.00'
fila = 3
if not tutor:
if v_saldo and v_total:
sheet.merge_cells('A2:L2')
elif v_saldo or v_total:
sheet.merge_cells('A2:K2')
else:
sheet.merge_cells('A2:J2')
else:
sheet.merge_cells('A2:F2')
sheet['A2'].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A2'].font = fuente3
if dic['seccion_id'] == False:
sheet['A2'] = 'COBRANZAS' + ' ' + str(dic['jornada_id'])
else:
sheet['A2'] = 'COBRANZAS' + ' ' + str(
dic['seccion_id'].encode('utf-8')) + ' ' + str(dic['jornada_id'])
if not tutor:
if v_saldo and v_total:
sheet.merge_cells('A3:L3')
elif v_saldo or v_total:
sheet.merge_cells('A3:K3')
else:
sheet.merge_cells('A3:J3')
else:
sheet.merge_cells('A3:F3')
sheet['A3'].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A3'].font = fuente2
sheet['A3'] = str(dic['fecha_corte'])
fila = 3
total_general = 0.0
saldo_general = 0.0
total_general_dias_mora = 0
total_general_pagos = 0
total_general_cheques_postfechados = 0
for recorrer in lista_alumnos:
columnas_filas(sheet, 1, str(fila + 2), 10.00)
columnas_filas(sheet, 1, str(fila + 3), 10.00)
sheet.merge_cells('A' + str(fila + 2) + ':B' + str(fila + 2))
sheet['A' + str(fila + 2)].alignment = Alignment(wrapText=True,
horizontal='left',
vertical='top')
sheet['A' + str(fila + 2)].font = fuente2
sheet['A' + str(fila + 2)] = 'Fecha de corte:'
sheet['C' + str(fila + 2)].alignment = Alignment(wrapText=True,
horizontal='left',
vertical='top')
sheet['C' + str(fila + 2)].font = fuente
sheet['C' + str(fila + 2)] = str(dic['fecha_corte'])
sheet['A' + str(fila + 3)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A' + str(fila + 3)].font = fuente
sheet['A' + str(fila + 3)] = 'Curso'
sheet.merge_cells('B' + str(fila + 3) + ':C' + str(fila + 3))
sheet['B' + str(fila + 3)].alignment = Alignment(wrapText=True,
horizontal='left',
vertical='top')
sheet['B' + str(fila + 3)].font = fuente
sheet['B' + str(fila + 3)] = (recorrer['curso'])
sheet['D' + str(fila + 3)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['D' + str(fila + 3)].font = fuente
sheet['D' + str(fila + 3)] = 'Paralelo: ' + str(
recorrer['paralelo'].encode('utf-8'))
if dic['seccion_id'] == False:
sheet['E' + str(fila + 3)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet['E' + str(fila + 3)].font = fuente
sheet['E' + str(fila + 3)] = 'Seccion: ' + str(
recorrer['seccion'].encode('utf-8'))
sheet['A' + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A' + str(fila + 5)].font = fuente2
sheet['A' + str(fila + 5)] = 'Tipo'
sheet['B' + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['B' + str(fila + 5)].font = fuente2
sheet['B' + str(fila + 5)] = 'Código Alumno'
sheet['C' + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['C' + str(fila + 5)].font = fuente2
sheet['C' + str(fila + 5)] = 'Alumno'
sheet['D' + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['D' + str(fila + 5)].font = fuente2
sheet['D' + str(fila + 5)] = 'Número'
sheet['E' + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['E' + str(fila + 5)].font = fuente2
sheet['E' + str(fila + 5)] = 'Emision'
if not tutor:
sheet['F' + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet['F' + str(fila + 5)].font = fuente2
sheet['F' + str(fila + 5)] = 'Vencimiento'
sheet['G' + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet['G' + str(fila + 5)].font = fuente2
sheet['G' + str(fila + 5)] = 'Días Mora'
letra = "H"
if v_total:
sheet['H' + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet['H' + str(fila + 5)].font = fuente2
sheet['H' + str(fila + 5)] = 'Valor'
letra = "I"
sheet[letra + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet[letra + str(fila + 5)].font = fuente2
sheet[letra + str(fila + 5)] = 'Pagos'
letra = aumentar_letra(letra)
sheet[letra + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet[letra + str(fila + 5)].font = fuente2
sheet[letra + str(fila + 5)] = 'Cheques Postfechados'
if v_saldo:
letra = aumentar_letra(letra)
sheet[letra + str(fila + 5)].alignment = Alignment(
wrapText=True, horizontal='center', vertical='top')
sheet[letra + str(fila + 5)].font = fuente2
sheet[letra + str(fila + 5)] = 'Saldo Actual'
if tutor:
letra = "F"
else:
letra = aumentar_letra(letra)
sheet[letra + str(fila + 5)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet[letra + str(fila + 5)].font = fuente2
sheet[letra + str(fila + 5)] = 'Comentario'
fila += 6
saldo = 0
total = 0
total_dias_mora = 0
total_pagos = 0
total_cheques_postfechados = 0
for det in recorrer['detalle']:
columnas_filas(sheet, 1, str(fila), 10.00)
sheet['A' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A' + str(fila)].font = fuente
sheet['A' + str(fila)] = det['tipo']
sheet['B' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['B' + str(fila)].font = fuente
sheet['B' + str(
fila)] = det['codigo_alumno'] if det['codigo_alumno'] else ''
sheet['C' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='justify',
vertical='top')
sheet['C' + str(fila)].font = fuente
sheet['C' + str(fila)] = det['alumno'] if det['alumno'] else ''
sheet['D' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['D' + str(fila)].font = fuente
sheet['D' + str(fila)] = det['numero'] if det['numero'] else ''
sheet['E' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet['E' + str(fila)].font = fuente
sheet['E' + str(fila)] = det['emision'] if det['emision'] else ''
if not tutor:
sheet['F' + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet['F' + str(fila)].font = fuente
sheet['F' + str(fila)] = det['vencimiento']
sheet['G' + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet['G' + str(fila)].font = fuente
sheet['G' + str(fila)] = det['dias_mora']
total_dias_mora += det['dias_mora']
letra = "H"
if v_total:
sheet['H' + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet['H' + str(fila)].font = fuente
sheet['H' + str(fila)].number_format = formato_numero
sheet['H' + str(fila)] = det["total"]
letra = "I"
sheet[letra + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet[letra + str(fila)].font = fuente
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = det['pagos']
total_pagos += det['pagos']
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet[letra + str(fila)].font = fuente
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = det['cheques_postfechados']
total_cheques_postfechados += det['cheques_postfechados']
if v_saldo:
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet[letra + str(fila)].font = fuente
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = det["saldo"]
if tutor:
letra = "F"
else:
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='left',
vertical='top')
sheet[letra + str(fila)].font = fuente
sheet[letra + str(fila)] = det['comentario']
saldo += float(det['saldo'])
total += float(det['total'])
fila += 1
if tutor:
page_break = Break(id=fila) # create Break obj
sheet.row_breaks.append(page_break) # insert page break
if not tutor:
#total
sheet['F' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['F' + str(fila)].font = fuente2
sheet['F' + str(fila)] = "TOTAL"
sheet['G' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet['G' + str(fila)].font = fuente2
sheet['G' + str(fila)] = total_dias_mora
letra = "H"
if v_total:
sheet[letra + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total
letra = "I"
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total_pagos
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total_cheques_postfechados
if v_saldo:
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(
wrapText=True, horizontal='right', vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = saldo
letra = "I"
total_general += total
saldo_general += saldo
total_general_cheques_postfechados += total_cheques_postfechados
total_general_dias_mora += total_dias_mora
total_general_pagos += total_pagos
fila = fila + 2
if not tutor:
#total general
sheet['F' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['F' + str(fila)].font = fuente2
sheet['F' + str(fila)] = 'Total General'
sheet['G' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet['G' + str(fila)].font = fuente2
sheet['G' + str(fila)] = total_general_dias_mora
letra = "H"
if v_total:
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total_general
letra = "I"
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total_general_pagos
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = total_general_cheques_postfechados
if v_saldo:
letra = aumentar_letra(letra)
sheet[letra + str(fila)].alignment = Alignment(wrapText=True,
horizontal='right',
vertical='top')
sheet[letra + str(fila)].font = fuente2
sheet[letra + str(fila)].number_format = formato_numero
sheet[letra + str(fila)] = saldo_general
sheet.merge_cells('A' + str(fila) + ':B' + str(fila))
sheet['A' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='center',
vertical='top')
sheet['A' + str(fila)].font = fuente2
sheet['A' + str(fila)] = 'Elaborado por:'
sheet.merge_cells('C' + str(fila) + ':D' + str(fila))
sheet['C' + str(fila)].alignment = Alignment(wrapText=True,
horizontal='left',
vertical='top')
sheet['C' + str(fila)].font = fuente
sheet['C' + str(fila)] = str(dic['usuario_id'])
def add_page_break(self, cell=None, row=None):
if cell:
page_break = Break(id=cell.row)
else:
page_break = Break(id=row)
self.ws.page_breaks[0].brk.append(page_break)
ws.page_margins.bottom = .5
wsprops = ws.sheet_properties
#Set Column Widths
for k, v in COLWIDTH.items():
ws.column_dimensions[k].width = v
i = 0
unit = 0
for row in reader:
newrow = []
if row[0].startswith('CA836'):
if unit == 3:
ws.row_breaks.append(Break(id=i))
unit = 0
i += 1
ws.merge_cells(start_row=i, start_column=1, end_row=i, end_column=10)
ws.cell(i, 11, "New Hours")
ws.cell(i, 1, value=row[0])
ws.cell(i, 12).style = 'hourfield'
i += 1
ws.append([
'Pos', 'Serial #', 'Brand', 'Description', 'Prev PSI', 'New PSI',
'Hot / Cold', 'Prev Outer TD', 'Current Outer TD', 'Prev Inner TD',
'Current Inner TD'
])
ws.row_dimensions[i].height = 30
for c in range(5, 12):
ws.cell(row=i, column=c).alignment = ALIGNMENT