C # operations Excel PivotTable report

I. Overview

Pivot table is an interactive table that allows you to perform certain calculations, such as summing and counting, to dynamically change the layout of a pivot table, or to rearrange line numbers, column labels, and page fields. When the layout is changed, the pivot table is updated according to the new layout, which can be said to be a powerful data analysis tool. Therefore, this article describes an example of an Excel PivotTable report in C # that contains the following main points:

1. Create a pivot table

(1) Creating a data cache

(2) Creating a pivot table

(3) Adding row and column fields

(4) Add Value field

(5) Setting style

2. Set line collapse, expand

3. Set Word orderby order, descending order

4. Delete pivot table

Second, the preparation tool

• Spire.xls for. NET (more than 80 Excel built-in PivotTable styles are supported)

PS: After installation, note that referencing Spire.XLS.dll in the project and then the code operation, the DLL files are obtained in the Bin folder under the installation path.

Three, example Operation1. Create a pivot table

C#

            //create an instance of the workbook class and load the Excel documentWorkbook Workbook =NewWorkbook (); Workbook. LoadFromFile ("test.xlsx"); //Get first worksheetWorksheet sheet = workbook. worksheets[0]; //Create a cache for data that needs to be aggregated and analyzedCellRange DataRange = sheet. range["A1:D10"]; PivotCache Cache=workbook.            Pivotcaches.add (DataRange); //use the cache to create a PivotTable report and specify the name of the pivot table and the location on the worksheetPivotTable PivotTable = sheet. Pivottables.add ("PivotTable", sheet. range["A12"], cache); //Add row Fields            varR1 = pivottable.pivotfields["Month"]; R1. Axis=Axistypes.row; varr2 = pivottable.pivotfields["manufacturer"]; R2. Axis=Axistypes.row; //set the title of a row fieldPivotTable.Options.RowHeaderCaption ="Month"; //Add column Fields            varcol1 = pivottable.pivotfields["Product"]; Col1. Axis=Axistypes.column; //set the title of a column fieldPivotTable.Options.ColumnHeaderCaption ="Product"; //Add Value FieldPIVOTTABLE.DATAFIELDS.ADD (pivottable.pivotfields["Total Production"],"sum: Total production", subtotaltypes.sum); //set the style of a pivot table (Spire.xls supports more than 80 Excel built-in pivottable styles)Pivottable.builtinstyle =pivotbuiltinstyles.pivotstyledark13; //Save and open a documentWorkbook. SaveToFile ("pivot table. xlsx", excelversion.version2013); System.Diagnostics.Process.Start ("pivot table. xlsx");

Test results:

2. Set line collapse, expand

C#

            //Create a Workbook class object, load an Excel documentWorkbook Workbook =NewWorkbook (); Workbook. LoadFromFile ("pivot table. xlsx"); //Get pivot TableXlspivottable pivotTable = workbook. worksheets[0]. pivottables[0] asxlspivottable; //Calculate DataPivottable.calculatedata (); //Expand the details of "2" under the "Month" field(pivottable.pivotfields["Month"] asSpire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField). Hideitemdetail ("2",false); //collapse details for "3" under the "Month" field(pivottable.pivotfields["Month"] asSpire.Xls.Core.Spreadsheet.PivotTables.XlsPivotField). Hideitemdetail ("3",true); //Save and open a documentWorkbook. SaveToFile ("collapse, expand line. xlsx", excelversion.version2013); System.Diagnostics.Process.Start ("collapse, expand line. xlsx");

Test results:

3. Set field sorting

There are three different types of sorting supported, and you can select the appropriate sort type as needed.

C#

            //Create a Workbook class object and load the Excel documentWorkbook Workbook =NewWorkbook (); Workbook. LoadFromFile ("pivot table. xlsx"); //Get pivot TableSpire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook. worksheets[0]. pivottables[0] asSpire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable; //sort the specified fields in ascending orderpivottable.pivotfields[2]. SortType =pivotfieldsorttype.ascending; //Save and open a documentWorkbook. SaveToFile ("Ascending. xlsx", excelversion.version2013); System.Diagnostics.Process.Start ("Ascending. xlsx");

Test results:

4. Delete pivot table

There are two ways to delete a pivot table:

• Delete based on pivot table name
• Delete based on pivot table index

            //Create a workbook, and load an Excel documentWorkbook Workbook =NewWorkbook (); Workbook. LoadFromFile ("pivot table. xlsx"); //Delete the PivotTable report with the name "PivotTable" on the first sheetWorkbook. worksheets[0]. Pivottables.remove ("PivotTable"); //Delete index 0 on first worksheet is the first PivotTable report//workbook. Worksheets[0].              Pivottables.removeat (0); //Save DocumentWorkbook. SaveToFile ("Delete a PivotTable report . xlsx", excelversion.version2013);

Test results:

The above content for the "Excel PivotTable report Example operation" of the full content, welcome reprint (Reproduced please specify the source)

Thanks for reading!

C # operations Excel PivotTable report