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 underline_border_cell(val, ws): underline_border = Border(bottom=Side(style='thin')) c = Cell(ws, value=val) c.font = Font(size=11, bold=True) c.border = underline_border return c
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 style_header_cell(cell: Cell) -> None: """Stylize a header cell :param cell: """ cell.fill = SOLID_BLUE_FILL cell.border = THIN_BLACK_BORDER cell.font = HEADER_FONT
def StyleRange(ws, str_list, isBold, fillColor): row_list = [] for str_i in str_list: cell = Cell(ws, value=str_i) cell.font = Font(bold=isBold) cell.fill = PatternFill(fill_type='solid', fgColor=fillColor) row_list.append(cell) return row_list
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 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 styled_row(sheet, data, number_format=None, bold=False, color=None, color_txt=None): """ Adds styles to row data before writing """ for cell in data: cell = Cell(sheet, column="A", row=1, value=cell) if number_format is not None: cell.number_format = number_format if bold: cell.font = Font(bold=True) if color is not None: cell.fill = PatternFill(start_color=color, fill_type='solid') if color_txt is not None: cell.font = Font(color=color_txt) yield 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 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 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 write_xlsx(writing_data, headers, output_file, color_delta=False): outfile_path = os.path.join(os.path.dirname(output_file), os.path.basename(output_file) + '.xlsx') wb = Workbook() ws = wb.active thin = Side(border_style="thin", color="000000") double = Side(border_style="medium", color="000000") header_cells = [Cell(ws, column="A", row=1, value=header) for header in headers] for cell in header_cells: cell.font = Font(bold=True) cell.border = Border(top=double, left=double, right=double, bottom=double) cell.alignment = Alignment(horizontal="center", vertical="center") ws.append(header_cells) for report_name, report in writing_data.items(): title_cell = Cell(ws, column="A", row=1, value=report['title']) title_cell.font = Font(bold=True) # title_cell.alignment = Alignment(horizontal="center", vertical="center") ws.append([title_cell]) for row in report['rows']: cells = [Cell(ws, column="A", row=1, value=(row[header] if header in row else "")) for header in headers] for i,cell in enumerate(cells): # if i == 0: # cell.font = Font(bold=True) cell.border = Border(top=thin, left=thin, right=thin, bottom=thin) cell.alignment = Alignment(horizontal="center", vertical="center") if color_delta: if str(cell.value).startswith('+'): if float(cell.value) != 0: cell.font = Font(bold=True, color='00a933') else: cell.font = Font(bold=True) elif str(cell.value).startswith('-'): if float(cell.value) != 0: cell.font = Font(bold=True, color='c9211e') else: cell.font = Font(bold=True) ws.append(cells) column_widths = [] for i,header in enumerate(headers): column_widths.append(len(header) * 1.4 + 4) for i, column_width in enumerate(column_widths): ws.column_dimensions[chr(ord('A') + i)].width = column_width wb.save(outfile_path)
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 style_single_cell(self, cell: Cell, style: Style) -> None: cell.border = style.border cell.alignment = style.alignment cell.font = style.font
def styled_cells(data): for idx, val in enumerate(data): if idx == 0: val = Cell(ws, value=val) val.font = Font(underline='single', color='0563C1') yield val
def create_bordered_cell(val, ws, is_bold=False): c = Cell(ws, value=val) c.font = Font(size=11, bold=is_bold) c.border = thin_border return c
def print_lot_list_sections_data(ws): sections = section_service.get_sections() for section in sections: lots = section.get_lots() sold_lots = filter(lambda lot: lot['status'] == 'sold' or lot['status'] == 'occupied', lots) unsold_lots = filter(lambda lot: lot['status'] != 'sold' and lot['status'] != 'occupied', lots) # SECTION NAME ROW section_name = 'SECTION ' + section.name section_name_cell = Cell(ws, column='C', value=section_name) section_name_cell.font = Font(size=14, bold=True) section_name_cell.border = thin_border ws.append([None, None, section_name_cell]) # NO OF BLOCKS ROW no_block_cell = create_bordered_cell('NO. OF BLOCKS', ws, True) no_block_value_cell = create_bordered_cell(len(section.blocks), ws) ws.append([None, None, no_block_cell, no_block_value_cell]) # NO OF LOTS ROW no_lots_cell = create_bordered_cell('NO. OF LOTS', ws, True) no_lots_value_cell = create_bordered_cell(len(lots), ws) ws.append([None, None, no_lots_cell, no_lots_value_cell]) # NO OF SOLD LOTS ROW no_sold_lots_cell = create_bordered_cell('SOLD LOTS', ws, True) no_sold_lots_value_cell = create_bordered_cell(len(sold_lots), ws) ws.append([None, None, no_sold_lots_cell, no_sold_lots_value_cell]) # NO OF UNSOLD LOTS ROW no_unsold_lots_cell = create_bordered_cell('UNSOLD LOTS', ws, True) no_unsold_lots_value_cell = create_bordered_cell(len(unsold_lots), ws) ws.append([None, None, no_unsold_lots_cell, no_unsold_lots_value_cell]) ws.append([]) # Table Header table_headers = ['BLOCK', 'LOT NO.', 'DIMENSION', 'AREA', 'PRICE/SM', 'AMOUNT', 'REMARKS', 'OWNER', 'DATE PURCHASED'] table_header_cells = [] for h in table_headers: c = create_bordered_cell(h, ws, True) table_header_cells.append(c) r = [''] + table_header_cells ws.append(r) # TABLE BODY - LOTS # sorted_blocks = sorted(section.blocks, key=lambda block: block.name) for b in section.blocks: sorted_lots = sorted(b.lots, key=lambda lot: int(re.sub('[^0-9]', '', lot.name))) for l in sorted_lots: client_name = l.client.get_full_name() if l.client is not None else '' price_formatted = '{:20,.2f}'.format(l.price_per_sq_mtr) amount = '{:20,.2f}'.format(l.lot_area * l.price_per_sq_mtr) row_val = [b.name, l.name, l.dimension, l.lot_area, price_formatted, amount, l.remarks, client_name, l.date_purchased] row_cells = [] for rv in row_val: row_cells.append(create_bordered_cell(rv, ws)) ws.append([''] + row_cells) # Total of Lots per block ws.append([len(b.lots)]) print_row_spacer(2, ws)
def print_lot_list_sections_data(ws): sections = section_service.get_sections() for section in sections: lots = section.get_lots() sold_lots = filter(lambda lot: lot['status'] == 'sold', lots) unsold_lots = filter(lambda lot: lot['status'] != 'sold', lots) # SECTION NAME ROW section_name = 'SECTION ' + section.name section_name_cell = Cell(ws, column='C', value=section_name) section_name_cell.font = Font(size=14, bold=True) section_name_cell.border = thin_border ws.append([None, None, section_name_cell]) # NO OF BLOCKS ROW no_block_cell = create_bordered_cell('NO. OF BLOCKS', ws, True) no_block_value_cell = create_bordered_cell(len(section.blocks), ws) ws.append([None, None, no_block_cell, no_block_value_cell]) # NO OF LOTS ROW no_lots_cell = create_bordered_cell('NO. OF LOTS', ws, True) no_lots_value_cell = create_bordered_cell(len(lots), ws) ws.append([None, None, no_lots_cell, no_lots_value_cell]) # NO OF SOLD LOTS ROW no_sold_lots_cell = create_bordered_cell('SOLD LOTS', ws, True) no_sold_lots_value_cell = create_bordered_cell(len(sold_lots), ws) ws.append([None, None, no_sold_lots_cell, no_sold_lots_value_cell]) # NO OF UNSOLD LOTS ROW no_unsold_lots_cell = create_bordered_cell('UNSOLD LOTS', ws, True) no_unsold_lots_value_cell = create_bordered_cell(len(unsold_lots), ws) ws.append([None, None, no_unsold_lots_cell, no_unsold_lots_value_cell]) ws.append([]) # Table Header table_headers = [ 'BLOCK', 'LOT NO.', 'DIMENSION', 'AREA', 'PRICE/SM', 'AMOUNT', 'REMARKS', 'OWNER', 'DATE PURCHASED' ] table_header_cells = [] for h in table_headers: c = create_bordered_cell(h, ws, True) table_header_cells.append(c) r = [''] + table_header_cells ws.append(r) # TABLE BODY - LOTS for b in section.blocks: for l in b.lots: client_name = l.client.get_full_name( ) if l.client is not None else '' price_formatted = '{:20,.2f}'.format(l.price_per_sq_mtr) amount = '{:20,.2f}'.format(l.lot_area * l.price_per_sq_mtr) row_val = [ b.id, l.id, l.dimension, l.lot_area, price_formatted, amount, l.remarks, client_name, l.date_purchased ] row_cells = [] for rv in row_val: row_cells.append(create_bordered_cell(rv, ws)) ws.append([''] + row_cells) # Total of Lots per block ws.append([len(b.lots)]) print_row_spacer(2, ws)
def get_audit_log_workbook(ws, program): # helper for indicator name column def _indicator_name(indicator): if indicator.results_aware_number: return u'{} {}: {}'.format( _('Indicator'), unicode(indicator.results_aware_number), unicode(indicator.name), ) else: return u'{}: {}'.format( _('Indicator'), unicode(indicator.name), ) # helper for result level column def _result_level(indicator): if indicator.leveltier_name and indicator.level_display_ontology: return u'{} {}'.format( unicode(indicator.leveltier_name), unicode(indicator.level_display_ontology), ) elif indicator.leveltier_name: return unicode(indicator.leveltier_name) else: return '' header = [ Cell(ws, value=_("Date and Time")), # Translators: Number of the indicator being shown Cell(ws, value=_('Result Level')), Cell(ws, value=_('Indicator')), Cell(ws, value=_('User')), Cell(ws, value=_('Organization')), # Translators: Part of change log, indicates the type of change being made to a particular piece of data Cell(ws, value=_('Change type')), # Translators: Part of change log, shows what the data looked like before the changes Cell(ws, value=_('Previous entry')), # Translators: Part of change log, shows what the data looks like after the changes Cell(ws, value=_('New entry')), # Translators: Part of change log, reason for the change as entered by the user Cell(ws, value=_('Rationale')) ] title = Cell(ws, value=_("Change log")) title.font = Font(size=18) ws.append([title,]) ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=len(header)) subtitle = Cell(ws, value=program.name) subtitle.font = Font(size=18) ws.append([subtitle,]) ws.merge_cells(start_row=2, end_row=2, start_column=1, end_column=len(header)) header_font = Font(bold=True) header_fill = PatternFill('solid', 'EEEEEE') for h in header: h.font = header_font h.fill = header_fill ws.append(header) alignment = Alignment( horizontal='general', vertical='top', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0 ) for row in program.audit_logs.all().order_by('-date'): prev_string = u'' for entry in row.diff_list: if entry['name'] == 'targets': for k, target in entry['prev'].iteritems(): prev_string += unicode(target['name'])+u": "+unicode(target['value'])+u"\r\n" else: prev_string += unicode(entry['pretty_name'])+u": "+unicode(entry['prev'] if entry['prev'] else _('N/A'))+u"\r\n" new_string = u'' for entry in row.diff_list: if entry['name'] == 'targets': for k, target in entry['new'].iteritems(): new_string += unicode(target['name'])+u": "+unicode(target['value'])+u"\r\n" else: new_string += unicode(entry['pretty_name'])+u": "+unicode(entry['new'] if entry['new'] else _('N/A'))+u"\r\n" xl_row = [ Cell(ws, value=row.date), Cell(ws, value=unicode(_result_level(row.indicator)) if row.indicator else _('N/A')), Cell(ws, value=unicode(_indicator_name(row.indicator)) if row.indicator else _('N/A')), Cell(ws, value=unicode(row.user.name)), Cell(ws, value=unicode(row.organization.name)), Cell(ws, value=unicode(row.pretty_change_type)), Cell(ws, value=unicode(prev_string)), Cell(ws, value=unicode(new_string)), Cell(ws, value=unicode(row.rationale)) ] for cell in xl_row: cell.alignment = alignment ws.append(xl_row) for rd in ws.row_dimensions: rd.auto_size = True for cd in ws.column_dimensions: cd.auto_size = True widths = [20, 12, 50, 20, 15, 20, 40, 40, 40] for col_no, width in enumerate(widths): ws.column_dimensions[utils.get_column_letter(col_no + 1)].width = width return ws