def write_excel(self): left, right, top, bottom = [Side(style='thin', color='000000')] * 4 title = NamedStyle(name="title") title.font = Font(name=u'宋体', size=11, bold=True) title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) title.border = Border(left=left, right=right, top=top, bottom=bottom) content = NamedStyle(name="content") content.font = Font(name=u'宋体', size=11) content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) content.border = Border(left=left, right=right, top=top, bottom=bottom) content_long = NamedStyle(name="content_long") content_long.font = Font(name=u'宋体', size=11) content_long.border = Border(left=left, right=right, top=top, bottom=bottom) content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True) self.ws.column_dimensions['A'].width = 15 self.ws.column_dimensions['B'].width = 15 self.ws.column_dimensions['C'].width = 80 self.ws.column_dimensions['D'].width = 55 self.ws.column_dimensions['E'].width = 55 self.ws.column_dimensions['F'].width = 10 self.ws_wrong.column_dimensions['A'].width = 20 self.ws_wrong.column_dimensions['B'].width = 15 self.ws_wrong.column_dimensions['C'].width = 15 self.ws_wrong.column_dimensions['D'].width = 80 self.ws_wrong.column_dimensions['E'].width = 80 for i in range(self.ws.max_row): self.ws.row_dimensions[i + 1].height = 30 for i in range(self.ws_wrong.max_row): self.ws_wrong.row_dimensions[i + 1].height = 50 for x in self.ws[1]: x.style = title for x in self.ws_wrong[1]: x.style = title for x in self.ws['A'][1:]: x.style = content for x in self.ws_wrong['A'][1:]: x.style = content for x in self.ws['B'][1:]: x.style = content for x in self.ws_wrong['B'][1:]: x.style = content for x in self.ws['C'][1:]: x.style = content_long for x in self.ws_wrong['C'][1:]: x.style = content for x in self.ws['D'][1:]: x.style = content_long for x in self.ws_wrong['D'][1:]: x.style = content_long for x in self.ws['E'][1:]: x.style = content_long for x in self.ws_wrong['E'][1:]: x.style = content_long for x in self.ws['F'][1:]: x.style = content print("格式正确个数 %d" % self.ws.max_row) print("格式错误个数 %d" % self.ws_wrong.max_row) self.wb.save(os.path.dirname(os.path.abspath(self.path)) + "\\" + os.path.basename(self.path).replace(".txt", ".xlsx"))
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 __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 export_xlsx(self, filename): um = self.units.description data = [(i+1,v,um) for (i,v) in enumerate(self.values)] wb = Workbook(write_only = True) ws = wb.create_sheet() # create some styles cellstyle = NamedStyle(name="highlight") headerstyle = NamedStyle(name='headercell') wb.add_named_style(cellstyle) wb.add_named_style(headerstyle) cellstyle.font = Font(name='Calibri', size=11) headerstyle.font = Font(name='Calibri', size=11, bold=True) bd = Side(border_style='thin') cellstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) headerstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) header_labels = ['#', 'Value', 'Units'] # write header header = [] for el in header_labels: cell = WriteOnlyCell(ws, value=el) cell.style = 'headercell' header.append(cell) ws.append(header) # write data for t in data: row = [] for el in t: cell = WriteOnlyCell(ws, value=el) cell.style = 'highlight' row.append(cell) ws.append(row) wb.save(filename) # doctest: +SKIP
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 build_styles(): #the excel file we are editating now is a global variable global excel_file #this is for aligning the text inside the cells alignment = Alignment( horizontal='center', vertical='center', ) #aplying a border to the cell border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'), ) #the default font style used in the headlines font = Font( #this makes the font bold for the headlines bold=True, color='000000', name='Calibri', size=11, ) #creating the top style for the headline cells top_style = NamedStyle('top_style') #applying our configuration to the style we just created top_style.alignment = alignment top_style.font = font top_style.border = border #adding the style to the file excel_file.add_named_style(top_style) #the normal style normal_style = NamedStyle('normal_style') #in this style we don't need an aligment nor a font, we're gonna use the default #which is: #horizontal = 'left', #vertical = 'bottom', #fontName = 'Calibri', #fontSize = 11, #color = '000000' normal_style.border = border #adding the style to the file excel_file.add_named_style(normal_style) #this is style is the same as the normal but with center aligning center_style = NamedStyle('center_style') center_style.alignment = alignment center_style.border = border excel_file.add_named_style(center_style)
def format_file(self): self.ws_new.column_dimensions['A'].width = 20 self.ws_new.column_dimensions['B'].width = 14 self.ws_new.column_dimensions['C'].width = 14 self.ws_new.column_dimensions['D'].width = 70 self.ws_new.column_dimensions['E'].width = 10 self.ws_new.column_dimensions['F'].width = 14 self.ws_new.column_dimensions['G'].width = 14 self.ws_new.column_dimensions['H'].width = 14 self.ws_new.column_dimensions['I'].width = 14 self.ws_new.column_dimensions['J'].width = 14 self.ws_new.column_dimensions['K'].width = 14 self.ws_new.column_dimensions['L'].width = 25 left, right, top, bottom = [Side(style='thin', color='000000')] * 4 title = NamedStyle(name="title") title.font = Font(name=u'宋体', size=11, bold=True) title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) title.border = Border(left=left, right=right, top=top, bottom=bottom) content = NamedStyle(name="content") content.font = Font(name=u'宋体', size=11) content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) content.border = Border(left=left, right=right, top=top, bottom=bottom) content_long = NamedStyle(name="content_long") content_long.font = Font(name=u'宋体', size=11) content_long.border = Border(left=left, right=right, top=top, bottom=bottom) content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True) for i in range(self.ws_new.max_row): self.ws_new.row_dimensions[i + 1].height = 40 for x in self.ws_new['A:C']: for y in x: y.style = content for x in self.ws_new['E:F']: for y in x: y.style = content for x in self.ws_new['H:K']: for y in x: y.style = content for x in self.ws_new[1]: x.style = title for x in self.ws_new['D'][1:]: x.style = content_long for x in self.ws_new['L'][1:]: x.style = content_long for x in self.ws_new['G'][1:]: x.style = content_long
def create(data): workbook = Workbook() sheet = workbook.active file_name = 'utils/tugs_scheduler.xlsx' # setting sheet.title = "scheduler" sheet["A1"] = "ID" sheet["B1"] = "REMOLCADOR" sheet["C1"] = "ACTUAL" sheet["D1"] = "PRÓXIMA" sheet["E1"] = "TIPO" # Let's create a style template for the header row header = NamedStyle(name="header") header.font = Font(bold=True) header.border = Border(bottom=Side(border_style="thin")) header.alignment = Alignment(horizontal="center", vertical="center") header_row = sheet[1] for cell in header_row: cell.style = header # put data into the file for item in data: sheet.append(item) # validate date (coming date <= today date) # whether validation is TRUE, add RED color to date big_red_text = Font(color=colors.BLUE) for i in sheet["D"][1:]: if i.value <= datetime.date.today(): i.font = big_red_text i.value = i.value.strftime("%d/%m/%Y") # save data into the file workbook.save(filename=file_name)
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 custom_research_base(ws1, d1, d2, result_query, research_title): style_border = NamedStyle(name="style_border_ca") bd = Side(style='thin', color="000000") style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd) style_border.font = Font(bold=True, size=11) style_border.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center') ws1.cell(row=1, column=1).value = 'Услуга:' ws1.cell(row=1, column=2).value = research_title ws1.cell(row=2, column=1).value = 'Период:' ws1.cell(row=3, column=1).value = f'c {d1} по {d2}' columns = [ ('Направление', 15), ('Пациент', 45), ('Пол', 10), ('Дата рождения', 26), ('Возраст', 10), ('Адрес', 40), ('Исполнитель', 35), ('Код врача', 15), ] columns2 = [(i, 25) for i in result_query["custom_fields"]] columns.extend(columns2) row = 5 for idx, column in enumerate(columns, 1): ws1.cell(row=row, column=idx).value = column[0] ws1.column_dimensions[get_column_letter(idx)].width = column[1] ws1.cell(row=row, column=idx).style = style_border return ws1
def write_to_spreadsheet(self): '''Formats and writes data to spreadsheet.''' wb = Workbook() highlight = NamedStyle(name="highlight") highlight.font = Font(bold=False, size=11) bd = Side(style='thin', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(highlight) # Register named style sh1 = wb.active sh1.title = 'Purchasing Exceptions' sh1.append(['Part Number']) sh1['A1'].font = Font(bold=True, size=11) sh1['A1'].border = Border(left=bd, top=bd, right=bd, bottom=bd) sh1['A1'].alignment = Alignment(horizontal='center') rnum = 2 sh1.column_dimensions['A'].width = (30) # col_width2 = 0 for item in self.part_list: # sh1_tool_list_data = item sh1.cell(row=rnum, column=1).value = item sh1.cell(row=rnum, column=1).style = 'highlight' # if len(str(sh1_description)) > col_width2: # col_width2 = len(str(sh1_description)) rnum += 1 # sh1.column_dimensions['A'].width = (col_width2 * 1.125) save_name = (('{}/Purchasing Exception Report.xlsx').format(self.idir)) self.saved_as_string = ('Results file: {}').format(save_name) self.file_listbox.insert(tk.END, self.count_string) self.file_listbox.insert(tk.END, self.saved_as_string) self.file_listbox.see(tk.END) wb.save(save_name) os.startfile(save_name)
def patologistology_buh_base(ws1): style_border = NamedStyle(name="style_border") bd = Side(style='thin', color="000000") style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd) style_border.font = Font(bold=True, size=11) style_border.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center') ws1.cell(row=2, column=1).value = 'Период:' columns = [ ('Медицинская организация ', 36), ('ФИО пациента ', 25), ('Дата рождения ', 17), ('Номер полиса ОМС ', 30), ('СНИЛС ', 15), ('Дата регистрации', 17), ('Код МКБ 10 заключение ', 12), ('Источник ', 12), ('Код оплаты (категория)', 30), ('Цель ', 26), ('Код мед услуги и категория сложности', 35), ('Направление', 30), ] for idx, column in enumerate(columns, 1): ws1.cell(row=4, column=idx).value = column[0] ws1.column_dimensions[get_column_letter(idx)].width = column[1] ws1.cell(row=4, column=idx).style = style_border return ws1
def style_base(name): style = NamedStyle(name=name) style.font = create_font(size=9) style.alignment = Alignment(vertical='top', wrap_text=True) side = Side(style='thin') style.border = Border(left=side, right=side, top=side, bottom=side) return style
def add_default_styles(workbook): """ plug in reusable styles """ border = Side(style="thin", color="000000") thick_border = Side(style="thick", color="000000") default_style = NamedStyle(name="default_style") default_style.font = Font(size=15) default_style.border = Border(top=border, left=border, bottom=border, right=border) workbook.add_named_style(default_style) top_row = copy(default_style) top_row.name = "top_row" top_row.border = Border(top=thick_border, left=border, bottom=border, right=border) workbook.add_named_style(top_row) centered = Alignment(horizontal="center", vertical="center") for style in (default_style, top_row): style = copy(style) style.name = style.name + "_centered" style.alignment = centered workbook.add_named_style(style) style = copy(style) style.name = style.name + "_no_letter" style.fill = PatternFill("solid", fgColor="b7b7b7") workbook.add_named_style(style)
def create_font_style(): border_style = NamedStyle(name="BorderAndFont") border_style.font = Font(name='Times New Roman', size=10, color='000000') bd = Side(style='thin', color='000000') border_style.border = Border(left=bd, top=bd, right=bd, bottom=bd) border_style.alignment.wrap_text = True return border_style
def dispansery_plan_base(ws1, d1, d2): style_border = NamedStyle(name="style_border_ca") bd = Side(style='thin', color="000000") style_border.border = Border(left=bd, top=bd, right=bd, bottom=bd) style_border.font = Font(bold=True, size=14) style_border.alignment = Alignment(wrap_text=True, horizontal='justify', vertical='center') ws1.cell(row=1, column=1).value = 'Д-учет план:' ws1.cell(row=2, column=1).value = f'{d1} {d2}' columns = [ ('№ карты', 13), ('ФИО', 55), ('Дата рождения', 15), ('Диагноз', 65), ('Месяц план', 20), ] for idx, column in enumerate(columns, 1): ws1.cell(row=4, column=idx).value = column[0] ws1.column_dimensions[get_column_letter(idx)].width = column[1] ws1.cell(row=4, column=idx).style = style_border return ws1
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 style_base(name): style = NamedStyle(name=name) style.font = create_font(size=9) style.alignment = Alignment(vertical='top', wrap_text=True) side = Side(style='thin') style.border = Border(left=side, right=side, top=side, bottom=side) return style
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 _set_style(self): my_style = NamedStyle(name="my_style") my_style.font = Font(name='Calibri') bd = Side(style='thin') my_style.border = Border(left=bd, top=bd, right=bd, bottom=bd) self.wb.add_named_style(my_style) return "my_style"
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 __cell_style(): #adding style highlight = NamedStyle(name="highlight") highlight.font = Font(name='Ebrima',size=8,) highlight.alignment=Alignment(horizontal='center') bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) return highlight
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 __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 create_styles(self, workbook): """ styles: style_title """ highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) workbook.add_named_style(highlight)
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 csv_to_xlsx(export_datas): wb = Workbook() my_style = NamedStyle(name="style") my_style.border = Border(left=Side(style='dashed'), right=Side(style='dashed'), top=Side(style='dashed'), bottom=Side(style='dashed')) my_style.font = Font(name='맑은 고딕', size=11, bold=False, italic=False, underline='none', strike=False, color='FF000000') wb.add_named_style(my_style) for export_data in export_datas: print(f"export_data:{export_data}") page = str(export_data['page']) index = str(export_data['index']) sheet_name = f'page-{page.zfill(4)}-table-{index.zfill(2)}' export_data['sheet_name'] = sheet_name ws = wb.create_sheet() ws.title = sheet_name with open(export_data['csv_path'], 'r', encoding='utf8') as f: next(f) for row in csv.reader(f): ws.append(row) cols = ws.max_column rows = ws.max_row header_fill = PatternFill("solid", fgColor="667b68") body_fill = PatternFill("solid", fgColor="f7f9f1") for row in ws.iter_rows(min_row=1, max_row=rows, min_col=1, max_col=cols): for cell in row: cell.style = my_style if (cell.row > 1): cell.fill = body_fill else: cell.fill = header_fill cell.font = Font(b=True, color="ead253") output_path = os.path.dirname(export_datas[0]['csv_path']) output_path += f'\{os.path.basename(output_path)}.xlsx' wb.save(output_path) wb.close() process_table(output_path, export_datas)
def save_excel_file(words_data): """ Saves the words_data to a styled excel sheet :param words_data: [[word, synonyms, sentences], ....] :return: None """ workbook = Workbook() sheet = workbook.active sheet.cell(row=1, column=1).value = 'word' for i in range(SYNONYMS_NUM): column_index = i + 2 sheet.cell(row=1, column=column_index).value = f'synonym {i+1}' sheet.column_dimensions[get_column_letter( column_index)].width = FONT_SIZE + 5 for i in range(SENTENCES_NUM): column_index = i + 2 + SYNONYMS_NUM sheet.cell(row=1, column=column_index).value = f'sentence {i+1}' sheet.column_dimensions[get_column_letter( column_index)].width = FONT_SIZE * 7 row_index = 2 # starts at 1 + header size (1) for word, synonyms, sentences in words_data: sheet.cell(row=row_index, column=1).value = word for i, synonym in enumerate(synonyms): column_index = i + 2 sheet.cell(row=row_index, column=column_index).value = synonym for i, sentence in enumerate(sentences): column_index = i + 2 + SYNONYMS_NUM sheet.cell(row=row_index, column=column_index).value = sentence row_index += 1 font = Font(size=FONT_SIZE) sheet.column_dimensions['A'].width = FONT_SIZE * 2.5 sheet.row_dimensions[1].height = FONT_SIZE * 2 sheet.freeze_panes = 'B2' # apply the font to all the cells in the seet for row in sheet.iter_rows(): for cell in row: cell.font = font # style template for the header row header = NamedStyle(name="header") header.font = Font(size=FONT_SIZE, bold=True) header.border = Border(bottom=Side(border_style="double")) header.alignment = Alignment(horizontal="center", vertical="center") for cell in sheet[1]: cell.style = header workbook.save(filename=OUTPUT_FILENAME) print(f'The data was saved successfully to {OUTPUT_FILENAME}')
def patologistology_buh_data(ws1, data): """ res - результат выборки SQL порядок возврата: napr, date_confirm, time_confirm, create_date_napr, create_time_napr, doc_fio, coast, discount, how_many, ((coast + (coast/100 * discount)) * how_many)::NUMERIC(10,2) AS sum_money, ist_f, time_confirmation, num_card, ind_family, ind_name, patronymic, birthday, date_born, to_char(EXTRACT(YEAR from age(time_confirmation, date_born)), '999') as ind_age :return: """ style_border_res = NamedStyle(name="style_border_res") bd = Side(style='thin', color="000000") style_border_res.border = Border(left=bd, top=bd, right=bd, bottom=bd) style_border_res.font = Font(bold=False, size=11) style_border_res.alignment = Alignment(wrap_text=True, horizontal='center', vertical='center') r = 4 for res in data: r += 1 ws1.cell(row=r, column=1).value = res["hospital"] ws1.cell(row=r, column=2).value = res["fio_patient"] ws1.cell(row=r, column=3).value = res["born_patient"] ws1.cell(row=r, column=4).value = res["polis"] ws1.cell(row=r, column=5).value = res["snils"] ws1.cell(row=r, column=6).value = res["visit_date"] try: mcb10 = json.loads(res["mcb10_code"]) result_mcb10 = mcb10.get("code", "") except: result_mcb10 = "" ws1.cell(row=r, column=7).value = result_mcb10 ws1.cell(row=r, column=8).value = res["fin_source"] ws1.cell(row=r, column=9).value = res["price_category"] ws1.cell(row=r, column=10).value = res["purpose"] try: service = json.loads(res["service_code"]) service_code = service.get('code', '') service_title = service.get('title', '') except: service_code = "" service_title = "" ws1.cell(row=r, column=11).value = f"{service_code} - {service_title}" ws1.cell(row=r, column=12).value = res["direction"] rows = ws1[f'A{r}:L{r}'] for row in rows: for cell in row: cell.style = style_border_res return ws1
def highlight_style(): normal = NamedStyle("highlight") normal.font = Font(bold=True, size=20) # style = CellStyle() normal.border = Border(Side(style='thin', color="000000"), Side(style='thin', color="000000"), Side(style='thin', color="000000"), Side(style='thin', color="000000")) normal.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) return normal
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 __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 list_files_v2(startpath): contents_file = os.path.join(startpath, "content.xlsx") if os.path.exists(contents_file): os.remove(contents_file) wb = openpyxl.Workbook() ws = wb.active ws.page_setup.fitToWidth = 1 none_border = Side(border_style=None, color="000000") thin_border = Side(border_style="thin", color="000000") #目录样式 dir_style = NamedStyle(name="dir_style") dir_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) #dir_style.fill = GradientFill(stop=("ecF2ec", "ecF2ec")) dir_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border) #文件样式 file_style = NamedStyle(name="file_style") file_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) #file_style.fill = GradientFill(stop=("e9edf0", "e9edf0")) file_style.border = Border(top=thin_border, left=thin_border, right=none_border, bottom=thin_border) #计算出文件需要显示在哪一列 file_colum = 1 for root, dirs, files in os.walk(startpath): for f in files: if is_vaild_txt_filename(f): level = root.replace(startpath, '').count(os.sep) + 1 file_colum = max(level, file_colum) break for f in files: if is_vaild_html_filename(f): level = root.replace(startpath, '').count(os.sep) + 1 file_colum = max(level, file_colum) dirs[:] = [] break #开始处理 row = 3 for root, dirs, files in os.walk(startpath): #去掉隐藏目录 for i in range(len(dirs)-1, -1, -1): if not is_vaild_dir(dirs[i]): dirs.pop(i) #处理单独的文档文件,txt #避免空目录 have_file = False for f in files: if is_vaild_txt_filename(f): have_file = True break if not have_file: for d in dirs: if is_have_vaildfile(os.path.join(root, d)): have_file = True break if have_file: relative_filename = root.replace(startpath, '') level = relative_filename.count(os.sep) root_basename = os.path.basename(root) dir_indent = "| " * (level-1) + "|-- " file_indent = "| " * level + "|-- " if not level: print('.') else: print('{}{}'.format(dir_indent, root_basename)) file_dirs = relative_filename.split(os.sep) for f in files: if is_vaild_txt_filename(f): print('{}{}'.format(file_indent, f)) #填写目录 file_link = startpath for di in range(1, len(file_dirs)): cell = ws.cell(row=row, column=di) cell.value = file_dirs[di] file_link = os.path.join(file_link, file_dirs[di]) cell.hyperlink = file_link cell = ws.cell(row=row, column=file_colum) cell.value = f cell.style = file_style ref = get_column_letter(file_colum) + str(row) cell.hyperlink = Hyperlink(ref = ref, tooltip=os.path.join(root, f), display=f) cell.hyperlink.target = file_link row += 1 #处理一个网站集合 #该目录下拥有一个html文件,则表示这是一个统一网站集合 for f in files: if is_vaild_html_filename(f): dirs[:] = [] relative_filename = root.replace(startpath, '') file_dirs = relative_filename.split(os.sep) file_link = startpath for di in range(1, len(file_dirs)): cell = ws.cell(row=row, column=di) cell.value = file_dirs[di] file_link = os.path.join(file_link, file_dirs[di]) cell.hyperlink = file_link cell = ws.cell(row=row, column=file_colum) cell.value = f cell.style = file_style ref = get_column_letter(file_colum) + str(row) cell.hyperlink = Hyperlink(ref = ref, tooltip=os.path.join(root, f), display=f) cell.hyperlink.target = os.path.join(root, f) row += 1 break #excel 修饰 print('{} {}'.format(ws.max_column, ws.max_row)) #标题日期 ws.cell(row=1, column=1).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column) title_style = NamedStyle(name="title_style") title_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) title_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border) for c in range(1, ws.max_column+1): ws.cell(row=1, column=c).style = title_style #设置标题 for c in range(1, ws.max_column): cell = ws.cell(row=2, column=c) cell.value = str(c) + "级目录" cell = ws.cell(row=2, column=ws.max_column) cell.value = "文档名称" #设置内容的样式 for c in range(1, ws.max_column + 1): row = 3 width = 0 for r in range(1, ws.max_row + 1): cell = ws.cell(row=r, column=c) if cell.value: w = len(str(cell.value)) * 1.1 width = max(width, w) cell.style = dir_style #调整单元格大小 ws.column_dimensions[get_column_letter(c)].width = width wb.save(contents_file)
def list_files(startpath): contents_file = os.path.join(startpath, "content.xlsx") if os.path.exists(contents_file): os.remove(contents_file) wb = openpyxl.Workbook() ws = wb.active ws.page_setup.fitToWidth = 1 none_border = Side(border_style=None, color="000000") thin_border = Side(border_style="thin", color="000000") #目录样式 dir_style = NamedStyle(name="dir_style") dir_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) #dir_style.fill = GradientFill(stop=("ecF2ec", "ecF2ec")) dir_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border) #文件样式 file_style = NamedStyle(name="file_style") file_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) #file_style.fill = GradientFill(stop=("e9edf0", "e9edf0")) file_style.border = Border(top=thin_border, left=thin_border, right=none_border, bottom=thin_border) row = 2 for root, dirs, files in os.walk(startpath): level = root.replace(startpath, '').count(os.sep) root_basename = os.path.basename(root) dir_indent = "| " * (level-1) + "|-- " file_indent = "| " * level + "|-- " for i in range(len(dirs)-1, -1, -1): if not is_vaild_dir(dirs[i]): dirs.pop(i) #避免空目录 have_file = False for f in files: if is_vaild_txt_filename(f): have_file = True break if have_file: if not level: print('.') else: print('{}{}'.format(dir_indent, root_basename)) cell = ws.cell(row=row, column=level) cell.value = root_basename cell.style = dir_style cell.hyperlink = root for f in files: if is_vaild_txt_filename(f): print('{}{}'.format(file_indent, f)) cell = ws.cell(row=row, column=level+1) cell.value = f cell.style = file_style cell.hyperlink = root row += 1 #excel 修饰 #合并单元格 print('{} {}'.format(ws.max_column, ws.max_row)) #标题日期 ws.cell(row=1, column=1).value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column) title_style = NamedStyle(name="title_style") title_style.alignment = Alignment(horizontal='left', vertical='center', wrap_text=False) title_style.border = Border(top=thin_border, left=thin_border, right=thin_border, bottom=thin_border) for c in range(1, ws.max_column+1): ws.cell(row=1, column=c).style = title_style for c in range(1, ws.max_column + 1): row = 2 width = 0 for r in range(2, ws.max_row + 1): cell = ws.cell(row=r, column=c) if cell.value: if c < (ws.max_column - 1): if r -1 > row: ws.merge_cells(start_row=row, start_column=c, end_row=r-1, end_column=c) for _r in range(row, r): ws.cell(row=_r, column=c).style = dir_style row = r w = len(str(cell.value)) * 1.1 width = max(width, w) #调整单元格大小 ws.column_dimensions[get_column_letter(c)].width = width wb.save(contents_file)
# ws3 = wb.create_sheet(title="Data") # # for row in range(10, 20): # # for col in range(27, 54): # # _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) # print(ws2['AA10'].value) # wb.save(filename = '3sheet.xlsx') # wb = load_workbook(filename = '3sheet.xlsx') # sheet_ranges = wb['first'] # print(sheet_ranges['B1'].value) # #Edit Page Setup wb=Workbook() ws=wb.active ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID ws.page_setup.fitToHeight = 0 ws.page_setup.fitToWidth = 1 #Creating a Named Style highlight = NamedStyle(name="highlight") highlight.font = Font(bold=True, size=20) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) #use st yle wb.add_named_style(highlight) ws['A1'].style = highlight ws['D5'].style = 'highlight' wb.save(filename = 'stylename1.xlsx')
def export_to_excel(self, bag, export_path=None): def as_text(value): if value is None: return "" return str(value) if len(bag) == 0: return None try: # create workbook wb = Workbook() # create header style highlight = NamedStyle(name="highlight") highlight.font = Font(name='DejaVu Sans', bold=True, size=10) bd = Side(style='thick', color="000000") highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) wb.add_named_style(highlight) # create data style normal = NamedStyle(name="normal") normal.font = Font(name='DejaVu Sans', bold=False, size=10) wb.add_named_style(normal) # Get worksheet ws = wb.active # build header header = [] for field in HEADER_FIELDS: header.append(str(HEADER[field])) ws.append(header) # Set data data = [] for sid in bag: row = [] metadata = self.srvdtb.get_sapnote_metadata(sid) for field in HEADER_FIELDS: if field == 'collections': cols = ', '.join([self.srvclt.get_name_by_cid(col) for col in metadata[field]]) row.append(cols) elif field == 'releasedon': excel_date = self.srvutl.get_excel_date(metadata[field]) row.append(excel_date) else: row.append(str(metadata[field])) ws.append(row) data.append(row) # assign style to header for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: cell = col + '1' ws[cell].style = 'highlight' # assign style to data for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J']: for i in range(len(bag)): cell = col + '%d' % (i + 2) ws[cell].style = 'normal' # adjust columns width try: for column_cells in ws.columns: length = max(len(as_text(cell.value)) for cell in column_cells) ws.column_dimensions[COL[column_cells[0].column]].width = length except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) self.log.error("This piece of code isn't working on Windows...") # header autofilter and sorting ws.auto_filter.ref = "A1:J1" ws.auto_filter.add_sort_condition("I2:I%d" % len(bag)) # save to export path wb.save(export_path) return True except Exception as error: self.log.error(error) self.log.error(self.get_traceback()) return False
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