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");
}