用vba对数据进行合并计算、自动填写数据

合并计算：利用数组去重的方法把相同名称的行汇总计算

Private Sub CommandButton1_Click()

Set d = CreateObject("scripting.dictionary")

myArray = Selection

Dim n
For Each n In myArray
    If Not d.exists(n) Then d.Add n, Application.WorksheetFunction.Match(n, Selection, 0)
Next

k = d.keys
t = d.items

For m = 0 To d.Count - 1
    Sheets("清分明细").Range("A" & Sheets("清分明细").Range("A1").End(xlDown).Row + 1).Value = k(m)   '单位名
    Sheets("清分明细").Range("B" & Sheets("清分明细").Range("B1").End(xlDown).Row + 1).Value = Application.WorksheetFunction.SumIf(Selection, k(m), Sheets("采购明细").Range("H" & Selection.Row & ":H" & Selection.Row + Selection.Rows.Count))    '供货总价
    Sheets("清分明细").Range("C" & Sheets("清分明细").Range("C1").End(xlDown).Row + 1).Value = Application.WorksheetFunction.SumIf(Selection, k(m), Sheets("采购明细").Range("I" & Selection.Row & ":I" & Selection.Row + Selection.Rows.Count))    '预付款
    
    Sheets("清分明细").Range("D" & Sheets("清分明细").Range("D1").End(xlDown).Row + 1).Value = Sheets("采购明细").Range("J" & Selection.Row - 1 + t(m)).Value  '政府合同金额
    Sheets("清分明细").Range("E" & Sheets("清分明细").Range("E1").End(xlDown).Row + 1).Value = Sheets("采购明细").Range("M" & Selection.Row - 1 + t(m)).Value  '业务编号
Next
End Sub

Private Sub CommandButton1_Click()

Dim name As String
For Each rng1 In Selection
name = rng1

For Each Rng In Sheets("清分明细").Range("A2：A" & Range("A1").End(xlDown).Row)  '这里导致卡顿
    If Rng = name Then
        a = Rng.Row
        Sheets("清分明细").Range("F" & a & ":H" & a).Value = Sheets("提款申请").Range("G" & rng1.Row & ":I" & rng1.Row).Value
        Sheets("清分明细").Range(a & ":" & a).Interior.Color = 65535
    End If
Next

Next
End Sub

自动填写数据

Private Sub CommandButton1_Click()
Shell "cmd.exe /c \\172.20.200.200\Shared\02项目文档\100产品代理业务\0君子\test\Output.bat"
End Sub

python \\172.20.200.200\Shared\02项目文档\100产品代理业务\0君子\test\OutputReport.py

# coding=gbk

import xlrd
from docxtpl import DocxTemplate

url ="\\\\172.20.200.200\\Shared\\02项目文档\\100产品代理业务\\0君子\\2020君子业务.xlsm"

workbook = xlrd.open_workbook(url)
sheet1_object = workbook.sheet_by_name(sheet_name="业务详情")

nrows = sheet1_object.nrows

inputs = input("请输入业务编号在第几列，如A列为第0列:")
dict = {}

for i in range(0,nrows):
    dict[sheet1_object.cell_value(rowx=i,colx=0)] = sheet1_object.cell_value(rowx = i,colx = int(inputs))

docurl = "\\\\172.20.200.200\\Shared\\02项目文档\\100产品代理业务\\0君子\\test\\代理填写模板.docx"
savepathname = "\\\\172.20.200.200\\Shared\\02项目文档\\100产品代理业务\\0君子\\test\\代理业务"+ inputs +".docx"

doc = DocxTemplate(docurl) #加载模板文件
doc.render(dict) #填充数据
doc.save(savepathname) #保存目标文件