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