ClosedXML / ClosedXML/ClosedXML

ClosedXml Pivot Fields Subtotal per Row

Open
#1,452 0 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.