|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 cztanghao 于 2024-1-28 21:28 编辑
如何根据货币资金明细表中M列打勾的银行用VBA汇总到这个列表里:
通过python自动化提取汇总,并写入的方案分享。excel文件不知道怎么上传,可以去vba程序开发版块看原贴的excel。
# Version : 1.0
# Author : 汤灏
# File : aggregateBankDetail2.py
# Time : 2024/1/28 17:14
import openpyxl,re,os
wb=openpyxl.load_workbook('./工作簿1.xlsx')
ws1=wb['货币资金明细表']
ws2=wb['长短借款明细表']
# nwb=openpyxl.Workbook()
# nws=nwb.active
nwb=openpyxl.load_workbook('./写入用1.xlsx')
nws=nwb.active
bankList=['嘉兴银行', '金华银行', '恒丰银行', '杭州银行', '工商银行', '浙江杭州余杭农村商业银行', '杭州联合农村商业银行', '湖州银行']
print(ws1.title)
for c in ws1['a']:
if c.value=='银行存款':
rowStart=c.row+1
print(rowStart)
dicAgg={}
for row in ws1.iter_rows(min_row=rowStart,min_col=1,max_row=ws1.max_row,max_col=6,values_only=True):
if row[0] is not None:
for bank in bankList:
if bank in row[0] :
# dicAgg.setdefault(row[0],[]).append([row[2],None,None,None,None,None,row[0],row[1],row[4] ])
dicAgg.setdefault(row[0],[row[2],None,None,None,None,None,row[0].strip(),row[1],row[4],None,None,None ])
# print(dicAgg)
for r in ws2.iter_rows(min_row=9,min_col=1,max_row=ws2.max_row):
if r[0].value is None:
break
if r[0].value in dicAgg:
dicAgg[r[0].value][3]=r[7].value
dicAgg[r[0].value][4]=r[13].value
dicAgg[r[0].value][-3]=r[8].value
dicAgg[r[0].value][-2]=r[2].value
dicAgg[r[0].value][-1]=r[3].value
print(dicAgg)
for rowNum,(key,value) in enumerate(dicAgg.items(),start=2):
print(rowNum,value)
for colNum,element in enumerate(value,start=3):
print(colNum,element)
if nws.cell(rowNum,colNum).value is None:
nws.cell(rowNum, colNum).value=element
nwb.save('ts1.xlsx')
|
|