一键式统计系统11实现企业一套表格式xlsx输出

最新推荐文章于 2025-12-13 12:34:36 发布

原创最新推荐文章于 2025-12-13 12:34:36 发布 · 184 阅读

0 ·

CC 4.0 BY-SA版权

一键式统计系统专栏收录该内容

1 篇文章

订阅专栏

该代码段使用openpyxl库读取并整合了资产负债表、利润表和财务状况表的数据。它从各个表格中提取关键财务指标，如年初存货、流动资产合计、应收账款等，并将这些数据写入财务状况表。此外，还进行了利息收入的正负判断及相应处理，并计算了总税费。最后，更新并保存了财务状况表。

import openpyxl
#
# # 加载 excel 文件

########################资产负债表
fn_fuzhaibiao = '资产负债表.xlsx'
wb_fuzhaibiao = openpyxl.load_workbook(fn_fuzhaibiao)

ws_fuzhaibiao = wb_fuzhaibiao.get_sheet_by_name('资产负债表')
'''
print("年初存货：")
print(ws_fuzhaibiao['D13'].value)

print("流动资产合计：")
print(ws_fuzhaibiao['C19'].value)


print("应收账款：")
print(ws_fuzhaibiao['C8'].value)

print("存货：")
print(ws_fuzhaibiao['C13'].value)


print("固定资产原价：")
print(ws_fuzhaibiao['C23'].value)



print("本年折旧：")
print(ws_fuzhaibiao['C24'].value)


print("资产总计：")
print(ws_fuzhaibiao['C35'].value)



print("负债合计：")
print(ws_fuzhaibiao['G26'].value)
'''
########################利润表
fn_lirunbiao = '利润表.xlsx'
wb_lirunbiao = openpyxl.load_workbook(fn_lirunbiao)

ws_lirunbiao = wb_lirunbiao.get_sheet_by_name('利润表')

'''
print("营业收入：")
print(ws_lirunbiao['C4'].value)

print("营业成本：")
print(ws_lirunbiao['C5'].value)

print("税金及附加：")
print(ws_lirunbiao['C6'].value)

print("销售费用：")
print(ws_lirunbiao['C14'].value)

print("管理费用：")
print(ws_lirunbiao['C17'].value)

print("研发费用：")
print(ws_lirunbiao['C20'].value)

print("利息收入：")
print(ws_lirunbiao['C22'].value)

print("利息费用：")
print(ws_lirunbiao['C22'].value)


print("营业利润：")
print(ws_lirunbiao['C24'].value)

print("营业外收入：")
print(ws_lirunbiao['C25'].value)

print("营业外支出：")
print(ws_lirunbiao['C27'].value)

print("利润总额：")
print(ws_lirunbiao['C33'].value)

print("所得税费用：")
print(ws_lirunbiao['C34'].value)
'''


########################财务状况表
fn_caiwuzhuangkuang = '财务状况表.xlsx'
wb_caiwuzhuangkuang = openpyxl.load_workbook(fn_caiwuzhuangkuang)

ws_caiwuzhuangkuang = wb_caiwuzhuangkuang.get_sheet_by_name('财务状况表')


ws_caiwuzhuangkuang['E3'] = ws_fuzhaibiao['D13'].value
ws_caiwuzhuangkuang['E5'] = ws_fuzhaibiao['C19'].value
ws_caiwuzhuangkuang['E6'] = ws_fuzhaibiao['C8'].value
ws_caiwuzhuangkuang['E7'] = ws_fuzhaibiao['C13'].value
ws_caiwuzhuangkuang['E8'] = ws_fuzhaibiao['C23'].value
ws_caiwuzhuangkuang['E10'] = ws_fuzhaibiao['C24'].value
ws_caiwuzhuangkuang['E11'] = ws_fuzhaibiao['C35'].value
ws_caiwuzhuangkuang['E12'] = ws_fuzhaibiao['G26'].value

ws_caiwuzhuangkuang['E14'] = ws_lirunbiao['C4'].value
ws_caiwuzhuangkuang['E15'] = ws_lirunbiao['C5'].value
ws_caiwuzhuangkuang['E16'] = ws_lirunbiao['C6'].value
ws_caiwuzhuangkuang['E17'] = ws_lirunbiao['C14'].value
ws_caiwuzhuangkuang['E18'] = ws_lirunbiao['C17'].value
ws_caiwuzhuangkuang['E19'] = ws_lirunbiao['C20'].value

if (ws_lirunbiao['C22'].value<0):
    ws_caiwuzhuangkuang['E21'] = abs(ws_lirunbiao['C22'].value)
    print(abs(ws_lirunbiao['C22'].value))
    print(ws_caiwuzhuangkuang['E21'].value)

else:
    ws_caiwuzhuangkuang['E22'] = ws_lirunbiao['C22'].value
    print(ws_lirunbiao['C22'].value)
    print(ws_caiwuzhuangkuang['E22'].value)



ws_caiwuzhuangkuang['E23'] = ws_lirunbiao['C24'].value
ws_caiwuzhuangkuang['E24'] = ws_lirunbiao['C25'].value
ws_caiwuzhuangkuang['E25'] = ws_lirunbiao['C27'].value
ws_caiwuzhuangkuang['E26'] = ws_lirunbiao['C33'].value
ws_caiwuzhuangkuang['E27'] = ws_lirunbiao['C34'].value

'''



print("营业收入：")
print(ws_lirunbiao['C4'].value)

print("营业成本：")
print(ws_lirunbiao['C5'].value)

print("税金及附加：")
print(ws_lirunbiao['C6'].value)

print("销售费用：")
print(ws_lirunbiao['C14'].value)

print("管理费用：")
print(ws_lirunbiao['C17'].value)

print("研发费用：")
print(ws_lirunbiao['C20'].value)

print("利息收入：")
print(abs(ws_lirunbiao['C22'].value))

print("利息费用：")
print(ws_lirunbiao['C22'].value)


print("营业利润：")
print(ws_lirunbiao['C24'].value)

print("营业外收入：")
print(ws_lirunbiao['C25'].value)

print("营业外支出：")
print(ws_lirunbiao['C27'].value)

print("利润总额：")
print(ws_lirunbiao['C33'].value)

print("所得税费用：")
print(ws_lirunbiao['C34'].value)




'''


########################财务状况表
fn_nashuishenbaobiao = '纳税申报表.xlsx'
wb_nashuishenbaobiao = openpyxl.load_workbook(fn_nashuishenbaobiao)

ws_nashuishenbaobiao = wb_nashuishenbaobiao.get_sheet_by_name('Sheet1')


ws_caiwuzhuangkuang['E28'] = ws_nashuishenbaobiao['Z20'].value-ws_nashuishenbaobiao['Z21'].value+ws_nashuishenbaobiao['Z23'].value+ws_nashuishenbaobiao['Z24'].value+ws_nashuishenbaobiao['Z30'].value+ws_nashuishenbaobiao['Z31'].value-ws_nashuishenbaobiao['Z32'].value




















wb_caiwuzhuangkuang.save('财务状况表.xlsx')