def StyleMerge(ws, val, range): ws.merge_cells(range) cell = Cell(ws, value=val) cell.font = Font(bold=True, color=colors.WHITE) cell.fill = PatternFill(fill_type='solid', fgColor=colors.BLACK) cell.alignment = Alignment(horizontal='center') return cell
def CellCopy(ws, s_cell): t_cell = Cell(ws, value=s_cell.value) t_cell.font = copy(s_cell.font) t_cell.fill = copy(s_cell.fill) t_cell.alignment = copy(s_cell.alignment) t_cell.border = copy(s_cell.border) return t_cell
def put_text(cell: Cell, text, font=None, border=None, alignment=None): cell.value = text if font: cell.font = font if border: cell.border = border if alignment: cell.alignment = alignment return cell
def set_format_styles_for_main_cells(cell: Cell, is_header: bool) -> None: cell.font = Font(name=constants.FONT.value, bold=True) cell.alignment = Alignment(horizontal=constants.CENTER_ALINGMENT.value) cell.border = (Border( bottom=constants.BORDER_STYLE.value, top=constants.BORDER_STYLE.value, right=constants.BORDER_STYLE.value, left=constants.BORDER_STYLE.value, ) if is_header else Border(top=constants.BORDER_STYLE.value))
def set_and_fill_hours_cell(cell: Cell, cell_value: str) -> None: if cell_value is not None: cell.value = cell_value else: cell.value = cell_value alignment = Alignment(vertical=constants.VERCTICAL_TOP.value, horizontal=constants.CENTER_ALINGMENT.value) cell.alignment = alignment cell.number_format = constants.HOURS_FORMAT.value set_borders_between_columns(cell)
def copyCellFormat(cellsrc:Cell, sheetSrc:Worksheet, celldest:Cell, sheetDes:Worksheet): celldest.fill = copy(cellsrc.fill) celldest.font = copy(cellsrc.font) celldest.border = copy(cellsrc.border) celldest.alignment = copy(cellsrc.alignment) celldest.number_format = copy(cellsrc.number_format) celldest.protection = copy(cellsrc.protection) for merged_cell in sheetSrc.merged_cells: if merged_cell.min_col==cellsrc.col_idx and merged_cell.min_row==cellsrc.row: sheetDes.merge_cells(start_row= celldest.row, end_row= celldest.row, start_column= merged_cell.min_col, end_column= merged_cell.max_col) break
def write_sheet(self, name, fields, rows): # add the sheet to the workbook ws = self.workbook.create_sheet(title=name) # add the header row row = list() for field in fields: cell = Cell(ws, value=field["title"]) # pylint: disable=assigning-non-slot cell.alignment = Alignment(horizontal="center") cell.font = Font(name=ReportWriter.TYPEFACE, size=ReportWriter.FONT_SIZE, bold=True) row.append(cell) ws.append(row) # add the data rows for data in rows: row = list() for field in fields: cell = Cell(ws, value=data[field["name"]]) # pylint: disable=assigning-non-slot cell.alignment = Alignment( horizontal=ReportWriter.DEFAULT_ALIGNMENT) if "align" in field.keys(): cell.alignment = Alignment(horizontal=field["align"]) cell.font = Font(name=ReportWriter.TYPEFACE, size=ReportWriter.FONT_SIZE) if "number_format" in field.keys(): cell.number_format = field["number_format"] row.append(cell) ws.append(row) # apply column widths for i, field in enumerate(fields): # get the alphabetical column index alpha_index = get_column_letter(i + 1) # set the column width ws.column_dimensions[alpha_index].width = field["width"]
def print_sales_content(collection, ws): # Table Header table_headers = ['Date', 'O.R. #', 'Name', 'Amount', 'Remarks'] table_header_cells = [] for h in table_headers: c = underline_border_cell(h, ws) table_header_cells.append(c) r = [''] + table_header_cells ws.append(r) sales_total = 0 for item in collection: if item.label == 'Cemetery Lot': amount = item.lot_area * item.price_per_sq_mtr # elif item.label == 'Cremation': # todo no amount for cremation yet # amount = 0 elif item.label == 'Columbary': amount = item.price if item.price is not None else 0 amount_formatted = 'P {:20,.2f}'.format(amount) amount_formatted_cell = Cell(ws, value=amount_formatted) amount_formatted_cell.style = Style(alignment=Alignment( horizontal='right')) client_name = item.client.get_full_name( ) if item.client is not None else '' sales_total += amount ws.append([ '', item.date_purchased, item.or_no, client_name, amount_formatted_cell, item.label ]) # Sales Total total_label_cell = Cell(ws, value='TOTAL') total_label_cell.font = Font(size=12, color='FFFF0000') total_cell = Cell(ws, value='P {:20,.2f}'.format(sales_total)) total_cell.font = Font(size=12, color='FFFF0000') total_cell.border = total_border total_cell.alignment = Alignment(horizontal='right') ws.append(['', '', '', total_label_cell, total_cell])
def print_sales_content(collection, ws): # Table Header table_headers = ['Date', 'O.R. #', 'Name', 'Amount', 'Remarks'] table_header_cells = [] for h in table_headers: c = underline_border_cell(h, ws) table_header_cells.append(c) r = [''] + table_header_cells ws.append(r) sales_total = 0 for item in collection: if item.label == 'Cemetery Lot': amount = item.lot_area * item.price_per_sq_mtr # elif item.label == 'Cremation': # todo no amount for cremation yet # amount = 0 elif item.label == 'Columbary': amount = item.price if item.price is not None else 0 amount_formatted = 'P {:20,.2f}'.format(amount) amount_formatted_cell = Cell(ws, value=amount_formatted) amount_formatted_cell.style = Style(alignment=Alignment(horizontal='right')) client_name = item.client.get_full_name() if item.client is not None else '' sales_total += amount ws.append(['', item.date_purchased, item.or_no, client_name, amount_formatted_cell, item.label]) # Sales Total total_label_cell = Cell(ws, value='TOTAL') total_label_cell.font = Font(size=12, color='FFFF0000') total_cell = Cell(ws, value='P {:20,.2f}'.format(sales_total)) total_cell.font = Font(size=12, color='FFFF0000') total_cell.border = total_border total_cell.alignment = Alignment(horizontal='right') ws.append(['', '', '', total_label_cell, total_cell])
def WriteExcel(fileName, iList, oList, pList, pageList): wb = Workbook() ws = wb.active #first write fileName as title ws.merge_cells('A1:F1') cell = Cell(ws, value=fileName.split('.')[0]) cell.font = Font(bold=True) cell.alignment = Alignment(horizontal='center') ws.append([cell]) ws.append([None]) #write input ports list ws.append([StyleMerge(ws, 'INPUTS', 'A3:C3')]) ws.append( StyleRange(ws, ['INDEX', 'POINT DESCRIPTION', 'TAG NAME'], True, colors.YELLOW)) conflict_lst = CheckIndexConflict(iList) for pin in iList: ws.append([pin.index, pin.desc]) if conflict_lst[iList.index(pin)]: ws[len(list(ws.rows))][0].fill = PatternFill(fill_type='solid', fgColor=colors.RED) else: pass StyleBorder(ws, 3, len(iList) + 4, 3) ws.append([None]) #write output ports list ws.append([StyleMerge(ws, 'OUTPUTS', 'A{0}:D{0}'.format(len(iList) + 6))]) ws.append( StyleRange(ws, ['INDEX', 'POINT DESCRIPTION', 'TAG NAME', 'DESCRIPTION'], True, colors.YELLOW)) conflict_lst = CheckIndexConflict(oList) for pin in oList: if not pin.inPointDir: ws.append( StyleRange(ws, [pin.index, pin.desc], False, colors.GREEN)) else: ws.append([pin.index, pin.desc]) if conflict_lst[oList.index(pin)]: ws[len(list(ws.rows))][0].fill = PatternFill(fill_type='solid', fgColor=colors.RED) else: pass StyleBorder(ws, len(iList) + 6, len(iList + oList) + 7, 4) ws.append([None]) #write function blocks list ws.append([ StyleMerge(ws, 'PARAMETERS', 'A{0}:F{0}'.format(len(iList + oList) + 9)) ]) ws.append( StyleRange(ws, [ 'INDEX', 'FUNCTION BLOCK DESCRIPTION', 'TAG NAME', 'DESCRIPTION', 'PARA', 'VALUE' ], True, colors.YELLOW)) conflict_lst = CheckIndexConflict(pList) for pin in pList: if not pin.inPointDir: ws.append( StyleRange(ws, [pin.index, pin.desc], False, colors.GREEN)) else: ws.append([pin.index, pin.desc]) if conflict_lst[pList.index(pin)]: ws[len(list(ws.rows))][0].fill = PatternFill(fill_type='solid', fgColor=colors.RED) else: pass StyleBorder(ws, len(iList + oList) + 9, len(iList + oList + pList) + 10, 6) #change the width of the columns ws.column_dimensions['B'].width = 30.0 ws.column_dimensions['C'].width = 15.0 ws.column_dimensions['D'].width = 30.0 #store page information in seperate sheet ws = wb.create_sheet('Info') ws.append(['Page count', len(pageList)]) ws.append(['Page list'] + pageList) #file is outputed under './Library' directory wb.save('Library/' + RemovePath(fileName).replace('.txt', '.xlsx'))
def process_xls(data, config=None): header = data['header'] title = header['title'] origin = data['dataOrigin'] book = Workbook() sheet = book.active doc_id = unique_id() files_path = config.get('files', 'path') if 'logoURL' in header: try: response = requests.get(header['logoURL'], stream=True) logo = Image(response.raw) logo = Image(logo.image.resize((100, 100))) except requests.ConnectionError as cerror: print(cerror, file=sys.stderr) else: logo = None hdr_bkg_color = header['backgroundColor'] header_bkg = PatternFill(fill_type="solid", start_color=hdr_bkg_color, end_color=hdr_bkg_color) colformats = [] coltypes = [] has_formats = False columns = data.get('columns', []) try: for col in columns: colfmt = col.get('format', None) coltype = col.get('type', None) colformats.append(colfmt) coltypes.append(coltype) has_formats = True except TypeError: pass if origin == 'array': rows = data['rows'] cell = Cell(sheet, value=title) cell.alignment = Alignment(horizontal='center', vertical='center') sheet.append(['', '', '', cell]) sheet.merge_cells('A1:C1') sheet.merge_cells('D1:G1') for row in rows: cells = [] for value in row: cell = Cell(sheet, value=value) cells.append(cell) sheet.append(cells) else: db = data['database'] sql_query = data['sqlQuery'] url_callback = data['urlCallback'] title = data['title'] columns = data['columns'] """ conn = pg_connect(host=db['host'], database=db['name'], password=db['password'], user=db['user']) cursor = conn.cursor() cursor.execute(sql_query) """ index = 0 is_first = True for row in cursor: if is_first: sheet.merge_cells('A1:C1') sheet.merge_cells('D1:G1') sheet.append(['', '', '', cell]) if logo: sheet.add_image(logo, 'A1') headcells = [] for col in columns: cell = Cell(sheet, value=col['label']) cell.fill = header_bkg coltype = col.get('type', None) colfmt = col.get('format', None) columns_format.append(colfmt) columns_type.append(coltype) headcells.append(cell) sheet.append(headcells) is_first = False #sheet.row_dimensions[0].height = 300 sheet.row_dimensions[1].height = 72 sheet.append(row) index += 1 outfile = '{}/{}.xlsx'.format(files_path, doc_id) book.save(outfile) return doc_id
def style_single_cell(self, cell: Cell, style: Style) -> None: cell.border = style.border cell.alignment = style.alignment cell.font = style.font
def set_and_fill_cell(cell: Cell, cell_value: str) -> None: wrapped_alignment = Alignment(vertical=constants.VERCTICAL_TOP.value, wrap_text=True) cell.alignment = wrapped_alignment cell.value = cell_value set_borders_between_columns(cell)