python_excel__openpyxl_一键式统计系统 增加模糊行列匹配2.0

原创 于 2020-09-30 15:58:35 发布 · 313 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

这段代码主要用于从多个财务报表文件（资产负债表、利润表、纳税申报表）中提取关键数据，并将这些数据整合到财务状况表中。具体操作包括定位到含有特定文字的单元格，读取其相邻单元格的值，然后写入到财务状况表的相应位置。涉及到的数据项有存货、资产总计、负债合计等财务指标。 
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_nashuishenbaobiao_Z20=0
print(ws_nashuishenbaobiao_Z20)
ws_nashuishenbaobiao_Z21=0
ws_nashuishenbaobiao_Z23=0
ws_nashuishenbaobiao_Z24=0
ws_nashuishenbaobiao_Z30=0
ws_nashuishenbaobiao_Z31=0
ws_nashuishenbaobiao_Z32=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('利润表')

rb_caiwuzhuangkuang = xlrd.open_workbook('财务状况表.xls', formatting_info=True)
wb_caiwuzhuangkuang = copy(rb_caiwuzhuangkuang)
ws_caiwuzhuangkuang = wb_caiwuzhuangkuang.get_sheet(0)


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



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



all_sheets_fuzhaibiao = wb_fuzhaibiao.get_sheet_names()
print(all_sheets_fuzhaibiao)
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:
            ws_fuzhaibiao_D13=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+3).value


for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("流动资产合计") != -1 and str(cell.value).find("非流动资产合计") == -1 :
            ws_fuzhaibiao_C19=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value

for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("应收账款") != -1:
            ws_fuzhaibiao_C8=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value


for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("存货") != -1:
            ws_fuzhaibiao_C13=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value

for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("固定资产原价") != -1:
            ws_fuzhaibiao_C23=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value

for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("累计折旧") != -1:
            ws_fuzhaibiao_C24=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value

for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("资产总计") != -1:
            ws_fuzhaibiao_C35=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).value

for row in sheet_fuzhaibiao.iter_rows():
    for cell in row:
        if str(cell.value).find("负债合计") == 0:
            ws_fuzhaibiao_G26=sheet_fuzhaibiao.cell(row=cell.row,column=int(cell.column)+2).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:
            ws_lirunbiao_C4=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_C5=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_C6=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_C14=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_C17=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_C20=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_C22=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_C24=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_C25=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_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_caiwuzhuangkuang.write(2, 4, ws_fuzhaibiao_D13)
ws_caiwuzhuangkuang.write(4, 4, ws_fuzhaibiao_C19)
ws_caiwuzhuangkuang.write(5, 4, ws_fuzhaibiao_C8)
ws_caiwuzhuangkuang.write(6, 4, ws_fuzhaibiao_C13)
ws_caiwuzhuangkuang.write(7, 4, ws_fuzhaibiao_C23)
ws_caiwuzhuangkuang.write(9, 4, ws_fuzhaibiao_C24)
ws_caiwuzhuangkuang.write(10, 4, ws_fuzhaibiao_C35)
ws_caiwuzhuangkuang.write(11, 4, ws_fuzhaibiao_G26)


ws_caiwuzhuangkuang.write(13, 4, ws_lirunbiao_C4)
ws_caiwuzhuangkuang.write(14, 4, ws_lirunbiao_C5)
ws_caiwuzhuangkuang.write(15, 4, ws_lirunbiao_C6)
ws_caiwuzhuangkuang.write(16, 4, ws_lirunbiao_C14)
ws_caiwuzhuangkuang.write(22, 4, ws_lirunbiao_C24)
ws_caiwuzhuangkuang.write(23, 4, ws_lirunbiao_C25)
ws_caiwuzhuangkuang.write(24, 4, ws_lirunbiao_C27)
ws_caiwuzhuangkuang.write(25, 4, ws_lirunbiao_C33)
ws_caiwuzhuangkuang.write(26, 4, ws_lirunbiao_C34)
#如下代码用于将多个表中的相关数据写入财务状况表------特例：利润表中利息费用指标，如果为正计财务状况表的利息收入，如果为负数计财务状况表的利息费用
if (ws_lirunbiao_C22<0):
    ws_caiwuzhuangkuang.write(20, 4, abs(ws_lirunbiao_C22))
else:
    ws_caiwuzhuangkuang.write(21, 4, ws_lirunbiao_C22)


ws_caiwuzhuangkuang.write(27, 4, ws_nashuishenbaobiao_Z20-ws_nashuishenbaobiao_Z21+ws_nashuishenbaobiao_Z23+ws_nashuishenbaobiao_Z24+ws_nashuishenbaobiao_Z30+ws_nashuishenbaobiao_Z31-ws_nashuishenbaobiao_Z32)



#保存财务状况表中修改的数据
wb_caiwuzhuangkuang.save('财务状况表.xls')
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值