税务会计一键式统计2.0_csdn jidawanghao countif-优快云博客

from openpyxl import load_workbook

‘’’
@File :
@Author : william
@Time : 2020/09/29
@notice ： null
@coding : utf-8
‘’’
import xlrd
from xlutils.copy import copy
import openpyxl

下一步还得训练程序

#使其满足对excel文件名和excel工作薄的识别

ws_fuzhaibiao_D13=’’
ws_fuzhaibiao_C19=’’
ws_fuzhaibiao_C8=’’
ws_fuzhaibiao_C13=’’
ws_fuzhaibiao_C23=’’
ws_fuzhaibiao_C24=’’
ws_fuzhaibiao_C35=’’
ws_fuzhaibiao_G26=’’

ws_lirunbiao_C4=’’
ws_lirunbiao_C5=’’
ws_lirunbiao_C6=’’
ws_lirunbiao_C14=’’
ws_lirunbiao_C24=’’
ws_lirunbiao_C25=’’
ws_lirunbiao_C27=’’
ws_lirunbiao_C33=’’
ws_lirunbiao_C34=’’
ws_lirunbiao_C22=’’

ws_nashuishenbaobiao_Z20=0
ws_nashuishenbaobiao_Z21=0
ws_nashuishenbaobiao_Z23=0
ws_nashuishenbaobiao_Z24=0
ws_nashuishenbaobiao_Z30=0
ws_nashuishenbaobiao_Z31=0
ws_nashuishenbaobiao_Z32=0

cunhuo_row = 0
cunhuo_column = 0

zichanheji_row = 0
zichanheji_column = 0

zichanyuanjia_row = 0
zichanyuanjia_column = 0

zhejiu_row = 0
zhejiu_column = 0

zichanzongji_row = 0
zichanzongji_column = 0

zuzhaiheji_row = 0
zuzhaiheji_column = 0

yingyeshouru_row = 0
yingyeshouru_column = 0

yingyechengben_row = 0
yingyechengben_column = 0

fujia_row = 0
fujia_column = 0

xiaoshoufeiyong_row = 0
xiaoshoufeiyong_column = 0

guanlifeiyong_row = 0
guanlifeiyong_column = 0

yanfafeiyong_row = 0
yanfafeiyong_column = 0

caiwufeiyong_row = 0
caiwufeiyong_column = 0

yingyelirun_row = 0
cunhuo_column = 0

yingyewaishouru_row = 0
yingyewaishouru_column = 0

yingyewaizhichu_row = 0
yingyewaizhichu_column = 0

lirunzonge_row = 0
lirunzonge_column = 0

suodeshuifeiyong_row = 0
suodeshuifeiyong_column = 0

yingjiaozengzhishui_row = 0
yingjiaozengzhishui_column = 0

pingjunrenshu_row = 0
pingjunrenshu_column = 0

xiaoxiangshuie_row = 0
xiaoxiangshuie_column = 0

jinxiangshuie_row = 0
jinxiangshuie_column = 0

jinxiangshuiezhuanchu_row = 0
jinxiangshuiezhuanchu_column = 0

yingtuishuie_row = 0
yingtuishuie_column = 0

yingnashuie_row = 0
yingnashuie_column = 0

yingbujiaoshuie_row = 0
yingbujiaoshuie_column = 0

yingnashuiejianzhenge_row = 0
yingnashuiejianzhenge_column = 0

fuzhaiheji_row = 0
fuzhaiheji_column = 0

#如下代码用于多个相关表的打开操作
fn_fuzhaibiao = ‘资产负债表.xlsx’
wb_fuzhaibiao = openpyxl.load_workbook(fn_fuzhaibiao)
ws_fuzhaibiao = wb_fuzhaibiao.get_sheet_by_name(‘资产负债表’)

fn_lirunbiao = ‘利润表.xlsx’
wb_lirunbiao = openpyxl.load_workbook(fn_lirunbiao)
ws_lirunbiao = wb_lirunbiao.get_sheet_by_name(‘利润表’)

fn_caiwuzhuangkuangbiao = ‘财务状况表.xlsx’
wb_caiwuzhuangkuangbiao = openpyxl.load_workbook(fn_caiwuzhuangkuangbiao)
ws_caiwuzhuangkuangbiao = wb_caiwuzhuangkuangbiao.get_sheet_by_name(‘财务状况表’)

fn_nashuishenbaobiao = ‘纳税申报表.xlsx’
wb_nashuishenbaobiao = openpyxl.load_workbook(fn_nashuishenbaobiao)
ws_nashuishenbaobiao = wb_nashuishenbaobiao.get_sheet_by_name(‘纳税申报表’)

#如下代码用于将多个表中的相关数据写入财务状况表

all_sheets_fuzhaibiao = wb_fuzhaibiao.get_sheet_names()

