import os
import os.path
import sys
import xml.etree.ElementTree as XETree
from openpyxl import load_workbook
import shutil

sourceExcel = None
destExcel = None


def extractForTable(cfgItem):
    endrow = 0
    beginrow = 0
    # sheet_names = sourceExcel.get_sheet_names()
    sheet = sourceExcel["Sheet1"]

    # sheet_names = destExcel.get_sheet_names()
    destSheet = destExcel["Sheet1"]
    # 判断在源文件中的起始行
    tempcell = findStr(sheet, cfgItem[0].attrib["anchor"], 1)
    beginrow = tempcell.row + int(cfgItem[0].attrib["skiprows"]) + 1

    destBeginRow = int(cfgItem[1].attrib["beginrow"])
    # 判断结束行
    # 如果范围已经确定，直接确定结束行
    if cfgItem[0].attrib["range"] != "":
        endrow = beginrow + int(cfgItem[0].attrib["range"]) - 1
    else:
        # 范围不确定，根据下一行字符确定结束行
        if cfgItem[0].attrib["anchorend"] != "":
            endrow = findStr(sheet, cfgItem[0].attrib["anchorend"], beginrow).row
        else:
            # 差找不到字符，则直到最后一个不为空行的为止
            limited = 1
            while sheet["A" + str(beginrow + limited)].value != "" and sheet["A" + str(beginrow + limited)].value is not None:
                limited += 1
            endrow = beginrow + limited - 1
    sList = cfgItem[0].attrib["cols"].split(',')
    dList = cfgItem[1].attrib["cols"].split(',')

    # 粘贴数据
    for row in range(beginrow, endrow + 1):
        for col in range(0, len(sList)):
            destSheet[dList[col] + str(destBeginRow + row - beginrow)].value = sheet[sList[col] + str(row)].value


def findStr(sheet, str, startRow):
    for row in sheet.iter_rows(min_row=startRow):
        for cell in row:
            if cell.value is not None and cell.value != "":
                if str in cell.value:
                    return cell


def extractForKeyValue(cfgItem):
    print('KeyValue')


def selectType(cfgItems):
    for i in range(len(cfgItems)):
        cfgItem = cfgItems[i]
        istable = cfgItem.attrib['istable'].lower()
        if istable == 'true':
            extractForTable(cfgItem)
        else:
            extractForKeyValue(cfgItem)


def main():
    global DATANOTFOUND
    global cdfp
    global clws
    global cwb
    global writeLog
    global sourceExcel
    global destExcel
    config_xml = r'''
    <mapping input="C:\Users\HHH\Documents\和享2016年第一期个人消费贷款资产证券化信托受托机构报告2016年第2期总第2期.xlsx"
             output = "C:\Users\HHH\Documents\HHH\和享2016年第一期个人消费贷款资产证券化信托受托机构报告2016年第2期总第2期.xlsx"
             template="C:\Users\HHH\Documents\HHH\受托报告导入模板.xlsx" writelog="0">

		<item desc="资产池整体表现情况" istable="true" keyword = "资产池整体表现情况">
            <source   anchor="资产池整体表现情况" skiprows="1" range = "7" anchorend="" cols="A,B,C,D,E" > </source>
            <dest limited="7" beginrow="24" cols="A,B,C,D,E"></dest>
        </item>
		<item desc="证券分层" istable="true" keyword = "证券分层">
            <source   anchor="各档次证券的本金、" skiprows="1" range = "" anchorend="" cols="C,D,E" > </source>
            <dest limited="12" beginrow="8" cols="C,D,F"></dest>
        </item>
		<item desc="证券分层" istable="true" keyword = "证券分层">
            <source   anchor="利息还款情况（续）" skiprows="1" range = "" anchorend="" cols="C" > </source>
            <dest limited="12" beginrow="8" cols="E"></dest>
        </item>
		<item desc="资产池现金流归集表" istable="true" keyword = "资产池现金流归集表">
            <source   anchor="资产池现金流归集表" skiprows="1" range = "" anchorend="" cols="A,B,C,D" > </source>
            <dest limited="431" beginrow="35" cols="A,B,C,D"></dest>
        </item>
		<item desc="累计违约率" istable="true" keyword = "累计违约率">
            <source   anchor="资产池情况 (四）累计违约率" skiprows="1" range = "" anchorend="" cols="C" > </source>
            <dest limited="421" beginrow="24" cols="V"></dest>
        </item>
		<item desc="入池资产户數与金额特征" istable="true" keyword = "入池资产户數与金额特征">
            <source   anchor="入池资产户數与金额特征" skiprows="1" range = "5" anchorend="入池资产期限特征" cols="B,C,D" > </source>
            <dest limited="5" beginrow="24" cols="P,Q,R"></dest>
        </item>
		<item desc="入池资产期限特征" istable="true" keyword = "入池资产期限特征">
            <source   anchor="入池资产期限特征" skiprows="1" range = "5" anchorend="入池资产期限特征" cols="B,C,D" > </source>
            <dest limited="5" beginrow="32" cols="P,Q,R"></dest>
        </item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="收 入 账;合计"  cols="E" > </source>
			<dest cols = "M33" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="本 金 账;合计"  cols="E" > </source>
			<dest cols = "M43" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="税费支 出;税收"  cols="E" > </source>
			<dest cols = "M45" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="税费支 出;服务总费用支出"  cols="E" > </source>
			<dest cols = "M46" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="税费支 出;其他费用支出"  cols="E" > </source>
			<dest cols = "M47" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="持续购买资产支出（注1)"  cols="E" > </source>
			<dest cols = "M48" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="转存下期本金"  cols="E" > </source>
			<dest cols = "M49" datatype ="F"></dest>
		</item>
		<item desc="资产池情况" istable="false" >
			<source  anchor="本 金 账;合计"  cols="E" > </source>
			<dest cols = "M50" value = "0" datatype ="F"></dest>
		</item>
    </mapping>
    '''
    cfgRoot = XETree.fromstring(config_xml)
    input = cfgRoot.attrib['input']
    output = cfgRoot.attrib['output']
    templateFilePath = cfgRoot.attrib['template']

    if os.path.exists(output):
        os.remove(output)
    shutil.copy(templateFilePath, output)

    sourceExcel = load_workbook(input)
    destExcel = load_workbook(output)

    selectType(cfgRoot)
    destExcel.save(output)


main()