search

dfinke / ImportExcel

PowerShell module to import/export Excel spreadsheets, without Excel
https://www.powershellgallery.com/packages/ImportExcel/
Apache License 2.0
2.47k stars 399 forks source link

PivotTable - Report Layout #1182

Closed daviscraigs closed 2 years ago

daviscraigs commented 2 years ago

I am looking to change the Pivot table layout from the default "show in compact form" to "show in tabular form". Additional I would like to be able to set Subtotals to "Do not show subtotals". Sample Excel file attached,

ImportExcelPivotTableLayout.xlsx

dfinke commented 2 years ago

Check the New-PivotTableDefinition function. I believe there should be an example in the https://github.com/dfinke/ImportExcel/tree/master/Examples. Or clues in the unit __tests__

daviscraigs commented 2 years ago

I see the option for PivotTableStyle, but not for Layout

dfinke commented 2 years ago

Don't know off the top of my head, may be -NoTotalsInPivot. If not, you can create Pivot table Add-PivotTable, use -PassThru to get to the object model. Not sure it is granular enough, or if what you're actually looking for has been exposed.

dbrennand commented 2 years ago

Hi @daviscraigs

I also have a use case for this. I had a look into it a while ago. Sadly, it looks that OfficeOpenXml's ExcelPivotTable class supports compact and outline 😞

dbrennand commented 2 years ago

I tried the solution here but I couldn't get it working.

# Using -NoTotalsInPivot to set `RowGrandTotals` to false: https://github.com/dfinke/ImportExcel/blob/master/Public/Add-PivotTable.ps1#L130
# Using -PivotDataToColumn to set `DataOnRows` to false: https://github.com/dfinke/ImportExcel/blob/master/Public/Add-PivotTable.ps1#L135
Add-PivotTable -PivotTableName "Sum" -NoTotalsInPivot -Address $ExcelPackage.$WorkSheetName.Cells["I1"] `
    -SourceWorksheet $ExcelPackage.$WorkSheetName -SourceRange $TableSourceRange `
    -PivotRows "Row1", "Row2", "Row3", "Row4", "Row5" -PivotDataToColumn -PivotData @{"Row6" = "Sum" } `
    -Verbose:($PSBoundParameters["Verbose"] -eq $true) -ErrorAction Stop

"PivotTable:"
$ExcelPackage.$WorkSheetName.PivotTables.Compact
$ExcelPackage.$WorkSheetName.PivotTables.CompactData
$ExcelPackage.$WorkSheetName.PivotTables.Indent
$ExcelPackage.$WorkSheetName.PivotTables.RowGrandTotals
$ExcelPackage.$WorkSheetName.PivotTables.UseAutoFormatting
$ExcelPackage.$WorkSheetName.PivotTables.ShowMemberPropertyTips
$ExcelPackage.$WorkSheetName.PivotTables.DataOnRows

"Fields:"
$ExcelPackage.$WorkSheetName.PivotTables.Fields.Outline
$ExcelPackage.$WorkSheetName.PivotTables.Fields.Compact
$ExcelPackage.$WorkSheetName.PivotTables.Fields.ShowAll
$ExcelPackage.$WorkSheetName.PivotTables.Fields.SubtotalTop

PivotTable:
False
False
0
False
True
False
False

Fields:
False
False
...
False

My PivotTable is still not in Tabular form 😞

dbrennand commented 2 years ago

Hmmm. I'm also reading about someone trying to get a tabular layout using Closed XML by setting ClassicPivotTableLayout to true. Looking a bit deeper ClassicPivotTableLayout sets the GridDropZones value: https://github.com/ClosedXML/ClosedXML/pull/199/files

However, my PivotTable already has the GridDropZones set to true 😞

daviscraigs commented 2 years ago

@dbrennand I have not had a chance to go back and do any testing. Thanks for sharing what you have found.

dbrennand commented 2 years ago

Hi @daviscraigs

You may find my latest comment in #1196 useful 🙂