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 #如下代码用于多个相关表的打开操作 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]) cunhuo_row="" cunhuo_column="" for row in sheet_fuzhaibiao.iter_rows(): for cell in row: if str(cell.value).find("存货") != -1: cunhuo_row = cell.row cunhuo_column = cell.column # print(str(cell.value).find("其中")) print(cell.row) print(int(cell.column)+3) print(cell.coordinate) print(sheet_fuzhaibiao.cell(row=13,column=1).value) # print(cunhuo_column.value) # cunhuo_column=cell.column+3 # print(cell(row=cell.row,column=cunhuo_column).value) #cunhuo_coordinate=cell.coordinate+3 #print(cunhuo_coordinate) #print(cell.column) 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) 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) #如下代码用于将多个表中的相关数据写入财务状况表------特例:利润表中利息费用指标,如果为正计财务状况表的利息收入,如果为负数计财务状况表的利息费用 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(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')
python_excel_openpyxl成功操作单元格位置变化并报数
最新推荐文章于 2024-04-22 16:04:07 发布
该代码示例展示了如何使用openpyxl和xlrd库读取多个Excel文件,并将数据写入到财务状况表中。它分别打开资产负债表、利润表和纳税申报表,提取特定单元格数据,并根据条件判断写入财务状况表。此外,还计算了利润表中利息费用指标,根据正负数分别写入不同位置。最后,保存了更新后的财务状况表。
摘要由CSDN通过智能技术生成