def format_cell_style(ws, language_dict): style_int = NamedStyle('int') style_int.number_format = '0' style_str = NamedStyle('str') style_str.number_format = '@' style_pcnt = NamedStyle('pcnt') style_pcnt.number_format = '0.0%' for cell in ws[column_index[language_dict['id']]][1:]: cell.style = style_int for cell in ws[column_index[language_dict['total_submitted']]][1:]: cell.style = style_int for cell in ws[column_index[language_dict['total_acs']]][1:]: cell.style = style_int for cell in ws[column_index[language_dict['title']]][1:]: cell.style = style_str for cell in ws[column_index[language_dict['slug']]][1:]: cell.style = style_str for cell in ws[column_index[language_dict['difficulty']]][1:]: cell.style = style_str for cell in ws[column_index[language_dict['paid_only']]][1:]: cell.style = style_str for cell in ws[column_index[language_dict['status']]][1:]: cell.style = style_str for cell in ws[column_index[language_dict['acceptance']]][1:]: cell.style = style_pcnt
def __init__( self, lang: str = "FR", url_base_edit: str = "", url_base_view: str = "", # additional **kwargs, ): """Instanciating the output workbook. :param str lang: selected language for output :param str url_base_edit: base url to format edit links (basically app.isogeo.com) :param str url_base_view: base url to format view links (basically open.isogeo.com) """ super(Isogeo2xlsx, self).__init__() # super(Isogeo2xlsx, self).__init__(write_only=True) self.stats = Stats() # URLS utils.app_url = url_base_edit # APP utils.oc_url = url_base_view # OpenCatalog url # styles s_date = NamedStyle(name="date") s_wrap = NamedStyle(name="wrap") s_wrap.alignment = Alignment(wrap_text=True) self.add_named_style(s_date) self.add_named_style(s_wrap) # deleting the default worksheet ws = self.active self.remove(ws) # LOCALE if lang.lower() == "fr": s_date.number_format = "dd/mm/yyyy" self.dates_fmt = "DD/MM/YYYY" self.locale_fmt = "fr_FR" self.tr = I18N_FR else: s_date.number_format = "yyyy/mm/dd" self.dates_fmt = "YYYY/MM/DD" self.locale_fmt = "uk_UK" self.tr = I18N_EN # FORMATTER self.fmt = Formatter(output_type="Excel")
def makeStyle (self, name, copyfromcell, sheet=None): ws = self._getTable (sheet) newstyle = NamedStyle (name=name) newstylecell = ws [copyfromcell] newstyle.font = copy (newstylecell.font) newstyle.fill = copy (newstylecell.fill) newstyle.border = copy (newstylecell.border) newstyle.alignment = copy (newstylecell.alignment) newstyle.number_format = newstylecell.number_format newstyle.protection = copy (newstylecell.protection) return newstyle
def __init__(self, lang: str = "EN", texts=OrderedDict()): """TO DOC. Keyword arguments: """ super(MetadataToXlsx, self).__init__() self.txt = texts # styles s_date = NamedStyle(name="date") s_date.number_format = "dd/mm/yyyy" s_wrap = NamedStyle(name="wrap") s_wrap.alignment = Alignment(wrap_text=True) self.add_named_style(s_date) self.add_named_style(s_wrap) # deleting the default worksheet ws = self.active self.remove_sheet(ws)
def add_named_style(wb, name, number_format=None, font_name='Calibri', font_size=10, font_color='FF000000', fill=None): """Add a named style to the open workbook. Already named styles are ignored. See add_default_styles for example usage. """ if name in wb.named_styles: return sty = NamedStyle(name=name) sty.font = Font(name=font_name, size=font_size, color=font_color) if fill: sty.fill = fill if number_format: sty.number_format = number_format wb.add_named_style(sty)
# # Write Output # centered = Alignment(horizontal="center", vertical="center") bd = Side(border_style="thin") header_style = NamedStyle(name="header_style") header_style.alignment = centered header_style.font = Font(bold=True) header_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) data_style = NamedStyle(name="data_style") # data_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) data_style.alignment = centered data_style.number_format = '0.0' def get_header(data_frame): header = data_frame.columns.tolist() header.extend(STAT_HEADER) return header def get_n_data_rows(data_frame): return data_frame.shape[0] def get_meth_percent_range(header): """ Get (first_col_no, last_col_no) of the columns that represent the methylation percentage measurements. """ first_col = header.index('Pos. 1')
def generate_styles(self): """Создание базовых стилей Названия стилей: 'Заголовок 1' 'Заголовок 2' 'Заголовок 3' 'Заголовок 32' 'Заголовок 4' 'Заголовок 42' 'Заголовок 5' 'Заголовок 52' 'Заголовок 6' 'Заголовок 62' 'Заголовок 7' 'Заголовок 8' 'Итоги 1' 'Таблица 1' 'Линейка 1 'Шапка 1' """ styles = [ { "name": "Заголовок 1", "sc": "F2F2F2", "ec": "F2F2F2", "bc": "D9D9D9", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 2", "sc": "D9D9D9", "ec": "D9D9D9", "bc": "BFBFBF", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 3", "sc": "8DB4E2", "ec": "8DB4E2", "bc": "16365C", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 4", "sc": "B8CCE4", "ec": "B8CCE4", "bc": "366092", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 5", "sc": "E6B8B7", "ec": "E6B8B7", "bc": "963634", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 6", "sc": "D8E4BC", "ec": "D8E4BC", "bc": "76933C", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 7", "sc": "CCC0DA", "ec": "CCC0DA", "bc": "60497A", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 8", "sc": "B7DEE8", "ec": "B7DEE8", "bc": "31869B", "b": True, "horAlign": "right", "bb": "thin", "ft": "solid", }, { "name": "Заголовок 32", "sc": "8DB4E2", "ec": "8DB4E2", "bc": "16365C", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Заголовок 42", "sc": "B8CCE4", "ec": "B8CCE4", "bc": "366092", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Заголовок 52", "sc": "E6B8B7", "ec": "E6B8B7", "bc": "963634", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Заголовок 62", "sc": "D8E4BC", "ec": "D8E4BC", "bc": "76933C", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Заголовок 72", "sc": "CCC0DA", "ec": "CCC0DA", "bc": "60497A", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Заголовок 82", "sc": "B7DEE8", "ec": "B7DEE8", "bc": "31869B", "b": True, "horAlign": "right", "bb": "double", "bt": "double", "ft": "solid", }, { "name": "Итоги 1", "sc": "FABF8F", "ec": "FABF8F", "bc": "E26B0A", "b": True, "horAlign": "right", "bb": "thin", "bt": "thick", "ft": "solid", }, { "name": "Таблица 1", "bl": "thin", "br": "thin", "bt": "thin", "bb": "thin", "wr": True, "bc": "808080", }, { "name": "Линейка 1", "bb": "thin", "wr": True, "bc": "808080" }, { "name": "Шапка 1", "bl": "thin", "br": "thin", "bt": "thin", "bb": "thin", "b": True, }, ] for s in styles: font = Font( name=self.font, size=self.fontsize, bold=s.get("b", False), italic=s.get("itl", False), vertAlign=s.get("vertAlign"), underline="none", strike=False, color=s.get("txtclr", "000000"), ) fill = PatternFill(fill_type=s.get("ft"), start_color=s.get("sc"), end_color=s.get("ec")) border = Border( left=Side(border_style=s.get("bl"), color=s.get("bc", "000000")), right=Side(border_style=s.get("br"), color=s.get("bc", "000000")), top=Side(border_style=s.get("bt"), color=s.get("bc", "000000")), bottom=Side(border_style=s.get("bb"), color=s.get("bc", "000000")), diagonal=Side(border_style=None, color=s.get("bc", "000000")), diagonal_direction=0, outline=Side(border_style=None, color=s.get("bc", "000000")), vertical=Side(border_style=s.get("bv"), color=s.get("bc", "000000")), horizontal=Side(border_style=s.get("bh"), color=s.get("bc", "000000")), ) alignment = Alignment( horizontal=s.get("horAlign", "left"), vertical="bottom", text_rotation=0, wrap_text=s.get("wr", False), shrink_to_fit=False, indent=0, ) tab = NamedStyle(name=s["name"]) tab.font = font tab.fill = fill tab.border = border tab.alignment = alignment tab.number_format = None self.wb.add_named_style(tab)
title_style.alignment = Alignment(horizontal='center', vertical='center', wrapText=True) title_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) num_style = NamedStyle(name='num_style') num_style.font = Font(name='微软雅黑', size=11) num_style.alignment = Alignment(horizontal='center', vertical='center') num_style.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) num_style.number_format = '0.00' num_style_fill = NamedStyle(name='num_style_fill') num_style_fill.font = Font(name='微软雅黑', size=11) num_style_fill.alignment = Alignment(horizontal='center', vertical='center') num_style_fill.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) num_style_fill.fill = PatternFill('solid', fgColor='E5E5E5') num_style_fill.number_format = '0.00' str_style = NamedStyle(name='str_style') str_style.font = Font(name='微软雅黑', size=11) str_style.alignment = Alignment(horizontal='center', vertical='center') str_style.border = Border(left=Side(style='thin'),
def __init__(self, wb, a=(1, 1)): ''' Create named styles. Register them with the Workbook wb, and add them to self.styles, a dict with named style title as a key. :params wb: The openpyxl Workbook object to register styes into :params a: A safe place to store the anchor in self.tradeAnchor. Overridable with every method and function that can use an achor to translate the location in the Workbook. ''' self.tradeAnchor = a self.styles = dict() # This code is subject to change on a whim. Primary importance is easy to find styles and # consistent layout # ###################################### titleStyle ###################### titleStyle = NamedStyle(name="titleStyle") titleStyle.font = Font(color="FFFFFF", size=16) titleStyle.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) titleStyle.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') titleStyle.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(titleStyle, 'titleStyle', wb) # ###################################### titleLeft ####################### titleLeft = NamedStyle(name="titleLeft") titleLeft.font = Font(color="FFFFFF", size=16) titleLeft.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) titleLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') titleLeft.border = Border(left=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(titleLeft, 'titleLeft', wb) # ###################################### titleNumberRight ###################### titleNumberRight = NamedStyle(name="titleNumberRight") titleNumberRight.font = Font(color="FFFFFF", size=16) titleNumberRight.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) titleNumberRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') titleNumberRight.border = Border(right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) titleNumberRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(titleNumberRight, 'titleNumberRight', wb) # ###################################### finalNoteStyle ######################## finalNoteStyle = NamedStyle(name="finalNoteStyle") finalNoteStyle.font = Font(color="FFFFFF", size=8) finalNoteStyle.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) finalNoteStyle.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') finalNoteStyle.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(finalNoteStyle, 'finalNoteStyle', wb) # ###################################### titleRight ############################ titleRight = NamedStyle(name="titleRight") titleRight.font = Font(color="FFFFFF", size=16) titleRight.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) titleRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') titleRight.border = Border(right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) # titleRight.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)' self.addNamedStyle(titleRight, 'titleRight', wb) # ###################################### normStyle ############################# normStyle = NamedStyle(name="normStyle") normStyle.font = Font(color="FFFFFF", size=11) normStyle.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) normStyle.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normStyle.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) normStyle.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normStyle, 'normStyle', wb) # ###################################### normalNumber ########################## normalNumber = NamedStyle(name="normalNumber") normalNumber.font = Font(color="FFFFFF", size=11) normalNumber.alignment = Alignment(horizontal="left", vertical="bottom") normalNumber.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumber.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) normalNumber.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumber, 'normalNumber', wb) # ###################################### normalFraction ######################## normalFraction = NamedStyle(name="normalFraction") normalFraction.font = Font(color="FFFFFF", size=11) normalFraction.alignment = Alignment(horizontal="left", vertical="bottom") normalFraction.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalFraction.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) normalFraction.number_format = '# ??/16' self.addNamedStyle(normalFraction, 'normalFraction', wb) # ###################################### linkStyle ############################## linkStyle = NamedStyle(name="linkStyle") linkStyle.font = Font(color="FFFFFF", size=16) linkStyle.alignment = Alignment(horizontal="center", vertical="center", wrapText=True) linkStyle.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') linkStyle.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(linkStyle, 'linkStyle', wb) # ###################################### normalNumberTopLeft ################### normalNumberTopLeft = NamedStyle(name="normalNumberTopLeft") normalNumberTopLeft.font = Font(color="FFFFFF", size=11) normalNumberTopLeft.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberTopLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberTopLeft.border = Border(left=Side(style='double'), right=Side(style='thin'), top=Side(style='double'), bottom=Side(style='thin')) normalNumberTopLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberTopLeft, 'normalNumberTopLeft', wb) # ###################################### normalNumberTop ####################### normalNumberTop = NamedStyle(name="normalNumberTop") normalNumberTop.font = Font(color="FFFFFF", size=11) normalNumberTop.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberTop.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberTop.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='double'), bottom=Side(style='thin')) normalNumberTop.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberTop, 'normalNumberTop', wb) # ###################################### normalNumberTopRight ################## normalNumberTopRight = NamedStyle(name="normalNumberTopRight") normalNumberTopRight.font = Font(color="FFFFFF", size=11) normalNumberTopRight.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberTopRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberTopRight.border = Border(left=Side(style='thin'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='thin')) normalNumberTopRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberTopRight, 'normalNumberTopRight', wb) # ###################################### normalNumberLeft ###################### normalNumberLeft = NamedStyle(name="normalNumberLeft") normalNumberLeft.font = Font(color="FFFFFF", size=11) normalNumberLeft.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberLeft.border = Border(left=Side(style='double'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) normalNumberLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberLeft, 'normalNumberLeft', wb) # ###################################### normalNumberRight ##################### normalNumberRight = NamedStyle(name="normalNumberRight") normalNumberRight.font = Font(color="FFFFFF", size=11) normalNumberRight.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberRight.border = Border(left=Side(style='thin'), right=Side(style='double'), top=Side(style='thin'), bottom=Side(style='thin')) normalNumberRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberRight, 'normalNumberRight', wb) # ###################################### normalNumberInside #################### normalNumberInside = NamedStyle(name="normalNumberInside") normalNumberInside.font = Font(color="FFFFFF", size=11) normalNumberInside.alignment = Alignment(horizontal="left", vertical="bottom") normalNumberInside.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalNumberInside.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) normalNumberInside.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalNumberInside, 'normalNumberInside', wb) # ###################################### timeSubLeft ######################### timeSubLeft = NamedStyle(name="timeSubLeft") timeSubLeft.font = Font(color="FABF8F", size=11) timeSubLeft.alignment = Alignment(horizontal="left", vertical="bottom") timeSubLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') timeSubLeft.border = Border(left=Side(style='double'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) timeSubLeft.number_format = 'h:mm:ss' self.addNamedStyle(timeSubLeft, 'timeSubLeft', wb) # ###################################### timeSub ############################# timeSub = NamedStyle(name="timeSub") timeSub.font = Font(color="FABF8F", size=11) timeSub.alignment = Alignment(horizontal="left", vertical="bottom") timeSub.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') timeSub.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) timeSub.number_format = 'h:mm:ss' self.addNamedStyle(timeSub, 'timeSub', wb) # ###################################### timeSubRight ######################## timeSubRight = NamedStyle(name="timeSubRight") timeSubRight.font = Font(color="FABF8F", size=11) timeSubRight.alignment = Alignment(horizontal="left", vertical="bottom") timeSubRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') timeSubRight.border = Border(left=Side(style='thin'), right=Side(style='double'), top=Side(style='thin'), bottom=Side(style='thin')) # normalSubLeft.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)' self.addNamedStyle(timeSubRight, 'timeSubRight', wb) # ###################################### normalSubLeft ######################### normalSubLeft = NamedStyle(name="normalSubLeft") normalSubLeft.font = Font(color="FABF8F", size=11) normalSubLeft.alignment = Alignment(horizontal="left", vertical="bottom") normalSubLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSubLeft.border = Border(left=Side(style='double'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) self.addNamedStyle(normalSubLeft, 'normalSubLeft', wb) # ###################################### normalSub ############################# normalSub = NamedStyle(name="normalSub") normalSub.font = Font(color="FABF8F", size=11) normalSub.alignment = Alignment(horizontal="left", vertical="bottom") normalSub.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSub.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) self.addNamedStyle(normalSub, 'normalSub', wb) # ###################################### normalSubRight ######################## normalSubRight = NamedStyle(name="normalSubRight") normalSubRight.font = Font(color="FABF8F", size=11) normalSubRight.alignment = Alignment(horizontal="left", vertical="bottom") normalSubRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSubRight.border = Border(left=Side(style='thin'), right=Side(style='double'), top=Side(style='thin'), bottom=Side(style='thin')) # normalSubLeft.number_format = '"$"#,##0.00_);[Red]\("$"#,##0.00\)' self.addNamedStyle(normalSubRight, 'normalSubRight', wb) # ###################################### normalSubNumberBottomLeft ############# normalSubNumberBottomLeft = NamedStyle( name="normalSubNumberBottomLeft") normalSubNumberBottomLeft.font = Font(color="FABF8F", size=11) normalSubNumberBottomLeft.alignment = Alignment(horizontal="left", vertical="bottom") normalSubNumberBottomLeft.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSubNumberBottomLeft.border = Border(left=Side(style='double'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='double')) normalSubNumberBottomLeft.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalSubNumberBottomLeft, 'normalSubNumberBottomLeft', wb) # ###################################### normalSubNumberBottom ################# normalSubNumberBottom = NamedStyle(name="normalSubNumberBottom") normalSubNumberBottom.font = Font(color="FABF8F", size=11) normalSubNumberBottom.alignment = Alignment(horizontal="left", vertical="bottom") normalSubNumberBottom.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSubNumberBottom.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='double')) normalSubNumberBottom.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalSubNumberBottom, 'normalSubNumberBottom', wb) # ###################################### normalSubNumberBottomRight ############ normalSubNumberBottomRight = NamedStyle( name="normalSubNumberBottomRight") normalSubNumberBottomRight.font = Font(color="FABF8F", size=11) normalSubNumberBottomRight.alignment = Alignment(horizontal="left", vertical="bottom") normalSubNumberBottomRight.fill = PatternFill(start_color='A6A6A6', end_color='A6A6A6', fill_type='solid') normalSubNumberBottomRight.border = Border(left=Side(style='thin'), right=Side(style='double'), top=Side(style='thin'), bottom=Side(style='double')) normalSubNumberBottomRight.number_format = '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' self.addNamedStyle(normalSubNumberBottomRight, 'normalSubNumberBottomRight', wb) # ###################################### explain ############################### explain = NamedStyle(name="explain") explain.font = Font(color="000000", size=10) explain.alignment = Alignment(horizontal="left", vertical="top", wrapText=True) explain.fill = PatternFill(start_color='FFFFCC', end_color='FFFFCC', fill_type='solid') explain.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(explain, 'explain', wb) # ###################################### noteStyle ############################# noteStyle = NamedStyle(name="noteStyle") noteStyle.font = Font(color="E26B0A", size=10) noteStyle.alignment = Alignment(horizontal="left", vertical="top", wrapText=True) noteStyle.fill = PatternFill(start_color='FFFFCC', end_color='FFFFCC', fill_type='solid') noteStyle.border = Border(left=Side(style='double'), right=Side(style='double'), top=Side(style='double'), bottom=Side(style='double')) self.addNamedStyle(noteStyle, 'noteStyle', wb)
def write_file(self, order_details): if self.delete_file_if_exists(): order_wb = Workbook() order_ws = order_wb.active # set sheet name order_ws.title = "Order Data (2014-2016)" # defining column styling header_style = NamedStyle(name="headerStyle") header_style.font = Font(bold=True) date_style = NamedStyle(name="dateStyle") date_style.number_format = 'MM/DD/YYYY' amount_style = NamedStyle(name="amountStyle") amount_style.number_format = u'_-"€"* #,##0.00_-;-"€"* #,##0.00_-;_-"€"* "-"??_-;_-@_-' order_ws.append([ "Contact Name", "Company", "Address 1", "County", "Country Code", "Phone", "Amount", "Order Date" ]) # applying styles to header columns order_ws["A1"].style = header_style order_ws.column_dimensions["A"].width = 25 order_ws["B1"].style = header_style order_ws.column_dimensions["B"].width = 25 order_ws["C1"].style = header_style order_ws.column_dimensions["C"].width = 40 order_ws["D1"].style = header_style order_ws.column_dimensions["D"].width = 15 order_ws["E1"].style = header_style order_ws.column_dimensions["E"].width = 12 order_ws["F1"].style = header_style order_ws.column_dimensions["F"].width = 10 order_ws["G1"].style = header_style order_ws.column_dimensions["G"].width = 14 order_ws["H1"].style = header_style order_ws.column_dimensions["H"].width = 12 max_count = len(order_details) row_index = 1 for index in range(1, max_count, 1): order = order_details[index] # print vars(order) # adding only valid rows to sheet if order.is_valid: row = (order.contact_name, order.company, order.address1, order.county, order.country_code, order.phone, order.amount, order.order_date) order_ws.append(row) # applying styles to Order Date and Amount columns order_ws["H" + str(row_index + 1)].style = date_style order_ws["G" + str(row_index + 1)].style = amount_style row_index += 1 order_wb.save(self.file_name) else: print( "Unable to delete file. Please close the file if it is already open." )
def exam_scores(exam_id): # Читаем данные из базы данных exam = Exam.objects.select_related('course').get(id=exam_id) group = exam.course.group exam_points = exam.exammarks_set.all().order_by('student__FIO') semester = exam.course.discipline_detail.semester.name if int(semester) % 2 == 1: additional = int(semester) // 2 else: additional = (int(semester) - 1) // 2 edu_period = EduPeriod.objects.get(begin_year__year=group.begin_year.year + additional) # Открываем шаблон app_dir = os.path.dirname(os.path.abspath(__file__)) template = os.path.join(app_dir, 'exam_scores.xlsx') workbook = load_workbook(template) ws = workbook.active # Семестр ws['A5'] = ws['L5'] = 'Семестр: {}, {} – {} уч.г.'.format( semester, edu_period.begin_year.year, edu_period.end_year.year, ) # Форма контроля ws['A6'] = ws['L6'] = 'Форма контроля: {}, курс: {}, группа: {}'.format( exam.get_controlType_display(), group.year, group.name, ) # Дисциплина ws['A7'] = ws['L7'] = 'Дисциплина: ' + exam.course.discipline_detail.discipline.name # ФИО преподавателя ws['A8'] = ws['L8'] = 'Фамилия, имя, отчество преподавателя: ' + exam.course.lecturer.FIO # Дата ws['A9'] = ws['L9'] = 'Дата проведения зачета/экзамена: {:%d.%m.%Y}'.format(exam.examDate) # Таблица с баллами summary = {ExamMarks.MARKS[0][0]: 0, ExamMarks.MARKS[1][0]: 0, ExamMarks.MARKS[2][0]: 0, ExamMarks.MARKS[3][0]: 0, ExamMarks.MARKS[4][0]: 0, ExamMarks.MARKS[5][0]: 0, ExamMarks.MARKS[6][0]: 0, ExamMarks.MARKS[7][0]: 0, ExamMarks.MARKS[8][0]: 0, ExamMarks.MARKS[9][0]: 0} k = 0 for points in exam_points: k += 1 ws.insert_rows(11 + k) ws.row_dimensions[11 + k].height = 30 row = str(11 + k) ws['A' + row] = ws['L' + row] = str(k) ws['B' + row] = ws['M' + row] = points.student.FIO ws['C' + row] = ws['N' + row] = points.student.student_id ws['D' + row] = ws['O' + row] = points.inPoints + points.additional_points ws['E' + row] = ws['P' + row] = points.examPoints ws['F' + row] = ws['Q' + row] = points.total_points ws['G' + row] = ws['R' + row] = points.get_mark_display() ws['H' + row] = ws['S' + row] = points.mark_symbol summary[points.mark]+=1 # Стиль для ячеек таблицы solid_line = Side(style='thin', color='000000') cell_style = NamedStyle(name='cell_style') cell_style.alignment.horizontal = 'center' cell_style.alignment.vertical = 'center' cell_style.alignment.wrapText = Bool(True) cell_style.border = Border(left=solid_line, right=solid_line, top=solid_line, bottom=solid_line) cell_style.font = Font(name='Arial', size=9) cell_style.number_format = '#.0' # Применяем стили к таблице for i in range(12, k + 12): for j in range(1, 10): ws.cell(row=i, column=j).style = ws.cell(row=i, column=j+11).style = cell_style ws['C' + str(k + 14)] = ws['N' + str(k + 14)] = summary[6] ws['C' + str(k + 15)] = ws['N' + str(k + 15)] = summary[7] ws['C' + str(k + 16)] = ws['N' + str(k + 16)] = summary[8] ws['C' + str(k + 17)] = ws['N' + str(k + 17)] = summary[5] ws['C' + str(k + 18)] = ws['N' + str(k + 18)] = summary[4] ws['C' + str(k + 19)] = ws['N' + str(k + 19)] = summary[3] ws['C' + str(k + 20)] = ws['N' + str(k + 20)] = summary[2] ws['C' + str(k + 21)] = ws['N' + str(k + 21)] = summary[0] # Суммы баллов и буквенные эквиваленты оценки for i in range(8): row = str(14 + k + i) ws.merge_cells('E' + row + ':F' + row) ws.merge_cells('P' + row + ':Q' + row) ws.merge_cells('G' + row + ':I' + row) ws.merge_cells('R' + row + ':T' + row) ws['E' + row].style = ws['F' + row].style = ws['G' + row].style = \ ws['H' + row].style = ws['I' + row].style = ws['P' + row].style = \ ws['Q' + row].style = ws['R' + row].style = ws['S' + row].style = \ ws['T' + row].style = cell_style # Подпись директора row = str(24 + k) ws.merge_cells('A' + row + ':I' + row) ws.merge_cells('L' + row + ':T' + row) ws.print_area = 'A1:T' + str(row) ws.page_setup = PrintPageSetup(worksheet=ws) ws.page_setup.paperSize = '9' ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.fitToHeight = True ws.page_setup.fitToWidth = True ws.page_setup.fitToPage = True ws.sheet_properties.pageSetUpPr.fitToPage = True return workbook
# Alapértelmezett változók olvasando_fajl = "sms_full.xml" logging.info('A beolvasando file: ' + olvasando_fajl) kiirando_fajl = "balance.xlsx" logging.info('A kiírandó file: ' + kiirando_fajl) arfolyam_file = "arfolyamok.csv" logging.info('A használt árfolyamok file: ' + arfolyam_file) arfolyam = {} egyenleg2 = "" # xml fájl betöltése mydoc = minidom.parse(olvasando_fajl) # Munkalap stílusának beálítása forintra still = NamedStyle(name="Pénzecske") # Ilyen nevű stílus hozzáadása still.number_format = '#,##0 "HUF";-#,##0 "HUF"' # A számformátum beállítása forintra diff_style = DifferentialStyle(fill=PatternFill( bgColor='C6EFCE', fgColor='006100')) # A feltételes formázás beállítása rule = Rule(type="expression", dxf=diff_style) # Feltételes kifejezés megadása rule.formula = ["$B2>0"] # Formula a feltételes formázáshoz # Árfolyam file meglétének ellenőrzése és frissítése ha két óránál régebbi fileido = datetime.fromtimestamp(os.stat( './arfolyamok.csv').st_ctime) # a meglévő file idejének lekérdezése now = datetime.now() max_delay = timedelta(hours=2) try: if now - fileido > max_delay: shutil.copyfile('arfolyamok.csv', 'arfolyamok.old') logging.debug( "Régi a fájl ezért lekérdezem az árfolyamokat: {} ".format(
def cell_style( *, wb: Workbook, style_name: str = 'cell_style', font_name: Optional[str] = 'Lucida Sans', font_size: Optional[int] = 11, font_bold: Optional[bool] = True, font_colour: Optional[str] = '000000', horizontal_alignment: Optional[str] = 'center', vertical_alignment: Optional[str] = 'center', wrap_text: Union[str, bool] = None, fill_type: Union[str, bool] = 'solid', foreground_colour: Union[str, bool] = 'd9d9d9', border_style: Union[str, bool] = None, border_colour: Union[str, bool] = None, number_format: Union[str, bool] = None ) -> NamedStyle: """ Define a cell style Parameters ---------- wb : Workbook The workbook in which to define the cell style. style_name : str = 'cell_style' The name for the cell style. font_name : Optional[str] = 'Lucida Sans' The font name for the style. font_size : Optional[int] = 11 The font size for the style. font_bold : Optional[bool] = True A boolean or string to apply bold style. font_colour : Optional[str] = 'ffffff' The string for the font colour. horizontal_alignment : Optional[str] = 'center' The string for horizontal alignment. vertical_alignment : Optional[str] = 'center' The string for vertical alignment. wrap_text : Union[str, bool] = None A boolean or string to wrap text. fill_type : Optional[str] = 'solid' The string for the fill type. foreground_colour : Optional[str] = 'd9d9d9' The string for the foreground colour. border_style : Union[str, bool] = None A boolean or string to apply a border. border_colour : Union[str, bool] = None A boolean or string to apply a border colour. number_format : Union[str, bool] = None A boolean or string to apply a number format. Returns ------- row_style : NamedStyle The named style. Example ------- >>> red_cell_style = ds.cell_style( >>> style_name='red_cell_style', >>> font_colour='ffffff', >>> foreground_colour='c00000' >>> ) >>> wb.add_named_style(red_cell_style) >>> for cell in ['C1', 'D1', 'E1']: >>> ws[cell].style = red_cell_style """ cell_style = NamedStyle(name=style_name) cell_style.font = Font( name=font_name, size=font_size, bold=font_bold, color=font_colour ) cell_style.alignment = Alignment( horizontal=horizontal_alignment, vertical=vertical_alignment, wrap_text=wrap_text ) cell_style.fill = PatternFill( fill_type=fill_type, fgColor=foreground_colour ) cell_style.border = Border( bottom=Side( border_style=border_style, color=border_colour ) ) cell_style.number_format = number_format wb.add_named_style(cell_style) return (wb, cell_style)
def aplicarFormatos(): # estilo de borde thin = Side(border_style="thin", color="000000") double = Side(border_style="double", color="ff0000") # color de celda my_red = openpyxl.styles.colors.Color(rgb='F2F2F2') my_fill = openpyxl.styles.fills.PatternFill(patternType='solid', fgColor=my_red) # elementos de formato normal = NamedStyle(name="normal") normal.font = Font(bold=False, size=11, name="verdana") normal.number_format = numbers.FORMAT_GENERAL normal.alignment.wrap_text = False # 'bottom', 'top', 'distributed', 'justify', 'center' normal.alignment.vertical = "center" normal.alignment.horizontal = "center" normal.border = Border(top=thin, left=thin, right=thin, bottom=thin) # elementos de formato bold = NamedStyle(name="bold") bold.font = Font(bold=True, size=11, name="verdana") bold.number_format = numbers.FORMAT_GENERAL bold.alignment.wrap_text = False # 'bottom', 'top', 'distributed', 'justify', 'center' bold.alignment.vertical = "center" bold.alignment.horizontal = "center" bold.border = Border(top=thin, left=thin, right=thin, bottom=thin) # formato de fecha fecha = NamedStyle(name="fecha") fecha.font = Font(bold=True, size=11, name="verdana") fecha.number_format = numbers.FORMAT_DATE_DMYSLASH fecha.alignment.wrap_text = False # 'bottom', 'top', 'distributed', 'justify', 'center' fecha.alignment.vertical = "center" fecha.alignment.horizontal = "center" fecha.border = Border(top=thin, left=thin, right=thin, bottom=thin) # formato de numero money = NamedStyle(name="money") money.font = Font(bold=False, size=11, name="verdana") money.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE money.alignment.wrap_text = False # 'bottom', 'top', 'distributed', 'justify', 'center' money.alignment.vertical = "center" money.alignment.horizontal = "center" money.border = Border(top=thin, left=thin, right=thin, bottom=thin) # iterar por todas las filas for i, row in enumerate(ws.iter_rows(ws.min_row, ws.max_row)): # print(i) ws.row_dimensions[i + 1].height = 40 # print(row[0]) # iterar en cada columna de la fila for cell in row: column_number = cell.column column_letter = get_column_letter(column_number) # print(column_letter) # print(cell) cell.style = normal cell.fill = my_fill if column_letter == "A": cell.style = fecha cell.fill = my_fill elif (column_letter == "H") or (column_letter == "I"): cell.style = money cell.fill = my_fill elif (column_letter == "L") or (column_letter == "J"): cell.style = bold cell.fill = my_fill return
right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) # format header cells header = NamedStyle(name="header") header.font = Font(bold=True, color='FFFFFFFF') header.alignment = Alignment(horizontal="center", vertical="center") header.height = 25 header.fill = time_head_fill header.border = thin_border # format numeric cells number_cell = NamedStyle(name="number_cell") number_cell.alignment = Alignment(horizontal="center", vertical="center") number_cell.number_format = '#,##0.000' number_cell.border = thin_border # format generic text cells text_cell = NamedStyle(name="text_cell") text_cell.alignment = Alignment(horizontal="center", vertical="center") text_cell.border = thin_border # open excel file and ammend styling as required workbook = openpyxl.load_workbook('temp_excelfile.xlsx') # sequentially format all sheets containing tables for sheet_name in final_df_names: current_sheet = workbook[sheet_name] print(f"Currently editing sheet {sheet_name}")
def excel_export(user_inputs, ten_table_data, bar_chart_x_axis_values, bar_chart_y_axis_values): wb = Workbook() # ---- create styles/formats to be used later ---- # for applicable numerical values comma_no_decimal_style = NamedStyle(name='comma_no_decimal_style') comma_no_decimal_style.number_format = '#,##0' comma_no_decimal_style.alignment = Alignment( horizontal='left') # left align like text is by default in Excel # for table header ten_table_header_style = NamedStyle(name='ten_table_header_style') ten_table_header_style.font = Font(bold=True) ten_table_header_style.fill = PatternFill(start_color='8DB4E2', end_color='8DB4E2', fill_type='solid') ten_table_header_style.border = Border(top=Side(border_style=BORDER_THIN, color='00000000'), bottom=Side( border_style=BORDER_THIN, color='00000000'), left=Side(border_style=BORDER_THIN, color='00000000'), right=Side(border_style=BORDER_THIN, color='00000000')) # for table title ten_table_title_style = NamedStyle(name='ten_table_title_style') ten_table_title_style.font = Font(size=18, bold=True) ten_table_title_style.alignment = Alignment(horizontal='center') # -----------------------INPUT TAB----------------------- ws_input = wb.active ws_input.title = 'input' # provide user selections for row, label in enumerate([ 'Rank Position', 'Rank By', 'State', 'City', 'Zip Code', 'Place of Service', 'Provider Type', 'Credential', 'HCPCS Code' ], start=1): ws_input.cell(column=1, row=row, value=label).font = Font(bold=True) ws_input.cell( column=2, row=1, value=', '.join(user_inputs['rank_position']) if 'rank_position' in user_inputs else '' ) # (rank position should always be in user inputs, since it's required) ws_input.cell( column=2, row=2, value=', '.join(user_inputs['rank_by']) if 'rank_by' in user_inputs else '') # (rank by should always be in user inputs, since it's required) ws_input.cell(column=2, row=3, value=', '.join(user_inputs['state']) if 'state' in user_inputs else '(all)') ws_input.cell(column=2, row=4, value=', '.join(user_inputs['city']) if 'city' in user_inputs else '(all)') ws_input.cell(column=2, row=5, value=', '.join(user_inputs['zip_code']) if 'zip_code' in user_inputs else '(all)') ws_input.cell(column=2, row=6, value=', '.join(user_inputs['place_of_service']) if 'place_of_service' in user_inputs else '(all)') ws_input.cell(column=2, row=7, value=', '.join(user_inputs['provider_type']) if 'provider_type' in user_inputs else '(all)') ws_input.cell(column=2, row=8, value=', '.join(user_inputs['credential']) if 'credential' in user_inputs else '(all)') ws_input.cell(column=2, row=9, value=', '.join(user_inputs['hcpcs_code']) if 'hcpcs_code' in user_inputs else '(all)') for col in ['A', 'B']: ws_input.column_dimensions[col].width = 20 # -----------------------DATA TAB----------------------- ws_data = wb.create_sheet(title='data') # ----provide table data---- for col, label in enumerate( ['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'], start=1): ws_data.cell(column=col, row=1, value=label) for row, data in enumerate(ten_table_data, start=2): ws_data.cell(column=1, row=row, value=data['provider_id']) ws_data.cell( column=2, row=row, value=int(data['patients'].replace(',', '')) ).style = comma_no_decimal_style # transforming string into number (so user can more easily perform calculations in Excel) and formatting ws_data.cell(column=3, row=row, value=int(data['avg_charged'].replace( ',', ''))).style = comma_no_decimal_style ws_data.cell(column=4, row=row, value=int(data['avg_allowed'].replace( ',', ''))).style = comma_no_decimal_style ws_data.cell(column=5, row=row, value=int(data['avg_paid'].replace( ',', ''))).style = comma_no_decimal_style # ----provide chart data---- ws_data.cell(column=8, row=1, value='Bar Chart') for row, x in enumerate(bar_chart_x_axis_values, start=2): ws_data.cell(column=8, row=row, value=x) for row, y in enumerate(bar_chart_y_axis_values, start=2): ws_data.cell(column=9, row=row, value=y).style = comma_no_decimal_style # set column width and bold headings for table & chart data for col in ['A', 'B', 'C', 'D', 'E', 'H', 'I']: ws_data.column_dimensions[col].width = 16 ws_data[f'{col}1'].font = Font(bold=True) # -----------------------RESULTS TAB----------------------- ws_results = wb.create_sheet(title='results') # ---- create and format table ---- ten_table_title = f"{user_inputs['rank_position'][0]} 10 Providers by {'Number of Patients' if user_inputs['rank_by'][0] == 'Patients' else 'Average Charged Amount'}" ws_results.merge_cells('A1:E1') ws_results.cell(column=1, row=1, value=ten_table_title).style = ten_table_title_style for col, label in enumerate( ['Provider ID', 'Patients', 'Avg Charged', 'Avg Allowed', 'Avg Paid'], start=1): ws_results.cell(column=col, row=2, value=label).style = ten_table_header_style for row in range(3, len(ten_table_data) + 3): ws_results.cell(column=1, row=row, value=f"=data!A{row-1}") ws_results.cell(column=2, row=row, value=f"=data!B{row-1}").style = comma_no_decimal_style ws_results.cell(column=3, row=row, value=f"=data!C{row-1}").style = comma_no_decimal_style ws_results.cell(column=4, row=row, value=f"=data!D{row-1}").style = comma_no_decimal_style ws_results.cell(column=5, row=row, value=f"=data!E{row-1}").style = comma_no_decimal_style ten_table = Table( displayName="TenTable", ref=f'A3:E{len(ten_table_data)+2}', headerRowCount=0 ) # didn't specify a header so that default column filters aren't created ten_table.tableStyleInfo = TableStyleInfo(name="TableStyleLight15", showRowStripes=True) ws_results.add_table(ten_table) for col in ['A', 'B', 'C', 'D', 'E']: ws_results.column_dimensions[col].width = 16 # ---- create bar chart ---- bar_chart = BarChart() bar_chart.type = 'col' bar_chart.style = 10 bar_chart.title = 'Total Patients by Average Charged Amount' bar_chart.y_axis.title = 'Patients' bar_chart.x_axis.title = 'Avg Charged' bar_chart.add_data(Reference(ws_data, min_col=9, max_col=9, min_row=2, max_row=len(bar_chart_y_axis_values) + 1), titles_from_data=False) bar_chart.set_categories( Reference(ws_data, min_col=8, max_col=8, min_row=2, max_row=len(bar_chart_x_axis_values) + 1)) bar_chart.shape = 4 bar_chart.legend = None bar_chart.height = 9 bar_chart.width = 18 ws_results.add_chart(bar_chart, 'H1') # ---- add footnotes ---- footnote1 = "Based on 2017 Medicare Provider Utilization and Payment Data accessed December 18, 2019 from data.cms.gov: https://data.cms.gov/Medicare-Physician-Supplier/Medicare-Provider-Utilization-and-Payment-Data-Phy/fs4p-t5eq." footnote2 = "Only providers listed as individuals and their services within the 50 states/DC are included. Please note that Provider ID is a generated number created by the developer in an effort to de-identify providers." footnote3 = "Additional data cleaning/transformations on the data set were performed as needed at the sole discretion of the developer." footnote4 = "The specific data shown in the above table & graph is based on user selections (see input tab) in the web application." ws_results.cell(column=1, row=20, value='Source Data:').font = Font(size=9, underline='single') for i, note in enumerate([footnote1, footnote2, footnote3], start=1): ws_results.cell(column=1, row=20 + i, value=note).font = Font(size=9) ws_results.cell(column=1, row=25, value=footnote4).font = Font( size=9 ) # writes a couple of lines down from the previous footnote in order to leave a blank line between this last footnote and the prior ones # set the results sheet (which is at index 2) as the active worksheet so that the user will be on this sheet when opening the file wb.active = 2 # the active worksheet is now the one at index 2 # save workbook to a stream (vs. saving to a file on disk) to later send to user excel_stream = io.BytesIO() wb.save(excel_stream) excel_stream.seek(0) # go to the beginning of the stream return excel_stream
def nanodrop(): if request.method == 'GET': return render_template('nanodrop/form.html') # # Validation # # Otherwise validate the form on a POST request and process uploaded files f = request.files.getlist('input') ref = request.files.getlist('reference') # Throw error if one of the required files are missing if not f[0]: flash('A required field is missing', 'alert-warning') return render_template('nanodrop/form.html') # Validate numeric fields try: # Acceptance criteria limits od_low = float(request.form.get("od_min")) if request.form.get("od_min") else 1.7 od_high = float(request.form.get("od_max")) if request.form.get("od_max") else 2.3 conc_low = float(request.form.get("conc_min")) if request.form.get("conc_min") else 80 conc_high = float(request.form.get("conc_max")) if request.form.get("conc_max") else 120 except: flash('Please make sure that the optional fields are all numbers') return render_template('nanodrop/form.html') # Validate and save input file filename = f[0].filename filefront, extension = os.path.splitext(filename) if not extension == '.xls' and not extension == '.xlsx' and not extension == '.xml': flash('Only .xls, .xlsx, and .xml files are allowed.', 'alert-warning') return render_template('nanodrop/form.html') # Throw error if more than one reference list was provided if len(ref) > 1: flash('Only one reference file should be selected', 'alert-warning') return render_template('nanodrop/form.html') # If a reference file was uploaded... if ref[0]: # Validate the file object. ref_filename = ref[0].filename.replace(' ', '_') ref_filefront, ref_extension = os.path.splitext(ref_filename) if not ref_extension == '.xlsx': flash('Only .xlsx files are allowed.', 'alert-warning') return render_template('nanodrop/form.html') # Save the file ref_file_path = os.path.join(app.config['UPLOAD_FOLDER'], ref_filename) ref[0].save(ref_file_path) os.chmod(ref_file_path, stat.S_IRWXO | stat.S_IRWXG | stat.S_IRWXU) list_ref = load_workbook(ref_file_path) else: list_ref = None ref_file_path = '' # # Prepare data # # Save the raw data file (to delete at the end of the program path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename) f[0].save(path) # Convert the file in f to xlsx so that openpyxl can work with them if extension == '.xls': csv_to_xlsx(path) extension = '.xlsx' f[0].filename = filefront + extension path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename) elif extension == '.xml': xml_to_xlsx(path) extension = '.xlsx' f[0].filename = filefront + extension path = os.path.join(app.config['UPLOAD_FOLDER'], f[0].filename) ################################ # Main part of computation ################################ # # Read the input spreadsheet into data # input_worksheet = load_workbook(path).active next(input_worksheet.rows) # Skip the first row data = [] for row in input_worksheet.rows: data.append([tryStrToNum(cell.value) for cell in row]) # # Read the reference spread sheet # # Open the ref file and store reference sequences in a list ref_seqs = {} # Key: Sample ID, Value: (Sequence, Factor) if list_ref: for sheet in list_ref.worksheets: header_row = [cell.value for cell in sheet[1]] # Skip the first row for row in range(2, sheet.max_row + 1): sample_id = sheet.cell(row=row, column=2).value ref_sequence = sheet.cell(row=row, column=3).value factor_col = getFactorColumn(header_row) if factor_col: factor = sheet.cell(row=row, column=factor_col).value else: factor = None ref_seqs[sample_id] = (ref_sequence, factor) # # Here are the column indices for the various data fields # # Raw data fields DATA_FIELDS = ['#', 'Sample ID', 'User name', 'Date and Time', 'Nucleic Acid Conc.', 'Unit', 'A260', 'A280', '260/280', '260/230', 'Sample Type', 'Factor', '', ''] # Last two fields are filled in later depending on order of sequence and mol weight # Important data fields SAMPLEID = 1 USERNAME = 2 DATETIME = 3 CONC = 4 ABSORPTION = 6 SAMPLETYPE = 10 FACTOR = 11 SEQUENCE, MOLWEIGHT = getSequenceColumn(data[0]) DATA_FIELDS[SEQUENCE] = 'Sequence' DATA_FIELDS[MOLWEIGHT] = 'Molecular Weight' # # Create the output workbook and write the headers # # Header data date = data[1][DATETIME].split(' ', 1)[0] # Parse '6/1/2021 2:55:08 PM' ==> '6/1/2021' operator = data[1][USERNAME] # Create the output Excel file output = Workbook() output_raw_title = 'Raw Data' output_final_title = 'NanoDrop Report' output_raw_sheet = output.active output_raw_sheet.title = output_raw_title output_final_sheet = output.create_sheet(output_final_title) # Styles bd = Side(border_style="thin") header_style = NamedStyle(name="header_style") header_style.font = Font(bold=True) header_style.alignment = Alignment(horizontal="center", vertical="center") header_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) data_style = NamedStyle(name="data_style") # data_style.border = Border(left=bd, right=bd, top=bd, bottom=bd) data_style.alignment = Alignment(horizontal="center", vertical="center") data_style.number_format = '0.0' output_raw_sheet['B1'] = '=@CELL("filename")' output_final_sheet['B1'] = 'Date' output_final_sheet['B1'].style = header_style output_final_sheet['C1'] = date output_final_sheet['B2'] = 'Operator' output_final_sheet['B2'].style = header_style output_final_sheet['C2'] = operator output_final_sheet['B3'] = 'Acceptance Criteria' output_final_sheet['B3'].style = header_style output_final_sheet['C3'] = 'OD260/280 is between ' + str(od_low) + ' - ' + str(od_high) + \ '. Concentration is between ' + str(conc_low) + ' uM - ' + str(conc_high) + ' uM' project_id = request.form.get('project_id') if project_id: output_final_sheet['B4'] = 'Project ID' output_final_sheet['B4'].style = header_style output_final_sheet['C4'] = project_id output_final_sheet['D6'] = 'Concentration' output_final_sheet.merge_cells('D6:E6') output_final_sheet['D6'].style = header_style output_final_sheet['E6'].style = header_style output_final_sheet['B7'] = 'Oligo Name' output_final_sheet['B7'].style = header_style output_final_sheet['C7'] = 'OD260/280' output_final_sheet['C7'].style = header_style output_final_sheet['D7'] = 'ng/uL' output_final_sheet['D7'].style = header_style output_final_sheet['E7'] = 'uM' output_final_sheet['E7'].style = header_style output_final_sheet['F7'] = 'QC' output_final_sheet['F7'].style = header_style output_raw_sheet.append(DATA_FIELDS) # The header for i in range(len(DATA_FIELDS)): output_raw_sheet.cell(row=2, column=i+1).style = header_style # # Write the data to the output file # # First write to the raw data sheet output_raw_start_row = 3 final_data = OrderedDict() for i, row in enumerate(data[1:]): # Skip the blank, indicated by a sample ID of 'b' if row[SAMPLEID] == 'b' or row[SAMPLEID].lower() == 'Blank': output_raw_sheet.append(row) continue if len(row) <= MOLWEIGHT or not row[MOLWEIGHT]: # Get the formula for molecular weight # Source: https://www.thermofisher.com/us/en/home/references/ambion-tech-support/ # rna-tools-and-calculators/dna-and-rna-molecular-weights-and-conversions.html sequence_col = "M" if SEQUENCE == 12 else "N" sequence = sequence_col + str(output_raw_start_row + i) if row[SAMPLETYPE] == "DNA" or row[SAMPLETYPE] == "dsDNA": mol_weight_formula = '=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(' + sequence + \ ',"A",""),"a",""),"T",""),"t","")) * (289.2 + 329.2) + ' + \ "LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" + sequence + \ ',"C",""),"c",""),"G",""),"g","")) * (313.2 + 304.2) + 157.9' elif row[SAMPLETYPE] == "Oligo DNA" or row[SAMPLETYPE] == "ssDNA": mol_weight_formula = '=313.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \ + sequence + ',"A",""),"a",""))) + ' + \ '304.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \ + sequence + ',"T",""),"t",""))) + ' + \ '289.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \ + sequence + ',"C",""),"c",""))) + ' + \ '329.2 * (LEN(' + sequence + ') - LEN(SUBSTITUTE(SUBSTITUTE(' \ + sequence + ',"G",""),"g",""))) + 79' else: mol_weight_formula = "Sample type not recognized" if len(row) <= MOLWEIGHT: row.append(mol_weight_formula) else: row[MOLWEIGHT] = mol_weight_formula # Get the sequence from the reference list if len(row) <= SEQUENCE or not row[SEQUENCE]: print("HELLO") try: (dna_string, factor) = ref_seqs[row[SAMPLEID]] if len(row) <= SEQUENCE: row.append(dna_string) else: row[SEQUENCE] = dna_string if factor: row[FACTOR] = factor factor_col = get_column_letter(FACTOR + 1) abs_col = get_column_letter(ABSORPTION + 1) row_num = str(output_raw_start_row + i) # Concentration = factor * absorption at 260nm row[CONC] = "=" + factor_col + row_num + "*" + abs_col + row_num except: row[MOLWEIGHT] = '' # Copy from data to raw_sheet output_raw_sheet.append(row) output_raw_sheet.cell(row=output_raw_start_row + i, column=CONC+1).number_format = '0.0' # Now update the dict that will give us the final data sample_id = row[SAMPLEID] if sample_id not in final_data: final_data[sample_id] = [output_raw_start_row + i] else: final_data[sample_id].append(output_raw_start_row + i) # Then write tot he summary data sheet output_final_start_row = 8 for i, (sample_id, raw_rows) in enumerate(final_data.items()): output_final_row = str(output_final_start_row + i) # Copy Sample ID from raw_sheet to final_sheet output_final_sheet.cell(row=output_final_start_row + i, column=2).value = \ "='" + output_raw_title + "'!B" + str(raw_rows[0]) output_final_sheet.cell(row=output_final_start_row + i, column=2).style = data_style # Copy OD260/280 from raw_sheet to final_sheet ods = [f"'{output_raw_title}'!I{str(raw_row)}" for raw_row in raw_rows] output_final_sheet.cell(row=output_final_start_row + i, column=3).value = \ f"=AVERAGE({', '.join(ods)})" output_final_sheet.cell(row=output_final_start_row + i, column=3).style = data_style # Copy Oligo Conc. from raw_sheet to final_sheet concs = [f"IF('{output_raw_title}'!F{str(raw_row)}=\"ng/µl\", " f"'{output_raw_title}'!E{str(raw_row)}, " f"'{output_raw_title}'!E{str(raw_row)} * 1000)" for raw_row in raw_rows] output_final_sheet.cell(row=output_final_start_row + i, column=4).value = \ f"=AVERAGE({', '.join(concs)})" output_final_sheet.cell(row=output_final_start_row + i, column=4).style = data_style # Calculate the uM concentration molweight_col = "M" if MOLWEIGHT == 12 else "N" um_concs = [f"D{output_final_row}*1000/'{output_raw_title}'!{molweight_col}{str(raw_row)}" for raw_row in raw_rows] output_final_sheet.cell(row=output_final_start_row + i, column=5).value = \ f"=AVERAGE({', '.join(um_concs)})" output_final_sheet.cell(row=output_final_start_row + i, column=5).number_format = '0.0' output_final_sheet.cell(row=output_final_start_row + i, column=5).alignment = \ Alignment(horizontal="center", vertical="center") # Calculate the QC output_final_sheet.cell(row=output_final_start_row + i, column=6).value = \ "=IF(AND(AND(C" + output_final_row + ">=" + str(od_low) + ",C" + \ output_final_row + "<=" + str(od_high) + "),AND(E" + output_final_row + \ ">=" + str(conc_low) + ",E" + output_final_row + "<=" + str(conc_high) + \ ")), \"Passed\", \"Failed\")" output_final_sheet.cell(row=output_final_start_row + i, column=6).style = data_style # # Make some final touches to the output file # # Edit column styles so that their widths are BestFit column_letters = tuple(get_column_letter(col_number + 1) for col_number in range(output_raw_sheet.max_column)) for column_letter in column_letters: output_raw_sheet.column_dimensions[column_letter].width = 15 column_letters = tuple(get_column_letter(col_number + 1) for col_number in range(output_final_sheet.max_column)) for column_letter in column_letters: output_final_sheet.column_dimensions[column_letter].width = 17 # Insert the EpigenDx logo logo_path = os.path.join(app.config['STATIC_FOLDER'], 'img/logo_small.gif') logo = drawing.image.Image(logo_path) output_final_sheet.add_image(logo, 'H1') # # Save the output file and send it to the user # # Save the output file (and overwrite the previous output file) out_path = os.path.join(app.config['UPLOAD_FOLDER'], 'nanodrop_results.xlsx') output.save(out_path) os.chmod(out_path, stat.S_IRWXO | stat.S_IRWXG | stat.S_IRWXU) # Delete the uploaded file from the server os.remove(path) if ref[0]: os.remove(ref_file_path) # Send the output sheet to the user user_filename = request.form.get("filename") if user_filename: if user_filename.endswith(".xlsx"): final_filename = user_filename else: final_filename = user_filename + ".xlsx" else: final_filename = "nanodrop_results.xlsx" try: return send_file(os.path.join(app.config['UPLOAD_FOLDER'], 'nanodrop_results.xlsx'), attachment_filename=final_filename, as_attachment=True) except: os.remove(out_path) flash('Could not send the results file', 'alert-warning') return render_template('nanodrop/form.html')
title_style_i = NamedStyle(name='income_title') title_style_i.font = Font(size=20, bold=True, underline='single', ) title_style_i.alignment = Alignment(horizontal='center', vertical='center') title_style_i.fill = PatternFill(fgColor=Color("66CC66"), fill_type=fills.FILL_SOLID ) title_style_i.border = title_border # Control header title_style_c = NamedStyle(name='control_title') title_style_c.font = Font(size=20, bold=True, underline='single', ) title_style_c.alignment = Alignment(horizontal='center', vertical='center') title_style_c.fill = PatternFill(fgColor=Color("AA00FF"), fill_type=fills.FILL_SOLID ) title_style_c.border = title_border # --------------- Field Styles --------------- # Field label style label_style = NamedStyle(name='field_labels') label_style.alignment = Alignment(horizontal='center', vertical='center') label_style.border = f_label_border label_style.font = Font(bold=True) # Field placeholder style w/ currency format ph_style = NamedStyle(name="ph_style") ph_style.alignment = Alignment(horizontal='center', vertical='center') ph_style.border = f_label_border ph_style.number_format = '$#,##0.00'