powershell - 使用 powershell 直接从 SQL 数据透视表

Question

我正在使用我在网上找到的 powershell 脚本的组合（如下所示）来创建一个带有数据透视表的 Excel 文档。但是，我只发现了数据透视表的数据源是已经通过 CSV 或其他类似方法导入到 excel 文档中的数据的示例。我可以让那些工作。但是，我的问题是我想绕过将 .csv 数据加载到 excel 中然后创建数据透视表的步骤......我的整个问题是我的数据集对于 excel 来说太大了......所以我想创建直接来自 SQL 连接的数据透视表。我无法弄清楚设置数据源的正确语法。下面是我最接近的尝试......应该使用“xlExternal”作为数据源吗？但是我该如何设置数据源呢？Micorsoft 的文档/API 非常有用，

我相信我坚持的唯一 1 或 2 行是这一行：

$qt = $ws.QueryTables.Add("ODBC;DSN=$connectionString", $ws.Range("A1"), 
$SQL) 

和/或这个：

$PivotTable = 
$wb.PivotCaches().Create($xlExternal,$selection,$xlPivotTableVersion10)

但是我的代码在这一行给出了例外：

$PivotTable.CreatePivotTable("R1C1","Tables1") | Out-Null 

错误是说：

Exception from HRESULT: 0x800A03EC
At C:\Users\me\Desktop\NEWpsCode.ps1:107 char:1
+ $PivotTable.CreatePivotTable("R1C1","Tables1") | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

所以，这是我的代码：

$DirectoryToSaveTo='C:\Users\me\Desktop\'
$Filename='myfile' 

$ServerName = "BLAH1"
$DatabaseName = "BLAH2"
$userName = "BLAH3"
$password = "BLAH4"

$connectionString = "Server = $ServerName; Database = $DatabaseName; User ID 
= $userName; Password = $password;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection 
$connectionString
$sqlConnection.Open()

$xlCenter=-4108 
$xlTop=-4160 
$xlOpenXMLWorkbook=[int]51 

$SQL=@" 
SELECT Account1,Account2,Account3 from myTable
"@ 

$excel = New-Object -Com Excel.Application #open a new instance of Excel 
$excel.Visible = $True
$wb = $Excel.Workbooks.Add() 
$currentWorksheet=1 
  if ($currentWorksheet-lt 4)  
  { 
    $ws = $wb.Worksheets.Item($currentWorksheet) 
  } 
  else   
  { 
    $ws = $wb.Worksheets.Add() 
  }
 $currentWorksheet += 1 

$qt = $ws.QueryTables.Add("ODBC;DSN=$connectionString", $ws.Range("A1"), 
$SQL) 

$xlPivotTableVersion12     = 3
$xlPivotTableVersion10     = 1
$xlCount                 = -4112
$xlDescending             = 2
$xlDatabase                = 1
$xlExternal     = 2
$xlHidden                  = 0
$xlRowField                = 1
$xlColumnField             = 2
$xlPageField               = 3
$xlDataField               = 4    
$xlDirection        = [Microsoft.Office.Interop.Excel.XLDirection]


$range1=$ws.range("A1")
$range1=$ws.range($range1,$range1.End($xlDirection::xlDown))
$range2=$ws.range("B1")
$range2=$ws.range($range2,$range2.End($xlDirection::xlDown))

$selection = $ws.range($range1, $range2)
$PivotTable = 
$wb.PivotCaches().Create($xlExternal,$selection,$xlPivotTableVersion10)
$PivotTable.CreatePivotTable("R1C1","Tables1") | Out-Null 


$filename = "$DirectoryToSaveTo$filename.xlsx"
if (test-path $filename ) { rm $filename } 
$wb.SaveAs($filename,  $xlOpenXMLWorkbook)
$wb.Saved = $True 
$wb.Close() 
$Excel.Quit() 
$wb = $Null 
$ws = $Null 
$Excel=$Null 

因此，如果有人可以指导我直接使用 SQL 连接设置数据透视表，那将非常有帮助！即使是一个非常简单的例子也会有很大帮助。

标签： powershellpivot-table