Consolidation Function -- Average

Aspose.Cells Product Family
1

Dear Aspose support team,
I have a Pivot Table in that i am providing the consolidation function Count then its working fine. for the same if i give the Average function then it is giving the attached error(#DIV/0! Error).

Please find the two excel sheets with data, pivottable in the attachment

count.xls : this excel is belongs to the Consolidation Function -- Count

Avg.xls : this excel is belongs to the Consolidation Function -- Average (Giving eror)

Any help from your is side is much appreciatable

Thanks in advance for your support

Please let me know you need any more information

Thankyou.

Saravanan

2

Hi,

Thank you for considering Aspose.

As you are applying the Average function on “Hub” column, please change the format of the “Hub” column to number before inserting the values in the column cells and adding the column to the pivot table.

Thank You & Best Regards,

3

Hi,

Please follow Nausherwan's instructions, see the following sample code for your reference, I have used your template excel file and created your desired pivot table in another sheet based on the source data in the Data sheet:

Sample code:

Workbook workbook = new Workbook();
workbook.Open("f:\\test\\avg.xls");
Cells cells = workbook.Worksheets[0].Cells;
//Format the column R (Hub column) to numeric column.
for (int i = 1; i < cells.MaxDataRowInColumn(17); i++)
{

cells[i, 17].PutValue(cells[i, 17].StringValue, true);

}

int sheetindex = workbook.Worksheets.Add();
Worksheet sheet = workbook.Worksheets[sheetindex];

PivotTables pivotTables = sheet.PivotTables;
//Adding a PivotTable to the worksheet
int index = pivotTables.Add("=Data!A1:T6049", "B10", "PivotTable2");
PivotTable pivotTable = pivotTables[index];
pivotTable.AddFieldToArea(PivotFieldType.Row, 1);
pivotTable.AddFieldToArea(PivotFieldType.Column, 7);
pivotTable.AddFieldToArea(PivotFieldType.Data, 17);
PivotField pivotField = pivotTable.DataFields[0];
pivotField.Function = ConsolidationFunction.Average;


workbook.Save("f:\\test\\outavg.xls");
Thank you.
4

Dear Aspose Support team,

My requirement is that, i need to set the style for the particular column, after that i want to create the Data sheet, after that i want to create the Pivot Data sheet. i want to set the number format and bold for the particular column. Kindly please suggest me how to achive this feature. Can you suggest by code?

Thanks
Saravanan

5

Hi Saravanan,

Please check the following documents for your reference to know how you can format cells, rows, columns etc.

Note: As we told you earlier, you cannot custom format data, row or columns fields in pivot table, we will support this feature in about 3 months, sorry for any inconvenience.

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/approaches-to-format-data-in-cells.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/setting-display-formats-of-numbers-dates.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/dealing-with-font-settings.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/formatting-rows-columns.html

Thank you.