Powershell pivot Table creation

Welcome Forums General PowerShell Q&A Powershell pivot Table creation

This topic contains 2 replies, has 2 voices, and was last updated by

1 year, 7 months ago.

  • Author
  • #74477

    Points: 0
    Rank: Member

    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

    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

    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

    Points: 0
    Rank: Member


    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?

The topic ‘Powershell pivot Table creation’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort