Pivot - Get Certain Column in Excel

Aspose.Cells Product Family
1

Hi, I want to select certain column to do pivot. For example, I have 15 column in the worksheet, but I only want to select column, “C, J, AJ, K, G” to make a pivot. Because in the tutorial, I only see, they take all the column like this,

Dim index As Integer = pivotTables.Add("=Data!A1:F30", “B3”, “PivotTable1”)

My question is, how can I select a few column only to make pivot table?

Thanks

2

@sitizalekoh,

Thanks for your query.

Well, you may set complete range of cells as data source for the PivotTable but then add your desired fields only to the report area (row section, column section, data section, etc.). See the sample code segment for your reference:
e.g
Sample code:

 Workbook wb = new Workbook("e:\\test2\\Book1.xlsx");
            Worksheet sheet = wb.Worksheets["Pivot"];
            PivotTableCollection pivotTables = sheet.PivotTables;
            int index = pivotTables.Add("=Data!$A$1:$AK$28", "A61", "MyTable1");

           PivotTable pivotTable = pivotTables[index];
            pivotTable.IsAutoFormat = true;
            pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;
            pivotTable.ColumnGrand = true;

            pivotTable.AddFieldToArea(PivotFieldType.Row, 0);  // first field
            pivotTable.AddFieldToArea(PivotFieldType.Row, 17); // 18th field
            pivotTable.AddFieldToArea(PivotFieldType.Row, 4); // 5th field
            pivotTable.AddFieldToArea(PivotFieldType.Row, 5); // sixth field

................

Hope, this helps a bit.

3

Thank you. It helps a lot. Thankssss a lot. by the way, how can I get the Σ-values from the column?

Blockquote
pivotTable.AddFieldToArea(PivotFieldType.Row, 5); // sixth field

Blockquote

I think the syntax almost like this, but I don’t know how to do it.

4

@sitizalekoh,

Thanks for your query.

See the document on how to use consolidation functions for pivot fields in pivot table report for your reference:

Hope this helps a bit.