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