sql-server - 需要帮助通过 PowerShell 模块 "PSExcel"将工作表添加到现有 xlsx

Question

我正在开展一个项目，使用 MSSQL 数据库中的数据创建一些 Excel 报告，并且需要一些有关最终结果的帮助。

免责声明 - 我不太擅长 PowerShell 和 MSSQL)。

到目前为止，在互联网的帮助下，我已经成功创建了一个 .ps1 文件，该文件可以 self 提升，导入一个名为 PSExcel ( https://github.com/RamblingCookieMonster/PSExcel ) 的 PS 模块，导入 SQLPS 模块并运行三个单独的查询来导出三个单独的 .xlsx 文件。

我最后几部分的脚本是:

# Import PSExcel module
Import-Module C:\scripts-and-reports\Modules\PSExcel

# Import SQLPS module
Import-Module sqlps

# Import PSExcel module
Import-Module C:\scripts-and-reports\Modules\PSExcel

# Import SQLPS module
Import-Module sqlps

# Create individuals
invoke-sqlcmd -inputfile "C:\scripts-and-reports\individuals.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Individuals" -Path "C:\scripts-and-reports\Individuals.xlsx" -Table -Force

# Create joint parties
invoke-sqlcmd -inputfile "C:\scripts-and-reports\joint-parties.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Joint Parties" -Path "C:\scripts-and-reports\Joint Parties.xlsx" -Table -Force

# Create organisations
invoke-sqlcmd -inputfile "C:\scripts-and-reports\organisations.sql" -serverinstance "SERVER\INSTANCE" -database "DATABASE" | 
Export-XLSX -WorksheetName "Organisations" -Path "C:\scripts-and-reports\Organisations.xlsx" -Table -Force

我尝试将最后两个查询导出合并到第一个查询的导出中作为附加工作表，但没有成功，这样我只有一个 Excel 工作簿可以交给我的老板，但我认为我的处理方式一定是错误的。

当我阅读../Export-XLSX.ps1的第94行上的示例时并尝试通过更改文件名以相互匹配来在我的场景中实现它，最后一个查询替换输出的 .xlsx 文件中的第一个查询。这一定是因为-Force。将其更改为 -Append 不会有帮助，因为它会说该文件已经存在。

任何人都可以帮助我，首先向我展示哪里出错了，然后为我指出正确的方向(这可能最终会出现在演练中)。

请并谢谢!

---** 更新 **---

通过 @gms0ulman 对 Export-XLSX.ps1 的修复，它看起来可以正常工作，因为我已经使用不同的 SQL 查询对其进行了测试，以满足我的需要，并且它添加了工作表，一切正常。我用于测试的查询是

SELECT DISTINCT
case mt.[Status] 
    when 0 then 'In Progress' 
    When 1 then 'On Hold' 
    when 2 then 'Completed' 
    when 3 then 'Not Proceeding' 
else 'Unknown' end as MatterStatus,
mt.LastUpdatedOn as LastModified
from matter mt 
where mt.LastUpdatedOn >= '2016-07-01'  
    AND (mt.[status] = 0 or mt.[status] = 1)

虽然这个(以及我的 PS 脚本中的其他两个迭代)有效，但我的实际查询却不起作用。查询本身可以工作，第一次导出也是如此，但是当在 PS 中使用 -Append 以及 invoke-sqlcmd 和 invoke-sqlcmd -inputfile "query2.sql"-serverinstance ""-数据库“”| Export-XLSX -WorksheetName "Joint Party"-Path "C:\scripts-and-reports\Matter Details.xlsx"-Table -Append，两个附加的工作表出现错误:

Exceptions calling "SaveAs" with "1" argument(s): "Error saving file C:\scripts-and-reports\Matter Details.xlsx"

At C:\scripts-and-reports\Modules\PSExcel\Export-XLSX.ps1:496 char:13

$Excel.SaveAs($Path)

~~~~~~~~~~~~~~~~~~~~

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : InvalidOperationException

Answer 1

问题出在您正在使用的模块上。 -Append 应该可以。在 Export-XLSX.ps1 文件中，查看 351 和 371 行。

当您提供现有路径时，351 的计算结果为 true，并且 371 永远不会被执行。371 行是模块为您创建新工作表的位置。

if (($Append -or $ClearSheet) -and ($PSBoundParameters.ContainsKey('Excel') -or (Test-Path $Path)) ) # line 351
{
    $WorkSheet=$Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName}
    if($ClearSheet)
    {
        $WorkSheet.Cells[$WorkSheet.Dimension.Start.Row, $WorkSheet.Dimension.Start.Column, $WorkSheet.Dimension.End.Row, $WorkSheet.Dimension.End.Column].Clear()
    }
    if($Append)
    {
        $RealHeaderCount = $WorkSheet.Dimension.Columns
        if($Header.count -ne $RealHeaderCount)
        {
            $Excel.Dispose()
            Throw "Found $RealHeaderCount existing headers, provided data has $($Header.count)."
        }
        $RowIndex = 1 + $Worksheet.Dimension.Rows
    }
}
else
{
    $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)  #line 371
}

为了解决这个问题，我在 Export-XLSX.ps1 文件中添加了几行。现在，即使路径存在，它也会:

• 检查是否提供了 $WorksheetName
• 检查此工作表尚不存在
• 如果两者都成立，则创建新工作表。

请注意，您必须Remove-Module 和Import-Module 才能识别更改。您将需要使用-Append。我在第一个工作表上使用了 -Force ，而不是第二个工作表。此外，您可能会发现 -Verbose 很有帮助，因为它在脚本运行时为您提供更多信息 - 非常有利于调试。

if (($Append -or $ClearSheet) -and ($PSBoundParameters.ContainsKey('Excel') -or (Test-Path $Path)) )
{

    # New line: even if path exists, check for $WorkSheetName and that this worksheet doesn't exist
    if($WorkSheetName -and $Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName} -eq $null)
    {
      # if you have $WorksheetName and it doesn't exist, create worksheet.
      $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)
    }
    else
    {
        $WorkSheet=$Excel.Workbook.Worksheets | Where-Object {$_.Name -like $WorkSheetName}
        if($ClearSheet)
        {
            $WorkSheet.Cells[$WorkSheet.Dimension.Start.Row, $WorkSheet.Dimension.Start.Column, $WorkSheet.Dimension.End.Row, $WorkSheet.Dimension.End.Column].Clear()
        }
        if($Append)
        {
            $RealHeaderCount = $WorkSheet.Dimension.Columns
            if($Header.count -ne $RealHeaderCount)
            {
                $Excel.Dispose()
                Throw "Found $RealHeaderCount existing headers, provided data has $($Header.count)."
            }
            $RowIndex = 1 + $Worksheet.Dimension.Rows
        }
    } # this is also new.
}
else
{
    $WorkSheet = $Workbook.Worksheets.Add($WorkSheetName)
}