sheet_fuzhaibiao = wb_fuzhaibiao.get_sheet_by_name(all_sheets_fuzhaibiao[0])
########### 资产负债表年初存货

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(str(cell.value).replace(’ ', ‘’)).find(“存货”) != -1:
cunhuo_row = cell.row
print(cunhuo_row)
break
for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
cunhuo_column = cell.column
print(cunhuo_column)
break

print(sheet_fuzhaibiao.cell(row = cunhuo_row, column = cunhuo_column).value)
ws_fuzhaibiao_D13 = sheet_fuzhaibiao.cell(row = cunhuo_row, column = cunhuo_column).value

########### 资产负债表流动资产合计

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“流动资产合计”) != -1 and str(cell.value).find(“非流动资产合计”) == -1 :
zichanheji_row = cell.row
print(zichanheji_row)
break
for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
zichanheji_column = cell.column
print(zichanheji_column)
break

ws_fuzhaibiao_C19=sheet_fuzhaibiao.cell(row=zichanheji_row,column=zichanheji_column).value

########### 资产负债表固定资产原价

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“应收账款”) != -1 :
zichanyuanjia_row = cell.row
print(zichanyuanjia_row)
break
for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
zichanyuanjia_column = cell.column
print(zichanyuanjia_column)
break

ws_fuzhaibiao_C8=sheet_fuzhaibiao.cell(row=zichanyuanjia_row,column=zichanyuanjia_column).value

########### 资产负债表累计折旧

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“应收账款”) != -1 :
zhejiu_row = cell.row
print(zhejiu_row)
break
for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
zhejiu_column = cell.column
print(zhejiu_column)
break

ws_fuzhaibiao_C24=sheet_fuzhaibiao.cell(row=zhejiu_row,column=zhejiu_column).value

########### 资产负债表资产总计

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:

    if str(str(cell.value).replace(' ','')).find("资产总计") != -1  :

        zichanzongji_row = cell.row
        print(zichanzongji_row)
        break

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
zichanzongji_column = cell.column
print(zichanzongji_column)
break

ws_fuzhaibiao_C35=sheet_fuzhaibiao.cell(row=zichanzongji_row,column=zichanzongji_column).value

########### 资产负债表负债合计

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:

    if str(str(cell.value).replace(' ','')).find("负债合计")  != -1 and str(cell.value).find("流动负债合计")  == -1 and str(cell.value).find("非流动负债合计")  == -1  :

        fuzhaiheji_row = cell.row
        print(fuzhaiheji_row)
        break

for row in sheet_fuzhaibiao.iter_rows():
for cell in row:
if str(cell.value).find(“期末余额”) != -1 or str(cell.value).find(“期末数”) != -1 :
fuzhaiheji_column = cell.column
print(fuzhaiheji_column)
break

ws_fuzhaibiao_G26=sheet_fuzhaibiao.cell(row = fuzhaiheji_row , column = fuzhaiheji_column).value

all_sheets_lirunbiao = wb_lirunbiao.get_sheet_names()

sheet_lirunbiao = wb_lirunbiao.get_sheet_by_name(all_sheets_lirunbiao[0])

########### 利润表营业收入

for row in sheet_lirunbiao.iter_rows():

for cell in row:
    if str(cell.value).find("营业收入") != -1   and str(cell.value).find("主营业务收入") == -1 :

        yingyeshouru_row = cell.row
        print(yingyeshouru_row)
        break

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yingyeshouru_column = cell.column
print(yingyeshouru_column)
break

ws_lirunbiao_C4=sheet_lirunbiao.cell(row=yingyeshouru_row,column=yingyeshouru_column).value

