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 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 #如下代码用于多个相关表的打开操作 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(cell.value).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 : zichanyuanjia_column = cell.column print(zichanyuanjia_column) break for row in sheet_fuzhaibiao.iter_rows(): for cell in row: if str(cell.value).find("应收账款") != -1: 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 : 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(cell.value).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 : 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(cell.value).find("负债合计") == 0 : fuzhaiheji_row = cell.row print(fuzhaiheji_row) break for row in sheet_fuzhaibiao.iter_rows(): for cell in row: if str(cell.value).find("负债合计") == 0 : 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() print(all_sheets_lirunbiao) 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 and 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 : 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 : guanlifeiyong_column = cell.column print(guanlifeiyong_column) break ws_lirunbiao_C17=sheet_lirunbiao.cell(row=gualifeiyong_row,column=gualifeiyong_column).value ########### 利润表 研发费用 for row in sheet_lirunbiao.iter_rows(): for cell in row: if 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 : 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 : 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 : 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 : 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 : 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 : yingyewaishouru_column = cell.column print(yingyewaishouru_column) break for row in sheet_lirunbiao.iter_rows(): for cell in row: if str(cell.value).find("营业外支出") != -1: ws_lirunbiao_C27=sheet_lirunbiao.cell(row=cell.row,column=int(cell.column)+2).value ########### 利润表 利润总额 for row in sheet_lirunbiao.iter_rows(): for cell in row: if str(cell.value).find("利润总额") != -1: ws_lirunbiao_C33=sheet_lirunbiao.cell(row=cell.row,column=int(cell.column)+2).value ########### 利润表 所得税费用 for row in sheet_lirunbiao.iter_rows(): for cell in row: if str(cell.value).find("所得税费用") != -1: ws_lirunbiao_C34=sheet_lirunbiao.cell(row=cell.row,column=int(cell.column)+2).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: ws_nashuishenbaobiao_Z20=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("进项税额 ") != -1 and str(cell.value).find("进项税额转出 ") == -1: ws_nashuishenbaobiao_Z21=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value print(ws_nashuishenbaobiao_Z21) for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("进项税额转出") != -1 and str(cell.value).find("进项税额 ") == -1: ws_nashuishenbaobiao_Z23=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value print(ws_nashuishenbaobiao_Z23) for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("免、抵、退应退税额") != -1: ws_nashuishenbaobiao_Z24=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value print(ws_nashuishenbaobiao_Z24) for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("简易计税办法计算的应纳税额") != -1: ws_nashuishenbaobiao_Z30=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value print(ws_nashuishenbaobiao_Z30) for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("按简易计税办法计算的纳税检查应补缴税额") != -1: ws_nashuishenbaobiao_Z31=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).value print(ws_nashuishenbaobiao_Z31) for row in sheet_nashuishenbaobiao.iter_rows(): for cell in row: if str(cell.value).find("应纳税额减征额") != -1: ws_nashuishenbaobiao_Z32=sheet_nashuishenbaobiao.cell(row=cell.row,column=int(cell.column)+24).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 #如下代码用于将多个表中的相关数据写入财务状况表------特例:利润表中利息费用指标,如果为正计财务状况表的利息收入,如果为负数计财务状况表的利息费用 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')