Access PivotTable Settings

[ToddBackman]

I'm gathering large quantities of data from REST API queries (~10-20 columns with up to 25,000 rows) and outputting to Excel. So far ImportExcel has made this an almost trivial process. I have setup several different Pivot Tables to make data analysis a more practical undertaking. Again, fairly trivial. However, I've run into 2 settings I cannot figure out how to access.

1. Row Field Settings
   • Set subtotals to None
   • Set layout to "Show item labels in tabular form"
2. Save the Pivot Table with "Collapse Entire Field" for the Row field.

Does anyone know if/how I can access these settings?
Thanks,
Todd
P.S. I know this amount of data is better suited to a DB but unfortunately that isn't an option at this time.

[dfinke]

Sounds interesting. Try getting the "excel package" it gives you access to the Excel object model.

You can navigate to the Pivot Table thru the collections I believe. You can do this debugging a ps1 file on a test xlsx. Open-ExcelPackage

Once you find the Pivot Table you can use get-member to see properties and methods on the object.

Many are not settable via just the xlsx file for various reasons.

In the examples folder and tests there is code that manipulates the pivot tables. Some settings were done but that was adhoc and a while when folks asked about them.

[ToddBackman]

Thanks Doug,
I'll give this a shot tomorrow and let you know the results.

Todd

[dfinke]

Let me know as you go. Post your test script and xlsx and I can walk through it when I can.

[ToddBackman]

Hi Doug,
I'm in a highly secured environment so I can't post any actual examples which I know makes it difficult to help. When i posted this question I had hoped it was something simple I just missed.

I didn't have much time today to work on this but I was able to find something close to what I'm trying to change..

$xl = open-excelpackage -path $xlfile

$xl.workbook.worksheets[3].PivotTables[0].rowfields[0].Compact

unfortunately the value I'm looking for is "tabular" which doesn't exist. I suspect it's a combination of "Compact" & "Outline" settings. Currently both are set to $false.

So far I've struck out trying to change either of the values. I guess that will be to figure out next week!

Todd

[dfinke]

Got it. PivotTables are a strange beast. I have not tried this in Excel or with the module.

Any chance you could create a "safe" version of the code. Also, sometimes Googling for these and using Excel VBA there may be a hit how to make it work, then reverse engineer it to PS.

Another thought. I sometimes record a macro and then manipulate the PivotTable. Then I examine the VBA it generates.

[ToddBackman]

Hi Doug,
I hate to admit defeat but even creating 2 spreadsheets with just the "tabular" format change and spending several hours manually searching the excel objects I can't find property to change. To make matters worse, even if I could find the appropriate property, it appears all the PivotTable properties are locked and cannot be edited (or I'm in way over my head :-( ).

I appreciate you taking the time to reply but I'm just going to live with the std pivot layout.

[ToddBackman]

Sorry Doug... you asked for a "safe" version of my code.... this might be tooo safe to be of use but this is the idea...

$output = "c:\Test.xlsx"
$object1

• name = "name"
• info1 = "info1"
• info2 = "info2"
• num1 = #
• num2 = #
• num3 = #

$xl = $object1 | Export-Excel -Path $output -WorksheetName "RawData" -AutoFilter -BoldTopRow -FreezeTopRow -AutoSzie -TableStyle Medium6 -PassThru

$pt1 = New_PivotTableDefinition -PivotTableName "PT1" -PivotRows 'name','info1','info2' -PivotData @{"num1" = "sum"; "num2" = "Sum"; "num3" = "Sum"} -PivotDataToColumn

Export-Excel -ExcelPackage $xl -Worksheetname 'RawData' -pivotTableDefinition $pt1

[dfinke]

Yeah, I would've gone that rabbit hole too. That's partly how I built ImportExcel. PivotTables are weird animals. Not everything is open for us to work with.

[cristtos]

I have an existing "template" xlsx with one worksheet for data and one worksheet with a pivot predefined.
I copy the template in a working space, open it and populate it from a SQL query.
How can I change/set the source data for the existing pivot to the extent of de data in the data page (which includes finding the last row)?
Is this possible using Import Excel?
TIA

[cristtos]

Made a little bit of progress.
I managed to get a variable for the pivot, on inspection it seems to have correct, name, properties.
$UACC = $workbook.Workbook.Worksheets["UACC"] - this is the worksheet with the source data
$Pivot = $workbook.Workbook.Worksheets["Pivot"] - this is the worksheet with the pivot
$PDef1 = $Pivot.PivotTables["PivotTable1"] - the existing pivot is named PivotTable1

one of the properties listed for $PDef1 is $PDef2.CacheDefinition.SourceRange and is empty when inspecting

I have tried to set this property using this defined range:
$UACCdata = $UACC.Cells["A7:W3090"] - which is the data range as populated
when inspecting $UACCdata I get a long listing of each cell object
when inspecting $UACCdata.FullAddress I get 'UACC'!A7:W3090
With the range I tried:
$PDef1.CacheDefinition.SourceRange = $UACCdata but I get "Exception setting "SourceRange": "Object reference not set to an instance of an object."
I also tried
$PDef1.CacheDefinition.SourceRange = $UACCdata.FullAddress but I got "Exception setting "SourceRange": "Cannot convert the "'UACC'!A7:W3090" value of type "System.String" to type "OfficeOpenXml.ExcelRangeBase"."

Not sure what else to try, any suggestions?
TIA

[cristtos]

So I tried to be clever and defined a Table named SourceTable in the template, from the header and 2 extra rows.
Once the data sheet is populated I though I could extend the last row of the named table to the last rows in the populated data

The table definition retrieved with

$tableName = "SourceTable"  # Replace with the name of your named table
$namedTable = $UACC.Tables[$tableName]

has various attributes, including
$namedTable.Address
Inspecting $namedTable.Address.End I get

Row Column Address IsRef
--- ------ ------- -----
  9     24 X9      False

I thought I could set the values here, eg:
$namedTable.Address.End.row = 587
But I get 'row' is a ReadOnly property. and same for any other Address related properties.

So not clever enough.

Any ideas?

TIA

[dfinke]

yesh, pivot tables unfortunately are a challenge. I don't have an answer. There are properties that can only be set only when the pivot table is live in Excel. I don't have a list of those and there could also be issues or bugs in the EPPlus library being used to interact with the the XLSX file itself. - Sorry

[cristtos]

It looks that there is no way to "refresh" the pivot. As mentioned before, it seems that all the properties are read only.
I may have to give up.

I managed to automate 21 reports that do not use pivots - using ImportExcel
I may have to used a different approach and open the template in Excel as COM object for the rest of the reports.
But I was aiming for a system without Excel installed.

Ah, well...

[dfinke]

Understood. It is a limitation of only interacting with the file. Last ditch effort. If you haven;t already. See if you can search and see if EPPlus can do what you need. May be that only their newer paid version can, but may be able to glean how and port it.