########### 利润表营业成本

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“营业成本”) != -1 :
yingyechengben_row = cell.row
print(yingyechengben_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yingyechengben_column = cell.column
print(yingyechengben_column)
break

ws_lirunbiao_C5=sheet_lirunbiao.cell(row=yingyechengben_row,column=yingyechengben_column).value

########### 利润表税金及附加

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“税金及附加”) != -1 or str(cell.value).find(“主营业务税金及附加”) != -1 :
fujia_row = cell.row
print(fujia_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
fujia_column = cell.column
print(fujia_column)
break

ws_lirunbiao_C6=sheet_lirunbiao.cell(row=fujia_row,column=fujia_column).value

########### 利润表销售费用

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“销售费用”) != -1 or str(cell.value).find(“营业费用”) != -1 :
xiaoshoufeiyong_row = cell.row
print(xiaoshoufeiyong_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
xiaoshoufeiyong_column = cell.column
print(xiaoshoufeiyong_column)
break

ws_lirunbiao_C14=sheet_lirunbiao.cell(row=xiaoshoufeiyong_row,column=xiaoshoufeiyong_column).value

########### 利润表管理费用

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“管理费用”) != -1 :
guanlifeiyong_row = cell.row
print(guanlifeiyong_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
guanlifeiyong_column = cell.column
print(guanlifeiyong_column)
break

ws_lirunbiao_C17=sheet_lirunbiao.cell(row=guanlifeiyong_row,column=guanlifeiyong_column).value

########### 利润表研发费用研究费用?

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“研究费用”) != -1 or str(cell.value).find(“研发费用”) != -1 :
yanfafeiyong_row = cell.row
print(yanfafeiyong_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yanfafeiyong_column = cell.column
print(yanfafeiyong_column)
break

ws_lirunbiao_C20=sheet_lirunbiao.cell(row=yanfafeiyong_row,column=yanfafeiyong_column).value

########### 利润表财务费用

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“财务费用”) != -1 :
caiwufeiyong_row = cell.row
print(caiwufeiyong_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
caiwufeiyong_column = cell.column
print(caiwufeiyong_column)
break

ws_lirunbiao_C22 = sheet_lirunbiao.cell(row=caiwufeiyong_row,column=caiwufeiyong_column).value

########### 利润表营业利润

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“营业利润”) != -1 :
yingyelirun_row = cell.row
print(yingyelirun_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yingyelirun_column = cell.column
print(yingyelirun_column)
break

ws_lirunbiao_C24=sheet_lirunbiao.cell(row=yingyelirun_row,column=yingyelirun_column).value

########### 利润表营业外收入

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“营业外收入”) != -1 :
yingyewaishouru_row = cell.row
print(yingyewaishouru_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yingyewaishouru_column = cell.column
print(yingyewaishouru_column)
break

ws_lirunbiao_C25=sheet_lirunbiao.cell(row=yingyewaishouru_row,column=yingyewaishouru_column).value

########### 利润表营业外支出

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“营业外支出”) != -1 :
yingyewaizhichu_row = cell.row
print(yingyewaizhichu_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
yingyewaizhichu_column = cell.column
print(yingyewaizhichu_column)
break

ws_lirunbiao_C27=sheet_lirunbiao.cell(row=yingyewaizhichu_row,column=yingyewaizhichu_column).value

########### 利润表利润总额

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“利润总额”) != -1 :
lirunzonge_row = cell.row
print(lirunzonge_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
lirunzonge_column = cell.column
print(lirunzonge_column)
break

ws_lirunbiao_C33=sheet_lirunbiao.cell(row=lirunzonge_row,column=lirunzonge_column).value

########### 利润表所得税费用

for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“所得税费用”) != -1 :
suodeshuifeiyong_row = cell.row
print(suodeshuifeiyong_row)
break
for row in sheet_lirunbiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计数”) != -1 or str(cell.value).find(“本年累计金额”) != -1 :
suodeshuifeiyong_column = cell.column
print(suodeshuifeiyong_column)
break

ws_lirunbiao_C34=sheet_lirunbiao.cell(row=suodeshuifeiyong_row,column=suodeshuifeiyong_column).value

all_sheets_nashuishenbaobiao = wb_nashuishenbaobiao.get_sheet_names()
print(all_sheets_nashuishenbaobiao)
sheet_nashuishenbaobiao = wb_nashuishenbaobiao.get_sheet_by_name(all_sheets_nashuishenbaobiao[0])

########### 纳税申报表应交增值税

