Column Label With no Date value- 0 value is shown

Aspose.Cells Product Family
1

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!

2

Hi,


I could not execute and evaluate your code properly with latest version v7.5.0.x, I got the exception:
“The PivotTable field name is invalid.” on the line:
int index = pivotTables.Add(“=Data!A3:N” + (3 + dt.Rows.Count).ToString(), “A3”, “PivotTable”, true);
so you must check your data table fields data and code.

Could you please create a sample console application (runnable), and provide us here (you may zip it prior attaching here) to evaluate your issue on our end.

Also, I have seen you posted lots of similar posts in the forums.

We also recommend you to kindly see our reply in your previous thread and get references to your issue:
https://forum.aspose.com/t/92153

And again, as we told you earlier, we cannot evaluate your issue based on older versions. We can only evaluate your issue and include fix to the latest version of the product (if we found the issue in it).

Thank you.

3

Hi Amjad, I replied in the previous thread using the Aspose latest version. Same issue.

Regards!

4

Hi,

Thanks for your posting and using Aspose.Cells.

Please refer to your other thread for a reply.
https://forum.aspose.com/t/92153