How do you add a count column to a PivotTable

Aspose.Cells Product Family
1

pivottable.gif (166.8 KB)

I’ve been able to create this pivot table using the Aspose Cells dll version 17.10.0.0 with the exception of the
“Count of Exception Amount” Data Column. How do you create a count column?

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheetSY.PivotTables;
int pIndx = pivotTables.Add("=Detail!A1:P" + iDetailRowCount.ToString(), “A11”, “PivotSummary”);
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[pIndx];
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
pivotTable.IsAutoFormat = true;

            // Page Filters
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 1);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 9);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 13);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, 14);

            // Set the latest year as the default to the Exception Year
            int SetIndex = 0;
            int YearSearch = 0;
            for (int i = 0; i < pivotTable.PageFields[1].ItemCount; i++)
            {
                if (Convert.ToInt32(pivotTable.PageFields[1].Items[i].ToString()) > YearSearch)
                {
                    SetIndex = i;
                    YearSearch = Convert.ToInt32(pivotTable.PageFields[1].Items[i].ToString());
                }
            }
            pivotTable.PageFields[1].CurrentPageItem = (short)SetIndex;

            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 12);
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 15);

            //// Data Totals
            pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 7);
            pivotTable.DataFields[0].Number = 3;
            pivotTable.DataFields[0].DisplayName = "Sum of Exception Amount";
            pivotTable.RowFields[0].IsAutoSubtotals = false;
            pivotTable.PivotTableStyleType = Aspose.Cells.Pivot.PivotTableStyleType.PivotTableStyleLight16;
            pivotTable.RowHeaderCaption = "Producer Name";
2

@cchance

Thanks for considering Aspose APIs.

Please use the PivotField.Function property for your needs. It takes the ConsolidationFunction enumeration which could be Sum, Min. Max, Count etc.

Please see the following sample code for your reference. Let us know if you still face any issue, we will look into it and help you asap.

C#

//Access the first data field
PivotField pf = pt.DataFields[0];

//Change the function to Count or whatever like Sum, Min, Max etc.
pf.Function = ConsolidationFunction.Count;

//Refresh and calculate data
pt.RefreshData();
pt.CalculateData();
3

Thanks for the code, I included it but it replaced the sum column that was generated with the count column. How do you render both a count and a sum column?

Thanks.
Charlie

4

@cchance

Please clarify your requirements. In order to clarify your requirement, I have made the following image. Please create one simple Excel file with your desired Pivot Table as shown in the image and provide it to us. It will help us look into your issue more precisely.

As shown in the image, I am able to create two pivot tables, one with Sum and one with Count, but I think, you need the third Pivot Table which has both Sum and Count. I created the third Pivot Table with MS-Paint. If you want third Pivot Table, then please create it using Microsoft Excel and then provide it to us.

5

count_sum_results.png (43.5 KB)
Hello, please review this image I just uploaded. I’m looking for the Sum and Count totals side by side as you showed in the bottom of the image you posted. Sorry for the delay in getting back with you. If you still need me to provide a spreadsheet I will. Your products are amazing and have been a tremendous help for our development projects.

Thanks,
Charlie

6

@cchance

Thanks for your feedback and considering Aspose APIs.

Probably, this is achieved by calculated fields. Please provide me the sample Excel file as you have highlighted in the image so that we could provide you a sample code to achieve the same thing in Aspose.Cells APIs. Thanks for your cooperation in this regard and have a good day.

7

samplepivottable.zip (11.1 KB)
Hello,
I’ve attached a zip file with a sample spreadsheet to show the sum and count columns I need. Please let me know how I can replicate this with the Aspose Cells product. Thanks for your assistance.

8

@cchance

Thanks for your sample Excel file and considering Aspose APIs.

Please see the following sample code, its output Excel file as well as screenshot for your reference.

Download Link:
Output Excel File.zip (8.3 KB)

C#

//Create workbook
Workbook wb = new Workbook();

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Add some data in column A
ws.Cells["A1"].PutValue("Fruit");
ws.Cells["A2"].PutValue("Apple");
ws.Cells["A3"].PutValue("Pear");
ws.Cells["A4"].PutValue("Apple");
ws.Cells["A5"].PutValue("Apple");
ws.Cells["A6"].PutValue("Pear");

//Add some data in column B
ws.Cells["B1"].PutValue("Grade");
ws.Cells["B2"].PutValue(1);
ws.Cells["B3"].PutValue(2);
ws.Cells["B4"].PutValue(1);
ws.Cells["B5"].PutValue(2);
ws.Cells["B6"].PutValue(1);

//Create pivot table
int idx = ws.PivotTables.Add("Sheet1!$A$1:$B$6", "E8", "TestPivot");
PivotTable pt = ws.PivotTables[idx];

//Add fields to row and data area
pt.AddFieldToArea(PivotFieldType.Row, pt.BaseFields[0]);
pt.AddFieldToArea(PivotFieldType.Data, pt.BaseFields[1]);
pt.AddFieldToArea(PivotFieldType.Data, pt.BaseFields[1]);

//This line is important - add virtual field to column
pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);

//Set the second data field function to count
pt.DataFields[1].Function = ConsolidationFunction.Count;

//Refresh and calculate the data
pt.RefreshData();
pt.CalculateData();

//Save the output Excel file
wb.Save("output.xlsx");

Screenshot