Powershell pivot Table creation


This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of sanketh sanketh 2 weeks, 4 days ago.

  • Author
  • #74477
    Profile photo of sanketh

    Hi Guys,

    I am facing issue with the pivot table creation through powershell. Below is the code that I am using

    # 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()
    #Connect to first worksheet to rename and make active
    $pivotSheet.Name ="Pivot"
    $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)= 'Environment'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column)= 'Drive'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column)= 'TotalCapacity(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column)= 'Used Capacity(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column)= 'Free Space(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column)= 'Freespace %'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    #Now it is time to add the data into the worksheet!
    #Increment Row and reset Column back to first column
    $Column = 1
    $disks = Get-WmiObject -ComputerName "sanky555" -Class Win32_LogicalDisk -Filter "DriveType = 3" 
    foreach($disk in $disks) 
            $deviceID = $disk.DeviceID; 
            $volName = $disk.VolumeName; 
            [float]$size = $disk.Size; 
            [float]$freespace = $disk.FreeSpace;  
            $percentFree = [Math]::Round(($freespace / $size) * 100, 2); 
            $sizeGB = [Math]::Round($size / 1073741824, 2); 
            $freeSpaceGB = [Math]::Round($freespace / 1073741824, 2); 
            $usedSpaceGB = $sizeGB - $freeSpaceGB; 
            $color = $whiteColor; 
            Write-Host $deviceID $volName $size $freespace $percentFree $freeSpaceGB $usedSpaceGB 
            $serverInfoSheet.Cells.Item($row,$column)= "Test";$Column+=1;
            $serverInfoSheet.Cells.Item($row,$column)= $deviceID;$Column+=1;
            $serverInfoSheet.Cells.Item($row,$column)= $sizeGB;$Column+=1;
            $serverInfoSheet.Cells.Item($row,$column)= $usedSpaceGB;$Column+=1;
            $serverInfoSheet.Cells.Item($row,$column)= $freeSpaceGB;$Column+=1;
            $serverInfoSheet.Cells.Item($row,$column)= $percentFree;$Column+=1;
        #Increment to next row and reset Column to 1
        $Column = 1
    # rename workbook
    #$workbook = $workbook
    #$workbook = $excel.Worksheets.add()
    # Get sheets
    $ws3 = $workbook.worksheets | where {Write-Host "Sheet Name;"$_.name} #< ------- Selects sheet 3
    $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"
    $selection = $ws3.Range($range1, $range2)
    #Write-Host "R2:"$range2
    $PivotTable = $workbook.PivotCaches().Create($xlDatabase,$selection,$xlPivotTableVersion10)
    $PivotTable.CreatePivotTable("Pivot!R1C1","Tables1") | Out-Null 
    $workbook.ShowPivotTableFieldList = $true 
    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")
    $PivotFields.DragToRow= $false
    $PivotFields.Orientation = $xlDataField
    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Farms/Classification")
    $PivotFields.Orientation = $xlRowField
    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Environment")
    $PivotFields.Orientation = $xlColumnField
    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Free Space(GB)")
    $PivotFields.DragToRow= $false
    $PivotFields.Orientation = $xlDataField

    Below is the error that I receive.

    Unable to get the PivotTables property of the Worksheet class

    for the line

    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")

    . Suprisingly this issue happens to be only when I try to create a pivot table in the new excel sheet. can you please help me to identify the issue here?

  • #74516
    Profile photo of Don Jones
    Don Jones

    Magazines have "issues;" you have a "problem." Part of that problem is that the Excel programmability components are ancient and COM-based, and sometimes get weird in .NET – and thus, in PowerShell. You might consider posting your question on StackOverflow – it gets a larger developer audience, and what you're dealing with isn't really a PowerShell problem. It's pure COM and .NET, despite the fact that you're accessing it from within PowerShell. The audience over there likely has more experience with this kind of thing.

  • #74549
    Profile photo of sanketh


    Thank you for the suggestion, but I have resolved it on my own. I just had to replace




    variable in-order to change the reference. But now it seems like I am facing a new problem. Summation of two coumns is automatically generating a


    column which is getting populated in


    section which should have been in

    columns section

    .Can you please help me out with this problem?

You must be logged in to reply to this topic.