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 __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 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 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
print(B1.value)
#注释的添加/保存/设置
#####
#使用：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']