python_excel_openpyxl成功操作單元格位置變化並報數

jidawanghao發表於2020-09-30
PythonExcel
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')

相關文章