PIvot Tables: "Show items in tabular form" & "Repeat Item Labels"

[astapelfeld]

Is it possible to use the options "Show items in tabular form" & "Repeat Item Labels" in ImportExcel?

https://support.microsoft.com/en-us/office/repeat-item-labels-in-a-pivottable-882bdb55-9cdc-4d8d-b531-8e96e41dea31

example.xlsx

[dfinke]

Thanks for the question @astapelfeld. I have not used that feature. I put a breakpoint on the Export-Excel line. I found the two properties I set to 1 but they did not make a difference.

I also unzipped your xlsx, and those properties are set in the XML.

I don't know what other pieces need to be set. If I figure it out, I will update.

$data = ConvertFrom-Csv @"
id,name,amount
1,Alfred,10
2,Bernd,20
3,John,30
4,Donald,40
5,Joe,50
6,Alfred,10
7,John,20
8,Alfred,30
"@

$xlfilename = './testOutlineViaCode.xlsx'
Remove-Item $xlfilename -ErrorAction SilentlyContinue

$excel = $data | Export-Excel $xlfilename -AutoSize -PivotRows Name, ID -Activate -PivotData @{Amount = 'Sum' } -PassThru
$tbl = $excel.Sheet1PivotTable.PivotTables #.outlinedata = $true

$tbl[0].Outline = 1
$tbl[0].OutlineData = 1

Close-ExcelPackage $excel -Show

[dbrennand]

Related to #1182 - I'm also looking for a way to show a PivotTable in tabular form. I'm going to play around with this today.

[dbrennand]

I've been looking at trying to get the "Repeat Item Labels" working. As I have a use case for that as well.

What I've done so far is load an Excel worksheet and inspected the XML of the PivotTable to see how it differs from the one generated by Add-PivotTable:

# Inspecting the XML of my "ideal" PivotTable
$Test = Open-ExcelPackage -Path "./test.xlsx"
$Test.'Sheet 1'.PivotTables[0].PivotTableXml.OuterXml

# Inspecting the XML of the PivotTable created by Add-PivotTable
$ExcelPackage.'Sheet 1'.PivotTables[0].PivotTableXml.OuterXml

Comparing the two, there are a number of attributes that are different:

XML of "ideal" PivotTable

<pivotTableDefinition
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="xr"
    xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xr:uid="{00000000-0007-0000-0000-000000000000}" name="" cacheId="138" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" updatedVersion="7" showMemberPropertyTips="0" useAutoFormatting="1" itemPrintTitles="1" createdVersion="4" indent="0" compact="0" compactData="0" gridDropZones="1">
    <location ref="I1:N53" firstHeaderRow="2" firstDataRow="2" firstDataCol="5" />
    <pivotFields count="7">
        <pivotField axis="axisRow" compact="0" outline="0" showAll="0" defaultSubtotal="0">
            <items count="7">
                <item x="0" />
                <item x="1" />
                <item x="2" />
                <item x="3" />
                <item x="4" />
                <item x="5" />
                <item x="6" />
            </items>
            <extLst>
                <ext uri="{2946ED86-A175-432a-8AC1-64E0C546D7DE}"
                    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
                    <x14:pivotField fillDownLabels="1" />
                </ext>
            </extLst>
        </pivotField>
        <pivotField compact="0" outline="0" showAll="0" defaultSubtotal="0">
            <extLst>
                <ext uri="{2946ED86-A175-432a-8AC1-64E0C546D7DE}"
                    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
                    <x14:pivotField fillDownLabels="1" />
                </ext>
            </extLst>
        </pivotField>
        ...
    </pivotFields>
    <rowFields count="5">
        <field x="4" />
        <field x="0" />
        <field x="2" />
        <field x="3" />
        <field x="5" />
    </rowFields>
    <rowItems count="51">
        ...
    </rowItems>
    <colItems count="1">
        <i />
    </colItems>
    <dataFields count="1">
        <dataField name="" fld="6" baseField="0" baseItem="0" />
    </dataFields>
    <pivotTableStyleInfo name="PivotStyleMedium9" showRowHeaders="1" showColHeaders="1" showRowStripes="0" showColStripes="0" showLastColumn="1" />
    <extLst>
        <ext uri="{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"
            xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
            <x14:pivotTableDefinition fillDownLabelsDefault="1"
                xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" />
            </ext>
            <ext uri="{747A6164-185A-40DC-8AA5-F01512510D54}"
                xmlns:xpdl="http://schemas.microsoft.com/office/spreadsheetml/2016/pivotdefaultlayout">
                <xpdl:pivotTableDefinition16 EnabledSubtotalsDefault="0" SubtotalsOnTopDefault="0" />
            </ext>
        </extLst>
    </pivotTableDefinition>

