Excel转Word报表自动化宏开发求助：多节动态内容与If..Then..Else应用

模板制作指南 #

解决模板难题的核心是用书签+自定义样式标记动态内容区域：

• 章节标题定位：在Word模板中，选中章节标题的占位位置（可先写<<章节标题>>），点击「插入」→「书签」，命名为Chapter_Title并保存。
• 条目区域标记：创建自定义样式（点击「开始」→「样式」→「新建样式」），命名为ItemEntry，设置好字体、缩进格式。在模板中添加一个带占位符（如<<条目内容>>、<<数值>>）的示例条目，应用该样式。

VBA代码示例 #

打开Excel的VBA编辑器（Alt+F11），先引用Microsoft Word对象库（工具→引用→勾选Microsoft Word xx.x Object Library），再插入模块粘贴以下代码：

Sub GenerateConsolidatedReport()
    Dim xlWs As Worksheet
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim lastRow As Long
    Dim i As Long
    Dim currentChapter As String
    
    ' 配置文件路径，请自行替换
    Const EXCEL_PATH As String = "C:\YourData.xlsx"
    Const TEMPLATE_PATH As String = "C:\YourTemplate.docx"
    Const OUTPUT_PATH As String = "C:\FinalReport.docx"
    
    ' 初始化Excel数据源
    Set xlWs = Workbooks.Open(EXCEL_PATH).Worksheets("Sheet1")
    lastRow = xlWs.Cells(xlWs.Rows.Count, "A").End(xlUp).Row
    
    ' 启动Word并加载模板
    Set wdApp = New Word.Application
    wdApp.Visible = True ' 调试时可见，发布可改为False
    Set wdDoc = wdApp.Documents.Open(TEMPLATE_PATH)
    
    ' 初始化并填充第一个章节标题
    currentChapter = xlWs.Cells(2, "A").Value
    wdDoc.Bookmarks("Chapter_Title").Range.Text = currentChapter
    
    ' 循环处理每一行数据
    For i = 2 To lastRow
        ' 切换章节时更新标题并插入分页（可选）
        If xlWs.Cells(i, "A").Value <> currentChapter Then
            currentChapter = xlWs.Cells(i, "A").Value
            wdDoc.Content.InsertBreak Type:=wdPageBreak
            With wdDoc.Content.Paragraphs.Add
                .Style = wdDoc.Styles("Heading 1") ' 用内置标题样式或自定义样式
                .Range.Text = currentChapter
            End With
        End If
        
        ' 插入条目内容
        With wdDoc.Content.Paragraphs.Add
            .Style = wdDoc.Styles("ItemEntry")
            .Range.Text = "• " & xlWs.Cells(i, "B").Value & "：" & xlWs.Cells(i, "C").Value
        End With
    Next i
    
    ' 删除模板中的示例条目
    For Each para In wdDoc.Content.Paragraphs
        If para.Style = wdDoc.Styles("ItemEntry") And para.Range.Text Like "*<<*>>*" Then
            para.Range.Delete
            Exit For
        End If
    Next
    
    ' 保存并释放资源
    wdDoc.SaveAs2 OUTPUT_PATH, FileFormat:=wdFormatXMLDocument
    MsgBox "报表已生成：" & OUTPUT_PATH
    
    wdDoc.Close: Set wdDoc = Nothing
    wdApp.Quit: Set wdApp = Nothing
    xlWs.Parent.Close SaveChanges:=False: Set xlWs = Nothing
End Sub

如果VBA调试遇到瓶颈，用pywin32库可实现相同逻辑，代码更灵活：

import win32com.client as win32

def generate_report():
    # 读取Excel数据
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    excel.Visible = False
    wb = excel.Workbooks.Open(r"C:\YourData.xlsx")
    ws = wb.Worksheets("Sheet1")
    last_row = ws.Cells(ws.Rows.Count, "A").End(-4162).Row  # xlUp对应值-4162
    
    # 加载Word模板
    word = win32.gencache.EnsureDispatch('Word.Application')
    word.Visible = True
    doc = word.Documents.Open(r"C:\YourTemplate.docx")
    
    current_chapter = ws.Cells(2, "A").Value
    # 填充初始章节标题
    doc.Bookmarks("Chapter_Title").Range.Text = current_chapter
    
    for i in range(2, last_row + 1):
        if ws.Cells(i, "A").Value != current_chapter:
            current_chapter = ws.Cells(i, "A").Value
            # 插入分页和新章节标题
            doc.Content.InsertBreak(win32.constants.wdPageBreak)
            para = doc.Content.Paragraphs.Add()
            para.Style = doc.Styles("Heading 1")
            para.Range.Text = current_chapter
        
        # 添加条目
        para = doc.Content.Paragraphs.Add()
        para.Style = doc.Styles("ItemEntry")
        para.Range.Text = f"• {ws.Cells(i, 'B').Value}：{ws.Cells(i, 'C').Value}"
    
    # 清理模板示例条目
    for para in doc.Content.Paragraphs:
        if para.Style.NameLocal == "ItemEntry" and "<<条目内容>>" in para.Range.Text:
            para.Range.Delete()
            break
    
    # 保存报表
    doc.SaveAs2(r"C:\FinalReport.docx", FileFormat=win32.constants.wdFormatXMLDocument)
    print("报表生成完成！")
    
    # 关闭应用
    doc.Close()
    word.Quit()
    wb.Close()
    excel.Quit()

if __name__ == "__main__":
    generate_report()

内容的提问来源于stack exchange，提问作者TamaraG