DsExcel provides functionality to create and add pivot tables in a spreadsheet using either a specified range or a table. Create method of IPivotCaches interface initializes the pivot cache for the pivot table, while Add method of IPivotTables interface uses this cache to insert the new pivot table into the spreadsheet.
Refer to the following example code to create a pivot table in a worksheet with a range:
C# |
Copy Code |
---|---|
//Create a new workbook. Workbook workbook = new Workbook(); object[,] sourceData = new object[,] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" }, { 1, "Bose 785593-0050", "Consumer Electronics", 4270, new DateTime(2018, 1, 6), "United States" }, { 2, "Canon EOS 1500D", "Consumer Electronics", 8239, new DateTime(2018, 1, 7), "United Kingdom" }, { 3, "Haier 394L 4Star", "Consumer Electronics", 617, new DateTime(2018, 1, 8), "United States" }, { 4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new DateTime(2018, 1, 10), "Canada" }, { 5, "Mi LED 40inch", "Consumer Electronics", 2626, new DateTime(2018, 1, 10), "Germany" }, { 6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new DateTime(2018, 1, 11), "United States" }, { 7, "Iphone XR", "Mobile", 9062, new DateTime(2018, 1, 11), "Australia" }, { 8, "OnePlus 7Pro", "Mobile", 6906, new DateTime(2018, 1, 16), "New Zealand" }, { 9, "Redmi 7", "Mobile", 2417, new DateTime(2018, 1, 16), "France" }, { 10, "Samsung S9", "Mobile", 7431, new DateTime(2018, 1, 16), "Canada" }, { 11, "OnePlus 7Pro", "Mobile", 8250, new DateTime(2018, 1, 16), "Germany" }, { 12, "Redmi 7", "Mobile", 7012, new DateTime(2018, 1, 18), "United States" }, { 13, "Bose 785593-0050", "Consumer Electronics", 1903, new DateTime(2018, 1, 20), "Germany" }, { 14, "Canon EOS 1500D", "Consumer Electronics", 2824, new DateTime(2018, 1, 22), "Canada" }, { 15, "Haier 394L 4Star", "Consumer Electronics", 6946, new DateTime(2018, 1, 24), "France" }, }; // Assigning data to the range. IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["G1:L16"].Value = sourceData; // Creating pivot table with range. var pivotcache = workbook.PivotCaches.Create(worksheet.Range["G1:L16"]); var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1"); // Configure pivot table's fields. var field_Category = pivottable.PivotFields["Category"]; field_Category.Orientation = PivotFieldOrientation.ColumnField; var field_Product = pivottable.PivotFields["Product"]; field_Product.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields["Amount"]; field_Amount.Orientation = PivotFieldOrientation.DataField; field_Amount.NumberFormat = "$#,##0.00"; var field_Country = pivottable.PivotFields["Country"]; field_Country.Orientation = PivotFieldOrientation.PageField; //Save the workbook. workbook.Save(@"ExcelOutput\PivotTable.xlsx"); |
Refer to the following example code to create a pivot table in a worksheet with a table:
C# |
Copy Code |
---|---|
// Create a new workbook. var workbook = new Workbook(); // Create source for the table. object[,] sourceData = new object[,] { { "Order ID", "Product", "Category", "Amount", "Date", "Country" }, { 1, "Bose 785593-0050", "Consumer Electronics", 4270, new DateTime(2018, 1, 6), "United States" }, { 2, "Canon EOS 1500D", "Consumer Electronics", 8239, new DateTime(2018, 1, 7), "United Kingdom" }, { 3, "Haier 394L 4Star", "Consumer Electronics", 617, new DateTime(2018, 1, 8), "United States" }, { 4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new DateTime(2018, 1, 10), "Canada" }, { 5, "Mi LED 40inch", "Consumer Electronics", 2626, new DateTime(2018, 1, 10), "Germany" }, { 6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new DateTime(2018, 1, 11), "United States" }, { 7, "Iphone XR", "Mobile", 9062, new DateTime(2018, 1, 11), "Australia" }, { 8, "OnePlus 7Pro", "Mobile", 6906, new DateTime(2018, 1, 16), "New Zealand" }, { 9, "Redmi 7", "Mobile", 2417, new DateTime(2018, 1, 16), "France" }, { 10, "Samsung S9", "Mobile", 7431, new DateTime(2018, 1, 16), "Canada" }, { 11, "OnePlus 7Pro", "Mobile", 8250, new DateTime(2018, 1, 16), "Germany" }, { 12, "Redmi 7", "Mobile", 7012, new DateTime(2018, 1, 18), "United States" }, { 13, "Bose 785593-0050", "Consumer Electronics", 1903, new DateTime(2018, 1, 20), "Germany" }, { 14, "Canon EOS 1500D", "Consumer Electronics", 2824, new DateTime(2018, 1, 22), "Canada" }, { 15, "Haier 394L 4Star", "Consumer Electronics", 6946, new DateTime(2018, 1, 24), "France" }, }; // Assigning data to the range. IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["G1:L16"].Value = sourceData; worksheet.Range["G:L"].ColumnWidth = 15; // Create table with range G1:L16. ITable table = worksheet.Tables.Add(worksheet.Range["G1:L16"], true); // Create pivot table using table as data source. var pivotcache = workbook.PivotCaches.Create(table); var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["A1"], "pivottable1"); worksheet.Range["J1:J16"].NumberFormat = "$#,##0.00"; // Configure pivot table's fields. var field_Category = pivottable.PivotFields["Category"]; field_Category.Orientation = PivotFieldOrientation.ColumnField; var field_Product = pivottable.PivotFields["Product"]; field_Product.Orientation = PivotFieldOrientation.RowField; var field_Amount = pivottable.PivotFields["Amount"]; field_Amount.Orientation = PivotFieldOrientation.DataField; field_Amount.NumberFormat = "$#,##0.00"; var field_Country = pivottable.PivotFields["Country"]; field_Country.Orientation = PivotFieldOrientation.PageField; worksheet.Range["A:D"].EntireColumn.AutoFit(); // Save the workbook. workbook.Save("CreatePivotTableUsingTable.xlsx"); |
Limitation
DsExcel does not support referencing partial table area, such as 'Table1[[Column1]:[Column3]]'.