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 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 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 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_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 set_borders_between_columns(cell: Cell) -> None:
cell.border = Border(left=constants.BORDER_STYLE.value,
right=constants.BORDER_STYLE.value)
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 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 style_value_cell(cell: Cell) -> None:
"""Stylize a header cell
:param cell:
"""
cell.border = THIN_BLACK_BORDER
def style_single_cell(self, cell: Cell, style: Style) -> None:
cell.border = style.border
cell.alignment = style.alignment
cell.font = style.font
def separate_days(cell: Cell) -> None:
cell.border = Border(left=constants.BORDER_STYLE.value,
right=constants.BORDER_STYLE.value,
top=constants.BORDER_STYLE.value)
