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 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 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 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 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 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
