详细描述将在稍后编辑。
以下适用于NC科目余额表
from openpyxl import Workbook
from openpyxl import load_workbook
import glob
import time
row_write = 1
col_write = 1
timenow = time.strftime('%y%m%d%H%M', time.localtime())
outfile = 'C:\\testout\\output' + timenow + '.xlsx'
wbwrite = Workbook()
wswmain = wbwrite.active
wswmain.title = '科目余额表'
wswlog = wbwrite.create_sheet(title = 'Log')
wswlog.cell(row = 1, column = 1).value = 'Name'
row_log = 2
row_write += 1
stop_sign = ['总计', 'SMCN']
files = glob.glob('*.xlsx')
for infile in files:
print('reading ' + infile)
obtained = False
col_write = 1
name = infile.split('-')[0]
wbread = load_workbook(infile)
for i in range(len(wbread.sheetnames)):
wsread = wbread.worksheets[i]
row_read = 1
col_read = 1
while row_read < 11:
if wsread.cell(row = row_read, column = col_read).value == '科目编码' and wsread.cell(row = row_read, column = 10).value == '期末余额':
wswlog.cell(row = row_log, column = 1).value = name
row_log += 1
row_read += 1
while wsread.cell(row = row_read, column = col_read).value != None and not wsread.cell(row = row_read, column = col_read).value in stop_sign:
obtained = True
wswmain.cell(row = row_write, column = 1).value = name
for col_read in range(1, 11):
corig = wsread.cell(row = row_read, column = col_read)
cdest = wswmain.cell(row = row_write, column = col_read + 1)
if col_read == 4 or col_read == 10:
if '贷' in wsread.cell(row = row_read, column = col_read - 1).value:
cdest.value = -corig.value
else:
cdest.value = corig.value
elif col_read == 6 or col_read == 8:
if corig.value != None:
cdest.value = -corig.value
else:
cdest.value = corig.value
else:
cdest.value = corig.value
row_read += 1
row_write += 1
col_read = 1
break
row_read += 1
if obtained == False and '科目余额表' in infile:
print('ignoring ' + infile)
wbwrite.save(filename = outfile)
wbwrite.close()
以下适用于SAP科目余额表
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()
发表评论