Create Pivot Table

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.

Create Pivot Table with Range-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a range:

//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");

Create Pivot Table with Table-Based Data Source

Refer to the following example code to create a pivot table in a worksheet with a table:

// 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");

Use Picture in Cell as Pivot Table Data Source

Pivot tables support using ranges that contain in-cell pictures as the data source. When sorting or filtering pivot fields that are based on in-cell pictures, the picture's alt text is used for comparison and filtering.

Refer to the following example code to create a pivot table with in-cell pictures as a source.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.ActiveSheet;

// Set data.
object[,] data = new object[,]
{
    { "Item", "Category", "Quantity", "Image" },
    { "Dress", "Clothing", 10, null },
    { "Hat", "Accessories", 5, null },
    { "Boots", "Shoes", 8, null },
    { "Glasses", "Accessories", 3, null }
};
sheet.Range["G1:J5"].Value = data;

// Set pictures in cells with alt text.
string[] imageFiles = { "Hat.png", "Boots.png", "Dress.png", "Glasses.png" };
for (int i = 0; i < imageFiles.Length; i++)
{
    byte[] imageData = File.ReadAllBytes(imageFiles[i]);
    sheet.Range[$"J{i + 2}"].CellPicture = new CellPicture(imageData, Path.GetFileNameWithoutExtension(imageFiles[i]));
}

// Configure pivot table's fields.
var pivotcache = workbook.PivotCaches.Create(sheet.Range["G1:J5"]);
var pivottable = sheet.PivotTables.Add(pivotcache, sheet.Range["A1"], "pivottable");
pivottable.PivotFields["Category"].Orientation = PivotFieldOrientation.ColumnField;
pivottable.PivotFields["Image"].Orientation = PivotFieldOrientation.RowField;
pivottable.PivotFields["Quantity"].Orientation = PivotFieldOrientation.DataField;

// Save to an Excel file.
workbook.Save("PivotTablewithPicture.xlsx");

The output is shown in the figure below:

Limitation

DsExcel does not support referencing partial table area, such as 'Table1[[Column1]:[Column3]]'.