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

Pivot Table Numeric Row Groups Fail with Blank Cell in Data Row Field #1125

Closed dlwearl closed 2 years ago

dlwearl commented 2 years ago

Doug,

I have a dataset that consists of three columns with headers, not in an Excel table, and all with whole numbers as follows:

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">

Item1 | Item2 | Item3 -- | -- | -- 1 | 1 | 100 2 | 2 | 200 3 | 3 | 300 4 |   | 400

The data reside on Sheet1. The only difference between Item1 and Item2 is the null value in the final row for Item2.

The code I use to generate pivot tables is as follows:

$exceltest = Export-Excel -Path $wbsource -WorksheetName "Sheet1" -PassThru

$wksht = Add-Worksheet $exceltest -WorksheetName "Test" -MoveToEnd

$pt = Add-PivotTable -SourceWorksheet $exceltest.Sheet1 -PivotTableName "PT1" -Address $wksht.Cells["A3"] -PivotRows Item1 -PivotData @{"Item3" = 'count'} ` -GroupNumericRow Item1 -GroupNumericMin 1 -GroupNumericMax 4 -GroupNumericInterval 2 -PassThru

$pt = Add-PivotTable -SourceWorksheet $exceltest.Sheet1 -PivotTableName "PT2" -Address $wksht.Cells["D3"] -PivotRows Item2 -PivotData @{"Item3" = 'count'} ` -GroupNumericRow Item2 -GroupNumericMin 1 -GroupNumericMax 4 -GroupNumericInterval 2 -PassThru

Close-ExcelPackage $exceltest -Show

Results are in the screen shot below, along with the desired pivot table. When the pivotrows source is Item1, the grouping works as expected; when it is Item2, the grouping syntax fails silently. The desired pivot table is in the screenshot.

pivot table grouping

Thanks, David

dfinke commented 2 years ago

@dlwearl The far right pic, you did that manually in Excel? Yeah, not sure, could be the Export-Excel, the underlying EPPlus, or a missing setting,

dlwearl commented 2 years ago

@dfinke Yes, the Desired PT2 is created manually.

dfinke commented 2 years ago

At the moment, looks like a bug in how the underlying library I use to create the xlsx has a bug. I don't have control over that library.

https://github.com/dfinke/ImportExcel/blob/6d97018de672f2f4f4b3b1c47dbf2262f2d35ea2/ExportedCommands/Add-PivotTable.ps1#L137

dfinke commented 2 years ago

@dlwearl I used Export-Excel to create a worksheet with that dataset, did not use the module to create the pivot table. I created the pivot table manually, tried to group it, and get this error. I suspect the Export-Excel function cannot detect that error when the xlsx is created.

image

stale[bot] commented 2 years ago

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.