Set-ExcelRange does not work on PivotTables

[relationaldba]

Hello, Firstly thank you so much for making this Powershell module. It's made my life easier.

I want to report a bug related to formatting the PivotTable using Set-ExcelRange My code looks like this:

Add-PivotTable -Address $ExcelPackage.Workbook.Worksheets["Pivot"].Cells['A2'] `
    -ExcelPackage $ExcelPackage -PivotTableName "Pivot" `
    -SourceWorkSheet $ExcelPackage.Workbook.Worksheets["Query"] `
    -SourceRange $ExcelPackage.Workbook.Worksheets["Query"].Tables[1] -Activate  -PivotNumberFormat "#,##0"   `
    -PivotRows "Department" -PivotColumns "Type" `
    -PivotData @{Amount="sum"} -PivotTotals "Both" `
    -PivotTableStyle "Light15" `

Set-ExcelRange -Range "B:M" -Worksheet $ExcelPackage.Workbook.Worksheets["Pivot"] -Width 12 -WrapText

Excel appears to simply ignore these formatting rules. The column widths are auto sized and there is no wrapping of text. I know that there was a similar issue 402 reported for formatting numbers in pivot tables and @jhoneill replied that it was outside his control as Excel overwrote the values upon opening the file.

I would like to know if this issue impacts other formatting options like Width and WrapText too and if there is a workaround?

Thanks again! Varun

[relationaldba]

Just to add to the above, Set-ExcelRange -Height does work on the PivotTables, just that the Width and WrapText options do not appear to work.

[dfinke]

Thanks for the updates. I'm a bit surprised the Height works. These may not work because of the EPPlus library used to manipulate the XLSX. @jhoneill is far more adept at figuring out if the XLSX is not properly being set via EPPlus or it cannot be done with to components like the PivotTable.

EPPlus has a new version, that version may allow doing this, it'd be work Googling.

There are no plans to move to that version, there is a licensing fee they are collecting now.

[relationaldba]

Thank you @dfinke for looking at it. I appreciate your response.

[jhoneill]

Just to fill this in a little. When we write to an XLSX file with EPPlus, we write the specification of tables, pivot tables, charts and such like. The cells in the sheet were the pivot table will go don't hold the table contents until Excel starts, so there are are some things we can't get to (selections, sort orders and so on). As Doug says I have explored in the XML and found things which can be changed - but with pivots there isn't anything in a new file. What happens here is the width of the columns gets set when you save the file, but as soon as the Excel sees there is a pivot table there it resets the column widths - it doesn't reset everything which is how row heights are preserved.

[relationaldba]

Thanks, @jhoneill for the detailed explanation. I get it now and I will close the bug. Have a nice weekend!