def add_styles(xlsx_obj): if "over_10k_style" not in xlsx_obj.style_names: over_10k_favorite_color = BLUE over_10k_favorite_bg_color = "F0E8DD" over_10k_style = NamedStyle(name="over_10k_style") over_10k_style.font = Font(color=over_10k_favorite_color) over_10k_style.fill = PatternFill( start_color=over_10k_favorite_bg_color, end_color=over_10k_favorite_bg_color, fill_type="solid") xlsx_obj.add_named_style(over_10k_style) if "over_20k_style" not in xlsx_obj.style_names: over_20k_favorite_color = RED over_20k_favorite_bg_color = "D1F5EC" over_20k_style = NamedStyle(name="over_20k_style") over_20k_style.font = Font(color=over_20k_favorite_color) over_20k_style.fill = PatternFill( start_color=over_20k_favorite_bg_color, end_color=over_20k_favorite_bg_color, fill_type="solid") xlsx_obj.add_named_style(over_20k_style)
def build_styles(): global excel_file from openpyxl.styles import NamedStyle, Font, PatternFill, Alignment, Border, Side alignment = Alignment( horizontal = 'center', vertical = 'center', wrap_text = True, ) border = Border( left = Side(style = 'thin'), right = Side(style = 'thin'), top = Side(style = 'thin'), bottom = Side(style = 'thin'), ) top_style = NamedStyle('top_style') top_style.alignment = alignment top_style.border = border top_style.fill = PatternFill('solid', fgColor = "000000") top_style.font = Font( bold = True, color = 'FFFFFF', name = 'Calibri', size = 10, ) excel_file.add_named_style(top_style) normal_style = NamedStyle('normal_style') normal_style.alignment = alignment normal_style.border = border normal_style.fill = PatternFill('solid', fgColor = "FFFFFF") normal_style.font = Font( color = '000000', name = 'Calibri', size = 10, ) excel_file.add_named_style(normal_style)
def paint_columns(ws, longHeader, lenData, num_col, row=11): rowPar2 = NamedStyle(name="rowPar2") rowPar2.fill = PatternFill("solid", fgColor="E0F8F1") ini_col = num_col + 1 fin_col = ini_col + 13 while fin_col < longHeader: for column in range(ini_col, fin_col): column_letter = get_column_letter(column) for rowD in range(row, lenData + row + 1): if (rowD % 2 == 0): ws[column_letter + str(rowD)].style = rowPar2 ini_col = fin_col + 13 fin_col = ini_col + 13 rowTotal = NamedStyle(name="rowTotal") rowTotal.fill = PatternFill("solid", fgColor="E6E6E6") ult_cols = longHeader - 13 for column in range(ult_cols + 1, longHeader + 1): column_letter = get_column_letter(column) for rowD in range(row, lenData + row + 1): if (rowD % 2 == 0): ws[column_letter + str(rowD)].style = rowTotal return ws
def writeObjectValues(self, objName): self.write_logger.info(self.wb.named_styles) if ('style1' not in self.wb.named_styles): style1 = NamedStyle(name="style1") style1.font = Font(name='Calibri', size=8, color='FF000000') style1.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) style1.fill = PatternFill(start_color='BDD7EE', end_color='BDD7EE', fill_type='solid') else: style1 = self.wb._named_styles['style1'] if ('style2' not in self.wb.named_styles): style2 = NamedStyle(name="style2") style2.font = Font(name='Calibri', size=8, color='FF000000') style2.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) style2.fill = PatternFill(start_color='00B050', end_color='00B050', fill_type='solid') else: style2 = self.wb._named_styles['style2'] username_json_path = config.project_path + '\\log\\' + config.customer + "\\" + objName + "\\" + objName + '_output.json' with open(username_json_path, 'r') as username_file: nokiaData = json.load(username_file) startingRow = self.dataRowNumber for nokiaRecord in nokiaData: cell = self.ws.cell(column=1, row=startingRow) try: cell.style = style2 except: self.write_logger.debug("style already exist ") self.ws.cell(column=1, row=startingRow, value='ADD') for nokiaAttr in nokiaRecord: #self.write_logger.debug(self.validationRules[nokiaAttr]) self.applyValidationToCell(self.ws, startingRow, nokiaAttr) #Create data validation object for this cell = self.ws.cell(column=self.cols[nokiaAttr] + 1, row=startingRow) try: cell.style = style1 except: self.write_logger.debug("style already exist ") self.ws.cell(column=self.cols[nokiaAttr] + 1, row=startingRow, value=nokiaRecord[nokiaAttr]) startingRow = startingRow + 1 #w_worksheet.write(startingRow, self.cols[nokiaAttr], nokiaRecord[nokiaAttr]) self.write_logger.info("Finished writing Excel")
def creationFichierResultat(): #nom = input("Nom du fichier:") if p.ELECTRICITE_ET_AUTRES and p.INTRANTS_ET_FRET and p.EMBALLAGES_ET_SACHERIE and p.FRET_AVAL: nom = "COMPLET "+str(int(100000*random.random())) else: nom = "test"+str(int(100000*random.random())) nom = "Fichier Resultat - "+str(nom)+ " - "+str(p.ANNEE)+".xlsx" fichierResultat = openpyxl.Workbook() feuille_principale = fichierResultat.active feuille_principale.title= "Résultats généraux" #Tout ce qu'il y a ci après est esthétique bd_dotted = Side(style="dotted", color="000000") bd_thin = Side(style="thin", color="FF000000") #Style du fond ffe2aa styleFond = NamedStyle(name="Fond") styleFond.fill = PatternFill("solid", fgColor= "ffe2aa") fichierResultat.add_named_style(styleFond) #Style du tete de tableau styleTeteTab = NamedStyle(name="TeteTab") styleTeteTab.border = Border(bottom = bd_dotted, top= bd_dotted) styleTeteTab.alignment = Alignment(wrapText = "true",horizontal = "center") styleTeteTab.fill = PatternFill("solid", fgColor= "FFFFFF") fichierResultat.add_named_style(styleTeteTab) #Style du valeurs de tableau styleVal = NamedStyle(name="Valeurs") styleVal.border = Border(left = bd_dotted, right = bd_dotted) styleVal.fill = PatternFill("solid", fgColor= "FFF0E1") fichierResultat.add_named_style(styleVal) #Style des titres styleTitre = NamedStyle(name="Titre") styleTitre.border = Border(top=bd_dotted, bottom = bd_dotted) styleTitre.fill = PatternFill("solid", fgColor= "EBEAF5") styleTitre.alignment = Alignment(horizontal = "center", vertical="center") styleTitre.font = Font(name = 'Calibri', size =36, italic = False, bold = True, color = 'FF000000') fichierResultat.add_named_style(styleTitre) #Style des entrees de tableau styleEntree = NamedStyle(name="Entree") styleEntree.border = Border(right = bd_thin) styleEntree.fill = PatternFill("solid", fgColor= "FFFFFF") fichierResultat.add_named_style(styleEntree) #Style des tableaux en général styleTableau = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) #On applique le fond à toutes les cellules for lin in range(1,200): for col in [x for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille_principale[case].style=styleFond for col in [x+y for x in "ABCDEFGHIJKLMNOPQRSTUVWXYZ" for y in "ABCDEFGHIJKLMNOPQRSTUVWXYZ"]: case = col+str(lin) feuille_principale[case].style=styleFond
def _create_styles(self, workbook): style = NamedStyle(name="title") style.font = Font(bold=True) style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFF9900') workbook.add_named_style(style) style = NamedStyle(name="id") style.font = Font(bold=True) style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFFFF00') workbook.add_named_style(style) style = NamedStyle(name="anonymous") style.fill = PatternFill(fill_type=FILL_SOLID, start_color='FFFF9999') workbook.add_named_style(style)
def wrapper(ws, number_of_row): # 设置标题样式 ws.merge_cells('a1:f1') color1 = Color(rgb=title_color) font = Font(name="Microsoft YaHei UI", size=34, b=True, color=color1) a1 = ws['a1'] alignment = Alignment(horizontal='center', vertical='center') a1.font = font a1.alignment = alignment # 设置表头样式 color2 = Color(rgb=header_color) style_for_row2 = NamedStyle(name='header') style_for_row2.font = Font(name='Calibri', size=16, color='FFFFFF') style_for_row2.alignment = Alignment(horizontal='center', vertical='center') style_for_row2.fill = PatternFill('solid', fgColor=color2) for each_cell in ws[2]: each_cell.style = style_for_row2 # 设置表格样式 for i in range(1, number_of_row + 3): ws.row_dimensions[i].height = 49.5 for i in range(1, 7): ws.column_dimensions[chr(64 + i)].width = 15 color3 = Color(rgb=body_color) style_for_body = NamedStyle(name='body') style_for_body.font = Font(name='Calibri') style_for_body.alignment = Alignment(horizontal='center', vertical='center') style_for_body.fill = PatternFill("solid", fgColor=color3) style_for_body.border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000')) for i in range(3, number_of_row + 3): for j in range(1, 7): ws.cell(row=i, column=j).style = style_for_body return function(ws, number_of_row)
def register_styles(wb): header = NamedStyle(name='header') header.font = Font(name="Verdana", size=12, bold=True, color="FFFFFF") header.fill = PatternFill("solid", fgColor="666666") wb.add_named_style(header) divider = NamedStyle(name='divider') divider.font = Font(name="Verdana", size=11, bold=True) divider.fill = PatternFill("solid", fgColor="b7b7b7") wb.add_named_style(divider) general = NamedStyle(name='general') general.font = Font(name="Verdana", size=11) wb.add_named_style(general)
def style_thead(): style = NamedStyle(name='thead') style.font = create_font(bold=True, color='00FFFFFF') style.fill = PatternFill(patternType='solid', fgColor='00000000') side = Side(style='thin', color='00FFFFFF') style.border = Border(left=side, right=side, top=side, bottom=side) return style
def make_style(wb): # создаем именованный стиль для серой заливки: from openpyxl.styles import NamedStyle, Border, Side, PatternFill ns = NamedStyle(name='Grey') ns.fill = PatternFill("solid", fgColor="909090") border = Side() ns.border = Border(left=border, top=border, right=border, bottom=border) wb.add_named_style(ns)
def get_style_from_dict(style_dict, style_name): """ Make NamedStyle instance from dictionary :param style_dict: dictionary with style properties. Example: {'fill': {'fill_type'='solid', 'start_color'='FFCCFFCC'}, 'alignment': {'horizontal': 'center', 'vertical': 'center', 'wrapText': True, 'shrink_to_fit': True}, 'border_side': {'border_style': 'thin', 'color': 'FF000000'}, 'font': {'name': 'Arial', 'size': 14, 'bold': True, 'color': 'FF000000'} } :param style_name: name of created style :return: openpyxl.styles.NamedStyle instance """ style = NamedStyle(name=style_name) if not style_dict: return style for key, value in style_dict.items(): if key == "font": style.font = Font(**value) elif key == "fill": style.fill = PatternFill(**value) elif key == "alignment": style.alignment = Alignment(**value) elif key == "border_side": side = Side(**value) style.border = Border(left=side, right=side, top=side, bottom=side) return style
def __init__(self, excel_name, name): self.excel_name = "{}.xlsx".format(excel_name) self.workbook = openpyxl.Workbook() self.worksheet = self.workbook.active self.worksheet.title = name border_style = NamedStyle(name="border_style") bian = Side(style="medium", color="000000") border = Border(top=bian, bottom=bian, left=bian, right=bian) border_style.border = border alignment = Alignment(horizontal="center", vertical="center") border_style.alignment = alignment self.border_style = border_style self.workbook.add_named_style(border_style) title_style = NamedStyle(name="title_style") ft = Font(name="Noto Sans CJK SC Regular", color="FFFFFF", size=11, b=False) fill = PatternFill("solid", fgColor="00A3FF") title_style.font = ft title_style.fill = fill title_style.border = border title_style.alignment = alignment self.title_style = title_style self.workbook.add_named_style(title_style)
def create_output_file(IP_list, output): workbook = openpyxl.Workbook() sheet = workbook.active sheet.title = "Reputations" highlight = NamedStyle(name="highlight") highlight.font = Font(name='Verdana', size=9, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FFFFFF') highlight.fill = PatternFill(fill_type="solid", bgColor='FF000000') workbook.add_named_style(highlight) cell_obj = sheet.cell(row=1, column=1) cell_obj.value = "IP" sheet.merge_cells('A1:A2') sheet['A1'].style = highlight row = 3 col = 1 for IP in IP_list: cell_obj = sheet.cell(row=row, column=col) cell_obj.value = IP row = row + 1 workbook.save(output + ".xlsx")
def total_summary(ws, listTotal, numberRow, lonTableHeader, font_color="FFFFFF", fill_color="afbcd7", formatPercent=[], formatMoney=[], formatNumberInteger=[], formatNumberDecimal=[]): ws.append(listTotal) totalOpe = NamedStyle(name="totalOpe") totalOpe.alignment = Alignment(horizontal='center') totalOpe.fill = PatternFill("solid", fgColor=fill_color) totalOpe.font = Font(color=font_color, size=12, bold=True) for row in ws.iter_rows('A' + str(numberRow) + ':' + get_column_letter(lonTableHeader) + str(numberRow)): for cell in row: cell.style = totalOpe for col_letter in formatPercent: ws[col_letter + str(numberRow)].number_format = '#,##0.00 %' for col_letter in formatMoney: ws[col_letter + str(numberRow)].number_format = '#,##0.00 $' for col_letter in formatNumberDecimal: ws[col_letter + str(numberRow)].number_format = '#,##0.00' for col_letter in formatNumberInteger: ws[col_letter + str(numberRow)].number_format = '#0' return ws
def paint_par(ws, longHeader, lenData, num_col, row=11, col_money=[], col_porc=[], col_nro_dec=[], col_nro_int=[]): rowPar = NamedStyle(name="rowPar") rowPar.fill = PatternFill("solid", fgColor="E0ECF8") for column in range(1, longHeader + 1): column_letter = get_column_letter(column) for rowD in range(row, lenData + row + 1): if (rowD % 2 == 0): ws[column_letter + str(rowD)].style = rowPar if (column > num_col): ws[column_letter + str(rowD)].number_format = '#,##0.00' if (column in col_nro_int): ws[column_letter + str(rowD)].number_format = '#0' if (column in col_nro_dec): ws[column_letter + str(rowD)].number_format = '#,##0.00' if (column in col_money): ws[column_letter + str(rowD)].number_format = '#,##0.00 $' if (column in col_porc): ws[column_letter + str(rowD)].number_format = '#,##0.00 %' return ws
def _build_styles(self): # Builds new styles for the spreadsheet title = NamedStyle("title") title.font = self.WHITE_FONT title.fill = self.BLACK_FILL title.alignment = self.MID_ALIGN self.workbook.add_named_style(title)
def generar_codigos_excel(self): response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', ) response[ 'Content-Disposition'] = 'attachment; filename={date}-codigos.xlsx'.format( date=datetime.now().strftime('%Y%m%d'), ) titulo = NamedStyle(name="titulo") titulo.font = Font(bold=True, size=10, color='ffffff') titulo.alignment = Alignment( horizontal="center", vertical="center", wrap_text=True, ) titulo.fill = PatternFill( start_color='244062', end_color='244062', fill_type='solid', ) wb = Workbook() wb.add_named_style(titulo) sheet = wb.active column_dimensions = sheet.column_dimensions['A'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['B'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['C'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['D'] column_dimensions.width = 20 column_dimensions = sheet.column_dimensions['E'] column_dimensions.width = 20 sheet['A1'] = "CODIGO PRINCIPAL" sheet['A1'].style = titulo sheet['B1'] = "CODIGO EXTERNO" sheet['B1'].style = titulo sheet['C1'] = "CODIGO PROVEEDOR" sheet['C1'].style = titulo sheet['D1'] = "CODIGO TESTIGO" sheet['D1'].style = titulo sheet['E1'] = "CODIGO BOLSA" sheet['E1'].style = titulo for codigo in self.__codigos: sheet.append( (codigo.numero, codigo.cod_externo, codigo.cod_proveedor, codigo.cod_testigo, codigo.cod_bolsa)) wb.save(response) return response
def __init__(self): __style = NamedStyle(name='simple') __style.font = Font(bold=True, size=11, color=colors.BLACK) __style.fill = PatternFill(patternType='solid', fgColor='FABF8F') bd = Side(style='thin', color=colors.BLACK) self.__border = Border(left=bd, top=bd, right=bd, bottom=bd) __style.border = self.__border self.__alignment = Alignment(horizontal='left', vertical='center') __style.alignment = self.__alignment self.__header_style = __style
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 adds_title_format_new(ws, lonTableHeader, font_color="FFFFFF", fill_color="afbcd7", rows=10): headOpe = NamedStyle(name="headOpe") headOpe.alignment = Alignment(horizontal='center') headOpe.fill = PatternFill("solid", fgColor=fill_color) headOpe.font = Font(color=font_color, size=12, bold=True) for row in ws.iter_rows('A' + str(rows) + ':' + get_column_letter(lonTableHeader) + str(rows)): for cell in row: cell.style = headOpe return ws
def _generate_spreadsheet_data(cls, request, out, report, *args, **kwargs): # Create a workbook wb = Workbook(write_only=True) ws = wb.create_sheet(title=report.name) # Create a named style for the header row readlonlyheaderstyle = NamedStyle(name="readlonlyheaderstyle") readlonlyheaderstyle.fill = PatternFill(fill_type="solid", fgColor="d0ebfb") wb.add_named_style(readlonlyheaderstyle) # Run the query conn = None try: conn = create_connection(request.database) comment = CellComment(force_text(_("Read only")), "Author", height=20, width=80) with conn.cursor() as cursor: sqlrole = settings.DATABASES[request.database].get( "SQL_ROLE", "report_role") if sqlrole: cursor.execute("set role %s" % (sqlrole, )) cursor.execute(sql=cls.getSQL(report.sql)) if cursor.description: # Write header row header = [] for f in cursor.description: cell = WriteOnlyCell(ws, value=f[0]) cell.style = "readlonlyheaderstyle" cell.comment = comment header.append(cell) ws.append(header) # Add an auto-filter to the table ws.auto_filter.ref = "A1:%s1048576" % get_column_letter( len(header)) # Write all output rows for result in cursor.fetchall(): ws.append( [_getCellValue(i, request=request) for i in result]) # Write the spreadsheet wb.save(out) finally: if conn: conn.close()
def add_col_style(wb, column, font_name='Calibri', font_bold=False, font_color='000000', font_size=11, top_border_type='thin', top_border_color='000000', bot_border_type='thin', bot_border_color='000000', left_border_type='thin', left_border_color='000000', right_border_type='thin', right_border_color='000000', align_horizn='left', align_vertic='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0, fill_start_color='FFFFFF', fill_end_color='FFFFFF', fill_type='solid'): name = 'column_' + str(column) + "_" + str(tab_name) st = NamedStyle(name=name) st.font = Font(name=font_name, bold=font_bold, color=font_color, size=font_size) bd_top = Side(style=top_border_type, color=top_border_color) bd_bot = Side(style=bot_border_type, color=bot_border_color) bd_left = Side(style=left_border_type, color=left_border_color) bd_right = Side(style=right_border_type, color=right_border_color) st.border = Border(left=bd_left, top=bd_top, right=bd_right, bottom=bd_bot) st.alignment = Alignment(horizontal=align_horizn, vertical=align_vertic, text_rotation=text_rotation, wrap_text=wrap_text, shrink_to_fit=shrink_to_fit, indent=indent) st.fill = PatternFill(start_color=fill_start_color, end_color=fill_end_color, fill_type=fill_type) wb.add_named_style(st) return name
def add_body_style(wb): name = 'body_' + str(tab_name) st = NamedStyle(name=name) st.font = Font(name='Calibri', bold=False, size=11) bd = Side(style='thin', color="000000") st.border = Border(left=bd, top=bd, right=bd, bottom=bd) st.alignment = Alignment(horizontal='left', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) st.fill = PatternFill(start_color=body_color, end_color=body_color, fill_type='solid') wb.add_named_style(st) return name
def xl_create_named_style(wb): ''' create excel named style wb : excel workbook ''' zbx_title = NamedStyle(name="zbx title") zbx_title.font = Font(bold=True, color="ffffff") zbx_title.fill = PatternFill(fill_type="solid", start_color="538DD5", end_color="538DD5") bd = Side(style="thin", color="538DD5") zbx_title.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(zbx_title) zbx_data = NamedStyle(name="zbx data") zbx_data.font = Font(color="000000") zbx_data.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(zbx_data) return
def add_highlighter(wb): name = 'highlight' st = NamedStyle(name=name) st.font = Font(name='Calibri', bold=True, size=11) bdb = Side(style='medium', color="000000") bdt = Side(style='thin', color="000000") st.border = Border(left=bdt, top=bdt, right=bdt, bottom=bdt) st.alignment=Alignment(horizontal='left', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) st.fill = PatternFill(start_color='ffcccc', end_color='ffcccc', fill_type='solid') wb.add_named_style(st) return name
def add_row_hlt_style(wb, highlight_vals, name_order, h_type=None): ''' This styler is for highlighting cells and works as for one style creation and for multiple as well due to 'name_order' argument which will distiguish each unique style. This function is used for both index and row highlighters Takes: - wb - object - workbook; - highlight_vals - dictionary from highlight_dict; - name_order - int - id from highlight_dict - h_type - string - highlighter type 'row' or 'index' ''' if h_type == None: print("Error: add_row_hlt_style(): Please"+\ "specify correct h_type variable.") return None # create unique style names depending on type of highlight name = '{}_highlighter_'.format(h_type) + str(tab_name) + str( name_order) st = NamedStyle(name=name) st.font = Font(name='Calibri', bold=highlight_vals['hlt_font_bold'], size=10, color=highlight_vals['hlt_txt_color']) if highlight_vals['hlt_border_bold']: bord_side = 'thick' else: bord_side = "thin" bdb = Side(style=bord_side, color="000000") bdt = Side(style='thin', color="000000") st.border = Border(left=bdt, top=bdb, right=bdt, bottom=bdb) st.alignment = Alignment(horizontal=highlight_vals['hlt_align_horiz'], vertical=highlight_vals['hlt_align_vert'], text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) st.fill = PatternFill(start_color=highlight_vals['hlt_color'], end_color=highlight_vals['hlt_color'], fill_type='solid') wb.add_named_style(st) return name
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)
def create_excel(product_list, product_name): # Format of excel # No. | Title of product | Price | URL link | E-Commerce # Define file name today = date.today().strftime("%d%m%Y") excel_file_name = "ExcelReport/{}_{}.xlsx".format(today, product_name) if os.path.exists(excel_file_name): workbook = load_workbook(excel_file_name) worksheet = workbook.active else: # Create Excel workbook = Workbook() worksheet = workbook.active worksheet.title = "Original Data" # Header style header_style = NamedStyle(name="hearder_style") header_style.font = Font(bold=True) header_style.fill = PatternFill(start_color="00FFFF00", end_color="00FFFF00", fill_type="solid") # Write header header_list = ("No.", "Product Title", "Price", "Url Link", "E-Commerce") for col in range(1, len(header_list) + 1): header_cell = worksheet.cell(1, col, header_list[col - 1]) header_cell.style = header_style # Write data into worksheet product_num = worksheet.max_row for data in product_list: content = (product_num, data[0], data[1], data[2], data[3]) worksheet.append(content) product_num += 1 workbook.save(excel_file_name) print("Done create excel.")
def prettify(self): b = load_workbook(self.fname) header_style = NamedStyle(name='table_header') header_style.font = Font(bold=True) header_style.fill = PatternFill(fill_type='solid', start_color='00CCCCCC', end_color='00CCCCCC') header_style.alignment = Alignment(horizontal='center') thin = Side(border_style='thin', color='000000') double = Side(border_style="double", color="ff0000") header_style.border = Border(top=double, left=thin, right=thin, bottom=double) b.add_named_style(header_style) for ws in b.worksheets: if ws.title in self.sheets_with_header: has_header = True for cell in ws[1]: cell.style = header_style else: has_header = False for x, c in enumerate(ws.columns, start=1): column_width = 5 for y, cell in enumerate(c, start=1): if cell.value is not None: w = len(str(cell.value)) if has_header and (y == 1): w = int(1.25 * w) if w > column_width: column_width = w if column_width > 50: column_width = 50 ws.column_dimensions[get_column_letter( x)].width = column_width + 2 b.save(self.fname)
if sys.version[0] == '2': reload(sys) sys.setdefaultencoding("utf-8") #sys.path.insert(0, "/var/www/cgi-bin/bank_recon.py") os.chdir(".") # bound path to the current directory #satisfy openpyxl requirements for highlighting cells highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=12) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) highlight.fill = PatternFill(fill_type='lightUp', start_color='fff000', end_color='6efdfd') #satisfy openpyxl requirements for highlighting cells2 highlight2 = NamedStyle(name="highlight2") highlight2.font = Font(bold=True, size=12) bd = Side(style='thick', color="000000") highlight2.border = Border(left=bd, top=bd, right=bd, bottom=bd) highlight2.fill = PatternFill(fill_type='lightUp', start_color='fff000', end_color='fff000') print("\n") print("arquivos excel disponiveis nesta pasta:")
def __init__(self, datetimenow=datetime.datetime.now()): dirname = "Logs/%s" % datetimenow.strftime("%Y-%m-%d_%H_%M_%S") if not os.path.exists(dirname): os.mkdir(dirname) self.wbname = "%s/Report.xlsx" % dirname self.wb = Workbook() ft = Font(name=u'Courier New', size=16, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') fill = PatternFill(fill_type="solid", start_color='FF88FFFF', end_color='FF008800') cellfill = PatternFill(fill_type="solid", start_color='FFFFFFFF', end_color='FF000000') bd = Border(left=Side(border_style="thin", color='FF001000'), right=Side(border_style="thin", color='FF110000'), top=Side(border_style="thin", color='FF110000'), bottom=Side(border_style="thin", color='FF110000'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF110000') ) alignment = Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) headerstyle = NamedStyle(name="header") headerstyle.font = ft headerstyle.fill = fill headerstyle.border = bd headerstyle.alignment = alignment self.wb.add_named_style(headerstyle) cellstyle = NamedStyle(name="cell") cellstyle.font = ft cellstyle.fill = cellfill cellstyle.border = bd cellstyle.alignment = alignment self.wb.add_named_style(cellstyle) self.current_row = 0 self.chart_row = 0 self.linkbd = bd pass
def create_groups_xlsx(day): activate("de") day = GroupAssignment.objects.monday(day) days = [day + timedelta(days=i) for i in range(5)] wb = Workbook() ws = wb.active thin_border = Side(border_style="thin", color="00000000") medium_border = Side(border_style="medium", color="00000000") font = Font(name="Calibri", size=14) centered = NamedStyle("centered") centered.font = font centered.alignment = Alignment(horizontal="center", vertical="center") wb.add_named_style(centered) dark = NamedStyle("dark") dark.font = font dark.fill = PatternFill("solid", "cccccc") dark.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=thin_border ) wb.add_named_style(dark) darker = NamedStyle("darker") darker.border = Border(top=thin_border, bottom=thin_border) darker.font = Font(name="Calibri", size=14, bold=True) darker.fill = PatternFill("solid", "aaaaaa") wb.add_named_style(darker) darker_border_left = NamedStyle("darkerBorderLeft") darker_border_left.border = Border( top=thin_border, bottom=thin_border, left=medium_border ) darker_border_left.font = darker.font darker_border_left.fill = darker.fill wb.add_named_style(darker_border_left) border = NamedStyle("borderThickLeft") border.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=medium_border ) border.font = font wb.add_named_style(border) border = NamedStyle("borderThickBottom") border.border = Border(bottom=medium_border) border.font = font wb.add_named_style(border) border = NamedStyle("borderThinLeft") border.border = Border(left=thin_border) border.font = font wb.add_named_style(border) border = NamedStyle("borderThinBottom") border.border = Border(bottom=thin_border) border.font = font wb.add_named_style(border) borderThin = NamedStyle("borderThin") borderThin.border = Border( top=thin_border, right=thin_border, bottom=thin_border, left=thin_border ) borderThin.font = font wb.add_named_style(borderThin) vertical_text = Alignment(text_rotation=90) def day_column(weekday): return 2 + 9 * weekday def style_row(row, style): ws[c(0, row)].style = style ws[c(1, row)].style = style ws[c(day_column(5), row)].style = ( "darkerBorderLeft" if style == "darker" else style ) for i in range(5): for j in range(9): ws[c(day_column(i) + j, row)].style = style if style == "darker": ws[c(day_column(i), row)].style = "darkerBorderLeft" def column_width(column, width): ws.column_dimensions[columns[column]].width = width def row_height(row, height): ws.row_dimensions[row + 1].height = height ws[c(0, 1)].style = "borderThickBottom" ws[c(1, 1)].style = "borderThickBottom" ws[c(day_column(5), 1)].style = "borderThickBottom" for i, cell in enumerate( [ date_format(day, "F y"), "Woche %s" % date_format(day, "W"), "Auftragsnummer Arbeit", "LEITUNG", "ZIVIS", ] ): ws[c(0, i + 1)] = cell ws[c(day_column(5), i + 1)] = cell ws[c(0, i + 1)].style = "borderThinBottom" ws[c(1, i + 1)].style = "borderThinBottom" if i > 0: ws[c(day_column(5), i + 1)].style = "borderThickLeft" if i < 2: ws[c(0, i + 1)].style = centered ws[c(day_column(5), i + 1)].style = centered column_width(0, 35) column_width(1, 15) column_width(day_column(5), 35) ws[c(0, 1)].style = "borderThickBottom" ws[c(1, 1)].style = "borderThickBottom" ws[c(0, 1)].alignment = centered.alignment ws[c(1, 1)].alignment = centered.alignment ws[c(0, 2)].style = "borderThinBottom" ws[c(0, 2)].alignment = centered.alignment ws[c(day_column(5), 1)].style = "borderThickBottom" ws[c(day_column(5), 1)].alignment = centered.alignment ws[c(day_column(5), 2)].style = "borderThickLeft" ws[c(day_column(5), 2)].alignment = centered.alignment for i, current in enumerate(days): ws[c(day_column(i), 0)] = date_format(current, "l") ws[c(day_column(i), 1)] = date_format(current, "d.m.y") ws[c(day_column(i), 0)].style = centered ws[c(day_column(i), 1)].style = "borderThickBottom" ws[c(day_column(i), 1)].alignment = centered.alignment ws.merge_cells("%s:%s" % (c(day_column(i), 0), c(day_column(i + 1) - 1, 0))) ws.merge_cells("%s:%s" % (c(day_column(i), 1), c(day_column(i + 1) - 1, 1))) ws[c(day_column(i), 2)] = "Absenz" for k in range(2, 499): ws[c(day_column(i), k)].style = "borderThickLeft" ws[c(day_column(i) + 1, k)].style = "borderThin" for j in range(1, 9): ws[c(day_column(i) + j, 2)] = "%s)" % j style = "borderThin" if j % 2 else "dark" for k in range(2, 499): ws[c(day_column(i) + j, k)].style = style ws[c(day_column(i) + j, 2)].alignment = vertical_text column_width(day_column(i) + j, 7) ws[c(day_column(i), 2)].alignment = vertical_text column_width(day_column(i), 7) row_height(2, 250) row_height(3, 60) row_height(4, 60) # ZIVIS line style_row(5, "darker") assignments = defaultdict(list) seen_assignments = set() for ga in GroupAssignment.objects.filter(week=day).select_related( "assignment__drudge__user" ): assignments[ga.group_id].append(ga.assignment) seen_assignments.add(ga.assignment_id) free_assignments = ( Assignment.objects.for_date(day) .exclude(pk__in=seen_assignments) .select_related("drudge__user") ) absences = defaultdict(dict) for absence in Absence.objects.filter(days__overlap=days): for day in absence.days: absences[absence.assignment_id][day] = absence def add_group(row, group_name, assignments): ws[c(0, row)] = group_name ws[c(day_column(5), row)] = group_name style_row(row, "darker") # TODO courses (UNA/MSK) for assignment in assignments: row += 1 ws[c(0, row)] = assignment.drudge.user.get_full_name() ws[c(day_column(5), row)] = assignment.drudge.user.get_full_name() ws[c(0, row)].style = "borderThinBottom" ws[c(1, row)].style = "borderThinBottom" ws[c(day_column(5), row)].style = "borderThickLeft" row_height(row, 35) if assignment.date_from in days: ws[c(1, row)] = "NEU" elif assignment.determine_date_until() in days: ws[c(1, row)] = "ENDE" else: ws[c(1, row)] = date_format(assignment.determine_date_until(), "d.m.y") for i, current in enumerate(days): if current < assignment.date_from: ws[c(day_column(i), row)] = "Vor Beginn" elif current > assignment.determine_date_until(): ws[c(day_column(i), row)] = "Nach Ende" elif current in absences[assignment.id]: ws[c(day_column(i), row)] = absences[assignment.id][ current ].pretty_reason() # Skip some lines for i in range(0, max(3, 6 - len(assignments))): row += 1 row_height(row, 35) ws[c(0, row)].style = "borderThinBottom" ws[c(1, row)].style = "borderThinBottom" ws[c(day_column(5), row)].style = "borderThickLeft" row += 1 return row row = 6 for group in Group.objects.active(): row = add_group(row, group.name, assignments[group.id]) row = add_group(row, "Nicht zugeteilt", free_assignments) return wb