ClosedXML / ClosedXML/ClosedXML
ClosedXml Pivot Fields Subtotal per Row
- Dominant language
- C#
- Stars
- 5.7k
- Forks
- 933
- Avg merge
- 12h 14m
- Merged PRs (30d)
- 1
Description
## Read and complete the full issue template
Do not randomly delete sections. They are here for a reason.
**Do you want to request a *feature* or report a *bug*?**
- [ ] Bug
- [x] Feature
- [ ] Question
**Did you test against the latest CI build?**
- [x] Yes
- [ ] No
If you answered `No`, please test with [the latest development build](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) first.
**Version of ClosedXML**
0.95.3
**What is the current behavior?**
After adding subtotals per row, the excel has stopped working and I cannot open the workbook when I add **Automatic** or **None**
But when I do custom subtotals like **Count**, **Average**, **Sum**, the workbook is opening fine.
**What is the expected behavior or new feature?**
XLSubtotalFunction: Row Labels Subtotals for **Automatic** and **None**.
**Is this a regression from the previous version?**
Regressions get higher priority. Test against the latest build of the previous minor version. For example, if you experience a problem on v0.95.3, check whether it the problem occurred in v0.94.2 too.
## Reproducibility
**This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.**
**Code to reproduce problem:**
```VB.NET
Try
Dim xlWB As New ClosedXML.Excel.XLWorkbook
Dim xlWorksheet As ClosedXML.Excel.IXLWorksheet = xlWB.Worksheets.Add("Data Source")
Dim xlintStartRow As Integer = 2
Dim xlWorksheet2 = xlWB.Worksheets.Add("SALESMAN")
Dim ptj As IXLPivotTable = xlWorksheet2.PivotTables.Add("SALESMAN", xlWorksheet.Cell(2, 1), xlWorksheet.Range("A1:K" & xlintStartRow - 1))
'ptj.Subtotals = XLPivotSubtotals.AtBottom
ptj.RowLabels.Add("Company").AddSubtotal(XLSubtotalFunction.Automatic)
ptj.RowHeaderCaption = "Company"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("Division").AddSubtotal(XLSubtotalFunction.Automatic)
ptj.RowHeaderCaption = "Division"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("Region").AddSubtotal(XLSubtotalFunction.Automatic)
ptj.RowHeaderCaption = "Region"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("District").AddSubtotal(XLSubtotalFunction.None)
ptj.RowHeaderCaption = "District"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("Empno").AddSubtotal(XLSubtotalFunction.None)
ptj.RowHeaderCaption = "Empno"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("Territory").AddSubtotal(XLSubtotalFunction.None)
ptj.RowHeaderCaption = "Territory"
ptj.ClassicPivotTableLayout = True
ptj.Layout = XLPivotLayout.Tabular
ptj.RowLabels.Add("Salesman Name")
ptj.RowHeaderCaption = "Salesman Name"
ptj.ColumnLabels.Add("YEAR") '.SetCollapsed()
'ptj.ColumnLabels.Add("YEAR").SortType = XLPivotSortType.Ascending
ptj.ColumnHeaderCaption = "YEAR"
ptj.Values.Add("AMT", "SALES PER SALESMAN").NumberFormat.Format = "#,##0.00"
xlWorksheet2.Column("A").Width = "38"
ptj.ShowEmptyItemsOnRows = True
xlWorksheet.Columns.AdjustToContents()
'xlWorksheet.Visibility = XLWorksheetVisibility.Hidden
Dim ls_xlsfilepath As String = "TEST.xlsx"
ls_xlsfilepath = Replace(ls_xlsfilepath, " ", "_")
xlWB.SaveAs(xlsFiledir & ls_xlsfilepath)
'System.Diagnostics.Process.Start("EXCEL.EXE", xlsFiledir & "\" & ls_xlsfilepath)
Catch ex As Exception
Throw ex
End Try
```
- [
[TEST.xlsx](https://github.com/ClosedXML/ClosedXML/files/4720478/TEST.xlsx)
] I attached a sample spreadsheet.
Contributor guide
Research direction
Start with the supplied VB.NET reproduction and attached TEST.xlsx, then inspect ClosedXML's pivot-table handling for XLSubtotalFunction.Automatic and XLSubtotalFunction.None. Confirm the generated workbook opens in Excel and that row-label subtotals behave correctly for those values, while the existing custom subtotal cases continue to work.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp
- Domain
- data
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100