Documents for Excel .NET Edition | GCDocuments
Features / Pivot Table / Create Pivot Table
In This Topic
    Create Pivot Table
    In This Topic

    GcExcel .NET allows you to create pivot tables in a spreadsheet. But, before generating a pivot table, you first need to create the pivot cache using the PivotCaches collection to stores all the pivot caches in the workbook.

    After you accomplish this, you need to call the Create method of the IPivotCaches interface to create a new pivot cache. After creating pivot cache, the next step is to create the new pivot table using CreatePivotTable method of the IPivotCache interface.

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

    C#
    Copy Code
                //Source data for PivotCache
                object[,] sourceData = new object[,] {
        { "Order ID", "Product",  "Category",   "Amount", "Date",                    "Country" },
        { 1,          "Carrots",  "Vegetables",  4270,    new DateTime(2012, 1, 6),  "United States" },
        { 2,          "Broccoli", "Vegetables",  8239,    new DateTime(2012, 1, 7),  "United Kingdom" },
        { 3,          "Banana",   "Fruit",       617,     new DateTime(2012, 1, 8),  "United States" },
        { 4,          "Banana",   "Fruit",       8384,    new DateTime(2012, 1, 10), "Canada" },
        { 5,          "Beans",    "Vegetables",  2626,    new DateTime(2012, 1, 10), "Germany" },
        { 6,          "Orange",   "Fruit",       3610,    new DateTime(2012, 1, 11), "United States" },
        { 7,          "Broccoli", "Vegetables",  9062,    new DateTime(2012, 1, 11), "Australia" },
        { 8,          "Banana",   "Fruit",       6906,    new DateTime(2012, 1, 16), "New Zealand" },
        { 9,          "Apple",    "Fruit",       2417,    new DateTime(2012, 1, 16), "France" },
        { 10,         "Apple",    "Fruit",       7431,    new DateTime(2012, 1, 16), "Canada" },
        { 11,         "Banana",   "Fruit",       8250,    new DateTime(2012, 1, 16), "Germany" },
        { 12,         "Broccoli", "Vegetables",  7012,    new DateTime(2012, 1, 18), "United States" },
        { 13,         "Carrots",  "Vegetables",  1903,    new DateTime(2012, 1, 20), "Germany" },
        { 14,         "Broccoli", "Vegetables",  2824,    new DateTime(2012, 1, 22), "Canada" },
        { 15,         "Apple",    "Fruit",       6946,    new DateTime(2012, 1, 24), "France" },
    };
    
                //Initialize the WorkBook and fetch the default WorkSheet
                Workbook workbook = new Workbook();
                IWorksheet worksheet = workbook.Worksheets[0];
                // Assigning data to the range
                worksheet.Range["A1:F16"].Value = sourceData;
                // Creating pivot
                var pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
                var pivottable = worksheet.PivotTables.Add(pivotcache, worksheet.Range["L7"], "pivottable1");