Python处理Excel（6）：处理清产核资表格
openpyxl能保持Excel表原有的格式。
一、复制应收款
#coding=utf-8
#复制粘贴应收款
import xlrd, xlwt, xlwings, pyautogui, time, pyperclip, easygui, os, tkinter, io,pandas,shutil,re, os
from tkinter import filedialog,ttk
from openpyxl import load_workbook #pip install openpyxl
from openpyxl.styles import Border, Side, colors
#import autopy as at
#file= tkinter.filedialog.askopenfile()#选择打开什么文件，返回IO流对象
#file= tkinter.filedialog.askopenfilename()#选择打开什么文件，返回文件名
#print(file)
from io import StringIO
import sys
from contextlib import contextmanager
import sys
curn_path = os.getcwd() #current path当前路径
temp_tabl_path = os.path.join(curn_path, 'Template Table')
outp_tabl_path = os.path.join(curn_path, 'Output Table')
data_sour_path = os.path.join(curn_path, 'Data Source')
#print(temp_tabl_path)
df = pandas.read_excel('贝佳电脑端账套号 所有经济组织按账套号排序 改标准名后.xlsx', dtype=str)
accnt_dict2 = dict(zip(df['单位简称1'],df['三位数账套号'] )) #字典格式是'八家经济联合社'：'044'
#totl = 0 #不加这个报错UnboundLocalError: local variable 'totl' referenced before assignment
#print(accnt_dict2)
# easygui.msgbox(msg='请选择数据源文件夹。')
# print('选择目录，返回目录名')
# raw_folder_path=tkinter.filedialog.askdirectory()
# #raw_folder_path = r'd:\贝佳会计系统导出数据\两项经费统计\2020序时簿'
# #easygui.msgbox(msg='请选择Excel模板（目标Excel表或数据写入Excel表）。')
# easygui.msgbox(msg='请选择写入的Excel文件。')
# targ_file_path =easygui.fileopenbox(msg='请选择Excel模板（目标Excel表或数据写入Excel表）。')
#targ_file_path = targ_file_path.replace(' 模板.xls', '.xls').replace(' 模板.xlsx', '.xlsx')
# fil_nam = ''
# raw_file_path = ''
# targ_file_path = ''
# # 判断是否存在output文件夹
# OutputPath = r"E:\MyProjects\PycharmProjects\python01\Python And Excel\Asset and Capital Verification"
# # if (os.path.exists(OutputPath)):
# # shutil.rmtree(OutputPath)
# # print('output dir has been rm -rf and new makedirs')
# # os.makedirs(OutputPath)
# if not os.path.exists(OutputPath):
# os.mkdir(OutputPath)
# #print('覆盖Excel原来的数据写入Excel')
# # 写入文件保存在output 文件夹下
# #filepath = os.path.join(OutputPath, '村（社区）办公经费.xls')
# #targ_data.to_excel(filepath, header=3, index=False, encoding='utf-8', sheet_name='村（社区）办公经费')
def get_file_list(raw_folder_path):
# 打开文件
dirs = os.listdir(raw_folder_path)
raw_file_list=[]
raw_file_path_list=[]
for home, dirs, files in os.walk(raw_folder_path):
for file_name in files:
raw_file_list.append((file_name))
raw_file_path_list.append(os.path.join(home, file_name))
#print('raw_file_list')
#print(raw_file_list)
return raw_file_list,raw_file_path_list
def gath_data(raw_file_path, targ_file_path):
#global totl #要定义全局变量
star_row = 9
shet_name = 'Sheet1'
wb_raw = load_workbook(raw_file_path)
ws_raw = wb_raw[shet_name]
row_raw = ws_raw.max_row
colm_raw = ws_raw.max_column
list_accn_name , list_init_baln , list_end_baln , list_vari_quan =[],[],[],[]
if ws_raw.cell(row=3, column=1).value == '应收款':
totl = float(ws_raw.cell(row=3, column=5).value) # total amount总额
else:
totl = 0
for r in range(3,row_raw):
accn_name = ws_raw.cell(row=r, column=1).value # account name科目名称
init_baln = float(ws_raw.cell(row=r, column=5).value) # initial balance期初余额
end_baln = float(ws_raw.cell(row=r, column=12).value) # ending balance期末余额
vari_quan = float(ws_raw.cell(row=r, column=6).value) #variable quantity变化量
if accn_name =='应收款':
pass
if accn_name[:3] == ' ' and accn_name[4:4] != ' ':
#if accn_name[:3] == ' ' and accn_name[:6] == ' ':
pass
if accn_name[:6] ==' ':
accn_name = accn_name.strip()
#print(accn_name)
list_accn_name.append(accn_name)
list_init_baln.append(init_baln)
print(list_accn_name)
print(list_init_baln)
wb_raw.save(raw_file_path)
# 释放资源，不然脚本无法打开，会处于锁定状态。
wb_raw.close()
shet_name = '应收款项清查登记表'
wb_targ = load_workbook(targ_file_path)
ws_targ = wb_targ[shet_name]
row_targ = ws_targ.max_row
colm_targ = ws_targ.max_column
#print(row)
#print(colm)
# print(list_accn_name)
# print(list_init_baln)
for a in range(len(list_accn_name)):
ws_targ.cell(row=star_row, column=2).value = list_accn_name[a]
for colm in [6, 9, 11]:
ws_targ.cell(row=star_row, column=colm).value = list_init_baln[a]
#print('在第{}行写入：{}，{}。'.format(star_row, list_accn_name[a], list_init_baln[a]))
star_row+=1
#for foot in ['相关事项说明：', '清产核资工作小组负责人（签章）：','填表人：','打印日期：']:
ws_targ.cell(row=row_targ, column=1).value = '相关事项说明：'
ws_targ.cell(row=row_targ, column=4).value = '清产核资工作小组负责人（签章）：'
ws_targ.cell(row=row_targ, column=7).value = '填表人：'
ws_targ.cell(row=row_targ, column=9).value = '打印日期：'
wb_targ.save(targ_file_path)
wb_targ.close()
return list_accn_name, list_init_baln, totl
# wb_targ.save(targ_file_path)
# # 释放资源，不然脚本无法打开，会处于锁定状态。
# wb_targ.close()
# # 没有engine='openpyxl会报错
# #合并单元格不能将数值转化为浮点型，所以要去除表头和表脚。header=3, skipfooter=1
# getInfo = pandas.read_excel(raw_file_path, sheet_name="Sheet1", header=3, skipfooter=1, keep_default_na=False, engine='openpyxl')
#
# print('不覆盖Excel原来的数据写入Excel：{}'.format(targ_file_path))
# book = load_workbook(targ_file_path)
# writer = pandas.ExcelWriter(targ_file_path, engine='openpyxl')
# writer.book = book
# writer.sheets = dict((ws.title, ws) for ws in book.worksheets) # 获取文件中已存在的表名，这行直接用，不用修改
# for i in getInfo.index:
# iden = getInfo.loc[i].values[0] # 固定资产编号identifier
# # print(iden)
# # print(type(iden)) #<class 'numpy.int64'>
# # print(type(str(iden))) #string
# # print(str(iden))
# # if str(iden) =='001':
# # print(iden)
# # # break
# # if iden == 1:
# # print(iden)
# # break
# if int(iden) ==1 or iden == 2 or iden == 3 or iden == 4 or iden == 5 or iden == 6 or iden == 7:
# #if str(iden) =='001' or iden == '002' or iden == '003' or iden == '004' or iden == '005' or iden == '006' or iden == '007':
# #if str(iden) == ('001' or '002' or '003' or '004' or '005' or '006' or '007'):
# #if int(iden) == (1 or 2 or 3 or 4 or 5 or 6 or 7):
# print(iden)
# pass
# else:
# accn_name = getInfo.loc[i].values[1] # account name科目名称
# accm_depr = getInfo.loc[i].values[3]#accumulated depreciation 累计折旧
# init_baln = getInfo.loc[i].values[2]#initial balance期初余额
# end_baln = getInfo.loc[i].values[4] # 期末余额ending balance
# # if accn_code[:2] =='112': #如果科目代号前面的三位数是112应收款。
# # #for j in range(len(getInfo.loc[i].values)):
# accm_depr_seri = pandas.Series(init_baln, dtype=float)
# init_baln_seri = pandas.Series(init_baln, dtype=float)
# end_baln_seri = pandas.Series(end_baln, dtype=float) # 期末余额转化为浮点型。
# # end_baln_seri = pandas.Series(end_baln) #依然为常规格式，数值能求和。
# #print(end_baln_seri)
# #print('写入: {} {} {} {}'.format(accn_name, init_baln, accm_depr, end_baln))
# # 写入 固定资产清查登记表-2（非经营性固定资产）从第12行，第2列写入，工作表名前有空格，在Excel表上看不出来
# pandas.Series(star_row-10).to_excel(writer, header=False, index=False, startrow=star_row, startcol=0,
# merge_cells=True,
# sheet_name=' 固定资产清查登记表-2') # 编号
# pandas.Series('机器设备').to_excel(writer, header=False, index=False, startrow=star_row, startcol=1,merge_cells=True,
# sheet_name=' 固定资产清查登记表-2') #类别
# pandas.Series(accn_name).to_excel(writer, header=False, index=False, startrow=star_row, startcol=2,merge_cells=True,
# sheet_name=' 固定资产清查登记表-2') #名称
# #pandas.Series('机器设备').to_excel(writer, header=False, index=False, startrow=11, startcol=1, sheet_name=' 固定资产清查登记表-2') #类别
# init_baln_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=10,
# sheet_name=' 固定资产清查登记表-2')
# init_baln_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=19,
# sheet_name=' 固定资产清查登记表-2')
# accm_depr_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=11,
# sheet_name=' 固定资产清查登记表-2')
# accm_depr_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=20,
# sheet_name=' 固定资产清查登记表-2')
# end_baln_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=12,
# sheet_name=' 固定资产清查登记表-2')
# end_baln_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=21,
# sheet_name=' 固定资产清查登记表-2')#核实数中净值
# end_baln_seri.to_excel(writer, header=False, index=False, startrow=star_row, startcol=26,
# sheet_name=' 固定资产清查登记表-2') #去年核实数
# star_row += 1
# #print('写入到第{}行。'.format(star_row))
#
#
# writer.save()
# #不添加writer.close()会损坏Excel，报错zipfile.BadZipFile: File is not a zip file
# writer.close()
# # row_data=getInfo.loc[i].values
# # print(row_data)
# # row_data_list=list(row_data)
def func():
#row_resu = 4
raw_file_list,raw_file_path_list =get_file_list(data_sour_path)
print(1)
#print(raw_file_list)
temp_file_list, temp_file_path_list = get_file_list(temp_tabl_path)
#模板文件夹template table的文件复制到output table文件夹下
outp_tabl_list = []
for i in range(len(temp_file_list)):
outp_file_path =os.path.join(outp_tabl_path, temp_file_list[i])
shutil.copy(temp_file_path_list[i], outp_file_path)
outp_file_list, outp_file_path_list = get_file_list(outp_tabl_path)
print(outp_file_path_list)
print('gather data to excel')
for i in range(0,len(outp_file_path_list)):
print(f'尝试第{i + 1}个文件读写。')
outp_fil_nam = outp_file_list[i]
orgz = outp_fil_nam.replace('.xlsx','').replace('台山市冲蒌镇','') #organization组织单位
#print(orgz)
list_tabl_accn_disc = ['资产负债表', '收益分配表', '应收款', '应付款', '固定资产明细表', '在建工程明细表']
tabl_type = '应收款'
if orgz in accnt_dict2.keys(): #前锋村升平经济合作社不在字典里，就会主键报错KeyError
accnt_Num = accnt_dict2[orgz]
raw_file = '2021 {}{}{}.xlsx'.format(accnt_Num,orgz,tabl_type) #构造文件名，如2021 044八家经济联合社固定资产明细表.xls
print(accnt_Num, orgz)
raw_file_path = os.path.join(data_sour_path, raw_file)
if os.path.exists(raw_file_path):
#print(raw_file_path)
# targ_file_path = os.path.join(outp_tabl_path, '{}.xlsx'.format(orgz))
# print(targ_file_path)
# raw_file_path = raw_file_path_list[i]
#print(raw_file_path)
#input_file_path_plan = r"E:\贝佳会计系统导出数据\两项经费统计\2020年序时簿\2020 045冲洋经联社序时簿.xls"
#modf_excl(targ_file_path=outp_file_path_list[i])
#row_inse(file_path= outp_file_path_list[i])
list_accn_name, list_init_baln, totl = gath_data(raw_file_path =raw_file_path, targ_file_path = outp_file_path_list[i])
resu_file_path = r'2021年度清产核资填写数据情况表.xlsx'
wb_resu = load_workbook(resu_file_path) #workbook result
ws_resu = wb_resu['Sheet1']
for row_resu in range(4, 232):
# print(accnt_Num)
# print(str(ws_resu.cell(row=row_resu, column=2).value))
if str(ws_resu.cell(row=row_resu, column=2).value) == str(accnt_Num): # 都是string
ws_resu.cell(row=row_resu, column=5).value = 1
ws_resu.cell(row=row_resu, column=15).value = totl #应收款总额
wb_resu.save(resu_file_path)
wb_resu.close()
# writer.save()
# writer.close()
# book.save('新建 Microsoft Excel 工作表.xlsx')
# book.close()
if __name__ == "__main__":
func()
#
#
#
#
#
#
#
#
#
#
# def saveAsNewExcelFile(raw_file_path_list):
# list1=[]
# print("file name")
# fil_nam='村委会1'
#
# for raw_file_path in raw_file_path_list:
#
# for i in getInfo.index:
# for j in range(len(getInfo.loc[i].values)):
# if (getInfo.loc[i].values[j] == '专项应付款-村（社区）办公经费'):
# start_col = i + 1
# row_data = getInfo.loc[i].values
# # print(row_data)
# row_data = list(row_data)
# row_data = list[fil_nam] + row_data
# list1.append(row_data)
# print(list1)
# print('target data目标数据')
# targ_data = pandas.DataFrame(list1)
#
#
#
#
# # get cared macro info from testplan and save as 'MacroInfo.xlsx'
# getInfo = pandas.read_excel(input_file_path_plan, sheet_name="Sheet1", dtype=str, keep_default_na=False)
# for i in getInfo.index:
# for j in range(len(getInfo.loc[i].values)):
# if (getInfo.loc[i].values[j] == '专项应付款-村（社区）办公经费'):
# start_col = i + 1
# row_data=getInfo.loc[i].values
# #print(row_data)
# row_data=list(row_data)
# row_data=list[fil_nam]+row_data
# list1.append(row_data)
# print(list1)
# print('target data目标数据')
# targ_data=pandas.DataFrame(list1)
# #break #add 2.3
# # print(getInfo.loc[i].values[j])
# #x = pandas.DataFrame(getInfo.iloc[start_col:, ])
# #print(x)
# # 判断是否存在output文件夹
# OutputPath=r"e:\贝佳会计系统导出数据\两项经费统计"
# if (os.path.exists(OutputPath)):
# shutil.rmtree(OutputPath)
# print('output dir has been rm -rf and new makedirs')
# os.makedirs(OutputPath)
#
# # 写入文件保存在output 文件夹下
# filepath = os.path.join(OutputPath, '村（社区）办公经费.xls')
# targ_data.to_excel(filepath, header=2, index=False, encoding='utf-8', sheet_name='村（社区）办公经费')
二、复制粘贴固定资产去年核实数等
#coding=utf-8
#复制粘贴固定资产去年核实数等
import xlrd, xlwt, xlwings, pyautogui, time, pyperclip, easygui, os, tkinter, io,pandas,shutil,re, os
from tkinter import filedialog,ttk
from openpyxl import load_workbook #pip install openpyxl
from openpyxl.styles import Border, Side, colors
#import autopy as at
import datetime
#file= tkinter.filedialog.askopenfile()#选择打开什么文件，返回IO流对象
#file= tkinter.filedialog.askopenfilename()#选择打开什么文件，返回文件名
#print(file)
from io import StringIO
import sys
from contextlib import contextmanager
import sys
curn_path = os.getcwd() #current path当前路径
temp_tabl_path = os.path.join(curn_path, 'Template Table')
outp_tabl_path = os.path.join(curn_path, 'Output Table')
data_sour_path = os.path.join(curn_path, 'Data Source')
#print(temp_tabl_path)
df = pandas.read_excel('贝佳电脑端账套号 所有经济组织按账套号排序 改标准名后.xlsx', dtype=str)
accnt_dict2 = dict(zip(df['单位简称1'],df['三位数账套号'] )) #字典格式是'八家经济联合社'：'044'
def get_file_list(raw_folder_path):
# 打开文件
dirs = os.listdir(raw_folder_path)
raw_file_list=[]
raw_file_path_list=[]
for home, dirs, files in os.walk(raw_folder_path):
for file_name in files:
raw_file_list.append((file_name))
raw_file_path_list.append(os.path.join(home, file_name))
#print('raw_file_list')
#print(raw_file_list)
return raw_file_list,raw_file_path_list
def gath_data(raw_file_path, targ_file_path):
shet_name = ' 固定资产清查登记表-2'
wb_raw = load_workbook(raw_file_path)
ws_raw = wb_raw[shet_name]
row_raw = ws_raw.max_row
list_accn_name = [] #account name科目名称
list_veri_amou = [] #Verify the amount核实数
list_leibie = [] #类别
list_goujian_shijian = [] #构建时间
for r in range(12,row_raw):
accn_name = ws_raw.cell(row=r, column=3).value # account name科目名称
if accn_name =='' or accn_name == None:
break
else:
try:
leibie = ws_raw.cell(row=r, column=2).value
goujian_shijian = ws_raw.cell(row=r, column=4).value
veri_amou = float(ws_raw.cell(row=r, column=22).value) # float容易报错，放在后面，去年核实数是去年净值数
# veri_amou = round(float(ws_raw.cell(row=r, column=14).value),2)
except:
veri_amou = ''
list_accn_name.append(accn_name)
list_veri_amou.append(veri_amou)
list_leibie.append(leibie)
list_goujian_shijian.append(goujian_shijian)
# dict_raw = dict(zip(list_accn_name, list_veri_amou))
# print(dict_raw)
print(list_accn_name)
print(list_veri_amou)
wb_raw.save(raw_file_path)
# 释放资源，不然脚本无法打开，会处于锁定状态。
wb_raw.close()
wb_targ = load_workbook(targ_file_path)
ws_targ = wb_targ[shet_name]
row_targ = ws_targ.max_row
for r in range(10, row_targ+1):
if ws_targ.cell(row=r, column=1).value == '相关事项说明：':
#print('相关事项说明在第{}行.'.format(r))
break
row_targ = r
#print(row_targ)
# # #方法1：双重遍历，遍历行，遍历列表。
for r in range(12, row_targ-1):
accn_name_targ = ws_targ.cell(row=r, column=3).value #account name target
ws_targ.cell(row=r, column=26).value = '1' #去年数量shuliang_qunian
ws_targ.cell(row=r, column=24).value = '否'#是否为扶贫资产fupin_zichan_or_not
#veri_amou_targ = ws_targ.cell(row=r, column=11).value
if accn_name_targ == None or accn_name_targ == '': #不是if accn_name_targ == ''
break
else:
for i in range(len(list_accn_name)):
if accn_name_targ == list_accn_name[i]:
ws_targ.cell(row=r, column=27).value = list_veri_amou[i]
ws_targ.cell(row=r, column=2).value = list_leibie[i]
ws_targ.cell(row=r, column=4).value = list_goujian_shijian[i]
print('相同', r, i, accn_name_targ, list_veri_amou[i])
break #双重遍历一定要加break，不然全部运行else的语句。
else:
ws_targ.cell(row=r, column=12).value = 0.00
#print(i, r, list_accn_name[i], accn_name_targ)
# #方法2：方法1：双重遍历，遍历行，遍历列表。
# for r in range(9, row_targ-1):
# accn_name_targ = ws_targ.cell(row=r, column=2).value #account name target
# if accn_name_targ == None or accn_name_targ == '': #不是if accn_name_targ == ''
# break
# else:
# for key, value in dict_raw.items():
# if accn_name_targ == key:
# ws_targ.cell(row=r, column=16).value = value
# print('相同', r, key, accn_name_targ, value)
# break #双重遍历一定要加break，不然全部运行else的语句。
# else:
# ws_targ.cell(row=r, column=11).value = 0.00
# #print('不相同')
# for key, value in dict_raw.items():
# print("\nKey :" + str(key))
# print("Value: " + str(value))
# for i in range(len(list_accn_name)):
# for r in range(9, row_targ):
# accn_name_targ = ws_targ.cell(row=r, column=2).value # account name target
# if accn_name_targ == list_accn_name[i]:
# ws_targ.cell(row=r, column=11).value = list_veri_amou[i]
# print(i, r, accn_name_targ, list_veri_amou[i])
# break
# else:
# ws_targ.cell(row=r, column=11).value = 0.00
wb_targ.save(targ_file_path)
wb_targ.close()
def func():
#row_resu = 4
raw_file_list,raw_file_path_list =get_file_list(data_sour_path)
#print(raw_file_list)
temp_file_list, temp_file_path_list = get_file_list(temp_tabl_path)
#模板文件夹template table的文件复制到output table文件夹下
outp_tabl_list = []
for i in range(len(temp_file_list)):
outp_file_path =os.path.join(outp_tabl_path, temp_file_list[i])
shutil.copy(temp_file_path_list[i], outp_file_path)
outp_file_list, outp_file_path_list = get_file_list(outp_tabl_path)
print(outp_file_path_list)
print('gather data to excel')
for i in range(0,len(outp_file_path_list)):
print(f'尝试第{i + 1}个文件读写。')
outp_fil_nam = outp_file_list[i]
orgz = outp_fil_nam.replace('.xlsx','').replace('台山市冲蒌镇','') #organization组织单位
#print(orgz)
if orgz in accnt_dict2.keys(): #前锋村升平经济合作社不在字典里，就会主键报错KeyError
accnt_Num = accnt_dict2[orgz]
raw_file = outp_fil_nam
print(accnt_Num, orgz)
raw_file_path = os.path.join(data_sour_path, raw_file)
if os.path.exists(raw_file_path):
gath_data(raw_file_path =raw_file_path, targ_file_path = outp_file_path_list[i])
resu_file_path = r'2021年度清产核资填写数据情况表.xlsx'
wb_resu = load_workbook(resu_file_path) #workbook result
ws_resu = wb_resu['Sheet1']
for row_resu in range(4, 232): #4+228=232，所有的经济组织的行
# print(accnt_Num)
# print(str(ws_resu.cell(row=row_resu, column=2).value))
if str(ws_resu.cell(row=row_resu, column=2).value) == str(accnt_Num): # 都是string
ws_resu.cell(row=row_resu, column=36).value = 1
wb_resu.save(resu_file_path)
wb_resu.close()
if __name__ == "__main__":
func()
三、Asset and Capital Verification 收益分配表从收支明细表读取数据
#coding=utf-8
#复制粘贴财务公开收益分配表
import xlrd, xlwt, xlwings, pyautogui, time, pyperclip, easygui, os, tkinter, io,pandas,shutil,re, os
from tkinter import filedialog,ttk
from openpyxl import load_workbook #pip install openpyxl
from openpyxl.styles import Border, Side, colors
#import autopy as at
#file= tkinter.filedialog.askopenfile()#选择打开什么文件，返回IO流对象
#file= tkinter.filedialog.askopenfilename()#选择打开什么文件，返回文件名
#print(file)
from io import StringIO
import sys
from contextlib import contextmanager
import sys
curn_path = os.getcwd() #current path当前路径
temp_tabl_path = os.path.join(curn_path, 'Template Table')
outp_tabl_path = os.path.join(curn_path, 'Output Table')
data_sour_path = os.path.join(curn_path, 'Data Source')
#print(temp_tabl_path)
df = pandas.read_excel('贝佳电脑端账套号 所有经济组织按账套号排序 改标准名后.xlsx', dtype=str)
accnt_dict2 = dict(zip(df['单位简称1'],df['三位数账套号'] )) #字典格式是'八家经济联合社'：'044'
def get_file_list(raw_folder_path):
# 打开文件
dirs = os.listdir(raw_folder_path)
raw_file_list=[]
raw_file_path_list=[]
for home, dirs, files in os.walk(raw_folder_path):
for file_name in files:
raw_file_list.append((file_name))
raw_file_path_list.append(os.path.join(home, file_name))
#print('raw_file_list')
#print(raw_file_list)
return raw_file_list,raw_file_path_list
def gath_data(raw_file_path, targ_file_path):
star_row = 9
shet_name = 'Sheet1'
wb_raw = load_workbook(raw_file_path)
ws_raw = wb_raw[shet_name]
row_raw = ws_raw.max_row
jingying_shouru = 0.0000 #经营收入
fabao_ji_shangjiao_shouru = 0.0000 #发包及上交收入
touzi_shouyi = 0.0000 #投资收益
nongyeshui_fanhuan = 0.0000 #农业税返还
buzhu_shouru = 0.0000 #补助收入
qita_shouru = 0.0000 #其他收入
fulifei_shouru = 0.0000 #福利费收入
jingying_zhichu = 0.0000 # 经营支出
guanlifei_zhichu = 0.0000 #管理费支出
ganbu_baochou = 0.0000 # 干部报酬
shubaofei = 0.0000 #书报费
qita_zhichu = 0.0000 #其他支出
fulifei_zhichu = 0.0000 # 福利费支出
fenpeinonghu_ji_touzizhe = 0.0000 #分配农户及投资者
gudingzichan_goujian = 0.0000 #固定资产构建
#因为零值为0，不是空白，所以都可以转浮点型。
for r in range(3,row_raw):
inco_item = ws_raw.cell(row=r, column=2).value #income item收入项目
inco_valu = float(ws_raw.cell(row=r, column=4).value)
#round()函数比较迷，它不完全是按照四舍五入的原则来进位的，因此不建议大家使用。
# 它的保留值将保留到离上一位更近的一端（四舍六入），如果距离两端一样远，则会保留到偶数的一边。好了笔者也要绕晕了，反正笔者是不会用这种方法的。
#inco_valu = round(inco_valu/10000,4) #以万元为单位，保留4位小数
#print(inco_valu)
inco_valu = float('%.4f' % (inco_valu/10000)) #以万元为单位，保留4位小数
#print(inco_valu)
if inco_item =='一、经营收入 ':
jingying_shouru = inco_valu
if inco_item == '二、发包及上交收入 ':
fabao_ji_shangjiao_shouru = inco_valu
if inco_item == '三、投资收益 ':
touzi_shouyi = inco_valu
if inco_item == '四、农业税附加返还收入 ':
nongyeshui_fanhuan = inco_valu
if inco_item == '五、补助收入 ':
buzhu_shouru =inco_valu
if inco_item == '六、其他收入 ':
qita_shouru = inco_valu
if inco_item == '七、福利费收入 ':
fulifei_shouru =inco_valu
expe_item = ws_raw.cell(row=r, column=5).value #expend item支出项目
expe_valu = float(ws_raw.cell(row=r, column=7).value) #expend value
#expe_valu = round(expe_valu/10000,4) #以万元为单位，保留4位小数
expe_valu = float('%.4f' % (expe_valu / 10000)) # 以万元为单位，保留4位小数
if expe_item == '一、经营支出 ':
jingying_zhichu = expe_valu
if expe_item == '二、管理费支出 ':
guanlifei_zhichu = expe_valu
if expe_item == ' 干部报酬 ' or expe_item == ' 干部报酬 ':
ganbu_baochou = expe_valu
if expe_item == ' 书报费 ' or expe_item == ' 书刊费 ':
shubaofei = expe_valu
if expe_item == '三、其他支出 ':
qita_zhichu = expe_valu
if expe_item == '、福利费支出 ':
fulifei_zhichu = expe_valu
if expe_item == '五、分配农户及投资者 ':
fenpeinonghu_ji_touzizhe = expe_valu
if expe_item == '六、固定资产购建 ':
gudingzichan_goujian = expe_valu
#清产核资表格中的其他收入
qita_shouru_qingchanhezi = nongyeshui_fanhuan + qita_shouru + fulifei_shouru
#total income总收入
totl_inco = jingying_shouru + fabao_ji_shangjiao_shouru +touzi_shouyi + buzhu_shouru +qita_shouru_qingchanhezi
# 清产核资表格中的其他收支出
qita_zhichu_qingchanhezi = qita_zhichu + fulifei_zhichu + fenpeinonghu_ji_touzizhe + gudingzichan_goujian
totl_expe = jingying_zhichu + guanlifei_zhichu + qita_zhichu_qingchanhezi
wb_raw.save(raw_file_path)
# 释放资源，不然脚本无法打开，会处于锁定状态。
wb_raw.close()
shet_name = '收益分配统计表'
wb_targ = load_workbook(targ_file_path)
ws_targ = wb_targ[shet_name]
ws_targ.cell(row=8, column=2).value = totl_inco
ws_targ.cell(row=8, column=3).value = jingying_shouru #万元为单位，保留四位小数
ws_targ.cell(row=8, column=4).value = fabao_ji_shangjiao_shouru #发包及上交收入
ws_targ.cell(row=8, column=5).value = touzi_shouyi #投资收益
ws_targ.cell(row=8, column=6).value = buzhu_shouru #补助收入
ws_targ.cell(row=8, column=7).value = qita_shouru_qingchanhezi #清产核资表中的其他收入
ws_targ.cell(row=8, column=8).value = totl_expe #总支出
ws_targ.cell(row=8, column=9).value = jingying_zhichu #经营支出
ws_targ.cell(row=8, column=10).value = guanlifei_zhichu #管理费用
ws_targ.cell(row=8, column=11).value = ganbu_baochou #干部报酬
ws_targ.cell(row=8, column=12).value = shubaofei #书报费、报刊费
ws_targ.cell(row=8, column=13).value = qita_zhichu_qingchanhezi #清产核资表中的其他支出
wb_targ.save(targ_file_path)
wb_targ.close()
return totl_inco, totl_expe
def func():
raw_file_list,raw_file_path_list =get_file_list(data_sour_path)
#print(1)
#print(raw_file_list)
temp_file_list, temp_file_path_list = get_file_list(temp_tabl_path)
#模板文件夹template table的文件复制到output table文件夹下
outp_tabl_list = []
for i in range(len(temp_file_list)):
outp_file_path =os.path.join(outp_tabl_path, temp_file_list[i])
shutil.copy(temp_file_path_list[i], outp_file_path)
outp_file_list, outp_file_path_list = get_file_list(outp_tabl_path)
print(outp_file_path_list)
print('gather data to excel')
for i in range(0,len(outp_file_path_list)):
print(f'尝试第{i + 1}个文件读写。')
outp_fil_nam = outp_file_list[i]
orgz = outp_fil_nam.replace('.xlsx','').replace('台山市冲蒌镇','') #organization组织单位
#print(orgz)
list_tabl_accn_disc = ['资产负债表', '收益分配表', '应收款', '应付款', '固定资产明细表', '在建工程明细表']
tabl_type = '收支明细表'
if orgz in accnt_dict2.keys(): #前锋村升平经济合作社不在字典里，就会主键报错KeyError
accnt_Num = accnt_dict2[orgz]
raw_file = '2021 {}{}{}.xlsx'.format(accnt_Num, orgz, tabl_type) #构造文件名，如2021 044八家经济联合社固定资产明细表.xls
print(accnt_Num, orgz)
raw_file_path = os.path.join(data_sour_path, raw_file)
if os.path.exists(raw_file_path):
#
totl_inco, totl_expe = gath_data(raw_file_path =raw_file_path, targ_file_path = outp_file_path_list[i])
print(f'总收入、总支出分别是:{totl_inco}, {totl_expe}')
print('总收入、总支出分别是:{}, {}'.format(totl_inco, totl_expe))
resu_file_path = r'2021年度清产核资填写数据情况表.xlsx'
wb_resu = load_workbook(resu_file_path) # workbook result
ws_resu = wb_resu['Sheet1']
for row_resu in range(4, 232):
if str(ws_resu.cell(row=row_resu, column=2).value) == str(accnt_Num): # 都是string
ws_resu.cell(row=row_resu, column=24).value = 1
ws_resu.cell(row=row_resu, column=25).value = totl_inco
ws_resu.cell(row=row_resu, column=26).value = totl_expe
wb_resu.save(resu_file_path)
wb_resu.close()
if __name__ == "__main__":
func()
480
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
