powershell script to create excel pivot table

1
votes

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

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

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

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

Thank you for the help in advanced.

2
excelpowershellpivot-table
The correct Excel syntax would be WB.PivotCaches.Add(...).CreatePivotTable() but if you look at your $PivotTable variable the line you're receiving the error on would be read as $workbook.PivotCaches().Create(...).CreatePivotTable() which would be incorrect syntax (I think) Here's an MSDN article about using this method with Excel that might help msdn.microsoft.com/en-us/library/office/… – SierraOscar

2 Answers

4
votes

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.

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))

Note the definition of $xlDirection as 

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

and for the second column:

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

and combine them into a single selection:

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

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

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

Here is the entire code:

# 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

EDIT:

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

or this one

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

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

Try this

$excel = New-Object -ComObject Excel.application
$excel.Visible = $True
$wb = $excel.Workbooks.Open('C:\Users\XYZ\Desktop\new.xlsx')
$ws2 = $wb.Sheets.Add()

#$ws2.Select()
$row=12
$pivCache = $wb.PivotCaches().Create(1,"Sheet1!R1C1:R"+$row+"C5",4)
$pivTable = $pivCache.CreatePivotTable("Sheet4!R1C1","PivotTable1",4)
$shape = $ws2.Shapes.AddChart()
#$shape.Select()
$chart = $shape.Chart
$chart.ChartType = 4
#$range = $ws2.Range("A1:C18")
#$chart.SetSourceData($range)
#$shape.IncrementLeft(19)
#$shape.IncrementTop(15)
$db2Field = $pivTable.AddDataField($pivTable.PivotFields("db2cpu"),"Sum of DB2 CPU",-4157)
$inField = $pivTable.PivotFields("intime")
$inField.Orientation = 1
$inField.Position = 1
$fidField = $pivTable.PivotFields("fid")
$fidField.Orientation = 2
$fidField.Position = 1