Powershell pivot Table creation

This topic contains 2 replies, has 2 voices, and was last updated by  sanketh 3 months, 1 week ago.

  • Author
    Posts
  • #74477

    sanketh
    Participant

    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
    Clear-Host
    $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=$workbook.Worksheets.Add();
    $pivotSheet=$workbook.Worksheets.Item("Sheet1")
    $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).Font.Bold=$True
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= 'Drive'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
    $Column++
    
    $serverInfoSheet.Cells.Item($row,$column)= 'TotalCapacity(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= 'Used Capacity(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= 'Free Space(GB)'
    $serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
    $serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
    $Column++
    $serverInfoSheet.Cells.Item($row,$column)= 'Freespace %'
    $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
    $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;
            
    
        $Column=1;
        $row++
    }
        #Increment to next row and reset Column to 1
        $Column = 1
        $row++
    
    
    
    # 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"
    
    $range1=$ws3.range("A1")
    $range1=$ws3.Range($range1,$range1.End($xlDirection::xlDown))
    $range2=$ws3.range("H1")
    $range2=$ws3.Range($range2,$range2.End($xlDirection::xlDown))
    $selection = $ws3.Range($range1, $range2)
    #Write-Host "R2:"$range2
    $PivotTable = $workbook.PivotCaches().Create($xlDatabase,$selection,$xlPivotTableVersion10)
    $PivotTable.CreatePivotTable("Pivot!R1C1","Tables1") | Out-Null 
    [void]$ws3.select();
    $workbook.ShowPivotTableFieldList = $true 
    $PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")
    $PivotFields.DragToRow= $false
    #$PivotFields.IncludeNewItemsInFilter=$true;
    $PivotFields.Orientation = $xlDataField
    
    #$PivotFields.Position=1
    $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

    Don Jones
    Keymaster

    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

    sanketh
    Participant

    Hi,

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

    ws3

    with

    pivotSheet

    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

    values

    column which is getting populated in

    rows

    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.