#coding:utf-8 # import os import win32com.client as win32 import shutil import openpyxl import os import win32com.client as win32 import shutil import openpyxl import time import time #####part one def makedir(): mydir = 'bak' # 基本逻辑就是分两种情况,文件夹有和无,有时,为了避免里面数据污染,删了重建一个;没有则是正常建一个就好 if os.path.exists(mydir) == 0: os.mkdir(mydir) else: shutil.rmtree('bak') os.mkdir(mydir) def movedir(): file = [] path = os.getcwd() # 转换前程序所在文件目录 path_bak = os.getcwd() + r'/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])) break else: 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 extension wb.Close() # FileFormat = 56 is for .xls extension excel.Application.Quit() elif os.path.splitext(newdir)[1] == ".xlsx": file.append(newdir) elif os.path.isdir(newdir): continue for 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_1 newname = path_0 + r'/bak/' + path_1 shutil.copyfile(oldname, newname) # time.sleep(5) #暂停10秒 #####第二步将文件目录切换至bak文件夹中,,切片u v w file_deal = [] path_deal = os.getcwd() # 转换前程序所在文件目录 pathdir_deal = os.listdir(path_deal) for u in pathdir_bak: newdir_deal = os.path.join(path_deal, u) file_deal.append(newdir_deal) # print(file_deal) for j in range(len(file_deal)): pcm_file_deal = file_deal[j] path_0_deal = os.path.split(file_deal[j])[0] # 文件所在目录 path_1_deal = os.path.split(file_deal[j])[1] # 文件名称带后缀 # print(pcm_file_deal) # print(path_0_deal) # print(path_1_deal) wb = openpyxl.load_workbook(path_1_deal) 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['资产负债表']) continue elif 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_deal fn['利润表'] = path_1_deal ws['利润表'] = allSheets[i] continue elif 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_deal fn['纳税申报表'] = path_1_deal #print(path_1_deal) ws['纳税申报表'] = allSheets[i] continue elif 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_deal fn['财务状况表'] = path_1_deal print(fn['财务状况表']) ws['财务状况表'] = allSheets[i] # print(type(ws['资产负债表'])) continue ################part two def getdata( path, filename,worksheet,data_row,data_column): #print( path + r'/' + filename ) wb = openpyxl.load_workbook( path + r'/' + filename) #print(wb) ws = wb.get_sheet_by_name(worksheet) getdata_result = 0 row_result = 0 row_column = 0 for row in ws.iter_rows(): for cell in row: for i in range(len(data_row)): #print(cell.value) #print(date_row[i]) #print(str(str(cell.value).replace(' ', '')).find(date_row[i])) #print(cell.value) #print(len(str(cell.value).replace(' ', ''))) #print(len(str(cell.value).replace(' ', ''))) #print(len(date_row[i])) if str(str(cell.value).replace(' ', '')).find(data_row[i]) != -1 : #print(len(str(cell.value).replace(' ', ''))) #print(len(date_row[i])) #print(str(str(cell.value).replace(' ', '')).find(date_row[i])) row_result = cell.row column_result = cell.column + 3 #print(cell.value) #print(cell.row) #print(row_result) break if row_result == 0 or column_result == 0: getdata_result = 0 else: 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 wb.close() return getdata_result def writedata( path, filename,worksheet,data_row,data_column,data_write): #print( path + r'/' + filename ) wb = openpyxl.load_workbook( path + r'/' + filename) #print(wb) ws = wb.get_sheet_by_name(worksheet) row_result = 0 column_result = 0 for row in ws.iter_rows(): for cell in row: #print(cell.value) for i in range(len(data_row)): #print(cell.value) #print(date_row[i]) #print(str(str(cell.value).replace(' ', '')).find(date_row[i])) #print(cell.value) #print(len(str(cell.value).replace(' ', ''))) #print(len(str(cell.value).replace(' ', ''))) #print(len(date_row[i])) if str(str(cell.value).replace(' ', '')).find(data_row[i]) != -1 : #print(len(str(cell.value).replace(' ', ''))) #print(len(date_row[i])) #print(str(str(cell.value).replace(' ', '')).find(date_row[i])) row_result = cell.row print(row_result) column_result = cell.column + 3 print(column_result) #print(cell.value) #print(cell.row) #print(row_result) break print(data_write) ws.cell(row=row_result, column=column_result).value = data_write wb.save(filename) wb.close if __name__ == '__main__': fn = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0} ws = {'资产负债表': 0, '利润表': 0, '纳税申报表': 0, '财务状况表': 0} makedir() movedir() print(fn) print(ws) print(getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['存货'], ['年初余额', '年初数'])) ###写入操作 # 季报/年报---年初存货 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['年初存货'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['存货'], ['年初余额', '年初数'])) # 季报/年报---流动资产合计 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['流动资产合计'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['流动资产合计'], ['期末余额', '期末数'])) # 季报/年报---应收账款 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['应收账款'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['应收账款'], ['期末余额', '期末数'])) # 季报/年报---存货 期末数 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['存货'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['存货'], ['期末余额', '期末数'])) # 季报/年报---固定资产原价 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['固定资产原价'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['固定资产原价'], ['期末余额', '期末数'])) # 年报---房屋和构筑物 # 年报---机器设备 # writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['无形资产'],['1-本季','本年'],getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'],['无形资产'],['期末余额','期末数'])) # 季报/年报---累计折旧 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['累计折旧'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['累计折旧'], ['期末余额', '期末数'])) # 季报/年报---本年折旧 # 年报---固定资产净额 # 年报---在建工程 # 年报---无形资产 # 年报---土地使用权 # 季报---资产总计 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['资产总计'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['资产总计'], ['期末余额', '期末数'])) # 季报/年报---流动负债合计 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['流动负债合计'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['流动负债合计'], ['期末余额', '期末数'])) ''' # 年报---应付账款 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['应付账款'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['应付账款'], ['期末余额', '期末数'])) # 季报/年报---负债合计 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['负债合计'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['负债合计'], ['期末余额', '期末数'])) # 年报---所有者权益合计 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['所有者权益合计'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['所有者权益合计'], ['期末余额', '期末数'])) # 年报---实收资本 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['实收资本'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'], ['实收资本'], ['期末余额', '期末数'])) # 年报---个人资本 # writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['个人资本'],['1-本季','本年'],getdata(os.getcwd() + r'/', fn['资产负债表'], ws['资产负债表'],['个人资本'],['期末余额','期末数'])) # 季报/年报---营业收入 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['营业收入'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['营业收入'], ['本年累计数', '本年累计金额'])) # 年报---主营业务收入 # 季报/年报---营业成本 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['营业成本'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['营业成本'], ['本年累计数', '本年累计金额'])) # 季报---税金及附加 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['税金及附加'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['税金及附加'], ['本年累计数', '本年累计金额'])) # 季报---销售费用 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['销售费用'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['销售费用'], ['本年累计数', '本年累计金额'])) # 季报---管理费用 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['管理费用'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['管理费用'], ['本年累计数', '本年累计金额'])) # 季报---研发费用 # writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['研发费用'],['1-本季','本年'],getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'],['研究费用','研发费用'],['本年累计数','本年累计金额'])) # 季报---财务费用 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['财务费用'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['财务费用'], ['本年累计数', '本年累计金额'])) # 季报---利息收入 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['利息收入'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['利息费用'], ['本年累计数', '本年累计金额'])) # 季报---利息费用 # writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['利息费用'],['1-本季','本年'],getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'],['利息费用'],['本年累计数','本年累计金额'])) # 季报---营业利润 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['营业利润'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['营业利润'], ['本年累计数', '本年累计金额'])) # 投资收益 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['投资收益'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['投资收益'], ['期末余额', '期末数'])) # 季报---营业外收入 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['营业外收入'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['营业外收入'], ['本年累计数', '本年累计金额'])) # 季报---营业外支出 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['营业外支出'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['营业外支出'], ['本年累计数', '本年累计金额'])) # 季报---利润总额 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['利润总额'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['利润总额'], ['本年累计数', '本年累计金额'])) # 季报---所得税费用 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['所得税费用'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['利润表'], ws['利润表'], ['所得税费用'], ['本年累计数', '本年累计金额'])) # 季报---应交增值税= 销项税额 -(进项税额 - 进项税额转出 - 免、抵、退应退税额)+ 简易计税办法计算的应纳税额 + 按简易计税办法计算的纳税检查应补缴税额 - 应征税额减征额 writedata(os.getcwd() + r'/', fn['财务状况表'], ws['财务状况表'], ['应交增值税'], ['1-本季', '本年'], getdata(os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['销项税额'], ['本年累计', '累计金额']) - ( getdata(os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['进项税额'], ['本年累计', '累计金额']) - getdata( os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['进项税额转出'], ['本年累计', '累计金额']) - getdata( os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['简易计税办法计算的应纳税额'], ['本年累计', '累计金额'])) + getdata( os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['按简易计税办法计算的纳税检查应补缴税额'], ['本年累计', '累计金额']) - getdata( os.getcwd() + r'/', fn['纳税申报表'], ws['纳税申报表'], ['应征税额减征额'], ['本年累计', '累计金额'])) # 季报---从事批发和零售业活动的从业人员平均人数 '''
季报年报待更新0222版
最新推荐文章于 2021-07-16 01:10:10 发布
该代码实现从当前工作目录读取.xls和.xlsx文件,将它们备份并修改后缀为.xlx,然后通过查找特定字符串来定位财务报表的特定表格,如资产负债表、利润表等。接着,从这些表格中提取数据,并进行写入操作,例如将资产负债表的数据填入财务状况表的相关单元格。整个过程涉及文件操作、Excel读写和数据迁移。
摘要由CSDN通过智能技术生成