使用PowerShell结合Open XML为Excel工作表设置自动筛选

问题原因 #

你遇到的情况是因为Open XML SDK的Worksheet对象并没有直接暴露AutoFilter属性，需要手动创建AutoFilter元素并插入到工作表的XML结构中，而非通过现成的属性或方法直接设置。

替代GetPreceedingElement的实现 #

你找到的C#示例中的GetPreceedingElement方法，作用是找到插入AutoFilter的合适位置（通常是SheetData元素的前一个同级元素）。在PowerShell中，我们可以直接通过元素遍历找到这个位置，无需额外方法。

PowerShell实现代码 #

以下是适配Open XML 2.19的PowerShell代码，可实现为指定工作表添加自动筛选：

# 加载Open XML SDK程序集（路径请根据你的安装目录调整）
Add-Type -Path "C:\Program Files\Open XML SDK\V2.19\lib\DocumentFormat.OpenXml.dll"

function Add-AutoFilterToExcel {
    param(
        [string]$FilePath,
        [string]$SheetName,
        [string]$FilterRange  # 示例："A1:D1" 表示第一行A到D列
    )

    # 打开XLSX文档（可写模式）
    $spreadsheetDocument = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($FilePath, $true)
    try {
        # 获取工作簿部分
        $workbookPart = $spreadsheetDocument.WorkbookPart
        # 根据名称定位目标工作表
        $sheet = $workbookPart.Workbook.Sheets.Elements([DocumentFormat.OpenXml.Spreadsheet.Sheet]) | 
                 Where-Object { $_.Name -eq $SheetName } | Select-Object -First 1
        
        if (-not $sheet) {
            Write-Error "找不到名为 $SheetName 的工作表"
            return
        }

        # 获取对应的工作表部件
        $worksheetPart = $workbookPart.GetPartById($sheet.Id.Value)
        $worksheet = $worksheetPart.Worksheet

        # 创建AutoFilter元素并设置筛选范围
        $autoFilter = New-Object DocumentFormat.OpenXml.Spreadsheet.AutoFilter
        $autoFilter.Reference = $FilterRange

        # 确定插入位置：优先插入到SheetData元素之前（符合Open XML结构规范）
        $sheetData = $worksheet.Elements([DocumentFormat.OpenXml.Spreadsheet.SheetData]) | Select-Object -First 1
        if ($sheetData) {
            $worksheet.InsertBefore($autoFilter, $sheetData)
        } else {
            $worksheet.AppendChild($autoFilter)
        }

        # 保存修改
        $worksheet.Save()
        $spreadsheetDocument.Save()
    } finally {
        # 确保文档关闭
        $spreadsheetDocument.Close()
    }
}

# 调用示例
Add-AutoFilterToExcel -FilePath "C:\test.xlsx" -SheetName "Sheet1" -FilterRange "A1:E1"

代码说明 #

• 先加载Open XML SDK程序集，确保路径与你的安装版本匹配。
• 以可写模式打开文档，定位到目标工作表对应的WorksheetPart。
• 创建AutoFilter元素并指定筛选范围，按照Open XML规范插入到SheetData之前（这是自动筛选元素的标准位置）。
• 最后保存并关闭文档，确保修改生效。

原C#示例逻辑说明 #

你提供的C#代码核心逻辑为：

1. 打开文档并定位到目标工作表的WorksheetPart。
2. 创建AutoFilter元素并设置筛选范围。
3. 通过GetPreceedingElement找到合适的插入位置。
4. 插入元素并保存文档。

在PowerShell中我们无需额外封装GetPreceedingElement，直接定位SheetData并插入到其之前即可，实现更简洁直接。

内容的提问来源于stack exchange，提问作者marsh-wiggle