haiyan1280001
Eileen1280001
2019-12-17 20:40
采纳率: 0%
浏览 1.7k

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文件,打开时候提示
图片说明
不太清楚这个提示是因为什么原因引起的,,之前也出现过这个问题,后来检查是因为重复保存的原因,改成保存一次就好了,现在又出现这个问题,几天了还没排查到原因,哪位大神看下?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

相关推荐