python_excel_一键式统计系统（完美解决xls xlsx excel新老版本交互式读取和写入）

最新推荐文章于 2024-08-23 21:49:13 发布

原创最新推荐文章于 2024-08-23 21:49:13 发布 · 205 阅读

0 ·

CC 4.0 BY-SA版权

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

3 篇文章

订阅专栏

该代码段读取并整合了'资产负债表.xlsx'、'利润表.xlsx'和'财务状况表.xls'的数据。它从各个工作表中提取特定单元格的值，并将它们写入'财务状况表.xls'的指定单元格。此外，根据'利润表.xlsx'的某单元格值进行条件判断，写入相应结果。最后，进行了复杂的计算并将结果保存到'财务状况表.xls'的末尾单元格。

import xlrd
from xlutils.copy import copy

import openpyxl
#
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)






ws_caiwuzhuangkuang.write(2, 4, ws_fuzhaibiao['D13'].value)

ws_caiwuzhuangkuang.write(4, 4, ws_fuzhaibiao['C19'].value)

ws_caiwuzhuangkuang.write(5, 4, ws_fuzhaibiao['C8'].value)

ws_caiwuzhuangkuang.write(6, 4, ws_fuzhaibiao['C13'].value)

ws_caiwuzhuangkuang.write(7, 4, ws_fuzhaibiao['C23'].value)

ws_caiwuzhuangkuang.write(9, 4, ws_fuzhaibiao['C24'].value)

ws_caiwuzhuangkuang.write(10, 4, ws_fuzhaibiao['C35'].value)

ws_caiwuzhuangkuang.write(11, 4, ws_fuzhaibiao['G26'].value)

ws_caiwuzhuangkuang.write(13, 4, ws_lirunbiao['C4'].value)

ws_caiwuzhuangkuang.write(14, 4, ws_lirunbiao['C5'].value)

ws_caiwuzhuangkuang.write(15, 4, ws_lirunbiao['C6'].value)

ws_caiwuzhuangkuang.write(16, 4, ws_lirunbiao['C14'].value)



if (ws_lirunbiao['C22'].value<0):
    ws_caiwuzhuangkuang.write(20, 4, abs(ws_lirunbiao['C22'].value))

else:
    ws_caiwuzhuangkuang.write(21, 4, ws_lirunbiao['C22'].value)





ws_caiwuzhuangkuang.write(22, 4, ws_lirunbiao['C24'].value)


ws_caiwuzhuangkuang.write(23, 4, ws_lirunbiao['C25'].value)


ws_caiwuzhuangkuang.write(24, 4, ws_lirunbiao['C27'].value)


ws_caiwuzhuangkuang.write(25, 4, ws_lirunbiao['C33'].value)


ws_caiwuzhuangkuang.write(26, 4, 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.write(27, 4, 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('财务状况表.xls')