if color coded rows, then pivot table not working

[GrantHarrington]

I'm wondering if it's a limitation of Excel in general or I'm not doing something right.

I'm able to add the zebra striping when using -tablename, and no pivot table assigned.

`$ptd += New-PivotTableDefinition @base -pivottablename servicetype -PivotRows servicetype -ChartType Area3D
$ptd += New-PivotTableDefinition @base -pivottablename status -PivotRows status -ChartType PieExploded3D
$ptd += New-PivotTableDefinition @base -pivottablename starttype -PivotRows starttype -ChartType BarClustered3D
$ptd += New-PivotTableDefinition @base -pivottablename canstop -PivotRows canstop -ChartType ConeColStacked

Get-Service | Export-Excel -path $xlSourcefile -WorkSheetname gsv -FreezeTopRow -AutoNa
meRange -PivotTableDefinition $ptd
Get-Service | Export-Excel -path $xlSourcefile -WorkSheetname testing -Show -FreezeTopRow -AutoNameRange -TableName 'mytable'`

This results in the 'gsv' worksheet in plain text. And the 'testing' worksheet with the zebra striping.

When I add this to the first Get-Service line, the pivot tables give this error message, and the 'gsv' worksheet is properly colored in, but the pivot tables are all "broke".

Get-Service | Export-Excel -path $xlSourcefile -WorkSheetname gsv -FreezeTopRow -AutoNameRange -TableName 'zebra' -PivotTableDefinition $ptd

Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition3.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition4.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable3.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable4.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)

[dfinke]

I'd need a small repro of this, data, script and xlsx

[GrantHarrington]

Thanks. I started with a complex script that was creating several worksheets in a single Excel file, based on various input data sources, with the zebra striping, and it worked great. I needed a single pivot table included and that's when I started noticing the issues. I don't use the native -show, because my script uses logic to show after it's run, or I'll just email it as part of the script.

I reviewed several examples on this site to get back to basics, and saw the same behavior when trying to make that happen, using this script:

https://github.com/dfinke/ImportExcel/blob/master/Examples/TryMultiplePivotTablesFromOneSheet.ps1

Script as is works (but the 'gsv' worksheet is not zebra striped, by default).

Get-Service | Export-Excel -path $file -WorkSheetname gsv -Show -PivotTableDefinition $ptd

Modifying the original get-service line, also works, and adds the zebra striping successfully, and keeps the pivot tables intact.

Get-Service | Export-Excel -path $file -WorkSheetname gsv -FreezeTopRow -AutoNameRange -TableName 'gsv' -PivotTableDefinition $ptd

With "$file" closed in Excel, and then running this second line of export-excel, is where I see the errors, and it clears out the exiting pivot table data on the four tabs. Both 'testing' and 'gsv' worksheets are there and zebra stripped, but the pivot table tabs are "missing" data references.

Get-Service | Export-Excel -path $file -WorkSheetname testing -Show -FreezeTopRow -AutoNameRange -TableName 'mytable'

(note: that script posted online is missing the -pivottablename declaration, so had to manually add those first)
$ptd += New-PivotTableDefinition @base -PivotTableName servicetype -PivotRows servicetype -ChartType Area3D

Screen grab notes:

1. When first opening the spreadsheet
2. After clicking Yes in step 1 (note: gsv tab now has zebra striping)
3. Existing pivot table is cleared.

[GrantHarrington]

I think I'm getting closer to a working solution, here is the specific workflow I used.

By adding the pivot table command after all of my other worksheets are created, and add the -movetostart, the dataset is second from end Tab and dataset PivotTable is last Tab.

filtered data set 01> Tab1, filtered data set 02> Tab2, etc.... , Rawdata > Second from last tab, Rawdata PivotTable > LastTab

Desired result
Rawdata > Tab1, Rawdata PivotTable > Tab2, filtered data set 01> Tab3, filtered data set 02> Tab4, etc....

Possible?

[dfinke]

Thanks for the update. I would need a small repro so I can determine the issue.