更新时间:2023-02-03 16:54:14
数据透视表和字段的创建存在一些问题.
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