Repeated Labels in Pivot Table

Hi,

The code below should produce the pivot table i nthe "Expected" sheet. However it produces the result in the "Actual" sheet. The labels are not repeated. My assumption was that the property "IsRepeatItemLabelsIsRepeatItemLabels" should take care of this.

internal void CreatePivot()
{

var rows = new List { "Institution", "Currency" };
var columns = new List { "Dates", "Indicator" };
var yValue = "Val";

CreatePivot(rows, columns, yValue);

}

private void CreatePivot(List rows, List columns, string yValue)
{
var filename = @"C:\Users\Public\Pivot.xlsx";
var workbook = new Workbook(filename);
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
sheet2.Name = "Actual";

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
int index = pivotTables.Add("=Data!A1:J49", "B3", "TestTable");

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report1;


for (int i = 0; i < rows.Count; i++)
{
var rowIndex = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, rows[i]);
var rowField = pivotTable.RowFields[rowIndex];
rowField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Automatic, false);
rowField.ShowInOutlineForm = false;
rowField.IsRepeatItemLabels = true;
rowField.ShowCompact = true;
}

for (int i = 0; i < columns.Count; i++)
{
var columnIndex = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, columns[i]);
var columnField = pivotTable.ColumnFields[columnIndex];
columnField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Automatic, false);
columnField.ShowInOutlineForm = false;
columnField.IsRepeatItemLabels = true;
columnField.ShowCompact = true;

}

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, yValue);
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.CalculateData();

//Saving the Excel file
workbook.Save(@"C:\Users\Public\Pivot.xlsx");
} internal void CreatePivot()
{

var rows = new List { "Institution", "Currency" };
var columns = new List { "Dates", "Indicator" };
var yValue = "Val";

CreatePivot(rows, columns, yValue);

}

private void CreatePivot(List rows, List columns, string yValue)
{
var filename = @"C:\Users\Public\Pivot.xlsx";
var workbook = new Workbook(filename);
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
sheet2.Name = "Actual";

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables;
int index = pivotTables.Add("=Data!A1:J49", "B3", "TestTable");

Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
pivotTable.IsAutoFormat = true;
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report1;


for (int i = 0; i < rows.Count; i++)
{
var rowIndex = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, rows[i]);
var rowField = pivotTable.RowFields[rowIndex];
rowField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Automatic, false);
rowField.ShowInOutlineForm = false;
rowField.IsRepeatItemLabels = true;
rowField.ShowCompact = true;
}

for (int i = 0; i < columns.Count; i++)
{
var columnIndex = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, columns[i]);
var columnField = pivotTable.ColumnFields[columnIndex];
columnField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.Automatic, false);
columnField.ShowInOutlineForm = false;
columnField.IsRepeatItemLabels = true;
columnField.ShowCompact = true;

}

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, yValue);
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.CalculateData();

//Saving the Excel file
workbook.Save(@"C:\Users\Public\Pivot.xlsx");
}

Hi Markus,

Thanks for your posting and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v8.3.2.5
it should fix your issue.

I have tested this issue with the following sample code and it sets the Repeat Item Labels of Institution field to true and now your actual pivot table looks exactly like your expected pivot table.

I have attached the output excel file with desired pivot table for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Pivot.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[“Actual”];


PivotTable pivotTable = worksheet.PivotTables[0];


//Institution field

pivotTable.RowFields[0].IsRepeatItemLabels = true;


workbook.Save(“output.xlsx”);


Same for Aspose.Cells for Java 8.4.1


PivotField pivotField = pivotTable.getRowFields().get(fieldIndex);
pivotField.setRepeatItemLabels(true);

doesn’t work

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing the attached sample project using the latest version and found that PivotField.IsRepeatItemLabels not working in the Pivot Table.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged in both .,NET and Java as

  • CELLSNET-43594 - PivotField.IsRepeatItemLabels not working in the Pivot Table
  • CELLSJAVA-41303 - PivotField.IsRepeatItemLabels not working in the Pivot Table

I have also attached the output excel file generated by the sample project for a reference.

Hi,

Thanks for your using Aspose.Cells.

We have fixed the issue CELLSNET-43594.

Please download and try the latest fix: Aspose.Cells for .NET v8.5.1.3 and let us know your feedback.

The issues you have found earlier (filed as CELLSNET-43594) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

The issues you have found earlier (filed as CELLSJAVA-41303) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.