Iterate through PageField Items in Excel Pivot Table

• martedì 26 giugno 2012 18:15

   

   
  

  0

  The code below takes a CSV file (a table) and creates an excel file with pivot tables. I am having trouble iterating through all the PageField items with PowerShell. I know in VBA it is something like this, but i can't get this to translate to Powershell. Any ideas?

  VBA:

  Sub PivotPages()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim pi As PivotItem
     Set pt = ActiveSheet.PivotTables.Item(1)
     For Each pf In pt.PageFields
       For Each pi In pf.PivotItems
         pt.PivotFields(pf.Name).CurrentPage = pi.Name
       Next
     Next pf
  End Sub

  PowerShell:

  $filePathcsv ="C:\My Documents\Raw Data\20120625.txt"
  $filePathxlsx ="C:\Raw Data\20120625.xlsx"
  #Create the COM object and set application settings
  $excel = New-Object -ComObject excel.application
  $excel.Visible = $False
  $excel.DisplayAlerts = $False
  #Open the CSV file in excel
  $workbook = $excel.Workbooks.Open($filePathcsv)
  #Rename SHeet
  $workbook.worksheets.Item(1).Name = "Table"
  #Create the Pivot Table
  $pivot = $workbook.PivotCaches().Create(1, $workbook.worksheets.Item(1).UsedRange)
  $pivot.CreatePivotTable("","AR") | Out-Null
  #Set the new sheets with pivot tables on them
  $pivotsheet = $workbook.worksheets.Item(1)   
  #Add items to the pivot table
  $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Category_Description")
  $pivotfields.Orientation=1
  $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Five")
  $pivotfields.Orientation=2
  $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Total")
  $pivotfields.Orientation=4 
  #Add the filter items
  $pivotfields = $pivotsheet.PivotTables("AR").PivotFields("Abbreviation")
  $pivotfields.Orientation=3
  #Part that does not work!!!
  foreach ($pivotfields in $pivot.PageFields)
      {
         foreach($pageitems in $pivotfields.PivotItems)
           {
            $pivotfields.CurrentPage =                   pageitems.Name             
              #Once i have the iteration
              #Rename sheet, copy sheet and continue
            }
      }
  $workbook.worksheets.Item(1).Copy($workbook.worksheets.Item(1))
  #Finally move table to the front
  $workbook.worksheets.Item("Table").Move($workbook.worksheets.Item(1))
      
  $xlFormat = 51
  $workbook.SaveAS($filePathxlsx,$xlFormat)
  $workbook.Close()
  $excel.Quit()
  $excel.DisplayAlerts = $True
  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

  
  
  • Risposta
  • Citazione