Powershell脚本创建Excel数据透视表

本文介绍了Powershell脚本创建Excel数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在处理Excel工作表时遇到问题，我不确定自己做错了什么.

I am having issues doing a pivot on a excel sheet and I am not sure what I am doing wrong.

这是我使用的powershell代码

Here is the powershell code I used

# requires excell COM #Create excel COM object $excel = New-Object -ComObject excel.application #Make Visible $excel.Visible = $True #Add a workbook $workbook = $excel.Workbooks.Add() #Remove other worksheets 1..2 | ForEach { $Workbook.worksheets.item(2).Delete() } #Connect to first worksheet to rename and make active $serverInfoSheet = $workbook.Worksheets.Item(1) $serverInfoSheet.Name = 'DiskInformation' $serverInfoSheet.Activate() | Out-Null #Create a Title for the first worksheet and adjust the font $row = 1 $Column = 1 #Create a header for Disk Space Report; set each cell to Bold and add a background color $serverInfoSheet.Cells.Item($row,$column)= 'ColumnA' $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48 $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $serverInfoSheet.Cells.Item($row,$column)= 'ColumnB' $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48 $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True $Column++ #Now it is time to add the data into the worksheet! #Increment Row and reset Column back to first column $row++ $Column = 1 $serverInfoSheet.Cells.Item($row,$column)= "a" $Column++ $serverInfoSheet.Cells.Item($row,$column)= "b" $Column++ #Increment to next row and reset Column to 1 $Column = 1 $row++ # rename workbook $workbook = $workbook #$workbook = $excel.Workbooks.Add() # Get sheets $ws3 = $workbook.worksheets | where {$_.name -eq "DiskInformation"} #<------- Selects sheet 3 $xlPivotTableVersion12 = 3 $xlPivotTableVersion10 = 1 $xlCount = -4112 $xlDescending = 2 $xlDatabase = 1 $xlHidden = 0 $xlRowField = 1 $xlColumnField = 2 $xlPageField = 3 $xlDataField = 4 # R1C1 means Row 1 Column 1 or "A1" # R65536C5 means Row 65536 Column E or "E65536" $PivotTable = $workbook.PivotCaches().Create($xlDatabase,"Report!R1C1:R65536C5",$xlPivotTableVersion10) $PivotTable.CreatePivotTable("Pivot!R1C1") | Out-Null [void]$ws3.Select() $ws3.Cells.Item(3,1).Select() $workbook.ShowPivotTableFieldList = $true $PivotFields = $ws3.PivotTables('Tables1') #.PivotFields("Computername") # Worksheet Name is Server $PivotFields.Orientation = $xlRowField $PivotFields.Position = 1

这是我收到的错误消息 使用参数"1"调用"CreatePivotTable"的异常:参数不正确.(来自HRESULT的异常:0x8007 0057(E_INVALIDARG))" 在此对象上找不到属性方向"；确保它存在并且可设置. 在C:\ ASM \ scripts \ Powershell \ TEST \ test_excel4.ps1:80 char:14 + $ PivotFields. <<<<方向= $ xlRowField + CategoryInfo:InvalidOperation:(Orientation:String)[]，RuntimeException + FullyQualifiedErrorId:PropertyNotFound

Here is the error message I receive Exception calling "CreatePivotTable" with "1" argument(s): "The parameter is incorrect. (Exception from HRESULT: 0x8007 0057 (E_INVALIDARG))" Property 'Orientation' cannot be found on this object; make sure it exists and is settable. At C:\ASM\scripts\Powershell\TEST\test_excel4.ps1:80 char:14 + $PivotFields. <<<< Orientation = $xlRowField + CategoryInfo : InvalidOperation: (Orientation:String) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound

在此对象上找不到属性位置"；确保它存在并且可设置. 在C:\ ASM \ scripts \ Powershell \ TEST \ test_excel4.ps1:81 char:14 + $ PivotFields. <<<<位置= 1 + CategoryInfo:InvalidOperation :(位置:字符串)[]，RuntimeException + FullyQualifiedErrorId:PropertyNotFound

Property 'Position' cannot be found on this object; make sure it exists and is settable. At C:\ASM\scripts\Powershell\TEST\test_excel4.ps1:81 char:14 + $PivotFields. <<<< Position = 1 + CategoryInfo : InvalidOperation: (Position:String) [], RuntimeException + FullyQualifiedErrorId : PropertyNotFound

感谢您提供高级帮助.

推荐答案

数据透视表和字段的创建存在一些问题.

There were a few issues with the creation of the pivot table and fields.

首先，最好在表中准确选择所需的行和列，而不必选择整个电子表格.

First, it's nicer to select exactly which rows and columns you want in the table, without selecting the entire spreadsheet.

