#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_fuzhaibiao)
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)
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(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['纳税申报表'],['应征税额减征额'],['本年累计', '累计金额']))
#从事批发和零售业活动的从业人员平均人数
一键式统计6.0
最新推荐文章于 2022-11-23 20:48:33 发布

121

被折叠的 条评论
为什么被折叠?



