Advanced Pivot Table Options

[jaswicki]

I am trying to create Pivot tables from an existing Excel Worksheet and haven't had any luck at all. I have tried Importing the sheet to a variable and Opening the workbook as an Excel Package. The only examples I have found are using tiny data sets that are supplied within the code. Does anyone have an example adding a pivot table to an existing XLSX?

I also want to format the Pivot table in Tabular Form Layout but haven't figured that out either.

Any help is appreciated.

[dfinke]

This may help. The -PassThru is similar to what the Open-ExcelPackage does you need to use Close-ExcelPackage on the variable that you set the open to, so it can be saved.

Add-PivotTable is the way to go.

https://github.com/dfinke/ImportExcel/blob/e42f23cd7cf2fa4747d195f9e8ce4eea268430ac/Examples/PivotTable/MultiplePivotTables.ps1#L1

For a tabular layout, not sure how to solve that.

[jaswicki]

Thank you for the reply. I will give it shot. If anyone else has an idea on the Tabular Format it would be appreciated.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[dtrue]

Thank you for the reply. I will give it shot. If anyone else has an idea on the Tabular Format it would be appreciated.

Did you ever figure this out? I can't seem to figure this out either. I see the RowFields.Compact and RowFields.Outline option but nothing that seems to imply Table Layout.

[jaswicki]

No I never did. Sadly we do those steps manually.

On Thu, Oct 14, 2021 at 1:19 PM Dan True @.***> wrote:

Thank you for the reply. I will give it shot. If anyone else has an idea on the Tabular Format it would be appreciated.

Did you ever figure this out? I can't seem to figure this out either. I see the RowFields.Compact and RowFields.Outline option but nothing that seems to imply Table Layout.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/939#issuecomment-943561744, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKXOE3BY5KNYJTT7XQLZHGTUG4GKBANCNFSM4S45YGVQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[uSlackr]

No I never did. Sadly we do those steps manually.

One thing that might help. Open an excel file containing the pivot as you'd like it and examine it using powershell

$ex.Workbook.Worksheets['mysheet']
$ex.Workbook.Worksheets['summary'].PivotTables
$ex.Workbook.Worksheets['summary'].PivotTables[0]

PivotTableXml           : #document
PivotTableUri           : /xl/pivotTables/pivotTable1.xml
Name                    : PivotTable2
CacheDefinition         : OfficeOpenXml.Table.PivotTable.ExcelPivotCacheDefinition
WorkSheet               : Summary
Address                 : A3:E210
DataOnRows              : False
ApplyNumberFormats      : False
ApplyBorderFormats      : False
ApplyFontFormats        : False
ApplyPatternFormats     : False
ApplyWidthHeightFormats : True
ShowMemberPropertyTips  : False
ShowCalcMember          : False
EnableDrill             : True
ShowDrill               : True
ShowDataTips            : True
FieldPrintTitles        : False
ItemPrintTitles         : True
ColumGrandTotals        : False
ColumnGrandTotals       : False
RowGrandTotals          : False
PrintDrill              : False
ShowError               : False
ErrorCaption            :
DataCaption             : Values
ShowHeaders             : False
PageWrap                : -2147483648
UseAutoFormatting       : True
GridDropZones           : False
Indent                  : 0
OutlineData             : True
Outline                 : True
MultipleFieldFilters    : False
Compact                 : False
CompactData             : False
GrandTotalCaption       :
RowHeaderCaption        : Location/Title/Host
ColumnHeaderCaption     :
MissingCaption          :
FirstHeaderRow          : 1
FirstDataRow            : 2
FirstDataCol            : 1
Fields                  : {Address, Name, Operating System, ID)...}
RowFields               : {Environment, Location, Title, Name}
ColumnFields            : {CVSS v31 Severity Level}
DataFields              : {Count of Name}
PageFields              : {}
StyleName               : PivotStyleLight16
TableStyle              : Medium6

PS C:\Data\Scan data> $t.Workbook.Worksheets['summary'].PivotTables[0].DataFields

Field     : OfficeOpenXml.Table.PivotTable.ExcelPivotTableField
Index     : 1
Name      : Count of Name
BaseField : 0
BaseItem  : 0
Format    : General
Function  : Count

I'm finding it very useful to reconstruct in code what is already have done manually

[jaswicki]

Thanks, I'll check it out.

On Fri, Feb 11, 2022 at 10:15 AM uSlackr @.***> wrote:

No I never did. Sadly we do those steps manually.

One thing that might help. Open an excel file containing the pivot as you'd like it and examine it using powershell

$ex.Workbook.Worksheets['mysheet'] $ex.Workbook.Worksheets['summary'].PivotTables $ex.Workbook.Worksheets['summary'].PivotTables[0]

I'm finding it very useful to reconstruct in code what is already have done manually

— Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/939#issuecomment-1036319891, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKXOE3C3H53DHJA2UK7V5WTU2URYLANCNFSM4S45YGVQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

You are receiving this because you authored the thread.Message ID: @.***>