一键式统计11.0列表资产负债表批量get

资产负债表批量get"/>

一键式统计11.0列表资产负债表批量get

# 需要进入的第三方库
# pip install pypiwin32
# openpyxl
# shutil
# pyinstaller# 更新了单元格中数值不填写的bug
# 不填写的数据，默认取值为空# coding:utf-8
import os
import win32com.client as win32
import shutil
import openpyxl# import time#####part one
import time
start = time.perf_counter()def makedir():mydir = 'bak'  # 基本逻辑就是分两种情况，文件夹有和无，有时，为了避免里面数据污染，删了重建一个；没有则是正常建一个就好if os.path.exists(mydir) == 0:os.mkdir(mydir)else:shutil.rmtree('bak')if os.path.exists(mydir) == 0:os.mkdir(mydir)def movedir():path = os.getcwd()  # 转换前程序所在文件目录path_bak = os.getcwd() + r'/bak/'pathdir_bak = os.listdir(path_bak)pathdir = os.listdir(path)# print(pathdir)for s in pathdir:newdir = os.path.join(path, s)if os.path.isfile(newdir):os.chdir(path_bak)pathdir_bak = os.listdir(path_bak)# print(pathdir_bak)if os.path.splitext(newdir)[1] == ".xls":t = os.path.split(s)[1] + r'x'if os.path.exists(t):#    print(os.path.exists(os.path.split(s)[1]))breakelse:excel = win32.gencache.EnsureDispatch('Excel.Application')wb = excel.Workbooks.Open(newdir)#   print(newdir)path_dir = os.path.split(newdir)[0]path_file = os.path.split(newdir)[1]#  print(path_dir)#   print(path_file)wb.SaveAs(path_dir + '/bak/' + path_file + 'x',FileFormat=51)  # FileFormat = 51 is for .xlsx extensionwb.Close()  # FileFormat = 56 is for .xls extensionexcel.Application.Quit()elif os.path.splitext(newdir)[1] == ".xlsx":file.append(newdir)elif os.path.isdir(newdir):continuefor i in range(len(file)):pcm_file = file[i]path_0 = os.path.split(file[i])[0]path_1 = os.path.split(file[i])[1]oldname = path_0 + '/' + path_1newname = path_0 + r'/bak/' + path_1shutil.copyfile(oldname, newname)# time.sleep(5)  #暂停10秒#####第二步将文件目录切换至bak文件夹中，，切片u v wdef getbaklist(path_deal):pathdir_deal = os.listdir(path_deal)# print(pathdir_deal)path_bak = os.getcwd()pathdir_bak = os.listdir(path_bak)# print(pathdir_bak)for u in pathdir_bak:newdir_deal = os.path.join(path_deal, u)file_deal.append(newdir_deal)# print(file_deal)# print(file_deal)# print(file_deal)def getbooksheet(path, filename):# print( path + r'/' + filename )wb = openpyxl.load_workbook(path + r'/' + filename)allSheets = wb.get_sheet_names()for i in range(len(allSheets)):sheet = wb.get_sheet_by_name(allSheets[i])for row in sheet.iter_rows():for cell in row:if str(str(cell.value).replace(' ', '')).find("资产负债表") != -1:sheet_selected = wb.get_sheet_by_name(allSheets[i])# print(sheet_selected)# print(path_1_deal)# print(path_0_deal)fn['资产负债表'] = path_1_deal# print(fn_fuzhaibiao)ws['资产负债表'] = allSheets[i]# print(type(ws['资产负债表']))# print(ws['资产负债表'])continueelif str(str(cell.value).replace(' ', '')).find("利润表") != -1 or str(str(cell.value).replace(' ', '')).find("损益表") != -1:sheet_selected = wb.get_sheet_by_name(allSheets[i])# print(sheet_selected)# print(path_1_deal)# path['利润表'] = path_0_dealfn['利润表'] = path_1_dealws['利润表'] = allSheets[i]continueelif str(str(cell.value).replace(' ', '')).find("一般纳税人适用") != -1 or str(str(cell.value).replace(' ', '')).find("小规模纳税人适用") != -1:sheet_selected = wb.get_sheet_by_name(allSheets[i])# print(sheet_selected)# print(path_1_deal)# path['纳税申报表'] = path_0_dealfn['纳税申报表'] = path_1_deal# print(path_1_deal)ws['纳税申报表'] = allSheets[i]continueelif str(str(cell.value).replace(' ', '')).find("***子表开始") != -1:sheet_selected = wb.get_sheet_by_name(allSheets[i])# print(sheet_selected)# print(path_1_deal)# path['财务状况表'] = path_0_dealfn['财务状况表'] = path_1_deal# print(fn['财务状况表'])ws['财务状况表'] = allSheets[i]# print(type(ws['资产负债表']))continuewb.close()def getdata(path, filename, worksheet, list):wb = openpyxl.load_workbook(path + r'/' + filename)ws = wb.get_sheet_by_name(worksheet)zichan_result = []for k in range(len(list)):getdata_result = 0row_result = 0row_column = 0column_result = 0column_temp = 0stop_row = 0stop_column = 0for row in ws.iter_rows():for cell in row:if stop_row == 1:breakelse:for i in range(len(list[k][0])):if str(str(cell.value).replace(' ', '')).find(list[k][0][i]) != -1:#print(zichan_list[k][0][i])# print(data_row[i])row_result = cell.row#print(row_result)column_temp = cell.columnstop_row = 1breakfor row in ws.iter_rows():for cell in row:if stop_column == 1:breakelse:for i in range(len(list[k][1])):if str(str(cell.value).replace(' ', '')).find(list[k][1][i]) != -1 and cell.column > column_temp:# print(data_column[i])column_result = cell.column#print(column_result)stop_column = 1break# print(row_result)# print(column_result)if row_result == 0 or column_result == 0:getdata_result = 0else:if ws.cell(row=row_result, column=column_result).value != None:# print(ws_source.cell(row=row_result, column=column_result).value)getdata_result = ws.cell(row=row_result, column=column_result).value#print(getdata_result)zichan_result.append(getdata_result)print(zichan_result)#print(getdata_result)wb.close()return zichan_resultif __name__ == '__main__':file = []file_deal = []fn = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}ws = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0}makedir()movedir()getbaklist(os.getcwd())for i in range(len(file_deal)):pcm_file_deal = file_deal[i]path_0_deal = os.path.split(file_deal[i])[0]path_1_deal = os.path.split(file_deal[i])[1]# print(path_0_deal)# print(path_1_deal)getbooksheet(path_0_deal, path_1_deal)# print(fn)# print(ws)#    zichan_list = [[['存货'], ['年初余额', '年初数', '期初余额']],[['流动资产合计'], ['期末余额', '期末数']],[['应收账款'], ['期末余额', '期末数']],[['存货'], ['期末余额', '期末数']].[['固定资产原价'], ['期末余额', '期末数']],[['无形资产'],['期末余额','期末数']],[['累计折旧'], ['期末余额', '期末数']],[['资产总计'], ['期末余额', '期末数']],[['流动负债合计'], ['期末余额', '期末数']],[['应付账款'], ['期末余额', '期末数']],[['负债合计'], ['期末余额', '期末数']],[['所有者权益合计'], ['期末余额', '期末数']],[['实收资本'], ['期末余额', '期末数']],[['个人资本'],['期末余额','期末数']]]zichan_list = [[['存货'], ['年初余额', '年初数', '期初余额']],[['流动资产合计'], ['期末余额', '期末数']],[['应收账款'], ['期末余额', '期末数']],[['存货'], ['期末余额', '期末数']],[['固定资产原价'], ['期末余额', '期末数']],[['无形资产'],['期末余额','期末数']],[['累计折旧'], ['期末余额', '期末数']],[['资产总计'], ['期末余额', '期末数']],[['流动负债合计'], ['期末余额', '期末数']],[['应付账款'], ['期末余额', '期末数']],[['负债合计'], ['期末余额', '期末数']],[['所有者权益合计'], ['期末余额', '期末数']],[['实收资本'], ['期末余额', '期末数']],[['个人资本'],['期末余额','期末数']]]print(getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'],zichan_list))end = time.perf_counter()print("运行耗时", end - start)