##############纳税申报表应交增值税销项税额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“销项税额”) != -1 :
xiaoxiangshuie_row = cell.row
print(xiaoxiangshuie_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
xiaoxiangshuie_column = cell.column
print(xiaoxiangshuie_column)
break

ws_nashuishenbaobiao_Z20=sheet_nashuishenbaobiao.cell(row=xiaoxiangshuie_row,column=xiaoxiangshuie_column).value

##############纳税申报表应交增值税进项税额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“进项税额”) != -1 and str(cell.value).find(“进项税额转出”) == -1:
jinxiangshuie_row = cell.row
print(jinxiangshuie_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
jinxiangshuie_column = cell.column
print(jinxiangshuie_column)
break

ws_nashuishenbaobiao_Z21=sheet_nashuishenbaobiao.cell(row=jinxiangshuie_row,column=jinxiangshuie_column).value

##############纳税申报表应交增值税进项税额转出

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“进项税额转出”) != -1 :
jinxiangshuiezhuanchu_row = cell.row
print(jinxiangshuiezhuanchu_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
jinxiangshuiezhuanchu_column = cell.column
print(jinxiangshuiezhuanchu_column)
break

ws_nashuishenbaobiao_Z23=sheet_nashuishenbaobiao.cell(row=jinxiangshuiezhuanchu_row,column=jinxiangshuiezhuanchu_column).value

##############纳税申报表应交增值税免、抵、退应退税额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“免、抵、退应退税额”) != -1 :
yingtuishuie_row = cell.row
print(yingtuishuie_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
yingtuishuie_column = cell.column
print(yingtuishuie_column)
break

ws_nashuishenbaobiao_Z24=sheet_nashuishenbaobiao.cell(row=yingtuishuie_row,column=yingtuishuie_column).value

##############纳税申报表应交增值税简易计税办法计算的应纳税额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“简易计税办法计算的应纳税额”) != -1 :
yingnashuie_row = cell.row
print(yingnashuie_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
yingnashuie_column = cell.column
print(yingnashuie_column)
break

ws_nashuishenbaobiao_Z30=sheet_nashuishenbaobiao.cell(row=yingnashuie_row,column=yingnashuie_column).value

##############纳税申报表应交增值税按简易计税办法计算的纳税检查应补缴税额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“按简易计税办法计算的纳税检查应补缴税额”) != -1 :
yingbujiaoshuie_row = cell.row
print(yingbujiaoshuie_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
yingbujiaoshuie_column = cell.column
print(yingbujiaoshuie_column)
break

ws_nashuishenbaobiao_Z31=sheet_nashuishenbaobiao.cell(row=yingbujiaoshuie_row,column=yingbujiaoshuie_column).value
print(ws_nashuishenbaobiao_Z31)

##############纳税申报表应交增值税应纳税额减征额

for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“应纳税额减征额”) != -1 :
yingnashuiejianzhenge_row = cell.row
print(yingnashuiejianzhenge_row)
break
for row in sheet_nashuishenbaobiao.iter_rows():
for cell in row:
if str(cell.value).find(“本年累计”) != -1 or str(cell.value).find(“累计金额”) != -1 :
yingnashuiejianzhenge_column = cell.column
print(yingnashuiejianzhenge_column)
break

ws_nashuishenbaobiao_Z32=sheet_nashuishenbaobiao.cell(row=yingnashuiejianzhenge_row,column=yingnashuiejianzhenge_column).value
print(ws_nashuishenbaobiao_Z32)

ws_caiwuzhuangkuangbiao[‘E3’] = ws_fuzhaibiao_D13
ws_caiwuzhuangkuangbiao[‘E5’] = ws_fuzhaibiao_C19
ws_caiwuzhuangkuangbiao[‘E6’] = ws_fuzhaibiao_C8
ws_caiwuzhuangkuangbiao[‘E7’] = ws_fuzhaibiao_C13
ws_caiwuzhuangkuangbiao[‘E8’] = ws_fuzhaibiao_C23
ws_caiwuzhuangkuangbiao[‘E10’] = ws_fuzhaibiao_C24
ws_caiwuzhuangkuangbiao[‘E11’] = ws_fuzhaibiao_C35
ws_caiwuzhuangkuangbiao[‘E12’] = ws_fuzhaibiao_G26

ws_caiwuzhuangkuangbiao[‘E14’] = ws_lirunbiao_C4
ws_caiwuzhuangkuangbiao[‘E15’] = ws_lirunbiao_C5
ws_caiwuzhuangkuangbiao[‘E16’] = ws_lirunbiao_C6
ws_caiwuzhuangkuangbiao[‘E17’] = ws_lirunbiao_C14
ws_caiwuzhuangkuangbiao[‘E23’] = ws_lirunbiao_C24
ws_caiwuzhuangkuangbiao[‘E24’] = ws_lirunbiao_C25
ws_caiwuzhuangkuangbiao[‘E25’] = ws_lirunbiao_C27
ws_caiwuzhuangkuangbiao[‘E26’] = ws_lirunbiao_C33
ws_caiwuzhuangkuangbiao[‘E27’] = ws_lirunbiao_C34
#如下代码用于将多个表中的相关数据写入财务状况表------特例：利润表中利息费用指标，如果为正计财务状况表的利息收入，如果为负数计财务状况表的利息费用

print(ws_lirunbiao_C22)

if (ws_lirunbiao_C22 < 0):
ws_caiwuzhuangkuangbiao[‘E21’] = abs(ws_lirunbiao_C22)
else:
ws_caiwuzhuangkuangbiao[‘E22’] = ws_lirunbiao_C22

ws_caiwuzhuangkuangbiao[‘E28’] = ws_nashuishenbaobiao_Z20-ws_nashuishenbaobiao_Z21+ws_nashuishenbaobiao_Z23+ws_nashuishenbaobiao_Z24+ws_nashuishenbaobiao_Z30+ws_nashuishenbaobiao_Z31-ws_nashuishenbaobiao_Z32

#保存财务状况表中修改的数据

wb_caiwuzhuangkuangbiao.save(‘财务状况表.xlsx’)