Create Pivot Table

Spread.Services Documentation
Create Pivot Table
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Manage Data in Spread Component > Use Pivot Table > Create Pivot Table

Spread.Services 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");

Copyright © GrapeCity, inc. All rights reserved.