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