def matrix_to_excel(path, core_matrix, names): from openpyxl import styles from openpyxl.utils import get_column_letter names = [name for name in names if name in core_matrix] writer = pd.ExcelWriter(path, engine="openpyxl") core_matrix.to_excel(writer) ws = writer.sheets["Sheet1"] rotated = styles.Alignment(textRotation=45, horizontal="center", vertical="bottom") bottom = styles.Alignment(vertical="bottom") for row in ws["A1:ZZ1"]: for cell in row: if cell.value in names: cell.alignment = rotated ws.column_dimensions[get_column_letter(cell.column)].width = 2.6 elif cell.value == "title": cell.alignment = bottom ws.column_dimensions[get_column_letter(cell.column)].width = 40 else: cell.alignment = bottom for row in ws["A1:A1000"]: for cell in row: if not cell.value: continue cell.hyperlink = "https://github.com/" + "/pull/".join( cell.value.split("#") ) cell.alignment = styles.Alignment(horizontal="right") cell.font = styles.Font(color="0000ff") ws.freeze_panes = "C2" writer.close()
def write_headers(sheet): print(sheet.title) rd = sheet.row_dimensions print(rd[0].height) sheet.merge_cells("A1:G1", start_row=1, end_row=1, start_column=1, end_column=7) title = sheet.cell(row=1, column=1) title.font = Styles.Font(name=FAMILY1, size=25, bold=False) title.alignment = Styles.Alignment(horizontal="center", vertical="center") title.value = "长山收费站{}年{}收文登记薄".format( datetime.datetime.fromtimestamp(time.time()).year, sheet.title) sheet.merge_cells("A2:G2", start_row=2, end_row=2, start_column=1, end_column=7) for i, item in enumerate( ["序号", "收文时间", "文件名称/标题", "发件单位/部门", "文件分类", "资料类型", "备注"]): cell = sheet.cell(row=3, column=i + 1) cell.alignment = Styles.Alignment(horizontal="center", vertical="center") cell.border = thin_border cell.value = item
def process_article(worksheet, Article, EntryIndex): logging.debug('found Article') try: Cell = 'D' + str(EntryIndex) worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=False) JournalString = Article.find('Journal').find('Title').text JournalString = JournalString + ", Vol {}".format( Article.find('Journal').find('JournalIssue').find('Volume').text) JournalString = JournalString + ", {}".format( Article.find('Journal').find('JournalIssue').find('PubDate').find( 'Year').text) JournalString = JournalString + ", {}".format( Article.find('Journal').find('JournalIssue').find('PubDate').find( 'Month').text) JournalString = JournalString + " {}".format( Article.find('Journal').find('JournalIssue').find('PubDate').find( 'Day').text) except AttributeError: logging.debug('did not find Journal.Title or some subfields') worksheet[Cell] = JournalString try: Cell = 'A' + str(EntryIndex) worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=False) worksheet[Cell] = Article.find('ArticleTitle').text except AttributeError: worksheet[Cell] = 'NA' logging.debug('did not find ArticleTitle') try: Cell = 'B' + str(EntryIndex) worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=False) worksheet[Cell] = "{}, {}".format( Article.find('AuthorList').find('Author').find('LastName').text, Article.find('AuthorList').find('Author').find('Initials').text) except AttributeError: worksheet[Cell] = 'NA' logging.debug('did not find ArticleTitle') try: Cell = 'C' + str(EntryIndex) worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=False) worksheet[Cell] = Article.find('Abstract').find('AbstractText').text except AttributeError: worksheet[Cell] = 'NA' logging.debug('did not find AbstractText')
def write_items(sheet, files, row_start): font12 = Styles.Font( name=FAMILY2, size=12, ) font14 = Styles.Font(name=FAMILY2, size=14) align1 = Styles.Alignment(horizontal="center", vertical="center") align2 = Styles.Alignment(horizontal="left", vertical="center") for f in files: for col in range(1, 7): sheet.cell(row=row_start, column=col).border = thin_border cell = sheet.cell(row=row_start, column=1) cell.font = font12 cell.alignment = align2 cell.value = row_start - 3 cell = sheet.cell(row=row_start, column=2) cell.font = font14 cell.alignment = align2 cell.value = f[1] cell = sheet.cell(row=row_start, column=3) cell.font = font14 cell.alignment = align2 cell.value = f[0] row_start += 1
def process_pmid(worksheet, PMIDField, EntryIndex): Cell = 'E' + str(EntryIndex) worksheet[Cell].alignment = PYXL_S.Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=False) worksheet[Cell] = PMIDField.text
def test_to_excel_styleconverter(ext): from openpyxl import styles hstyle = { "font": {"color": "00FF0000", "bold": True}, "borders": {"top": "thin", "right": "thin", "bottom": "thin", "left": "thin"}, "alignment": {"horizontal": "center", "vertical": "top"}, "fill": {"patternType": "solid", "fgColor": {"rgb": "006666FF", "tint": 0.3}}, "number_format": {"format_code": "0.00"}, "protection": {"locked": True, "hidden": False}, } font_color = styles.Color("00FF0000") font = styles.Font(bold=True, color=font_color) side = styles.Side(style=styles.borders.BORDER_THIN) border = styles.Border(top=side, right=side, bottom=side, left=side) alignment = styles.Alignment(horizontal="center", vertical="top") fill_color = styles.Color(rgb="006666FF", tint=0.3) fill = styles.PatternFill(patternType="solid", fgColor=fill_color) number_format = "0.00" protection = styles.Protection(locked=True, hidden=False) kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle) assert kw["font"] == font assert kw["border"] == border assert kw["alignment"] == alignment assert kw["fill"] == fill assert kw["number_format"] == number_format assert kw["protection"] == protection
def format_spreadsheet(ws): # freeze top row ws.freeze_panes = ws['A2'] # set top row to bold # we can't address the whole row at once, gotta do cells. # not going to bother with a content checker top_row = [ws['A1'], ws['B1'], ws['C1'], ws['D1'], ws['E1']] for r in top_row: r.font = styles.Font(bold=True) dims = {} for row in ws.rows: for cell in row: # set length of cells to length of content, with 100 char limit. if cell.value: if len(cell.value) < 100: cell_length = len(cell.value) + 1 else: cell_length = 100 dims[cell.column] = max((dims.get(cell.column, 0), cell_length)) # Set alignment to wrap text, this preserves newlines. cell.alignment = styles.Alignment(wrap_text=True) for col, value in dims.items(): ws.column_dimensions[col].width = value return ws
def align_center(): for i in range(_ws.max_column+1): if(i > 0): for j in range( _ws.max_row+1): if(j > 0): cell = _ws.cell(row=j,column=i) cell.alignment = __oxl_styles.Alignment(horizontal='center', vertical='center')
def stylesAlignment(self): ''' 设置字体样式,默认垂直 :return: ''' self.Alignment = styles.Alignment(horizontal='center') return self.Alignment
def test_to_excel_styleconverter(ext): from openpyxl import styles hstyle = { "font": { "color": '00FF0000', "bold": True, }, "borders": { "top": "thin", "right": "thin", "bottom": "thin", "left": "thin", }, "alignment": { "horizontal": "center", "vertical": "top", }, "fill": { "patternType": 'solid', 'fgColor': { 'rgb': '006666FF', 'tint': 0.3, }, }, "number_format": { "format_code": "0.00" }, "protection": { "locked": True, "hidden": False, }, } font_color = styles.Color('00FF0000') font = styles.Font(bold=True, color=font_color) side = styles.Side(style=styles.borders.BORDER_THIN) border = styles.Border(top=side, right=side, bottom=side, left=side) alignment = styles.Alignment(horizontal='center', vertical='top') fill_color = styles.Color(rgb='006666FF', tint=0.3) fill = styles.PatternFill(patternType='solid', fgColor=fill_color) number_format = '0.00' protection = styles.Protection(locked=True, hidden=False) kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle) assert kw['font'] == font assert kw['border'] == border assert kw['alignment'] == alignment assert kw['fill'] == fill assert kw['number_format'] == number_format assert kw['protection'] == protection
def export_excel(kinders, header): wb = Workbook() ws = wb.active sheet = wb['Sheet'] widths = [13, 15, 35, 16, 16, 9, 9, 9, 18] sheet.row_dimensions[1].height = 30 for i, h in enumerate(header): sheet.column_dimensions[chr(65 + i)].width = widths[i] cell = ws.cell(row=1, column=i + 1) cell.value = h cell.alignment = styles.Alignment(horizontal='center', vertical='center') for row, data in enumerate(kinders, start=2): for col, v in enumerate(data.values(), start=1): cell = ws.cell(row=row, column=col) cell.value = v cell.alignment = styles.Alignment(horizontal='center') wb.save('result.xlsx')
def make_workbook(self, gst, igst): len_gst = len(gst) len_igst = len(igst) wb = Workbook() x = datetime.datetime.strftime(self.start, '%d-%m-%Y') y = datetime.datetime.strftime(self.end, '%d-%m-%Y') c = Utils.current_time() c = datetime.datetime.strftime(c, '%d-%m-%Y') dest_filename = c + '_' + x + '_' + y + '_' + str(len_igst + len_gst) + '.xlsx' ws1 = wb.active ws1.title = "GST" for row in range(len_gst): ws1.append(gst[row]) ws1['M' + str(row + 1)].alignment = styles.Alignment(wrap_text=True) wb["GST"].freeze_panes = "A2" ws2 = wb.create_sheet('IGST') for row in range(len_igst): ws2.append(igst[row]) ws2['L' + str(row + 1)].alignment = styles.Alignment(wrap_text=True) wb["IGST"].freeze_panes = "A2" wb.save("C:\\Users\\Dell\\Desktop\\" + dest_filename) #Database.initialize() #start = datetime.datetime.strptime('27-01-2019','%d-%m-%Y') #end = datetime.datetime.strptime('27-08-2019','%d-%m-%Y') #x = Excel_export(start,end).make_sheet() #print(x.gst) #print(x.igst)
def _format_report_title(ws_report): """ Форматирование отчета :param ws_report: :return: """ # форматирование заголовков for char in "ABCDEFGH": ws_report.column_dimensions[char].width = 30 ws_report[char + '1'].font = style.Font(bold=True, size=13) ws_report[char + '1'].alignment = style.Alignment(wrap_text=True) # форматирование столбца с D for index in range(2, ws_report.max_row + 1): _cell = ws_report[f'D{index}'] _cell.number_format = '[h]:mm:ss'
def diff_export_excel(request): sel_date_start=request.GET['date_start'] sel_date_end=request.GET["date_end"] diff = datadiff.objects\ .exclude(amount=0)\ .exclude(id_shop__sName__in=['元隆利嘉生活馆','满洲里友谊商厦'])\ .filter(id_shop__shopType__in=["D", "C"], date__range=(sel_date_start,sel_date_end))\ .order_by("id_shop", "date") # 创建一个excel表格对象 F_styleB=styles.Font(color=styles.colors.BLACK,bold=True) F_styleR=styles.Font(color=styles.colors.RED,bold=True,italic=True) F_styleCenter=styles.Alignment(horizontal='center',vertical='center',indent=1,readingOrder=2,text_rotation=0) diff_excel_book = openpyxl.Workbook() diff_excel_book.remove_sheet(diff_excel_book.get_sheet_by_name(u'Sheet')) book_sheet = diff_excel_book.create_sheet(u"销售差异") book_sheet.append([u"店铺",u'销售经理', u"日期", u"系统金额", u"上报金额", u"差异金额",u'正确金额', u"差异原因", u"备注"]) for i in range(1,8): book_sheet.cell(row=1,column=i).font=F_styleB row=2 for diff_y in diff: book_sheet.cell(row=row, column=1).value = diff_y.id_shop.sysName book_sheet.cell(row=row, column=1).alignment=F_styleCenter book_sheet.cell(row=row, column=2).value = diff_y.id_shop.managerId.name book_sheet.cell(row=row, column=2).alignment = F_styleCenter book_sheet.cell(row=row, column=3).value = diff_y.date book_sheet.cell(row=row, column=3).alignment = F_styleCenter book_sheet.cell(row=row, column=4).value = diff_y.sys_amount book_sheet.cell(row=row, column=4).alignment = F_styleCenter book_sheet.cell(row=row, column=5).value = diff_y.shop_amount book_sheet.cell(row=row, column=5).alignment = F_styleCenter book_sheet.cell(row=row, column=6).value = diff_y.amount book_sheet.cell(row=row, column=6).alignment = F_styleCenter book_sheet.cell(row=row, column=7).value = diff_y.true_amount if diff_y.true_amount!=diff_y.shop_amount: book_sheet.cell(row=row, column=7).font = F_styleR book_sheet.cell(row=row, column=7).alignment = F_styleCenter book_sheet.cell(row=row, column=8).value = diff_y.diff book_sheet.cell(row=row, column=8).alignment=F_styleCenter book_sheet.cell(row=row, column=9).value = diff_y.remark book_sheet.cell(row=row, column=9).alignment = F_styleCenter row+=1 month_start = str(sel_date_start).replace('-','') month_end = str(sel_date_end).replace('-','') response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=销售差异%s-%%s.xlsx'%month_start %month_end diff_excel_book.save(response) return response
def write_price(self, change_root, wholesale_root): #book = xlwt.Workbook() #sheet = book.add_sheet('Sheet', cell_overwrite_ok=True) #rb = xlrd.open_workbook('Copy of Hook Full Price List interns.xlsx') #r_sheet = rb.sheet_by_index(4) #book = copy(rb) #sheet = book.get_sheet(4) book = openpyxl.load_workbook('Hook Full Price List interns.xlsx') #r_sheet = rb['A-Z INTERNAL'] #book = copy(rb) sheet = book['A-Z INTERNAL'] final_list = [] self.to_database(change_root, final_list) title = [] title.append(final_list.pop(0)) temp = format().quicksort(final_list, 1) final_list = title + temp colnum = 9 for i in range(len(final_list)): provider = final_list.pop(0) provider.pop(5) provider.pop(5) for j in range(colnum): if i == 0: #st = xlwt.easyxf('align: horiz center') row = sheet.row_dimensions[1] row.alignment = styles.Alignment(horizontal='center') #for row in rows: #cell = row[i][j] #cell.alignment = Alignment(horizontal='center') sheet.cell(row=i + 1, column=j + 1).value = provider[j + 1] else: sheet.cell(row=i + 1, column=j + 1).value = provider[j + 1] sheet.freeze_panes = sheet['A2'] book.save('Pricing Sheet.xlsx') log_file().info("Source Compiler.log", 'Pricing sheet has been updated.') print('Pricing sheet has been updated.')
def __align(sheet: opx.worksheet.worksheet.Worksheet, row: int, start_col: int, end_col: int, horizontal_align: str = "center") -> None: """ Align row from start_col to end_col cells to some horizontal_align :param sheet: Sheet where a row will be aligned :param row: Row to be aligned accessed with cell.row :param start_col: Starting column :param end_col: Ending column :param horizontal_align: How the row will be aligned :return: None """ for row_cells in sheet.iter_cols(start_col, end_col, row, row): for cell in row_cells: cell.alignment = opx_style.Alignment(horizontal_align)
def reformatExcel(readDict, destFile): wb = opxl.Workbook() ws = wb.active HeadFt = opst.Font(bold=True) HeadAlign = opst.Alignment(horizontal='center', vertical='center') flatFt = opst.Font(color='FF00FF00') slope_epsilon= 0.0005 #Prepare header rows ws['A1'].value = 'Temperature' ws['A1'].font = HeadFt ws['A1'].alignment = HeadAlign ws.column_dimensions['A'].width = 20 columnIterator = 2 for wellpos in readDict.keys(): # fix this ws[oput.get_column_letter(columnIterator)+'1'].value = wellpos ws[oput.get_column_letter(columnIterator) + '1'].font = HeadFt ws[oput.get_column_letter(columnIterator) + '1'].alignment = HeadAlign columnIterator = columnIterator+1 #Preparing the temperature column rowIterator = 2 firstWell = list(readDict.keys())[0] for temp in readDict[firstWell].keys(): ws['A'+str(rowIterator)].value = temp ws['A' + str(rowIterator)].alignment = HeadAlign rowIterator = rowIterator+1 columnIterator=2 for well in readDict.keys(): rowIterator=2 tempList= list(readDict[well].keys()) for temp in tempList: ws[oput.get_column_letter(columnIterator)+str(rowIterator)].value = readDict[well][temp] rowIterator = rowIterator+1 columnIterator = columnIterator+1 wb.save(filename=destFile)
from rekall.ui import text import six if six.PY3: long = int # pylint: disable=unexpected-keyword-arg,no-value-for-parameter # pylint: disable=redefined-outer-name HEADER_STYLE = styles.Style(font=styles.Font(bold=True)) SECTION_STYLE = styles.Style( fill=styles.PatternFill( fill_type=fills.FILL_SOLID, start_color=styles.Color(colors.RED))) FORMAT_STYLE = styles.Style( alignment=styles.Alignment(vertical="top", wrap_text=False)) class XLSObjectRenderer(renderer.ObjectRenderer): """By default the XLS renderer delegates to the text renderer.""" renders_type = "object" renderers = ["XLSRenderer"] STYLE = None def _GetDelegateObjectRenderer(self, item): return self.ForTarget(item, "TextRenderer")( session=self.session, renderer=self.renderer.delegate_text_renderer) def RenderHeader(self, worksheet, column): cell = worksheet.cell(
bottom=xl_styles.Side(border_style='thin', color='FF000000'), # diagonal=xl_styles.Side(border_style=None,color='FF000000'), # diagonal_direction=0,outline=xl_styles.Side(border_style=None,color='FF000000'), # vertical=xl_styles.Side(border_style=None,color='FF000000'), # horizontal=xl_styles.Side(border_style=None,color='FF000000') ) border_tbl = dict(thin=thin_border, ) ################################################################# # Align Types # # # # If you add an align type, remember to add it to align_tbl # ################################################################# wrap_center_alignment = xl_styles.Alignment(horizontal='center', vertical='top', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) wrap_right_alignment = xl_styles.Alignment(horizontal='right', vertical='top', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) wrap_alignment = xl_styles.Alignment(horizontal='general', vertical='top', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) wrap_vert_center_alignment = xl_styles.Alignment(horizontal='center',
workbook.sheets = dict((ws.title, ws) for ws in book.worksheets) sheet_name = date.today().strftime('prediction on %d %b %Y') result.to_excel( workbook, sheet_name=sheet_name, index=False) # don't show the row numbers sheet = workbook.sheets[sheet_name] # Set column widths. You have to change this if you add columns col_widths = zip('ABCDEFGHIJ', (9, 16, 12, 20, 12, 20, 12, 16, 14, 14)) for col, width in col_widths: sheet.column_dimensions[col].width = width # Bold and center the headers cell = col + "1" try: sheet[cell].font = styles.Font(bold=True) sheet[cell].alignment = styles.Alignment(horizontal = 'center') except TypeError: sheet[cell].style.font = styles.fonts.Font() sheet[cell].style.font_style = styles.fonts.Font.bold = True sheet[cell].style.alignment = styles.alignment.Alignment() sheet[cell].style.alignment_style = styles.alignment.Alignment.HORIZONTAL_CENTER # Make a horizontal boundary between each week try: separator = styles.borders.Border(bottom=styles.borders.Side(style='thin')) except AttributeError: separator = styles.borders.Border() separator.border_style = styles.borders.Border.BORDER_THIN for row_offset in result[['Week','Home Team']].groupby('Week').count().cumsum().values: for col_offset in range(result.shape[1]): try: sheet.cell( row=row_offset[0]+1, column=col_offset+1
def __init__(self, filename=None, model_dir=None, license_summary=False, params=None): self.wb = None self.filename = filename self.params = params if self.params is None: self.params = {} if filename is not None: self.wb = openpyxl.load_workbook(filename=filename) else: self.wb = openpyxl.Workbook() self.ws_models = self.wb.active self.ws_models.title = 'Index' thin = styles.Side( border_style=self.params.get('side_border', 'thin'), color=self.params.get('side_color', '999999')) for i in range(1, len(group_styles) + 1): key = 'suns_group_%s' % i name = 'suns_group_entry_%s' % i style = styles.NamedStyle(name=name) color = group_styles[key]['group_color'] # self.params.get('group_color', color) style.fill = styles.PatternFill('solid', fgColor=color) style.font = styles.Font() style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) style.alignment = styles.Alignment(horizontal='center', wrapText=True) self.wb.add_named_style(style) name = 'suns_group_text_%s' % i style = styles.NamedStyle(name=name) style.fill = styles.PatternFill('solid', fgColor=color) style.font = styles.Font() style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) style.alignment = styles.Alignment(horizontal='left', wrapText=True) self.wb.add_named_style(style) name = 'suns_point_entry_%s' % i style = styles.NamedStyle(name=name) color = group_styles[key]['point_color'] # self.params.get('group_color', color) style.fill = styles.PatternFill('solid', fgColor=color) style.font = styles.Font() style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) style.alignment = styles.Alignment(horizontal='center', wrapText=True) self.wb.add_named_style(style) name = 'suns_point_text_%s' % i style = styles.NamedStyle(name=name) style.fill = styles.PatternFill('solid', fgColor=color) style.font = styles.Font() style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) style.alignment = styles.Alignment(horizontal='left', wrapText=True) self.wb.add_named_style(style) if 'suns_hdr' not in self.wb.named_styles: hdr_style = styles.NamedStyle(name='suns_hdr') hdr_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('hdr_color', 'dddddd')) hdr_style.font = styles.Font(bold=True) hdr_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) hdr_style.alignment = styles.Alignment(horizontal='center', wrapText=True) self.wb.add_named_style(hdr_style) if 'suns_group_entry' not in self.wb.named_styles: model_entry_style = styles.NamedStyle( name='suns_group_entry') model_entry_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('group_color', 'fff9e5')) model_entry_style.font = styles.Font() model_entry_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) model_entry_style.alignment = styles.Alignment( horizontal='center', wrapText=True) self.wb.add_named_style(model_entry_style) if 'suns_group_text' not in self.wb.named_styles: model_text_style = styles.NamedStyle( name='suns_group_text') model_text_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('group_color', 'fff9e5')) model_text_style.font = styles.Font() model_text_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) model_text_style.alignment = styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(model_text_style) if 'suns_point_entry' not in self.wb.named_styles: fixed_entry_style = styles.NamedStyle( name='suns_point_entry') fixed_entry_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('point_color', 'e6f2ff')) fixed_entry_style.font = styles.Font() fixed_entry_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) fixed_entry_style.alignment = styles.Alignment( horizontal='center', wrapText=True) self.wb.add_named_style(fixed_entry_style) if 'suns_point_text' not in self.wb.named_styles: fixed_text_style = styles.NamedStyle( name='suns_point_text') fixed_text_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('point_color', 'e6f2ff')) fixed_text_style.font = styles.Font() fixed_text_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) fixed_text_style.alignment = styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(fixed_text_style) if 'suns_point_variable_entry' not in self.wb.named_styles: fixed_entry_style = styles.NamedStyle( name='suns_point_variable_entry') fixed_entry_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('point_variable_color', 'ecf9ec')) fixed_entry_style.font = styles.Font() fixed_entry_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) fixed_entry_style.alignment = styles.Alignment( horizontal='center', wrapText=True) self.wb.add_named_style(fixed_entry_style) if 'suns_point_variable_text' not in self.wb.named_styles: fixed_text_style = styles.NamedStyle( name='suns_point_variable_text') fixed_text_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('point_variable_color', 'ecf9ec')) fixed_text_style.font = styles.Font() fixed_text_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) fixed_text_style.alignment = styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(fixed_text_style) if 'suns_symbol_entry' not in self.wb.named_styles: repeating_entry_style = styles.NamedStyle( name='suns_symbol_entry') repeating_entry_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('symbol_color', 'fafafa')) repeating_entry_style.font = styles.Font() repeating_entry_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) repeating_entry_style.alignment = styles.Alignment( horizontal='center', wrapText=True) self.wb.add_named_style(repeating_entry_style) if 'suns_symbol_text' not in self.wb.named_styles: repeating_text_style = styles.NamedStyle( name='suns_symbol_text') repeating_text_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('symbol_color', 'fafafa')) repeating_text_style.font = styles.Font() repeating_text_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) repeating_text_style.alignment = styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(repeating_text_style) if 'suns_comment' not in self.wb.named_styles: symbol_text_style = styles.NamedStyle(name='suns_comment') symbol_text_style.fill = styles.PatternFill( 'solid', fgColor=self.params.get('comment_color', 'dddddd')) # fgColor=self.params.get('symbol_color', 'fffcd9')) symbol_text_style.font = styles.Font() symbol_text_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) symbol_text_style.alignment = styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(symbol_text_style) if 'suns_entry' not in self.wb.named_styles: entry_style = styles.NamedStyle(name='suns_entry') entry_style.fill = styles.PatternFill('solid', fgColor='ffffff') entry_style.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) entry_style.alignment = styles.Alignment( horizontal='center', wrapText=True) self.wb.add_named_style(entry_style) if 'suns_text' not in self.wb.named_styles: text_style = styles.NamedStyle(name='suns_text') text_style.font = styles.Font() text_style.alignment = styles.Alignment(horizontal='left', wrapText=True) self.wb.add_named_style(text_style) if 'suns_hyper' not in self.wb.named_styles: hyper_style = openpyxl.styles.NamedStyle(name='suns_hyper') hyper_style.font = openpyxl.styles.Font(color='0000ee', underline='single') hyper_style.alignment = openpyxl.styles.Alignment( horizontal='left', wrapText=True) self.wb.add_named_style(hyper_style) for i in range(len(models_hdr)): self.set_cell(self.ws_models, 1, i + 1, models_hdr[i][0], 'suns_hdr') if models_hdr[i][1]: self.ws_models.column_dimensions[chr( 65 + i)].width = models_hdr[i][1]
return f"{month.strftime('%B_%Y')}_Inventory" def MONTHTITLE(month): return f"{month.strftime('%B %Y')}" #################################################################### """----------------------------------------------------------------- STYLE SETUP -----------------------------------------------------------------""" #################################################################### TITLESTYLE = xlstyle.NamedStyle(name='TITLESTYLE') TITLESTYLE.font = xlstyle.Font(bold=True, size=16) TITLESTYLE.alignment = xlstyle.Alignment(horizontal="center") TOTALCELLSTYLE = xlstyle.NamedStyle(name="TOTALCELLSTYLE") TOTALCELLSTYLE.font = xlstyle.Font(bold=True, size=12, color='FFFFFF') ALLTHICKBORDERS = xlstyle.Border( **{ side: xlstyle.Side(border_style='thick', color='000000') for side in ['left', 'right', 'top', 'bottom'] }) TOTALCELLSTYLE.border = ALLTHICKBORDERS TOTALCELLSTYLE.fill = xlstyle.PatternFill(fill_type="solid", start_color='BFBFBF') MONTHTABLESTYLE = xltable.TableStyleInfo(name="TableStyleMedium4", showFirstColumn=False, showLastColumn=False,
vertAlign=None, underline='none', strike=False, color='FF000000') font_b = styles.Font(name='Calibri', size=11, bold=True, color='FF000000') thin = styles.Side(border_style="thin", color="000000") border = styles.Border(outline = thin, top = thin, left=thin, right=thin, bottom=thin, vertical=thin, horizontal=thin) alignment = styles.Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) ws.merge_cells('B1:D2') # show error if insert value into first # row - строка, column - поле ws['C8'] = str(ws['C9'].value) + '!' ws.cell(row=1, column=2, value='huy') b1 = ws['B1'] b1.font = font_b b1.alignment = alignment # заливка
def pathName(self, path): # workbook_name = '0806黄爱枝(1).xlsx' # path = "/Users/liulunyang/Desktop/0806黄爱枝.xlsx" wb = openpyxl.load_workbook(path) # wb = Workbook() ws = wb.active # ws["A1"] = 0 # ws.remove_named_range() # help(ws) # ws.merge_cells('A2:B2') # 需要先取消合并 max_row = ws.max_row print(max_row) ws.unmerge_cells(start_row=max_row, start_column=1, end_row=max_row, end_column=3) ws.unmerge_cells(start_row=max_row, start_column=5, end_row=max_row, end_column=8) ws.unmerge_cells(start_row=max_row, start_column=10, end_row=max_row, end_column=14) ws.unmerge_cells(start_row=max_row - 1, start_column=1, end_row=max_row - 1, end_column=3) ws.unmerge_cells(start_row=max_row - 1, start_column=5, end_row=max_row - 1, end_column=8) ws.unmerge_cells(start_row=max_row - 1, start_column=10, end_row=max_row - 1, end_column=14) ws.unmerge_cells('A1:O1') # 删除列 for i in range(4): ws.delete_cols(1) ws.delete_cols(2) ws.delete_cols(8) ws.delete_cols(8) # 删除第一行 ws.delete_rows(1) ws.delete_rows(max_row - 2) # 改变列宽 ws.column_dimensions['A'].width = 25 ws.column_dimensions['B'].width = 25 ws.column_dimensions['C'].width = 13 ws.column_dimensions['D'].width = 3.8 ws.column_dimensions['E'].width = 8 ws.column_dimensions['F'].width = 11 ws.column_dimensions['G'].width = 11 ''' 这方法对一行不起作用 ws.column_dimensions['A'].font = Font(bold=True,size=15) ws['A1'].font = Font(size = 20) ''' ws.cell(row=max_row - 2, column=1).value = "总计:" # print(ws['D2'].value,len(ws['D2'].value)) ws.row_dimensions[max_row - 2].height = 15 for i in range(max_row - 3): if i == max_row - 4: break ws.cell(row=i + 1, column=1).font = Font(size=12, bold=True) ws.cell(row=i + 1, column=1).alignment = styles.Alignment(vertical='center', horizontal='center') a = ws.cell(row=i + 2, column=1).value print(a, len(a)) d = ws.cell(row=i + 2, column=4).value print(d) if d == 0: d = 1 if len(a) < 14: if d > 1: ws.row_dimensions[i + 2].height = 13 * d else: ws.row_dimensions[i + 2].height = 15 * d else: if d > 2: ws.row_dimensions[i + 2].height = 13 * d else: ws.row_dimensions[i + 2].height = 15 * 2 ## 有问题这里, 改变cell的背景颜色 # ws['A1'].styles=styles.PatternFill(fill_type='solid',fgColor="0d5330") # ws.row_dimensions[1].fill = styles.PatternFill(bgColor=colors.BLACK) ''' 元格样式 font(字体类):字号、字体颜色、下划线等 fill(填充类):颜色等 border(边框类):设置单元格边框 alignment(位置类):对齐方式 number_format(格式类):数据格式 protection(保护类):写保护 ''' fill = styles.PatternFill(fill_type=None, bgColor=colors.WHITE) # dashDot','dashDotDot', 'dashed','dotted', # 'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', # 'mediumDashed', 'slantDashDot', 'thick', 'thin' # 修改边框 border = Border(left=Side(style='hair', color=colors.BLACK), right=Side(style='hair', color=colors.BLACK), top=Side(style="hair", color=colors.BLACK), bottom=Side(style='hair', color=colors.BLACK)) ws['D1'] = '数量' for r in range(max_row - 2): for i in range(7): # ws.cell(row= r + 1, column =1+i).fill = styles.PatternFill(bgColor=colors.BLACK) ws.cell(row=r + 1, column=1 + i).fill = fill ws.cell(row=r + 1, column=1 + i).border = border ws.cell(row=r + 1, column=1).alignment = styles.Alignment(wrap_text=True) # 改变第一行的居中对齐 for i in range(7): ws.cell(row=1, column=i + 1).alignment = styles.Alignment( vertical='center', horizontal='center') ws.cell(row=1, column=1 + i).font = Font(size=12, bold=True, b=1) wb.save(path) # 自适应行高 没有实现 # help(openpyxl.styles) '''
def bloco3_resumo_por_acao(worksheet, acoes, conta_associacao, periodo): global OFFSET thin = styles.Side(border_style="thin", color="000000") linha_inicial = 21 + OFFSET linha_atual = linha_inicial offset_local = 0 total_valores = 0 total_conciliacao = 0 destinacoes = ['C', 'K', 'CK'] totalizador = { SALDO_ANTERIOR: { 'C': 0, 'K': 0, 'CK': 0 }, CREDITO: { 'C': 0, 'K': 0, 'CK': 0 }, DESPESA_REALIZADA: { 'C': 0, 'K': 0, 'CK': 0 }, DESPESA_NAO_REALIZADA: { 'C': 0, 'K': 0, 'CK': 0 }, SALDO_REPROGRAMADO_PROXIMO: { 'C': 0, 'K': 0, 'CK': 0 }, DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS: { 'C': 0, 'K': 0, 'CK': 0 }, SALDO_BANCARIO: { 'C': 0, 'K': 0, 'CK': 0 }, TOTAL_SALDO_BANCARIO: { 'C': 0, 'K': 0, 'CK': 0 }, CREDITO_NAO_DEMONSTRADO: { 'C': 0, 'K': 0, 'CK': 0 }, } logging.info(f'Linha inicial:{linha_inicial}') for linha_acao, acao_associacao in enumerate(acoes): # Movendo as linhas para baixo antes de inserir os dados novos linha_atual = linha_inicial + (linha_acao * 3) offset_local = linha_atual - linha_inicial logging.info( f'LAção:{linha_acao}, LAtual:{linha_atual}, offset:{offset_local}, Ação:{acao_associacao.acao.nome}' ) if offset_local > 0: insert_row(worksheet, LAST_LINE + OFFSET + offset_local, linha_atual - 1) insert_row(worksheet, LAST_LINE + OFFSET + offset_local, linha_atual - 1) insert_row(worksheet, LAST_LINE + OFFSET + offset_local, linha_atual - 1) fechamento_periodo = FechamentoPeriodo.fechamentos_da_acao_no_periodo( acao_associacao=acao_associacao, periodo=periodo, conta_associacao=conta_associacao).first() sub_valores, sub_conciliacao, totalizador = sintese_receita_despesa( worksheet, acao_associacao, conta_associacao, periodo, fechamento_periodo, linha_atual, totalizador) total_valores += sub_valores total_conciliacao += sub_conciliacao for destinacao_idx in range(0, 2): if destinacao_idx == 0: try: worksheet.unmerge_cells( f'B{linha_atual + 1}:B{linha_atual + 3}') except ValueError: # Ignora caso o campo ja esteja desmergeado pass row = list(worksheet.rows)[linha_atual + destinacao_idx] row[0].value = destinacoes[destinacao_idx] if destinacao_idx == 0: worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}') top_left_cell = worksheet[f'B{linha_atual + 1}'] top_left_cell.value = acao_associacao.acao.nome top_left_cell.font = styles.Font(name='Arial', size=10.5, b=True, color="000000") top_left_cell.alignment = styles.Alignment(horizontal="left", vertical="center") # Ajusta o fundo para que a apenas as Colunas E, F, I e L tenham fundo cinza na linha de destinação CK for col in ('E', 'F', 'I', 'L'): col_cell = worksheet[ f'{col}{linha_atual + destinacao_idx + 1}'] col_cell.border = styles.Border(top=thin, left=thin, right=thin, bottom=thin) col_cell.fill = styles.PatternFill( "solid", fgColor="808080" if destinacao_idx == 2 else "FFFFFF") linha_atual += 3 worksheet.merge_cells(f'B{linha_atual + 1}:B{linha_atual + 3}') top_left_cell = worksheet[f'B{linha_atual + 1}'] top_left_cell.value = "TOTAL" top_left_cell.font = styles.Font(name='Arial', size=10.5, b=True, color="000000") top_left_cell.alignment = styles.Alignment(horizontal="left", vertical="center") for idx, destinacao in enumerate(destinacoes): row = list(worksheet.rows)[linha_atual + idx] row[SALDO_ANTERIOR].value = formata_valor( totalizador[SALDO_ANTERIOR][destinacao]) row[CREDITO].value = formata_valor(totalizador[CREDITO][destinacao]) row[DESPESA_REALIZADA].value = formata_valor( totalizador[DESPESA_REALIZADA][destinacao]) row[DESPESA_NAO_REALIZADA].value = formata_valor( totalizador[DESPESA_NAO_REALIZADA][destinacao]) row[SALDO_REPROGRAMADO_PROXIMO].value = formata_valor( totalizador[SALDO_REPROGRAMADO_PROXIMO][destinacao]) row[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS].value = formata_valor( totalizador[DESPESA_NAO_DEMONSTRADA_OUTROS_PERIODOS][destinacao]) row[SALDO_BANCARIO].value = formata_valor( totalizador[SALDO_BANCARIO][destinacao]) if destinacao != 'CK': row[CREDITO_NAO_DEMONSTRADO].value = formata_valor( totalizador[CREDITO_NAO_DEMONSTRADO][destinacao]) if destinacao == 'K': valor_total_reprogramado_proximo = totalizador[ SALDO_REPROGRAMADO_PROXIMO]['CK'] valor_total_reprogramado_proximo = ( valor_total_reprogramado_proximo + totalizador[SALDO_REPROGRAMADO_PROXIMO]['C'] if totalizador[SALDO_REPROGRAMADO_PROXIMO]['C'] > 0 else valor_total_reprogramado_proximo) valor_total_reprogramado_proximo = ( valor_total_reprogramado_proximo + totalizador[SALDO_REPROGRAMADO_PROXIMO]['K'] if totalizador[SALDO_REPROGRAMADO_PROXIMO]['K'] > 0 else valor_total_reprogramado_proximo) row[TOTAL_REPROGRAMADO_PROXIMO].value = formata_valor( valor_total_reprogramado_proximo) # Apresenta o subtotal bancário da ação na posição destinada ao valor valor_saldo_bancario_total = totalizador[SALDO_BANCARIO]['C'] + totalizador[SALDO_BANCARIO]['K'] + \ totalizador[SALDO_REPROGRAMADO_PROXIMO]['CK'] row[TOTAL_SALDO_BANCARIO].value = "Subtotal" row[TOTAL_SALDO_BANCARIO].value = formata_valor( valor_saldo_bancario_total) OFFSET += offset_local
print(ws.max_row) words = [chr(i) for i in range(65, 91)] current_max_row = ws.max_row for i in range(0, len(words)): foo = ws.cell(row=ws.max_row + 1, column=ws.min_column) # # or # foo = ws.cell(row = current_max_row+i, column = ws.min_column) print(i) foo.value = words[i] # 單元格對齊 ws.cell(row=11, column=11).value = "CENTER" align_center = styles.Alignment(horizontal="center", vertical="center") ws.cell(row=11, column=11).alignment = align_center # 合併單行儲存格 # 只有起始格的資料會留下,不會置中 ws.cell(10, 11).value = "merge_single_line" ws.merge_cells("K10:M10") # 合併多行儲存格 ws.cell(6, 11).value = "merge_multi-line" ws.merge_cells(start_row=6, start_column=11, end_row=9, end_column=15) # 移除表單 wb.remove_sheet(ws1) # 插入列行 ws.insert_rows(3)
class TemplateRender: align_center_center = styles.Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) align_center_center_not_wrap = styles.Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) align_left_top = styles.Alignment(horizontal='left', vertical='top', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) align_left_top_not_wrap = styles.Alignment(horizontal='left', vertical='top', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) def __init__(self, report_columns=1, header_width=50, file_name='', copyfile=False, open_in_excel=False, dirs_copy_name=[], sheet_title=''): self.sheet_count = 1 self.current_line = 0 self.report_columns = report_columns self.header_width = header_width self.file_name = file_name self.wb = Workbook() self.ws = self.wb.active self.copyfile = copyfile self.open_in_excel = open_in_excel self.dirs_copy_name = dirs_copy_name self.first_data_row = None self.add_sheet(sheet_title) def add_sheet(self, sheet_title=''): self.current_line = 1 if self.sheet_count > len(self.wb.worksheets): self.wb.create_sheet() self.ws = self.wb.worksheets[self.sheet_count-1] if sheet_title: self.ws.title = sheet_title self.sheet_count += 1 def add_data_row(self, rows, aligment=None): for row in rows: for en, el in enumerate(row): if isinstance(el, datetime.date): self.ws.cell(row=self.current_line, column=en + 1).value = el.strftime('%d.%m.%Y') else: self.ws.cell(row=self.current_line, column=en + 1).value = el if aligment is not None: self.ws.cell(row=self.current_line, column=en + 1).alignment = aligment if self.first_data_row is None: self.first_data_row = self.current_line self.current_line += 1 def add_header_row(self, title): for col_num in range(1, self.report_columns+1): self.ws.cell(row=self.current_line, column=col_num).alignment = self.align_left_top_not_wrap self.ws.cell(row=self.current_line, column=1).value = title self.current_line += 1 def add_titles_row(self, rows): for en, row in enumerate(rows): self.ws.cell(row=self.current_line, column=en + 1).value = row[0] self.ws.cell(row=self.current_line, column=en + 1).alignment = self.align_center_center self.ws.column_dimensions[self.ws.cell(row=self.current_line, column=en + 1).column_letter].width = row[1] self.current_line += 1 def close_template_file(self): logging.warning(self.file_name) self.wb.save(filename=self.file_name) self.wb.close() # if self.copyfile: # self.copy_template_file() if self.open_in_excel: self.open_template_in_excel() def copy_template_file(self): for dir_copy_name in self.dirs_copy_name: shutil.copy(self.file_name, os.path.join(dir_copy_name, self.file_name)) def open_template_in_excel(self): temp_file_name = os.path.join(os.environ['TMP'], "report_" + ''.join(random.choice(string.ascii_letters + string.digits) for _ in range(10)) + '.xlsx') shutil.copy(self.file_name, temp_file_name) subprocess.Popen(temp_file_name, shell=True)
class TraceabilityGenerator: ''' Utility class for generating a requirements traceability matrix''' HEADER_FONT = styles.Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') CELL_FONT = styles.Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') SUMMARY_FONT = styles.Font(name='Calibri', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') HEADER_ALIGNMENT = styles.Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) CELL_ALIGNMENT = styles.Alignment(horizontal='left', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) SUMMARY_ALIGNMENT = styles.Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=True, shrink_to_fit=False, indent=0) REQ_NOT_MET_FILL = styles.PatternFill(start_color='EE1111', end_color='EE1111', fill_type='solid') REQ_MET_FILL = styles.PatternFill(start_color='0FFFFF', end_color='0FFFFF', fill_type='solid') COL_COUNT_FORMULA = 'COUNTA(\'%s\'!%s:%s)-1' PERCENT_FORMULA = '=IF(%s%d, %s%d/%s%d, 0.0)' REQ_IS_MET_FORMULA = 'OR(%s,ISBLANK($%s$%d))' @staticmethod def generateTraceabilityMatrix(reqMap, args): ''' Generates a workbook with a traceability matrix from a requirements map''' outputDir = os.path.expanduser(args.outputDir) outputDir = os.path.expandvars(outputDir) # make sub-directories for exported traceability matrix if (not os.path.exists(outputDir)): os.makedirs(outputDir) outfile = os.path.join(outputDir, args.outfile + '.xlsx') wb = Workbook() # generate summary sheet summarySheet = wb.active TraceabilityGenerator._generateTraceabilitySummary( summarySheet, reqMap, args) # generate sheet for each module for moduleName, module in six.iteritems(reqMap): TraceabilityGenerator._generateTraceabilitySheet( wb, moduleName, module, args) wb.save(outfile) @staticmethod def _generateTraceabilitySummary(sheet, reqMap, args): ''' Generates a summary sheet with summary details for each module and overall summary''' sheet.title = 'Summary' sheet.column_dimensions['A'].width = 50 sheet.column_dimensions['B'].width = 30 sheet.column_dimensions['C'].width = 15 sheet.column_dimensions['D'].width = 3 sheet.column_dimensions['E'].width = 15 sheet.column_dimensions['F'].width = 3 sheet.column_dimensions['G'].width = 15 cellRow = 1 for moduleName in reqMap: cellRow += TraceabilityGenerator._generateModuleSummary( sheet, moduleName, cellRow, 1, args) cellRow += 1 # set summary of module summaries cell = sheet.cell(row=cellRow, column=1) cell.value = 'Summary' cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellRow += 1 moduleCol = 3 expectedCount = TraceabilityGenerator._getSummaryFormula(reqMap, 'A') if (True == args.checkSrcLinks): # set summary of source links actualCount = TraceabilityGenerator._getSummaryFormula( reqMap, get_column_letter(moduleCol)) TraceabilityGenerator._generateSummaryColSummary( sheet, 'Source Links:', actualCount, expectedCount, cellRow, 2) cellRow += 1 moduleCol += 1 if (True == args.checkTestLinks): # set summary of test links actualCount = TraceabilityGenerator._getSummaryFormula( reqMap, get_column_letter(moduleCol)) TraceabilityGenerator._generateSummaryColSummary( sheet, 'Test Links:', actualCount, expectedCount, cellRow, 2) cellRow += 1 moduleCol += 1 @staticmethod def _getSummaryFormula(reqMap, colStr): ''' Generates an summary formula for all the modules for the specified column (requirement type)''' countFormula = '=0' for moduleName in reqMap: countFormula += '+' + TraceabilityGenerator.COL_COUNT_FORMULA % ( moduleName, colStr, colStr) return countFormula @staticmethod def _generateSummaryColSummary(sheet, colName, countFormula, totalFormula, rowOffset, colOffset): ''' Generates a column summary details for all the modules in the format <summary_description> | <actual_value> / <expected_value> = <percent_value>''' # set summary type cell = sheet.cell(row=rowOffset, column=colOffset) cell.value = colName cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set actual summary count cell = sheet.cell(row=rowOffset, column=colOffset + 1) cell.value = countFormula cell.font = TraceabilityGenerator.SUMMARY_FONT alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT) alignment.horizontal = 'right' cell.alignment = alignment cell = sheet.cell(row=rowOffset, column=colOffset + 2) cell.value = '/' font = copy(TraceabilityGenerator.SUMMARY_FONT) font.bold = True cell.font = font cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set expected summary count cell = sheet.cell(row=rowOffset, column=colOffset + 3) cell.value = totalFormula cell.font = TraceabilityGenerator.SUMMARY_FONT alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT) alignment.horizontal = 'left' cell.alignment = alignment cell = sheet.cell(row=rowOffset, column=colOffset + 4) cell.value = '=' font = copy(TraceabilityGenerator.SUMMARY_FONT) font.bold = True cell.font = font cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set percentage of actual/expected cell = sheet.cell(row=rowOffset, column=(colOffset + 5)) cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\ get_column_letter(colOffset+3), rowOffset, get_column_letter(colOffset+1), rowOffset, get_column_letter(colOffset+3), rowOffset) cell.font = TraceabilityGenerator.SUMMARY_FONT cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT cell.number_format = '0.00%' @staticmethod def _generateModuleColSummary(sheet, moduleName, colName, countCol, totalCol, rowOffset, colOffset): ''' Generates a column summary details for the specified module in the format <summary_description> | <actual_value> / <expected_value> = <percent_value>''' # set column summary type cell = sheet.cell(row=rowOffset, column=colOffset) cell.value = colName cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set column actual count cell = sheet.cell(row=rowOffset, column=(colOffset + 1)) cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % ( moduleName, get_column_letter(countCol), get_column_letter(countCol)) alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT) alignment.horizontal = 'right' cell.alignment = alignment cell = sheet.cell(row=rowOffset, column=(colOffset + 2)) cell.value = '/' font = copy(TraceabilityGenerator.SUMMARY_FONT) font.bold = True cell.font = font cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set column expected count cell = sheet.cell(row=rowOffset, column=(colOffset + 3)) cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % ( moduleName, get_column_letter(totalCol), get_column_letter(totalCol)) cell.font = TraceabilityGenerator.SUMMARY_FONT alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT) alignment.horizontal = 'left' cell.alignment = alignment cell = sheet.cell(row=rowOffset, column=(colOffset + 4)) cell.value = '=' font = copy(TraceabilityGenerator.SUMMARY_FONT) font.bold = True cell.font = font cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT # set actual / expected percentage cell = sheet.cell(row=rowOffset, column=(colOffset + 5)) cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\ get_column_letter(colOffset+3), rowOffset, get_column_letter(colOffset+1), rowOffset, get_column_letter(colOffset+3), rowOffset) cell.font = TraceabilityGenerator.SUMMARY_FONT cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT cell.number_format = '0.00%' @staticmethod def _generateModuleSummary(sheet, moduleName, rowOffset, colOffset, args): ''' Generates a summary section for the specified module''' cellRow = 0 # set module name cell = sheet.cell(row=rowOffset + cellRow, column=colOffset) cell.value = moduleName cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellRow += 1 # start at offset because of default columns: (name | text | satisfied) moduleCol = 4 if (True == args.checkSrcLinks): # set source links summary TraceabilityGenerator._generateModuleColSummary( sheet, moduleName, 'Source Links:', moduleCol, 1, rowOffset + cellRow, colOffset + 1) moduleCol += 1 cellRow += 1 if (True == args.checkTestLinks): # set test links summary TraceabilityGenerator._generateModuleColSummary( sheet, moduleName, 'Test Links:', moduleCol, 1, rowOffset + cellRow, colOffset + 1) cellRow += 1 return cellRow @staticmethod def _generateTraceabilitySheet(workbook, moduleName, module, args): ''' Generates a sheet with all the requirements, requirements details, and requirement links for the specified module''' # create sheet for module moduleSheet = workbook.create_sheet(title=moduleName) moduleSheet.fill = TraceabilityGenerator.REQ_MET_FILL # set column dimensions for requirement name moduleSheet.column_dimensions['A'].width = 30 # set column dimensions for requirement text moduleSheet.column_dimensions['B'].width = 70 # set header and requirement text as fixed moduleSheet.freeze_panes = 'B2' # set column for requirement satisfaction moduleSheet.column_dimensions['C'].hidden = True cellRow = 1 # setup header cellCol = 1 # set start column for conditional formatting startCol = get_column_letter(cellCol) # add requirement name column cell = moduleSheet.cell(row=cellRow, column=cellCol) cell.value = 'Requirement Name' cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellCol += 1 # add requirement text column cell = moduleSheet.cell(row=cellRow, column=cellCol) cell.value = 'Requirement Text' cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellCol += 1 areReqLinksChecked = \ (True == args.checkSrcLinks) or \ (True == args.checkTestLinks) if (True == areReqLinksChecked): # add requirement satisfied column cell = moduleSheet.cell(row=cellRow, column=cellCol) cell.value = 'SATISFIED' cellCol += 1 if (True == args.checkSrcLinks): # set column dimension for test links moduleSheet.column_dimensions[get_column_letter( cellCol)].width = 75 cell = moduleSheet.cell(row=cellRow, column=cellCol) cell.value = 'Source Code Links' cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellCol += 1 if (True == args.checkTestLinks): # set column dimension for test links moduleSheet.column_dimensions[get_column_letter( cellCol)].width = 75 cell = moduleSheet.cell(row=cellRow, column=cellCol) cell.value = 'Test Links' cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT cellCol += 1 # get end column for conditional formatting endCol = get_column_letter(cellCol - 1) cellRow += 1 # add row for each requirement in module for req, reqValue in six.iteritems(module): rowCol = 1 # requirement name cell = moduleSheet.cell(row=cellRow, column=rowCol) cell.value = req cell.font = TraceabilityGenerator.HEADER_FONT cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT rowCol += 1 # requirement text cell = moduleSheet.cell(row=cellRow, column=rowCol) cell.value = reqValue.reqText cell.font = TraceabilityGenerator.CELL_FONT cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT rowCol += 1 if (True == areReqLinksChecked): # requirement satisfied reqMetCell = moduleSheet.cell(row=cellRow, column=rowCol) reqMetFormula = 'False' rowCol += 1 # TODO add requirements as hyperlinks '=HYPERLINK(<URL>, <text>)' if (True == args.checkSrcLinks): # add source links linksText = '' for link in reqValue.reqLinks: if (tLinkType.LINK_TYPE__SRC == link.linkType): linksText += link.linkName if ((link.linkFile is not None) and (link.linkFileLineNum is not None)): if (True == args.basename): linksText += ' - (%s line %s)' % ( os.path.basename(link.linkFile), link.linkFileLineNum) else: linksText += ' - (%s line %s)' % ( link.linkFile, link.linkFileLineNum) linksText += '\n' cell = moduleSheet.cell(row=cellRow, column=rowCol) cell.value = linksText cell.font = TraceabilityGenerator.CELL_FONT cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % ( reqMetFormula, get_column_letter(rowCol), cellRow) rowCol += 1 if (True == args.checkTestLinks): # add test links linksText = '' for link in reqValue.reqLinks: if (tLinkType.LINK_TYPE__TEST == link.linkType): linksText += link.linkName + '\n' cell = moduleSheet.cell(row=cellRow, column=rowCol) cell.value = linksText cell.font = TraceabilityGenerator.CELL_FONT cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % ( reqMetFormula, get_column_letter(rowCol), cellRow) rowCol += 1 # set requirement met column value reqMetCell.value = '=IF(NOT(%s), "PASS", "FAIL")' % ( reqMetFormula) # set conditional formatting for if requirement is met moduleSheet.conditional_formatting.add( '%s%d:%s%d' % (startCol, cellRow, endCol, cellRow), formatting.rule.FormulaRule( formula=[reqMetFormula], stopIfTrue=True, fill=TraceabilityGenerator.REQ_NOT_MET_FILL)) cellRow += 1
def insertData(rollNoRow, rollNoCol, mySheet): # Getting current date date = datetime.now().strftime('%d') month = datetime.now().strftime('%b') year = datetime.now().strftime('%Y') # Adding full stop so that when last digit is 0, # it doesnt consider last session as 0 full_date = month + "\n" + date + ",\n" + year + "." # # Reading .txt File txtfile = "list.txt" if not (os.path.exists(txtfile)): print(f"File '{txtfile}' Does NOT Exists") exit(1) file = open("list.txt", "r") # rollNoRow Storing to another variable tempRollNoRow = rollNoRow # Default row, col for "list.txt" row, col = 0, 0 # By Default, considering that only one lecture was held multipleSessions = False # Getting date of last attendance recorded lastDateAt = mySheet.cell(row=rollNoRow, column=max_col) lastDateAt.alignment = styles.Alignment(wrapText=True) # Checking if attendance of same date exists if (lastDateAt.value is not None) and (full_date in lastDateAt.value): print("\nNOTE: More than 1 sessions found") print("So, naming column name as S-1, S-2, ..\n") # Multiple lectures were held multipleSessions = True # Getting last digit of last session if lastDateAt.value[-1] >= '0' and lastDateAt.value[-1] <= '9': lastSessionNo = int(lastDateAt.value[-1]) else: # Renaming cellvalue to session1 lastSessionNo = 1 lastDateAt.value = full_date + "\n S-1" # Insert date at last column insertDateAt = mySheet.cell(row=rollNoRow, column=max_col + 1) insertDateAt.alignment = styles.Alignment(wrapText=True, horizontal='center') # If Multiple lectures were held if multipleSessions: insertDateAt.value = full_date + "\n S-" + str(lastSessionNo + 1) # if Multiple lectures were not held else: insertDateAt.value = full_date # Iterating over each attendee for attendee in file: # Remove blank lines if not attendee.strip(): continue # Finding Roll no from entire String roll = findall("\d+", attendee) # Teachers wont have roll no, so ignoring them if not len(roll): print("Skipping", attendee, end="") continue # rollNoRow must be changed after each Iteration # so giving back the same value rollNoRow = tempRollNoRow # Iterating over all roll no of class for i in range(rollNoRow + 1, max_row + 1): # Getting cell_object of roll no of classes cell = mySheet.cell(row=i, column=rollNoCol) # NOTE: roll[0] is a `string` # If roll no of text file matches the roll no of xls file if (int(roll[0]) == cell.value): print("Inserteddddd") # Getting cell_object where new attendance will be recorded insertAtCell = mySheet.cell(row=i, column=max_col + 1) # Store only first Number found from list insertAtCell.value = 'P' insertAtCell.alignment = styles.Alignment(horizontal='center') # Printing those rollno who were recorded as present print(roll[0]) break # Going to next record in .xls file rollNoRow += 1 # Going to next record in .txt file row += 1
然后保存一个csv,还得设置index=False,不然第一列会多出来索引,然后得设置编码,否则用excel打开会乱码 然后保存一个excel,也得设置index=False,并且pandas保存后,原来的格式都没了,所以把excel的格式设置都放在了最后 """ print('正在保存csv...') df = read_excel(target_dir + "渠道数据整合模板.xlsx", engine='openpyxl') df.sort_values(by='日期', ascending=False, inplace=True) df.to_csv(target_dir + "渠道数据整合模板.csv", index=False, encoding='utf_8_sig') df.to_excel(target_dir + "渠道数据整合模板.xlsx", index=False) print('正在设置xlsx文件格式...') # 设置excel格式 wb2 = load_workbook(target_dir + "渠道数据整合模板.xlsx") ws2 = wb2.active # 左对齐,上下居中 alignment = styles.Alignment(horizontal='left', vertical='center') # 要对每个单元格都设置 for row in ws2.rows: for cell in row: cell.alignment = alignment # 设置列宽 ws2.column_dimensions['A'].width = 20 ws2.column_dimensions['B'].width = 20 ws2.column_dimensions['C'].width = 20 ws2.column_dimensions['D'].width = 20 ws2.column_dimensions['E'].width = 20 ws2.column_dimensions['F'].width = 20 ws2.column_dimensions['G'].width = 20 ws2.column_dimensions['H'].width = 20 ws2.column_dimensions['I'].width = 20 ws2.column_dimensions['J'].width = 20