Hi Team! Im trying to create a pivot table with a column label and values (Max Function) but after adding them any value is shown. I’m using Apose Cells version 7.2.0.
I could add the row labels, the page level but after adding the column label and date values, they are not displayed. A 0 value is displayed when the values are DATE.
You can check the Aspose result in the attached file: AsposeTest, and a sample of what I need in ReportTest file.
Is anything wrong?
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Worksheet sheet2 = workbook.Worksheets.Add("Data");
DataTable dt = GetPivotTimesData();
sheet.Name = "PivotTable";
Worksheet sheet = workbook.Worksheets["PivotTable"];
Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A3:N" + (3 + dt.Rows.Count).ToString(), "A3", "PivotTable", true);
//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];
unshowTotals(pivotTable);
//unshowTotals(pivotTable2);
////Draging the first field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 0);
////Draging the second field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
////Draging the third field to the row area.
pivotTable.AddFieldToArea(PivotFieldType.Row, 2);
pivotTable.AddFieldToArea(PivotFieldType.Row, 3);
pivotTable.AddFieldToArea(PivotFieldType.Row, 4);
pivotTable.AddFieldToArea(PivotFieldType.Row, 5);
pivotTable.AddFieldToArea(PivotFieldType.Row, 6);
pivotTable.AddFieldToArea(PivotFieldType.Row, 7);
pivotTable.AddFieldToArea(PivotFieldType.Row, 8);
pivotTable.AddFieldToArea(PivotFieldType.Row, 9);
pivotTable.AddFieldToArea(PivotFieldType.Row, 10);
//pivotTable.AddFieldToArea(PivotFieldType.Row, 11);
pivotTable.AddFieldToArea(PivotFieldType.Page, 11);
pivotTable.PageFields[0].InsertBlankRow = false;
// Stage
pivotTable.AddFieldToArea(PivotFieldType.Column, 12);
pivotTable.ColumnFields[0].InsertBlankRow = false;
// Max Date
pivotTable.AddFieldToArea(PivotFieldType.Data, 13);
pivotTable.DataFields[0].ShowAllItems = true;
SettingFilters(pivotTable, ConsolidationFunction.Max);
//Accessing the row fields.
Aspose.Cells.Pivot.PivotFieldCollection pivotFields = pivotTable.RowFields;
Cells cells = sheet.Cells;
//Accessing the first row field in the row fields.
for (int i = 0; i < pivotFields.Count; i++)
{
Aspose.Cells.Pivot.PivotField pivotField = pivotFields[i];
pivotField.SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);
pivotField.IsAutoShow = false;
pivotField.IsAutoSubtotals = false;
cells.SetColumnWidth(i, 20);
}
setPivotStyle(sheet, pivotTable, workbook);
pivotTable.IsGridDropZones = true;
pivotTable.RefreshDataFlag = true;
if (pivotTable.DataFields[0].IsCalculatedField)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
}
private void SettingFilters(PivotTable pivotTable, ConsolidationFunction function)
{
PivotFieldCollection col = pivotTable.ColumnFields;
if (col.Count > 0 )
{
pivotTable.ColumnFields[0].Function = function;
pivotTable.ColumnFields[0].SetSubtotals(Aspose.Cells.Pivot.PivotFieldSubtotalType.None, true);
pivotTable.ColumnFields[0].DisplayName = "Etapa";
}
col = pivotTable.DataFields;
if (col.Count > 0)
// Setting the Value Type Setting
pivotTable.DataFields[0].Function = function;
if (pivotTable.DataFields[0].IsCalculatedField)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
}
private void unshowTotals(PivotTable pivotTable)
{
//UnShowing the grand totals
pivotTable.RowGrand = false;
pivotTable.ColumnGrand = false;
pivotTable.ShowEmptyCol = false;
pivotTable.ShowPivotStyleRowHeader = true;
pivotTable.ShowValuesRow = true;
}
Attached the rest of the code: Code file.
Please help!!!
Thanks in advance!