def compareExecuteFromTo(self, excel_expect, excel_actual, row_from, row_to, column_from, column_to): wb_expect = load_workbook(excel_expect, data_only=False) wb_actual = load_workbook(excel_actual, data_only=False) sheet_expect = wb_expect.active sheet_actual = wb_actual.active list = [] for row in sheet_expect.iter_rows(min_row=row_from, min_col=column_from, max_col=column_to, max_row=row_to): for cell in row: list.append(cell.value) fill = PatternFill("solid", fgColor="FF0000") for row in sheet_actual.iter_rows(min_row=row_from, min_col=column_from, max_col=column_to, max_row=row_to): for cell in row: index = (cell.row - row_from) * (column_to - column_from + 1) + cell.column - column_from result_expect = list[index] result_actual = cell.value if result_actual != result_expect: if result_expect is not None and result_actual is not None: if isinstance(result_expect, str) and result_expect.startswith("="): if "$" in result_expect: result_expect = result_expect.replace("$", "") if result_expect[1:] not in result_actual: try: comment = Comment('预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value), 'lxy') comment.width = 400 comment.height = 400 cell.comment = comment cell.fill = fill self.textBrowser.append( '第{2}行第{3}列,\n预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value, cell.row + 1, cell.column + 1)) except: pass else: try: comment = Comment('预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value), 'lxy') comment.width = 400 comment.height = 400 cell.comment = comment cell.fill = fill self.textBrowser.append( '第{2}行第{3}列,\n预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value, cell.row + 1, cell.column + 1)) except: pass wb_actual.save(excel_actual)
def compareExecute(self, excel_expect, excel_actual): wb_expect = load_workbook(excel_expect, data_only=False) wb_actual = load_workbook(excel_actual, data_only=False) sheet_expect = wb_expect.active sheet_actual = wb_actual.active list = [] for row in sheet_expect.rows: for cell in row: list.append(cell.value) fill = PatternFill("solid", fgColor="FF0000") for index_row, row in enumerate(sheet_actual.rows): for index_column, cell in enumerate(row): index = index_row * sheet_actual.max_column + index_column if cell.value != list[index]: try: comment = Comment('预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value), 'lxy') comment.width = 400 comment.height = 400 cell.comment = comment cell.fill = fill self.textBrowser.append( '第{2}行第{3}列,\n预期是:{0},\n实际结果是:{1}'.format(list[index], cell.value, index_row + 1, index_column + 1)) except: pass wb_actual.save(excel_actual)
def up_comment(row, trunk_line, up_time, down_time, set_up_time): """非招商部门上班考勤异常自动填批注""" comment = Comment( f"考勤异常,上班打卡时间为{up_time.__format__('%H:%M')},下班打卡时间为{down_time.__format__('%H:%M')}," f"迟到{up_time.hour - set_up_time.hour}小时{up_time.minute - set_up_time.minute}分钟", "") comment.width, comment.height = 120, 120 sheet_trunk[convert2title(row + 4) + str(trunk_line)].comment = comment
def up_comment_bus(row, trunk_line, up_time, down_time, set_up_time1, set_up_time2): """招商部门上班考勤异常自动填批注""" if (up_time.hour == set_up_time1.hour) and ( down_time.hour - up_time.hour >= 8): # 如果上下班小时数不超过8小时可能只有一个记录 comment = Comment( f"考勤异常,上班打卡时间{up_time.__format__('%H:%M')},下班打卡时间{down_time.__format__('%H:%M')}," f"迟到{up_time.hour - set_up_time1.hour}小时{up_time.minute - set_up_time1.minute}分钟", "") comment.width, comment.height = 120, 120 sheet_trunk[convert2title(row + 4) + str(trunk_line)].comment = comment elif (up_time.hour == set_up_time2.hour) and (down_time.hour - up_time.hour >= 8): comment = Comment( f"考勤异常,上班打卡时间{up_time.__format__('%H:%M')},下班打卡时间{down_time.__format__('%H:%M')}," f"迟到{up_time.hour - set_up_time2.hour}小时{up_time.minute - set_up_time2.minute}分钟", "") comment.width, comment.height = 120, 120 sheet_trunk[convert2title(row + 4) + str(trunk_line)].comment = comment
def __get_error_comment(errors: List[str], existing_comment: Comment = None): stack = [] if existing_comment and existing_comment.text: stack.append(existing_comment.text) stack.extend(errors) text = '\r\n'.join(stack) comment = Comment(text, f'Validation') comment.width = 500 comment.height = 100 return comment
def addComment(self, diskCell, diskCommandText): '''添加批注 这一步只是添加一个单元格的批注,在下一个模块中可以批量添加。 Args: diskCell(str) : 表格的单元格坐标,对应 commentList 的 key diskCommandText(str) : 批注信息,对应 commentList[key] 的 value ''' diskComment = Comment(diskCommandText, 'NOC') diskComment.width = 200 diskComment.height = 100 self.ws[diskCell].comment = diskComment diskFill = PatternFill(patternType='solid', fgColor="FFC125") self.ws[diskCell].fill = diskFill
def add_excel_comment(file="D:\\11.xlsx", sheet_name="部署包配置页", commonts="这是批注的内容"): writer = pd.ExcelWriter(file, engine='openpyxl', mode='a') # 用于首次写入还可自动加表头 workbook = load_workbook(file) # 打开要写入数据的工作簿 writer.book = workbook # wb.get_sheet_by_name("部署包配置页") print(workbook.sheetnames) ws = workbook[sheet_name] # ws = wb.active 当前使用的sheet,一般都是第一页 comment = Comment(commonts, 'author') # 设置批注框的宽,高 comment.width = 200 comment.height = 100 # 在指定的单元格上面设置批注 ws['C16'].comment = comment writer.save() writer.close()
def referrals(info, grade, excel, cell, num): """无代课老师时添加推荐人员""" cache = [] for each in info: if grade in info[each][0]: cache.append(each) for sheet_name in excel.sheetnames: if excel[sheet_name][cell].value is not None: if excel[sheet_name][cell].value.replace( '\r', '').split("\n")[1] in cache: cache.remove(excel[sheet_name][cell].value.replace( '\r', '').split("\n")[1]) if len(cache) != 0: output = "" for n in cache: output += f"班级:{teacher_info[n][0]} {teacher_info[n][2]} 姓名:{n} 科目:{teacher_info[n][1]}\n" comm = Comment(output, "") comm.width, comm.height = 600, 500 excel["统计总表"]["A" + str(num)].comment = comm
def TestInput(data): fdp = atheris.FuzzedDataProvider(data) wb = Workbook() ws = wb.active #Set Comment c1 = ws['A1'].comment c1 = Comment(fdp.ConsumeString(10),fdp.ConsumeString(10)) c1.width = 300 c1.height = 50 ws['A1'].comment = c1 #Double assign comment c2 = Comment(fdp.ConsumeString(10),fdp.ConsumeString(10)) ws['B1'].comment = c2 ws['C1'].comment = c2 wb.save('%s.xlsx'%fdp.ConsumeString(10))
def add_sheet_header(self, column_names, column_widths, column_comments, sheet_name='devices'): #print(column_names) sheet = self.wb[sheet_name] for i, column in enumerate(column_names): sheet[cell.get_column_letter(i + 1) + '1'] = column sheet[cell.get_column_letter(i + 1) + '1'].font = HEADER_FONT sheet[cell.get_column_letter(i + 1) + '1'].fill = HEADER_FILL #sheet[cell.get_column_letter(i) + '1'].border = THIN_BORDER_STYLE sheet.column_dimensions[cell.get_column_letter( i + 1)].width = column_widths[i] if column_comments[i]: comment = Comment(column_comments[i], REPORT_AUTHOR) comment.width = 500 comment.height = 50 sheet[cell.get_column_letter(i + 1) + '1'].comment = comment sheet.freeze_panes = sheet['A2'] sheet.auto_filter.ref = sheet.dimensions self.wb.save(self.filename)
def comment(cell, old_teacher, new_teacher): """修改过的表格自动填入批注""" cache = Comment(f"初始教师:{old_teacher}\n代课教师:{new_teacher}", "") cache.width, cache.height = 120, 40 cell.comment = cache
def set_info(ws, email, keywords, genes, root): """Writes TOTAL COUNT column and %keyword% COLUMN (helper function _write_info), as well as descriptions and sorting if these options are selected by the user.""" global pb_int, form_elements quick_save = False all_counts = [] number = 1 # for each list of aliases (one gene) in the full list of genes for aliases in genes: if ask_quit: logging.info("Quitting...") sys.exit() if ask_save_and_quit: quick_save = True logging.info("Saving...") break logging.info("#%d" % number) # makes sure no aliases are common words that throw off the search # (string length is longer than 2 letters) aliases = ["(" + alias + ")" for alias in aliases if len(alias) > 2] counts = get_count(aliases, keywords, email) # the length of the list counts returned is the length of keywords + 1 if len(counts) < 2: # if counts list is not complete quick_save = True break else: logging.info(counts) all_counts.append(counts) number += 1 ws, rows = _write_info(ws, all_counts, keywords) if not quick_save: if form_elements['sort']: if root.custom_frames['AdvancedPage'].reverse_sort.get() == 1: ws_rows = sort_ws(rows, True) else: ws, rows = sort_ws(rows) # sets the ratio column col = total_count_col + 2 ws.column_dimensions[colnum_string(col)].width = 16 ws.cell(row=1, column=col).value = "COUNT RATIO" row = 2 # TODO ratio does not work check test14!!! # sorts all_counts so it matches the sorted file all_counts = sorted(all_counts, key=lambda el: int(el[0])) for counts in all_counts: try: count = int(counts[1]) / int(counts[0]) except ZeroDivisionError: count = 0 # divide by zero error always means 0/0 ws.cell(row=row, column=col).value = count row += 1 if form_elements['descriptions']: logging.info("Getting descriptions...") ws.cell(row=1, column=entrez_id_col).value = "ENTREZ ID" # symbol col is now 0 symbols_list = [row[0] for row in rows[1:]] row = 2 for i, symbol in enumerate(symbols_list): if ask_quit: sys.exit() pb_int += 1 if symbol != '': try: entrez_id, summary = get_summary(symbol) ws.cell(row=row, column=entrez_id_col).value = entrez_id comment = Comment(summary, "PubMed") comment.width = '500pt' # TODO see if this works comment.height = '700pt' # original was 108 x 59.25 # assuming symbols column is in the first column ws.cell(row=row, column=1).comment = comment except Exception as e: error_msg = ("Getting descriptions was interrupted by" " an error, but your spreadsheet was " "saved.") showerror(title='Error', message=error_msg) logging.info(str(e)) break row += 1 else: logging.info("Quick save") wb.save(form_elements['save_as_name']) total_time = clock() total_time_str = "Total time: " + str(int(total_time / 60)) + " min " + \ str(int(total_time % 60)) + " sec " logging.info("Done! " + total_time_str) if showinfo(title='Success', message=total_time_str + "Your file is located in " + path.dirname(form_elements['save_as_name'])) == 'ok': root.bar.pb.pack_forget() root.custom_frames['FormPage'].reset() root.custom_frames['AdvancedPage'].reset() pb_int = 0
#注释的添加/保存/设置 ##### #使用:openpyxl.comments.Comment(text, author, height=79, width=144) #来创建一个注释对象。以便接下来通过赋值添加给单元格。 #参数text : 添加注释内容 #参数author : 添加作者信息 #参数height=79 : 注释框高度,也可以后期通过Comment.height 属性 来赋值设置高度 #参数width=144 : 注释框宽度,也可以后期通过Comment.hight 属性 来赋值设置宽度 #使用:Cell.comments 属性 来赋值添加该单元格cell的注释 help(openpyxl.comments.Comment) comment = Comment('这里是添加注释内容...', '血皇敖天', height=79, width=144) comment.height = 79 comment.width = 300 ws['C22'].comment = comment #对单元格的comment属性进行赋值操作添加注释对象 ws['I3'].comment = comment #同一个注释对象 可以 赋值给 多个单元格 #使用公式 ##### #使用:公式使用字符串形式代表,格式如:ws['E1']='=SUM(1,1)'或 ws['E1']='=C1+D1' ws['C1'] = 3 ws['D1'] = 4 ws['E1'] = '=C1+D1' #单个及多个单元格 ##### #使用:Worksheet['A2'] 键名格式 来表示/获取/赋值cell单元格的行/列/单个单元格 #使用:Worksheet['A15':'D25'] 切片格式 来表示/获取/赋值工作表单中 cell单元格 的范围。如:cell_range = ws['A15':'D25'] #使用:Worksheet['A:D'] 切片格式 来表示/获取/赋值工作表单中 列 的范围。如:col_range = ws['A:D']
ws[k].font = font_dict[k] #################################################################### # must be unicode comment_dict = { 'C1':job_arg_info, 'E1':u'同一时间同一规格主机上\n测试一块磁盘在某一模式的读写测试,\n所有同批次进行单盘测试主机的\n所有同类型磁盘的带宽平均值', 'G1':u'该盘单盘独立测试带宽值/\n单盘独立测试的平均带宽值;\n低于90%标为●否则记为○', 'I1':u'同一时间同一规格主机上\n所有数据磁盘进行同一模式的读写测试,\n所有同批次进行并行测试主机的\n所有同类型磁盘的带宽平均值', 'L1':u'该盘并行测试带宽值/\n单盘独立测试的平均带宽值;\n低于90%标为●否则记为○' } for k in comment_dict: if k[0] <= column_max: t = comment_dict[k] cmt = Comment(t, 'note') cmt.width = 300 ws[k].comment = cmt #################################################################### # freeze the first line ws.freeze_panes = 'A2' #################################################################### # add avg sheet # load csv s_avg_sheet_file = sys.argv[2].replace('all_host','avg') p_avg_sheet_file = sys.argv[1].replace('all_host','avg') #print(s_avg_sheet_file) s_avg_sheet = sorted(cu.load_csv(s_avg_sheet_file), key=lambda x : cu.bp2num(x[0])) p_avg_sheet = sorted(cu.load_csv(p_avg_sheet_file), key=lambda x : cu.bp2num(x[0]))