Generated by Add-PivotTable

<pivotTableDefinition
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="" cacheId="1" dataOnRows="0" applyNumberFormats="0" applyBorderFormats="0" applyFontFormats="0" applyPatternFormats="0" applyAlignmentFormats="0" applyWidthHeightFormats="1" dataCaption="Data" createdVersion="4" showMemberPropertyTips="0" useAutoFormatting="1" itemPrintTitles="1" indent="0" compact="0" compactData="0" gridDropZones="1" rowGrandTotals="1" colGrandTotals="1">
    <location ref="I1" firstHeaderRow="1" firstDataRow="1" firstDataCol="1" />
    <pivotFields count="7">
        <pivotField showAll="0" axis="axisRow">
            <items count="1">
                <item t="default" />
            </items>
        </pivotField>
        <pivotField showAll="0" />
        <pivotField showAll="0" axis="axisRow">
            <items count="1">
                <item t="default" />
            </items>
        </pivotField>
        <pivotField showAll="0" axis="axisRow">
            <items count="1">
                <item t="default" />
            </items>
        </pivotField>
        <pivotField showAll="0" axis="axisRow">
            <items count="1">
                <item t="default" />
            </items>
        </pivotField>
        <pivotField showAll="0" axis="axisRow">
            <items count="1">
                <item t="default" />
            </items>
        </pivotField>
        <pivotField showAll="0" dataField="1" />
    </pivotFields>
    <rowFields>
        <field x="4" />
        <field x="0" />
        <field x="2" />
        <field x="3" />
        <field x="5" />
    </rowFields>
    <dataFields>
        <dataField fld="6" subtotal="sum" />
    </dataFields>
    <pivotTableStyleInfo name="PivotStyleMedium9" showRowHeaders="1" showColHeaders="1" showRowStripes="0" showColStripes="0" showLastColumn="1" />
</pivotTableDefinition>

Specifically, the extra schemas, updatedVersion attribute, and the <extLst> XML structure.

I'm working on some code to manipulate the XML of the PivotTableDefinition to add these in and hopefully get the desired behaviour for repeated label items.

[dbrennand]

Hi both,

So I've tried manipulating the XML of the PivotTable but sadly I haven't had much luck. Although I can see the changes in the XML of the pivotTableDefinition and the CacheDefinition of the PivotTable, when I open in Excel it still looks the exact same. Even though when I load the XLSX file and inspected the XML and my changes are there.

My code below adds the extLst after the pivotTableStyleInfo and in all the pivotField nodes where it shows in the XML of "ideal" PivotTable.

Code:

