xlwings操作Excel

import os
import shutil
import datetime
from string import Template
import xlwings as xw
# 整个流程测试

# 1. 打开报表 (目前仅考虑单工作表）reportSheet
def getreportfilename(reportpath):
    # 如果模板文件不存在,如何抛出异常
    if not os.path.exists(reportpath):
        raise '报表模板不存在'

    tempatepath = os.path.dirname(reportpath)
    tempatename = os.path.basename(reportpath)

    basename = os.path.split(tempatename)[0]
    ext = os.path.split(tempatename)[1]

    datadir = os.path.join(tempatepath, '.')
    reportfile = os.path.join(datadir, '报表_' + datetime.datetime.today().strftime('%y%m%d') + '.xlsx')

    if os.path.exists(reportfile):
        os.remove(reportfile)
    shutil.copy(reportpath, reportfile)

    return reportfile

# 2. 根据配置表处理数据
# 检查: 路径、文件名是否存在于各表异常set中，如果存在，填入状态，（值）
# 是否存在于已经打开的工作簿,如未打开则打开工作簿，记入dict中（是否可以打开不同路径的同名工作簿？本项目可以暂时不考虑）
# 是否存在于已经开开的工作表，不存在则记录（工作表名）
# 取值并写入替换到reportSheet中
'''处理器'''
class Dealer:
    def __init__(self,rptsht,cfgsht):
        l = cfgsht.range('A1').expand().value
        del l[0]
        lists = []
        for item in l:
            lists.append(SourceItem(item))
        self.items = lists

    def getcontent(self):
        lists = []
        for item in self.items:
            lists.append(item.getcontent())
        return ''.join(lists)

    '''一行一行处理'''
    def deal(self):
        for item in self.items:
            if not self.isexists(item):
                item.result = False
                item.resultmsg = ','.join( self.msg)
            else:
                if not hasattr(self,'app'):
                    self.app = xw.App(visible=True,add_book=False)
                self.dealPos(item)
        if hasattr(self,'app'):
            self.app.quit()


    '''路径、文件名是否存在于各表异常set中，如果存在，填入状态，（值）'''
    def isexists(self,item):
        self.msg = []

        if not hasattr(self,'pathset'):
            self.pathset = set()
        if  item.path in self.pathset:
            self.msg.append('路径不存在')
            return False
        if  not os.path.exists(item.path):
            self.pathset.add(item.path)
            self.msg.append('路径不存在')
            return False

        fullname = os.path.join(item.path,item.workbook)
        if  not hasattr( self,'wbset'):
            self.wbset = set()
        if fullname in self.wbset:
            self.msg.append('工作簿不存在')
            return False
        if  not os.path.exists(fullname):
            self.wbset.add(fullname)
            self.msg.append('工作簿不存在')
            return False
        return True

    '''处理工作簿、工作表对应单元格'''
    def dealPos(self,item):
        if not hasattr(self,'wbs'):
            self.wbs = {}

        currWb = None
        if item.workbook in self.wbs:
            currWb = self.wbs[item.workbook]
        else:
            fullname = os.path.join(item.path,item.workbook)
            currWb = self.app.books.open(fullname)
            self.wbs[item.workbook] = currWb

        if not hasattr(self,'noshts'):
            self.noshts = set()

        if item.worksheet in self.noshts:
            item.resultmsg = '无工作表'
        else:
            flag = False
            for sht in currWb.sheets:
                if sht.name == item.worksheet:
                    flag = True
                    break

            if not flag:
                self.noshts.add(item.worksheet)
                item.resultmsg = '无工作表'
                item.result = False
            else:
                item.value = currWb.sheets(item.worksheet).range(item.address).value
                rptsht.cells.api.replace(item.pos,item.value)

'''SourceItem'''
class SourceItem:
    '''SourceItem 构造方法'''
    def __init__(self,l):
        self.pos = l[0]
        self.path =l[1]
        self.workbook = l[2]
        self.worksheet = l[3]
        self.address = l[4]
        self.resultmsg =''
        self.result = True
        self.value = None

    '''SourceItem ToString'''
    def getcontent(self):
        tem = '''
        占位符: $pos
        路径: $path
        工作簿: $workbook
        工作表: $worksheet
        单元格: $address
        结果： $result
        错误信息: $resultmsg
        值: $value
        '''
        d = dict(pos=self.pos, path=self.path, workbook=self.workbook, worksheet=self.worksheet, address=self.address,resultmsg=self.resultmsg,result=self.result,value=self.value)
        return Template(tem).substitute(d)

if __name__ == '__main__':
    cfgfilename = r'C:\Users\Administrator\Desktop\py报表\配置.xlsx'
    reportfilename = getreportfilename(r'C:\Users\Administrator\Desktop\py报表\报表模板.xlsx')
    app = xw.App(visible=True,add_book=False)
    reportwb = app.books.open(reportfilename)
    reportsht = reportwb.sheets(1)

    cfgsht = app.books.open(cfgfilename).sheets(1)

    test = Dealer(reportsht,cfgsht)
    test.deal()

    print(test.getcontent())
    #app.quit()