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 color(self, value): if value == "FAIL": return styles.Font(color=styles.colors.RED) elif value == "PASS": return styles.Font(color=styles.colors.GREEN) else: return
def generate_block_sheet(self, workbook): worksheet = workbook.create_sheet(title="{}".format(self.block.name)) self.generate_dates() headers = ['Regd. No.', 'Name'] + [ date.strftime("%d/%m/%y") for date in self.generated_dates ] row_num = 1 for col_num, column_title in enumerate(headers, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.font = styles.Font(bold=True) cell.value = column_title for attendance in self.attendance_list: row_num += 1 row_data = [attendance.student.regd_no, attendance.student.name ] + self.get_student_attendance(attendance) for col_num, cell_value in enumerate(row_data, 1): cell = worksheet.cell(row=row_num, column=col_num) cell.value = cell_value if cell_value == 'P': cell.font = styles.Font(color='28A745') elif cell_value == 'A': cell.font = styles.Font(bold=True, color='DC3545')
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 remark(self, number, line, row): """填写备注""" info = self.sheet[row + str(number)].comment temp = re.search(r"考勤异常,上班打卡时间.*,下班打卡时间.*,迟到(\d+)小时(\d+)分钟", info.content) # 提取迟到时间的正则 hour = temp.group(1) # 提取迟到小时 minute = temp.group(2) # 提取迟到分钟 if int(hour) == 0: if self.sheet['BI' + str(number)].value is not None: self.sheet['BI' + str(number)].value = self.sheet['BI' + str(number)].value + \ str(datetime.datetime.now().month - 1) + \ "月" + str(line - 4) + "日" \ + "迟到" + str(minute) + "分钟," self.check_time(number, hour, minute) else: self.sheet['BI' + str(number)].value = str(datetime.datetime.now().month - 1) + \ "月" + str(line - 4) + "日" \ + "迟到" + str(minute) + "分钟," self.check_time(number, hour, minute) else: if self.sheet['BI' + str(number)].value is not None: self.sheet['BI' + str(number)].value = self.sheet['BI' + str(number)].value + \ str(datetime.datetime.now().month - 1) + \ "月" + str(line - 4) + "日" \ + "迟到" + str(hour) + "小时" + str(minute) + "分钟," self.check_time(number, hour, minute) else: self.sheet['BI' + str(number)].value = str(datetime.datetime.now().month - 1) + \ "月" + str(line - 4) + "日" \ + "迟到" + str(hour) + "小时" + str(minute) + "分钟," self.check_time(number, hour, minute) self.sheet['BI' + str(number)].font = styles.Font(name='宋体', size=6, bold=True)
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 do_low_hanging_fruit_analysis(self): func_list = [ self.domain_user_admin, self.everyone_admin, self.authenticated_users_admin, self.domain_users_control, self.everyone_control, self.authenticated_users_control, self.domain_users_rdp, self.everyone_rdp, self.authenticated_users_dcom, self.domain_users_dcom, self.everyone_dcom, self.authenticated_users_dcom, self.shortest_acl_path_domain_users, self.shortest_derivative_path_domain_users, self.shortest_hybrid_path_domain_users, self.shortest_acl_path_everyone, self.shortest_derivative_path_everyone, self.shortest_hybrid_path_everyone, self.shortest_acl_path_auth_users, self.shortest_derivative_path_auth_users, self.shortest_hybrid_path_auth_users, self.kerberoastable_path_len, self.high_admin_comps ] sheet = self.workbook._sheets[2] self.write_single_cell(sheet, 1, 1, "Domain Users to Domain Admins") self.write_single_cell(sheet, 1, 2, "Everyone to Domain Admins") self.write_single_cell(sheet, 1, 3, "Authenticated Users to Domain Admins") font = styles.Font(bold=True) sheet.cell(1, 1).font = font sheet.cell(1, 2).font = font sheet.cell(1, 3).font = font for f in func_list: s = timer() f(sheet) print "{} completed in {}s".format(f.__name__, timer() - s)
def add_record_to_excel(data): """ Create a new record to excel file or add to existing file """ EXCEL_FILE = "dbmonitor.xlsx" file_exists = path.exists(EXCEL_FILE) if file_exists: book = load_workbook(EXCEL_FILE) ws = book.active for item in data: ws.append(list(item.values())) else: print("new file") book = Workbook() ws = book.active dbs = [] header = list(data[0].keys()) # get the headers from the keys ws.append(header) row = ws.row_dimensions[1] row.font = styles.Font(bold=True, size=13) for item in data: ws.append(list(item.values())) book.save("dbmonitor.xlsx")
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 form_cell(row, column, value): ws.merge_cells(start_row=row, end_row=row, start_column=column, end_column=6) target_cell = ws.cell(row=row, column=column, value=value) font = styles.Font(color='FFFFFF', bold=True) fill = styles.PatternFill(fill_type='solid', fgColor='0076CE') target_cell.fill = fill target_cell.font = font
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 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 to_igschel_hiso(model, name): input_keys = input_keys_template if features == 6: del input_keys[1] wb = Workbook() ws = wb.active for i, x in enumerate(range(features)): ws.cell(row=i + 1, column=1).value = input_keys[i] ws.cell(row=i + 1, column=2).value = 1 for x in range(hidden): cell = chr(ord("A") + x) ws.cell(row=x + 1, column=4).value = \ "=SUMPRODUCT(B1:B{features}, weights0!{cell}1:{cell}{features}) + weights1!A{idx}" \ .format(cell=cell, features=features, idx=x + 1) for x in range(outputs): cell = chr(ord("A") + x) ws.cell(row=x + 1, column=6).value = 2**x ws.cell(row=x + 1, column=7).value = \ "=SUMPRODUCT(D1:D{hidden}, weights2!{cell}1:{cell}{hidden}) + weights3!A{idx}" \ .format(cell=cell, hidden=hidden, idx=x + 1) rule = ColorScaleRule(start_type='percentile', start_value=40, start_color='ffb6d7a8', mid_type='percentile', mid_value=70, mid_color='ff9fc5e8', end_type='percentile', end_value=95, end_color='ffea9999') bold = styles.Font(bold=True) ws.conditional_formatting.add('D1:D{}'.format(hidden), rule) ws.conditional_formatting.add('G1:G{}'.format(outputs), rule) ws.conditional_formatting.add( 'G1:G{}'.format(outputs), CellIsRule(operator='equal', formula=['MAX(G$1:G${})'.format(outputs)], font=bold)) w = model.get_weights() for y in range(4): weights = wb.create_sheet("weights{}".format(y)) wy = w[y] shape = wy.shape if len(shape) == 2: for ix, iy in np.ndindex(shape): weights.cell(row=ix + 1, column=iy + 1).value = float(wy[ix, iy]) else: for ix in range(shape[0]): weights.cell(row=ix + 1, column=1).value = float(wy[ix]) wb.save(name + ".xlsx")
def exportar_planilha_importacao_usuarios_perfil_codae(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_CODAE.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'CODAE' headers = [ 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', 'Número CRN' ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"COORDENADOR_GESTAO_ALIMENTACAO_TERCEIRIZADA,' 'COORDENADOR_DIETA_ESPECIAL,' 'COORDENADOR_SUPERVISAO_NUTRICAO,' 'COORDENADOR_GESTAO_PRODUTO"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('G2:G1048576') workbook.save(response) return response
def write_column_data(self, sheet, title, results): count = len(results) offset = 1 font = styles.Font(bold=True) c = sheet.cell(offset, self.col_count) c.font = font sheet.cell(offset, self.col_count, value=title.format(count)) for i in xrange(0, count): sheet.cell(i + offset + 1, self.col_count, value=results[i]) self.col_count += 1
def check_time2(self, number, letter): """在对应迟到的表格内填上迟到次数""" if self.sheet[letter + str(number)].value is None: self.sheet[letter + str(number)].value = 1 else: self.sheet[letter + str(number)].value = int( self.sheet[letter + str(number)].value) + 1 self.sheet[letter + str(number)].font = styles.Font(name='宋体', size=6, bold=True)
def modify_cell_format(save_dir): wt=load_workbook(save_dir) sheet=wt.get_sheet_by_name('Sqldata') sheet.column_dimensions['A'].width =40 sheet.column_dimensions['B'].width =25 sheet.column_dimensions['C'].width =150 size_font=styles.Font(size=12,bold=True) colums=sheet.max_column for i in range(1,colums+1): sheet.cell(row=1,column=i).font=size_font #sheet.column_dimensions[1].font=size_font wt.save(save_dir)
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 exportar_planilha_importacao_usuarios_perfil_escola(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_ESCOLA.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'ESCOLA' headers = [ 'Cód. EOL da U.E', 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"DIRETOR, DIRETOR CEI"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('H2:H1048576') workbook.save(response) return response
def stylestFout(self, size, bold=None, color="FF010701"): ''' 单元格字体的设置 :param size: 字体大小 int :param bold: 是否加粗,默认为None,需要传 True :param color: 颜色默认黑色,需要传入其他 :return: 无 ''' self.font = styles.Font(name=u'微软雅黑', size=size, bold=bold, color=color) return self.font
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)
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 __add_font(cell: opx.worksheet.worksheet.Cell, font_name: str, font_size: int, font_color: str, font_bold: Optional[bool] = False) -> None: """ Add font to the excel cell :param cell: Cell to be given a new font :param font_name: Name of a font :param font_size: Size of a font :param font_color: Color of a font :param font_bold: True if a font should be bold else False :return: None """ cell.font = opx_style.Font(name=font_name, size=font_size, bold=font_bold, color=font_color)
def write_column(self, sheetNum, row, column, title, results): try: count = len(results) sheet = self.workbook._sheets[sheetNum] # Update title cell font = styles.Font(bold=True) titleCell = sheet.cell(row, column) titleCell.font = font sheet.cell(row, column, value=title.format(count)) # Update the rows for i in range(0, count): sheet.cell(i + row + 1, column, value=results[i]) except Exception as e: logging.error('Failed to write column data.') logging.error(e)
def do_front_page_analysis(self): func_list = [self.create_node_statistics, self.create_edge_statistics, self.create_qa_statistics] sheet = self.workbook._sheets[0] self.write_single_cell(sheet, 1, 1, "Node Statistics") self.write_single_cell(sheet, 1, 2, "Edge Statistics") self.write_single_cell(sheet, 1, 3, "QA Info") font = styles.Font(bold=True) sheet.cell(1, 1).font = font sheet.cell(1, 2).font = font sheet.cell(1, 3).font = font for f in func_list: s = timer() f(sheet) print "{} completed in {}s".format(f.__name__, timer() - s)
def setupBaseFormats(): # The common fonts borders and number_formats are set up here return { 'fonts': { 'header': opStyles.Font(color="444444", size=14, bold=True), 'memberHeader': opStyles.Font(color="333388", size=12, bold=True), 'memberName': opStyles.Font(color="333388", size=10, bold=True), 'memberValue': opStyles.Font(color="000000", name='Courier', size=10, bold=False), 'boolean1': opStyles.Font(color="000000", name='Courier', size=10, bold=True), 'boolean0': opStyles.Font(color="888888", name='Courier', size=10, bold=False) }, 'borders': { 'thinBox': op.styles.Border(left=op.styles.Side(style='thin'), right=op.styles.Side(style='thin'), top=op.styles.Side(style='thin'), bottom=op.styles.Side(style='thin')), 'thickBottom': op.styles.Border( bottom=op.styles.Side(style='thick', color='000000')), 'thinBottom': op.styles.Border( bottom=op.styles.Side(style='thin', color='000000')), 'thinRight': op.styles.Border( right=op.styles.Side(style='thin', color='000000')), 'thinBottomBlue': op.styles.Border( bottom=op.styles.Side(style='thin', color='000088')) }, 'number_formats': { 'percentage': '0.00%' } }
''' try: val = self.uppercase[col - 1] except Exception: shang = col // 26 #取商值 yu = col % 26 #取余数 '''有bug''' val = self.uppercase[shang - 1] + self.uppercase[yu] return val if __name__ == '__main__': # print(excelReport.Gold) wb = openpyxl.load_workbook("caogao.xlsx") ws = wb["报告模板"] ws.cell(1, 1).font = styles.Font(color="FF010701") wb.save("caogao.xlsx") # case = caseExcel() # case.status("工作流接口测试用例.xlsx") # print(case.excelDic) # path = r'C:\Users\Administrator\Desktop\文档\博云BeyondDevOps测试用例.xlsx' # wb = xlrd.open_workbook(path) # sh = wb.sheet_by_name('POC测试用例') # s = sh.cell_value(1,1) # print(s) # print(sh.nrows) # print(sh.cell_value(1,1)) # a = caseExcel() # a.status() # for name in a.excelDic: # for case in a.excelDic[name]:
from openpyxl import formatting, styles wb = Workbook() ws = wb.active red_color = 'ffc7ce' red_color_font = '9c0103' red_font = styles.Font(size=14, bold=True, color=red_color_font) red_fill = styles.PatternFill(start_color=red_color, end_color=red_color, fill_type='solid') for row in range(1, 10): ws.cell(row=row, column=1, value=row - 5) ws.cell(row=row, column=2, value=row - 5) ws.conditional_formatting.add( 'A1:A10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill, font=red_font)) ws.conditional_formatting.add( 'B1:B10', formatting.CellIsRule(operator='lessThan', formula=['0'], fill=red_fill)) wb.save("test.xlsx")
from openpyxl.styles import colors from openpyxl.styles import fills from rekall_lib import utils from rekall.ui import renderer 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):
def set_word_style(path, sheet_name): wb = load_workbook(path) ws = wb[sheet_name] # sheet_name=wb.sheetnames[0] font1 = styles.Font(name=u'宋体', size=12, bold=True) ws.cell(row=1, column=1).font = font1 wb.save(path)