$PivotTable = Add-PivotTable -PivotTableName "Sum of Stuff" -Address $ExcelPackage.'Sheet 1'.Cells["I1"] `
    -SourceWorksheet $ExcelPackage.'Sheet 1' -SourceRange $TableSourceRange `
    -PivotRows "Row1", "Row2", "Row3", "Row4", "Row5" -PivotTotals "None" -PivotData @{ "Stuff" = "Sum" } `
    -Verbose:($PSBoundParameters["Verbose"] -eq $true) -PassThru -ErrorAction Stop

# Add PivotTableDefinition attributes
$PivotTable.PivotTableXml.pivotTableDefinition.SetAttribute("xmlns:mc", "http://schemas.openxmlformats.org/markup-compatibility/2006")
$PivotTable.PivotTableXml.pivotTableDefinition.SetAttribute("mc:Ignorable", "xr")
$PivotTable.PivotTableXml.pivotTableDefinition.SetAttribute("xmlns:xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision")
$PivotTable.PivotTableXml.pivotTableDefinition.SetAttribute("xr:uid", "{00000000-0007-0000-0000-000000000000}")
$PivotTable.PivotTableXml.pivotTableDefinition.SetAttribute("updatedVersion", "7")

# Declare XML nodes
[Xml]$ExtLstNode = @'
<extLst>
<ext uri="{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"
xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
<x14:pivotTableDefinition fillDownLabelsDefault="1"
xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" />
</ext>
<ext uri="{747A6164-185A-40DC-8AA5-F01512510D54}"
xmlns:xpdl="http://schemas.microsoft.com/office/spreadsheetml/2016/pivotdefaultlayout">
<xpdl:pivotTableDefinition16 EnabledSubtotalsDefault="0" SubtotalsOnTopDefault="0" />
</ext>
</extLst>
'@
[Xml]$ExtLstNodePivotField = '<extLst><ext uri="{2946ED86-A175-432a-8AC1-64E0C546D7DE}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"><x14:pivotField fillDownLabels="1" /></ext></extLst>'

# Insert ExtLstNode after pivotTableStyleInfo node
$FoundNode = $PivotTable.PivotTableXml.pivotTableDefinition.pivotTableStyleInfo
$NewNode = $PivotTable.PivotTableXml.ImportNode($ExtLstNode.extLst, $true)
$PivotTable.PivotTableXml.pivotTableDefinition.InsertAfter($NewNode, $FoundNode) | Out-Null
# Check it is there
$PivotTable.PivotTableXml.pivotTableDefinition.extLst.ext

# For each PivotTableField, add the ExtLstNodePivotField node
for ($Index = 0; $Index -lt $PivotTable.PivotTableXml.pivotTableDefinition.PivotFields.PivotField.Count; $Index++) {
    # Find the node to insert the new node after
    $FoundNode = $PivotTable.PivotTableXml.pivotTableDefinition.PivotFields.PivotField[$Index].Items
    # Import new XML node
    $NewNode = $PivotTable.PivotTableXml.ImportNode($ExtLstNodePivotField.extLst, $true)
    # Insert the new node after the found node
    $PivotTable.PivotTableXml.pivotTableDefinition.PivotFields.PivotField[$Index].InsertAfter($NewNode, $FoundNode) | Out-Null
}
# Check it is there
$PivotTable.PivotTableXml.pivotTableDefinition.PivotFields.PivotField

[dbrennand]

Hi @astapelfeld @dfinke

After battling with the XML, I've looked at using a Macro for my use case. I've written a short Excel macro which changes all PivotTables in the workbook to Tabular form, disables subtotals and repeats item labels:

Private Sub Workbook_Open()
'
' ChangePivotTables Macro
' Runs when the Excel workbook is opened.
'
' Changes all PivotTables in the workbook to Tabular form, repeats labels
' and disables Subtotals.
'
    ' Declare variables
    Dim Ws As Worksheet
    Dim Pt As PivotTable
    Dim Pf As PivotField
    ' Disable screen updates
    Application.ScreenUpdating = False
    ' Continue even if an error occurs
    On Error Resume Next
    For Each Ws In ActiveWorkbook.Worksheets
        For Each Pt In Ws.PivotTables
            Pt.RowAxisLayout xlTabularRow
            Pt.RepeatAllLabels xlRepeatLabels
            For Each Pf In Pt.PivotFields
                Pf.Subtotals(1) = False
            Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

Usage example:

$Path = "Test.xlsm"

$Macro = @"
Private Sub Workbook_Open()
'
' ChangePivotTables Macro
' Runs when the Excel workbook is opened.
'
' Changes all PivotTables in the workbook to Tabular form, repeats labels
' and disables Subtotals.
'
    ' Declare variables
    Dim Ws As Worksheet
    Dim Pt As PivotTable
    Dim Pf As PivotField
    ' Disable screen updates
    Application.ScreenUpdating = False
    ' Continue even if an error occurs
    On Error Resume Next
    For Each Ws In ActiveWorkbook.Worksheets
        For Each Pt In Ws.PivotTables
            Pt.RowAxisLayout xlTabularRow
            Pt.RepeatAllLabels xlRepeatLabels
            For Each Pf In Pt.PivotFields
                Pf.Subtotals(1) = False
            Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub
"@

# $Data needs to be populated with something :)
$ExcelPackage = $Data | Export-Excel -Path $Path -PassThru -ClearSheet -AutoSize -TableName 'Table 1' `
        -WorksheetName 'Sheet 1' -Verbose:($PSBoundParameters["Verbose"] -eq $true) -ErrorAction Stop

# Add Macro to the ThisWorkbook module
$ExcelPackage.Workbook.CreateVBAProject()
$VBAThisWorkbookModule = $ExcelPackage.Workbook.VbaProject.Modules | Where-Object -FilterScript { $_.Name -eq "ThisWorkbook" }
$VBAThisWorkbookModule.Code = $Macro

Close-ExcelPackage -ExcelPackage $ExcelPackage

Now, when you open the Excel workbook and enable content, the Macro will execute 👍🏻 Hopefully you also find this useful 🙂

EDIT: I just noticed that the Macro doesn't actually run when the workbook opens. The documentation says to call the Macro Private Sub Workbook_Open() and place it in the ThisWorkbook module. I've updated the example above to reflect this 🙂

[dbrennand]

@dfinke If you think it's worth doing, I can raise a PR to add this macro to the Examples/CommunityContributions directory 🙂

[dfinke]

@dbrennand Thanks! Sorry, I thought I responded to the macro comment. At first I thought wonder if that would work by adding it using ImportExcel -Passthru etc. Then saw, cool you did that!

Please raise a PR as an example. It will good if/when folks ask about VBA etc.

Thanks again.