Forev3rNAlway5
INTP / UvA / Accountancy
SJH.MOE
使用Python快速合并科目余额表

详细描述将在稍后编辑。

from openpyxl import Workbook
from openpyxl import load_workbook
import os
import glob
import time

timenow = time.strftime('%y%m%d%H%M', time.localtime())

outfile = "C:\\shimao conso spreadsheet\\testout\\output" + timenow + ".xlsx"
wbwrite = Workbook()
wswmain = wbwrite.active
wswmain.title = '科目余额表'

wswexcp = wbwrite.create_sheet(title = '格式异常')

wswlog = wbwrite.create_sheet(title = 'Log')

momain = 1
moexcp = 1
molog = 1

wswlog.cell(row = molog, column = 1).value = 'code'
wswlog.cell(row = molog, column = 2).value = 'name'
wswlog.cell(row = molog, column = 3).value = 'status'
wswlog.cell(row = molog, column = 4).value = 'duplicate?'
molog += 1

target = '其他应收款'
title = ['公司代码', '公司名称', '一级科目', '一级科目(描述)', '明细科目', '明细科目(描述)', '期初方向', '期初余额', '本期借方', '本期贷方', '借方累计', '贷方累计', '期末方向', '期末余额']
title_must = ['一级科目(描述)', '明细科目(描述)', '期初方向', '期初余额', '本期借方', '本期贷方', '借方累计', '贷方累计', '期末方向', '期末余额']

for lc in range(len(title)):
    wswmain.cell(row = momain, column = lc + 1).value = title[lc]
    wswexcp.cell(row = momain, column = lc + 1).value = title[lc]
momain += 1
moexcp += 1

mc = len(title)

dirs = os.listdir(path='.')
for q in dirs:
    if os.path.isdir(q):
        times = 0
        code = q.split(' ')[0]
        name = q.split(' ')[1]
        a = glob.glob(q + '/*.xlsx', recursive=True)
        for infile in a:
            contain = False
            print(f'Reading {infile}')
            if '~$' in infile:
                continue
            wbread = load_workbook(infile)
            sheets = wbread.sheetnames
            for i in range(len(sheets)):
                if '港方科目' in sheets[i]:
                    contain = True
                    times += 1
                    wsread = wbread.worksheets[i]
                    mi = 1
                    titletest = True
                    for col in range(mc):
                        titletest = titletest and (wsread.cell(row = 1, column = col + 1).value == title[col])
                    if titletest:
                        excp = 0
                        while wsread.cell(row = mi , column = 1).value != None:
                            account = wsread.cell(row = mi , column = 4).value
                            if account == target:
                                for n in range(1, mc + 1):
                                    corig = wsread.cell(row = mi, column = n)
                                    cdest = wswmain.cell(row = momain, column = n)
                                    cdest.value = corig.value
                                if times > 1:
                                    wswmain.cell(row = momain, column = mc + 1).value = '!!DUPLICATE!!'
                                momain += 1
                            mi += 1
                    else:
                        titlen = 1
                        contain_must = True
                        wsreadtitle_list = []
                        wsreadtitle_dic = {}
                        title_item = wsread.cell(row = 1, column = titlen).value
                        while title_item != None:
                            
                            wsreadtitle_dic[title_item] = titlen
                            wsreadtitle_list.append(title_item)
                            titlen += 1
                            title_item = wsread.cell(row = 1, column = titlen).value
                        
                        for title_must_item in title_must:
                            title_tmp = False
                            for title_item in wsreadtitle_list:
                                title_tmp = title_tmp or (title_item == title_must_item)
                            contain_must = contain_must and title_tmp
                        
                        if contain_must:
                            excp = 2
                            while wsread.cell(row = mi , column = 1).value != None or wsread.cell(row = mi , column = 2).value != None or wsread.cell(row = mi , column = 3).value != None:

                                if target == wsread.cell(row = mi, column = wsreadtitle_dic['一级科目(描述)']).value:
                                    wswmain.cell(row = momain, column = 1).value = code
                                    wswmain.cell(row = momain, column = 2).value = name
                                    for item in title_must:
                                        corig = wsread.cell(row = mi, column = wsreadtitle_dic[item])
                                        cdest = wswmain.cell(row = momain, column = title.index(item) + 1)
                                        cdest.value = corig.value
                                    if times > 1:
                                        wswmain.cell(row = momain, column = mc + 1).value = '!!DUPLICATE!!'
                                    momain += 1
                                mi += 1
                        else:
                            excp = 1
                            wswexcp.cell(row = moexcp, column = 1).value = infile
                            nonetimes = 0
                            n = 1
                            while nonetimes < 4:
                                corig = wsread.cell(row = mi, column = n)
                                cdest = wswexcp.cell(row = moexcp, column = n + 1)
                                cdest.value = corig.value
                                if corig.value == None:
                                    nonetimes += 1
                                n += 1
                            if times > 1:
                                wswexcp.cell(row = moexcp, column = n + 1).value = '!!DUPLICATE!!'
                            moexcp += 1
                            mi += 1
                            while wsread.cell(row = mi , column = 1).value != None or wsread.cell(row = mi , column = 2).value != None or wsread.cell(row = mi , column = 3).value != None:
                                ntest = 1
                                while ntest < 20:
                                    if target in str(wsread.cell(row = mi , column = ntest).value):
                                        wswexcp.cell(row = moexcp, column = 1).value = infile
                                        n = 1
                                        nonetimes = 0
                                        while nonetimes < 4:
                                            corig = wsread.cell(row = mi, column = n)
                                            cdest = wswexcp.cell(row = moexcp, column = n + 1)
                                            cdest.value = corig.value
                                            if corig.value == None:
                                                nonetimes += 1
                                            n += 1
                                            if times > 1:
                                                wswexcp.cell(row = moexcp, column = n + 1).value = '!!DUPLICATE!!'
                                        moexcp += 1
                                        break
                                    ntest += 1
                                mi += 1
                wbread.close()
            wbwrite.save(filename = outfile)
            if contain:
                print(f'Done    {infile}')

        status = ''
        dupe = ''
        if excp == 1 and times > 1:
            status = 'Format ERROR. Data imported in Sheet "格式异常"'
            dupe = 'Duplicate'
        if excp == 0 and times > 1:
            status = 'Success'
            dupe = 'Duplicate'
        if excp == 2 and times > 1:
            status = 'Only compulsory data imported'
            dupe = 'Duplicate'
        if times == 0:
            status = 'NOT FOUND'
        if excp == 1 and times == 1:
            status = 'Format ERROR. Data imported in Sheet "格式异常"'
        if excp == 0 and times == 1:
            status = 'Success'
        if excp == 2 and times == 1:
            status = 'Only compulsory data imported'
        
        wswlog.cell(row = molog, column = 1).value = code
        wswlog.cell(row = molog, column = 2).value = name
        wswlog.cell(row = molog, column = 3).value = status
        wswlog.cell(row = molog, column = 4).value = dupe
        molog += 1
        wbwrite.save(filename = outfile)
wbwrite.close()
赞赏
本文链接:https://sjh.moe/pwclife/2298
本文采用 CC BY-NC-SA 4.0 协议进行许可
首页      代码      使用Python快速合并科目余额表

发表评论

textsms
account_circle
email

SJH.MOE

使用Python快速合并科目余额表
详细描述将在稍后编辑。
扫描二维码继续阅读
2021-01-31