Excel按类别分页导出PDF技巧

EXCEL根据类别分页预览或生成PDF的技术实现

在数据处理和报表生成过程中，经常需要将EXCEL数据按类别分页显示或导出为PDF。以下是几种实现方法，包含VBA代码示例和详细操作步骤。

---

使用VBA实现按类别分页预览

通过VBA代码可以动态调整分页符，实现按类别分页预览的效果。以下代码假设数据已按类别排序，且类别列在A列。

Sub InsertPageBreaksByCategory()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim currentCategory As String
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    currentCategory = ws.Cells(2, 1).Value
    
    ' 清除现有分页符
    ws.ResetAllPageBreaks
    
    ' 遍历数据插入分页符
    For i = 3 To lastRow
        If ws.Cells(i, 1).Value <> currentCategory Then
            ws.HPageBreaks.Add Before:=ws.Rows(i)
            currentCategory = ws.Cells(i, 1).Value
        End If
    Next i
    
    ' 进入分页预览模式
    ws.Activate
    ActiveWindow.View = xlPageBreakPreview
End Sub

---

使用Power Query分组后分页导出

Power Query可以对数据进行分组处理，再通过VBA将每组数据导出为独立PDF。

Sub ExportGroupsToPDF()
    Dim ws As Worksheet
    Dim pdfPath As String
    Dim categoryRange As Range
    Dim categoryCell As Range
    Dim printArea As String
    
    Set ws = ActiveSheet
    pdfPath = "C:\Output\Category_"
    
    ' 假设类别在A列且已排序
    Set categoryRange = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp