将System.Data.IDataReader管道传入ImportExcel的Export-Excel函数失败排查

我有一个由SQL作业调用的PowerShell脚本，通过ImportExcel模块的Export-Excel函数将SQL数据库查询结果写入Excel文件。该脚本在中等规模数据集上表现良好，但处理大型数据集时速度极慢。推测这与需先将查询结果加载到System.Data.DataTable有关，因此修改了Export-Excel使其支持System.Data.IDataReader，性能得到大幅提升。但仅当通过Export-Excel的InputObject参数显式传入System.Data.IDataReader时能正常工作，通过管道传入则会生成空Excel文件。为验证管道代码逻辑，将相关代码移至显式传入InputObject时执行的begin块，可正常运行。

可行代码 #

Export-Excel -InputObject $reader -Path $filePath -NoNumberConversion * -FreezeTopRow -AutoFilter -MoveToStart

失败代码 #

$reader | Export-Excel -Path $filePath -NoNumberConversion * -FreezeTopRow -AutoFilter -MoveToStart

process块相关代码 #

if ($null -ne $InputObject -and ($null -ne $InputObject.GetType().GetInterface("IDataReader")) ) {
    # get field names the first time around
    if ($firstTimeThru) {
        $firstTimeThru = $false
        $script:Header = @()
        for ($j = 0; $j -lt $InputObject.FieldCount; $j++) {
            $script:Header += $InputObject.GetName($j)
        }

        # region Write Headers to worksheet
        if ($DisplayPropertySet -and ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0) ) {
            $script:Header = $script:Header | Where-Object { $_ -notin $ExcludeProperty }
        }

        if ( ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0)) {
            foreach ($exclusion in $ExcludeProperty) { 
                $script:Header = $script:Header -notlike $exclusion 
            }
        }

        if ($NoHeader) {
            # Don't push the headers to the spreadsheet
            $row -= 1
        }
        else {
            $ColumnIndex = $StartColumn
            foreach ($Name in $script:Header) {
                $ws.Cells[$row, $ColumnIndex].Value = $Name
                Write-Verbose "Cell '$row`:$ColumnIndex' add header '$Name'"
                $ColumnIndex += 1
            }
        }
        #endregion
    }
                     
    #region Add databody values
    $row++
    $ColumnIndex = $StartColumn
    foreach ($Name in $script:Header) {
        $j = $InputObject.GetOrdinal($Name)

        $fieldType = $InputObject.GetFieldType($j)
        $fieldValue = $null

        try {
            switch ($fieldType) {
                { $_ -is [Int32] } {
                    $fieldValue = $InputObject.GetInt32($j); break
                }
                { $_ -is [String] } {
                    $fieldValue = $InputObject.GetString($j); break
                }
                { $_ -is [Boolean] } {
                    $fieldValue = $InputObject.GetBoolean($j); break
                }
                { $_ -is [DateTime] } {
                    $fieldValue = $InputObject.GetDateTime($j); break
                }
                { $_ -is [TimeSpan] } {
                    $fieldValue = [TimeSpan]$InputObject.GetValue($j); break
                }
                { $_ -is [Decimal] } {
                    $fieldValue = $InputObject.GetDecimal($j); break
                }
                { $_ -is [Double] } {
                    $fieldValue = $InputObject.GetDouble($j); break
                }
                { $_ -is [Boolean] } {
                    $fieldValue = $InputObject.GetBoolean($j); break
                }
                { $_ -is [Float] } {
                    $fieldValue = $InputObject.GetFloat($j); break
                }
                { $_ -is [Single] } {
                    $fieldValue = $InputObject.GetFloat($j); break
                }
                { $_ -is [Int64] } {
                    $fieldValue = $InputObject.GetInt64($j); break
                }
                { $_ -is [Int16] } {
                    $fieldValue = $InputObject.GetInt16($j); break
                }
                { $_ -is [Byte] } {
                    $fieldValue = [int]$InputObject.GetByte($j); break
                }
                { $_ -is [Char] } {
                    $fieldValue = $InputObject.GetChar($j).ToString(); break
                }
                { $_ -is [Guid] } {
                    $fieldValue = $InputObject.GetGuid($j).ToString(); break
                }
                { $_ -is [Object] } {
                    $fieldValue = $InputObject.GetValue($j); break
                }
                default {
                    throw "Unsupported field type: $($fieldType.FullName)"
                }
            }

            if ($InputObject.IsDBNull($j)) { $fieldValue = $null }

            if ($fieldType -is [DateTime]) {
                $ws.Cells[$row, $ColumnIndex].Value = $fieldValue
                $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = 'm/d/yy h:mm' # This is not a custom format, but a preset recognized as date and localized.
            }
            elseif ($fieldType -is [TimeSpan]) {
                $ws.Cells[$row, $ColumnIndex].Value = $fieldValue
                $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = '[h]:mm:ss'
            }
            elseif ($fieldType -is [System.ValueType]) {
                $ws.Cells[$row, $ColumnIndex].Value = $fieldValue
                if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
            }
            elseif ($fieldType -isnot [String] -or $null -eq $fieldValue ) {
                #Other objects or null.
                if ($null -ne $fieldValue ) { $ws.Cells[$row, $ColumnIndex].Value = $fieldValue.ToString() }
            }
            elseif ($fieldValue[0] -eq '=') {
                $ws.Cells[$row, $ColumnIndex].Formula = ($fieldValue -replace '^=', '')
                if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
            }
            else {
                if ( $NoHyperLinkConversion -ne '*' -and # Put the check for 'NoHyperLinkConversion is null' first to skip checking for wellformedstring
                        $NoHyperLinkConversion -notcontains $Name -and
                        [System.Uri]::IsWellFormedUriString($fieldValue, [System.UriKind]::Absolute)
                    ) { 
                    if ($fieldValue -match "^xl://internal/") {
                        $referenceAddress = $fieldValue -replace "^xl://internal/" , ""
                        $display = $referenceAddress -replace "!A1$"   , ""
                        $h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display
                        $ws.Cells[$row, $ColumnIndex].HyperLink = $h
                    }
                    else { $ws.Cells[$row, $ColumnIndex].HyperLink = $fieldValue }
                    $ws.Cells[$row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
                    $ws.Cells[$row, $ColumnIndex].Style.Font.UnderLine = $true
                }
                else {
                    $number = $null
                    if ( $NoNumberConversion -ne '*' -and # Check if NoNumberConversion isn't specified. Put this first as it's going to stop the if clause. Quicker than putting regex check first
                        $numberRegex.IsMatch($fieldValue) -and # and if it contains digit(s) - this syntax is quicker than -match for many items and cuts out slow checks for non numbers
                        $NoNumberConversion -notcontains $Name -and
                        [Double]::TryParse($fieldValue, [System.Globalization.NumberStyles]::Any, [System.Globalization.NumberFormatInfo]::CurrentInfo, [Ref]$number)
                    ) {
                        $ws.Cells[$row, $ColumnIndex].Value = $number
                        if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
                    }
                    else {
                        $ws.Cells[$row, $ColumnIndex].Value = $fieldValue
                    }
                }
            }
        }
        catch { Write-Warning -Message "Could not insert the '$Name' property at Row $row, Column $ColumnIndex" }

        $ColumnIndex += 1
    }
    #endregion
}

问题根源 #

PowerShell管道会自动枚举可枚举对象，而IDataReader本身实现了IEnumerable接口。当你将$reader通过管道传入时，PowerShell会尝试枚举它，每次管道传递的是IDataReader的当前记录（而非整个IDataReader对象），但你的process块代码是期望接收完整的IDataReader对象来逐行读取数据，导致实际处理的是单个记录而非读取器本身，最终没有数据写入Excel。

解决方法 #

1. 阻止管道枚举：使用逗号运算符将IDataReader包装成单元素数组，这样PowerShell会传递整个对象而非枚举它：

,$reader | Export-Excel -Path $filePath -NoNumberConversion * -FreezeTopRow -AutoFilter -MoveToStart

2. 修改函数参数定义：在Export-Excel的参数定义中，将InputObject的类型指定为[IDataReader]，或者添加[Parameter(ValueFromPipeline)]属性时，确保PowerShell不会自动枚举该类型。例如：

[Parameter(ValueFromPipeline=$true)]
[IDataReader]$InputObject

这样PowerShell会识别该类型不需要枚举，直接传递整个对象。

3. 调整process块逻辑：如果需要兼容管道传递记录的场景，可修改代码判断当前InputObject是IDataReader还是单个记录，但这会破坏原有的性能优化逻辑，不推荐。

补充说明 #

当你通过-InputObject显式传入时，PowerShell不会自动枚举对象，直接传递完整的IDataReader，所以代码能正常工作。而管道传递时的自动枚举是PowerShell的默认行为，针对实现了IEnumerable的类型都会触发。

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