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 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 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 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 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 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 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
value = float(value) / float(
data['Creatinine'])
except TypeError:
print(
'ERROR: Creatinine value for ID %s is not number.'
% file_id)
amounts[acid_name] = value
if acid_name in acid_norms:
new_cell = Cell(new_ws)
new_cell.value = value
total_cell = Cell(total_ws)
total_cell.value = value
lbound, rbound = acid_norms[acid_name]
if float(value) < float(lbound):
new_cell.fill = yellow_fill
total_cell.fill = yellow_fill
elif float(value) <= float(rbound):
new_cell.fill = green_fill
total_cell.fill = green_fill
else:
new_cell.fill = red_fill
total_cell.fill = red_fill
new_row.append(new_cell)
new_row.append('%s--%s' % (lbound, rbound))
total_row[1 +
acid_list.index(acid_name)] = total_cell
else:
new_row.append(value)
else:
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 get_value_from_placement_dict(attrib, placement_dict, week):
black_solid_fill = PatternFill("solid", fgColor="3b3b3b")
dark_grid_fill = PatternFill("darkGrid", fgColor="3b3b3b")
wb = Workbook()
ws = wb.active
return_cell = Cell(ws)
return_cell.value == ''
if attrib == 'fields':
return_cell.value == ''
if re.search('\d{1,2}', str(attrib)):
plan_weeks = placement_dict.get('plan_weeks')
fact_weeks = list()
for value in placement_dict['postclick']:
fact_weeks.append(int(value['weeknumber']))
if attrib in plan_weeks:
return_cell.fill = dark_grid_fill
if attrib in fact_weeks:
return_cell.fill = dark_grid_fill
if attrib in plan_weeks and attrib in fact_weeks:
return_cell.fill = black_solid_fill
if attrib in ('platform_site', 'description', 'format', 'plan_impressions',
'plan_reach', 'plan_clicks', 'plan_views', 'plan_budget'):
return_cell.value = placement_dict.get(attrib)
if attrib in ('fact_impressions', 'fact_clicks', "fact_reach",
"fact_views", 'fact_budget'):
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
return_cell.value = value.get(attrib)
if attrib == "plan_cpm":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_impressions"):
return_cell.value = placement_dict.get(
"plan_budget") * 1000 / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpt":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_reach") is not None:
return_cell.value = placement_dict.get(
"plan_budget") * 1000 / placement_dict.get("plan_reach")
else:
return_cell.value = "N/A"
if attrib == "plan_ctr":
if placement_dict.get(
"plan_clicks") is not None and placement_dict.get(
"plan_impressions") is not None:
return_cell.value = placement_dict.get(
"plan_clicks") / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpc":
if placement_dict.get(
"plan_budget") is not None and placement_dict.get(
"plan_clicks") is not None:
return_cell.value = placement_dict.get(
"plan_budget") / placement_dict.get("plan_clicks")
else:
return_cell.value = "N/A"
if attrib == "plan_vtr":
if placement_dict.get("plan_views") != 'N/A' and placement_dict.get(
"plan_impressions") is not None:
return_cell.value = placement_dict.get(
"plan_views") / placement_dict.get("plan_impressions")
else:
return_cell.value = "N/A"
if attrib == "plan_cpv":
if placement_dict.get(
"plan_budget"
) is not None and placement_dict.get("plan_views") != 'N/A':
return_cell.value = placement_dict.get(
"plan_budget") / placement_dict.get("plan_views")
else:
return_cell.value = "N/A"
if attrib == "fact_cpm":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get(
"fact_budget") * 1000 / value.get("fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpt":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_reach") is not None:
return_cell.value = value.get(
"fact_budget") * 1000 / value.get("fact_reach")
else:
return_cell.value = "N/A"
if attrib == "fact_ctr":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_clicks") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get("fact_clicks") / value.get(
"fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpc":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_clicks") is not None:
return_cell.value = value.get("fact_budget") / value.get(
"fact_clicks")
else:
return_cell.value = "N/A"
if attrib == 'fact_vtr':
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_views") is not None and value.get(
"fact_impressions") is not None:
return_cell.value = value.get("fact_views") / value.get(
"fact_impressions")
else:
return_cell.value = "N/A"
if attrib == "fact_cpv":
for value in placement_dict['postclick']:
if value['weeknumber'] == week:
if value.get("fact_budget") is not None and value.get(
"fact_views") is not None:
return_cell.value = value.get("fact_budget") / value.get(
"fact_views")
else:
return_cell.value = "N/A"
if attrib == 'period':
return_cell.value = str(len(
placement_dict.get('plan_weeks'))) + " weeks"
if attrib == 'fact_impressions_adriver':
if placement_dict.get('adriver_id') is not None:
return_cell.value = get_adriver_value(
placement_dict.get('adriver_id'), week)
else:
return_cell.value = "N/A"
return return_cell
