openpyxl写入excel后打开报错“已删除的记录: /xl/comments/comment1.xml 部分的 批注 (批注)”？
CompareExcel.py代码如下：
from openpyxl.styles import Font,Alignment,Side,Border,Color,colors,PatternFill
from MMSpackagefile.codefile.ExcelData import ExcelData
class CompareExcel(object):
def __init__(self):
pass
def settruecelltype(self, sheet, row, col):
cell = sheet.cell(row, col)
font = Font(size=12, bold=False, name='Arial', color=colors.BLACK)
thin = Side(border_style='thin', color='0000FF')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
cell.font = font
cell.border = border
def setfalsecelltype(self, sheet, row, col):
cell = sheet.cell(row, col)
font = Font(size=12, bold=False, name='Arial', color=colors.RED)
fill = PatternFill(start_color=colors.YELLOW, end_color=colors.YELLOW, fill_type='solid')
thin = Side(border_style='thin', color='0000FF')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
cell.fill = fill
cell.font = font
cell.border = border
#expectlist,actlist 分别为预期返回list和实际返回list;
#data 为案例sheet页中过滤的案例数据；
#fullfield 为所有字段项去重（无论是否都需要比较，即是结果明细sheet页中标题字段）fulladdfield所有添加字段项去重
#categoryfield为从案例中获得的比较字段类;
#sheetfield为配置文件excel里，比较字段sheet页里获取的list，其中list1为所有的子字段列表，list2为添加的字段列表，list3为两个dict,key分别为IsCompare和IsAdd
def comp(self, expectlist, actlist, data, full, fulladdfield, categoryfield, sheetfield, sheet_result):
comparefield = [] #为根据案例中的需比较字段类获得的需比较字段项
addfield = [] #为根据案例中的需比较字段类获得的需比较添加字段项
rownum = sheet_result.max_row
fullli = ExcelData().sort_list(full)
fullfield = fullli[:-1]
if len(fulladdfield) != 0:
fullfield = [e for e in fullfield if e not in fulladdfield]
for j in categoryfield:
for m in sheetfield:
if m.get('checkfield') == 'IsCompare':
comparefield.extend(list(m.get(j).split(',')))
else:
if not m.get(j) != m.get(j):
addfield.extend(list(m.get(j).split(',')))
if len(expectlist) == len(actlist) and len(expectlist) != 0:
sumlogicvalue = CompareExcel().setresultsheet(expectlist, actlist, fullfield, fulladdfield, comparefield, addfield, data, sheet_result, rownum)
if len(expectlist) != 1:
sheet_result.merge_cells(start_row=rownum + 1, start_column=1, end_row=rownum + len(expectlist), end_column=1)
# return sumlogicvalue
elif len(expectlist) == len(actlist) and len(expectlist) == 0:
rowvalue = []
rowvalue.append(data.get('case_id'))
sumlogicvalue = False
for q in range(2, 2*len(fullfield)+len(fulladdfield)+2):
rowvalue.append('')
rowvalue.append(sumlogicvalue)
sheet_result.append(rowvalue)
CompareExcel().settruecelltype(sheet_result, rownum+1, 1)
for w in range(2, 2*len(fullfield)+len(fulladdfield)+3):
CompareExcel().setfalsecelltype(sheet_result, rownum+1, w)
# return sumlogicvalue
elif len(expectlist) > len(actlist):
commonexpectlist = []
commonactlist = []
surplusexpectlist = []
for i in expectlist:
sign = 0
for j in actlist:
if i['data']['id'] == j['data']['id']:
sign = 1
commonexpectlist.append(i)
commonactlist.append(j)
if sign == 0:
surplusexpectlist.append(i)
CompareExcel().setresultsheet(commonexpectlist, commonactlist, fullfield, fulladdfield, comparefield, addfield, data, sheet_result, rownum)
si = 'ex'
CompareExcel().setsurlistvalue(si, surplusexpectlist, fullfield, fulladdfield, sheet_result, rownum, commonexpectlist)
sheet_result.merge_cells(start_row=rownum + 1, start_column=1, end_row=rownum + len(expectlist), end_column=1)
sumlogicvalue = False
# return sumlogicvalue
else:
commonexpectlist = []
commonactlist = []
surplusexpectlist = []
for i in actlist:
sign = 0
for j in expectlist:
if i['data']['id'] == j['data']['id']:
sign = 1
commonactlist.append(i)
commonexpectlist.append(j)
if sign == 0:
surplusexpectlist.append(i)
CompareExcel().setresultsheet(commonexpectlist, commonactlist, fullfield, fulladdfield, comparefield,
addfield, data, sheet_result, rownum)
si = 'ac'
CompareExcel().setsurlistvalue(si, surplusexpectlist, fullfield, fulladdfield, sheet_result, rownum,
commonexpectlist)
sheet_result.merge_cells(start_row=rownum + 1, start_column=1, end_row=rownum + len(actlist),
end_column=1)
sumlogicvalue = False
return sumlogicvalue
def setresultsheet(self, expectlist, actlist, fullfield, fulladdfield, comparefield, addfield, data, sheet_result, rownum):
for i in range(len(expectlist)):
rowvalue = []
expectreturn = []
actreturn = []
addreturn = []
logicvalue = True
columnnum = []
for j in range(len(fullfield)):
if fullfield[j] in comparefield:
# if fullfield[j] == 'name':
# actlist[i]['data'][fullfield[j]] = '浦发银'
if fullfield[j] in list(expectlist[i]['data'].keys()):
expectreturn.append(expectlist[i]['data'][fullfield[j]])
else:
expectreturn.append('')
if fullfield[j] in list(actlist[i]['data'].keys()):
actreturn.append(actlist[i]['data'][fullfield[j]])
else:
actreturn.append('')
if expectlist[i].get('data').get(fullfield[j]) != actlist[i].get('data').get(fullfield[j]):
logicvalue = False
columnnum.append(2+j)
columnnum.append(2+j+len(fullfield))
columnnum.append(2*len(fullfield)+len(fulladdfield)+2)
else:
expectreturn.append('')
actreturn.append('')
if len(fulladdfield) != 0:
logicvalue = False
columnnum.append(2 * len(fullfield) + len(fulladdfield) + 2)
for m in range(len(fulladdfield)):
if fulladdfield[m] in addfield:
columnnum.append(2 + 2 * len(fullfield) + m)
if fulladdfield[m] in list(actlist[i].get('data').keys()):
addreturn.append(actlist[i].get('data').get(fulladdfield[m]))
else:
addreturn.append('')
else:
addreturn.append('')
if i == 0:
rowvalue.append(data.get('case_id'))
else:
rowvalue.append('')
rowvalue.extend(expectreturn)
rowvalue.extend(actreturn)
if len(addreturn) != 0:
if len(addreturn) == 1:
rowvalue.append(addreturn)
else:
rowvalue.extend(addreturn)
rowvalue.append(logicvalue)
sheet_result.append(rowvalue)
for o in range(1, 2*len(fullfield)+len(fulladdfield)+3):
if o in columnnum:
CompareExcel().setfalsecelltype(sheet_result, rownum + 1 + i, o)
else:
CompareExcel().settruecelltype(sheet_result, rownum + 1 + i, o)
return logicvalue
def setsurlistvalue(self, sig, surplusexpectlist, fullfield, fulladdfield, sheet_result, rownum, commonexpectlist):
for k in surplusexpectlist:
surrowvalue = ['']
expectlis = []
actlis = []
for l in fullfield:
sign = 0
if l in list(k['data'].keys()):
sign = 1
expectlis.append(k['data'][l])
if sign == 0:
expectlis.append('')
actlis.append('')
if sig == 'ex':
surrowvalue.extend(expectlis)
surrowvalue.extend(actlis)
else:
surrowvalue.extend(actlis)
surrowvalue.extend(expectlis)
if len(fulladdfield) != 0:
addlis = []
for m in fulladdfield:
if m in list(k['data'].keys()):
addlis.append(k['data'][m])
addlis.append('')
surrowvalue.extend(addlis)
surrowvalue.append('False')
sheet_result.append(surrowvalue)
for t in range(len(surplusexpectlist)):
for n in range(2, len(surrowvalue) + 1):
CompareExcel().setfalsecelltype(sheet_result, rownum + 1 + len(commonexpectlist) + t, n)
test_stock_info.py内容如下：
import pytest
import os
import time
import openpyxl as op
from MMSpackagefile.codefile.ExcelData import ExcelData
from MMSpackagefile.codefile.CompareExcel import CompareExcel
path1 = 'E:\\MMS\\myfirstproject\\MMSpackagefile\\configdatasource\\stock_info_option12.xlsx'
path2 = 'E:\\MMS\\myfirstproject\\MMSpackagefile\\configdatasource\\mms_cofigfile.xlsx'
executecase = ExcelData().getexecutecase(path1, 1, 0) #返回可执行案例的list
configcomparefield = ExcelData().getexcelfield(path1, 0, 0) #返回stock_info_option12.xlsx第一个sheet页的比对字段list
configcomparefield1 = ExcelData().getexcelfield(path1, 0, 0)
dirpath = os.path.abspath('..')
version_list = ExcelData().getversion(path2, 0, 1) #返回配置文件的版本信息
localtime = time.strftime('%Y%m%d%H%M%S', time.localtime())
path = dirpath + '\\Result\\' + version_list[0] + '_' + version_list[1] + '_' + localtime
os.makedirs(path)
excel = op.load_workbook(path2)
excel.save(path + '\\mms_cofigfile_' + localtime + '.xlsx')
exce = op.load_workbook(path1)
comparefield, addfield, sheetfield = ExcelData().getfullfielditem(configcomparefield1)
excel, sheet = ExcelData().setresulttitle(comparefield, addfield, exce)
@pytest.fixture(scope='module')
def stock_info(request):
value = {}
value['case_id'] = request.param['case_id']
value['title'] = request.param['title']
value['ticker'] = request.param['ticker']
value['exchange'] = request.param['exchange']
value['asset_class'] = request.param['asset_class']
value['IsCompare'] = request.param['IsCompare']
value['IsAdd'] = request.param['IsAdd']
return value
@pytest.mark.parametrize('stock_info', executecase, indirect=True)
def test_stock_info(stock_info):
configdata = ExcelData().getversion(path2, 0, 1)
oldreturnlist = ExcelData().getrequestdata(configdata[2], stock_info, 'stock-info')
newreturnlist = ExcelData().getrequestdata(configdata[3], stock_info, 'stock-info')
categoryfield = stock_info.get('IsCompare').split(',')
isbool = CompareExcel().comp(oldreturnlist, newreturnlist, stock_info, comparefield, addfield, categoryfield, configcomparefield, sheet)
ExcelData().setcolumnautowidth(sheet)
excel.save(path + '\\stock_info_option12_' + localtime + '.xlsx')
assert isbool
现在有两个问题，一个是CompareExcel.py里函数setresultsheet最后一行logicvalue高亮显示：
提示：This inspection warns about local variables referenced before assignment.我并未在函数外部使用过这个变量logicvalue为什么会有这种提示？
第二个问题，我用openpyxl写excel文件，打开时候提示
不太清楚这个提示是因为什么原因引起的，，之前也出现过这个问题，后来检查是因为重复保存的原因，改成保存一次就好了，现在又出现这个问题，几天了还没排查到原因，哪位大神看下？