执行此操作的一种好方法是从一个范围开始，然后要求Excel选择每个单元格，直到找到一个空单元格，像这样:

A nice way to do this is to start with a range, then ask Excel to select every cell until it finds an empty one, like this:

$range1=$ws3.range("A1") $range1=$ws3.Range($range1,$range1.End($xlDirection::xlDown))

请注意$xlDirection的定义为

$xlDirection = [Microsoft.Office.Interop.Excel.XLDirection]

第二栏:

$range2=$ws3.range("B1") $range2=$ws3.Range($range2,$range2.End($xlDirection::xlDown))

并将它们合并为一个选择:

and combine them into a single selection:

$selection = $ws3.Range($range1, $range2)

然后在创建数据透视表时，务必为其命名(即"Tables1").我们稍后将使用该名称来引用它:

Then when creating a pivot table, it's important to give it a name (i.e. "Tables1"). We will use that name later to reference it:

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

最后，我们要定义数据透视表中的哪个字段执行的操作以及其位置是什么.在我们的例子中，我们希望该列既是$ xlRowField又是$ xlDataField，我们只是覆盖如下所示的值:

Finally we want to define which field in the pivot table does what and what is its position. In our case we want the column to be both $xlRowField and $xlDataField and we just override the value like below:

$PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnA") $PivotFields.Position = 1 $PivotFields.Orientation = $xlRowField $PivotFields.Orientation = $xlDataField

这是完整的代码:

# requires excell COM #Create excel COM object $excel = New-Object -ComObject excel.application #Make Visible $excel.Visible = $True #Add a workbook $workbook = $excel.Workbooks.Add() #Remove other worksheets 1..2 | ForEach { $Workbook.worksheets.item(2).Delete() } #Connect to first worksheet to rename and make active $serverInfoSheet = $workbook.Worksheets.Item(1) $serverInfoSheet.Name = 'DiskInformation' $serverInfoSheet.Activate() | Out-Null #Create a Title for the first worksheet and adjust the font $row = 1 $Column = 1 #Create a header for Disk Space Report; set each cell to Bold and add a background color $serverInfoSheet.Cells.Item($row,$column)= 'ColumnA' $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48 $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True $Column++ $serverInfoSheet.Cells.Item($row,$column)= 'ColumnB' $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48 $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True $Column++ #Now it is time to add the data into the worksheet! #Increment Row and reset Column back to first column $row++ $Column = 1 $serverInfoSheet.Cells.Item($row,$column)= "a" $Column++ $serverInfoSheet.Cells.Item($row,$column)= "b" $Column++ #Increment to next row and reset Column to 1 $Column = 1 $row++ # rename workbook $workbook = $workbook #$workbook = $excel.Workbooks.Add() # Get sheets $ws3 = $workbook.worksheets | where {$_.name -eq "DiskInformation"} #<------- Selects sheet 3 $xlPivotTableVersion12 = 3 $xlPivotTableVersion10 = 1 $xlCount = -4112 $xlDescending = 2 $xlDatabase = 1 $xlHidden = 0 $xlRowField = 1 $xlColumnField = 2 $xlPageField = 3 $xlDataField = 4 $xlDirection = [Microsoft.Office.Interop.Excel.XLDirection] # R1C1 means Row 1 Column 1 or "A1" # R65536C5 means Row 65536 Column E or "E65536" $range1=$ws3.range("A1") $range1=$ws3.Range($range1,$range1.End($xlDirection::xlDown)) $range2=$ws3.range("B1") $range2=$ws3.Range($range2,$range2.End($xlDirection::xlDown)) $selection = $ws3.Range($range1, $range2) $PivotTable = $workbook.PivotCaches().Create($xlDatabase,$selection,$xlPivotTableVersion10) $PivotTable.CreatePivotTable("R1C6","Tables1") | Out-Null [void]$ws3.Select() $ws3.Cells.Item(3,1).Select() $workbook.ShowPivotTableFieldList = $true $PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnA") $PivotFields.Orientation = $xlRowField $PivotFields.Orientation = $xlDataField $PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnB") $PivotFields.Orientation = $xlRowField $PivotFields.Orientation = $xlDataField

一个自定义字段的示例是这样的:

An example of field customization is this one:

$PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnA") $PivotFields.Orientation = $xlHidden $PivotFields.Orientation = $xlDataField $PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnB") $PivotFields.Orientation = $xlHidden $PivotFields.Orientation = $xlDataField

或这个

$PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnA") $PivotFields.Orientation = $xlPageField $PivotFields.Orientation = $xlDataField $PivotFields = $ws3.PivotTables("Tables1").PivotFields("ColumnB") $PivotFields.Orientation = $xlPageField $PivotFields.Orientation = $xlDataField