//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); object[,] data = { {"Order ID", "Product", "Category", "Amount", "Date", "Country" }, {1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"}, {2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"}, {3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"}, {5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"}, {6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"}, {7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"}, {8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"}, {9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"}, {10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"}, {11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"}, {12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"}, {13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"}, {15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"}, {16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"}, {17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"}, {18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"}, {20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"}, {21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"}, {22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"}, {23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"}, {24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"}, {25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"}, {26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"}, {27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"}, {28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"}, {29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"}, {30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"}, {31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"}, {32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"}, {33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"}, {34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"}, {35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"}, {36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"}, {37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"}, {38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"}, {39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"}, {40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"}, {41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"}, {42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"}, {43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"}, {44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"}, {45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"}, {46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"}, {47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"}, {48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"}, {49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"}, {50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"}, {51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"}, {52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"}, {53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"}, {55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"}, {57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"}, {58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"}, {59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"}, {60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"}, {61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"}, {62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"}, {63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"}, {64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"}, {65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"}, {66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"}, {67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"}, {68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"}, {69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"}, {70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France"}, }; IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Name = "Source"; //Add data source of pivot table. worksheet.Range["A1:F71"].Value = data; worksheet.Range["E2:E71"].NumberFormat = "m/d/yyyy;@"; worksheet.Range["D2:D71"].NumberFormat = "$#,##0_);($#,##0)"; IWorksheet calculatedItemSheet = workbook.Worksheets.Add(); calculatedItemSheet.Name = "CalculatedItem"; // Add pivot table. IPivotCache pivotCache = workbook.PivotCaches.Create(worksheet.Range["A1:F71"]); IPivotTable calculatedItemTable = calculatedItemSheet.PivotTables.Add(pivotCache, calculatedItemSheet.Range["A1"]); calculatedItemTable.PivotFields["Product"].Orientation = PivotFieldOrientation.RowField; calculatedItemTable.PivotFields["Country"].Orientation = PivotFieldOrientation.ColumnField; calculatedItemTable.PivotFields["Amount"].Orientation = PivotFieldOrientation.DataField; calculatedItemTable.DataFields[0].NumberFormat = "$#,##0_);($#,##0)"; ICalculatedItems countryCalcItems = calculatedItemTable.PivotFields["Country"].CalculatedItems(); ICalculatedItems productCalcItems = calculatedItemTable.PivotFields["Product"].CalculatedItems(); // add some calculated items countryCalcItems.Add("Oceania", "=Australia+NewZealand"); countryCalcItems.Add("Europe", "=France+Germany"); countryCalcItems.Add("America", "=Canada"); productCalcItems.Add("IPhone 13", "=2500"); // hide the duplicate normal item. IPivotItems countrys = calculatedItemTable.PivotFields["Country"].PivotItems; countrys["United Kingdom"].Visible = false; countrys["United States"].Visible = false; IPivotItems products = calculatedItemTable.PivotFields["Product"].PivotItems; products["IPhone 13"].Visible = false; calculatedItemSheet.Range["A:I"].AutoFit(); calculatedItemSheet.Activate();
' Create a new Workbook Dim workbook As New Workbook Dim data As Object(,) = { {"Order ID", "Product", "Category", "Amount", "Date", "Country"}, {1, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2300, "1/1/2020", "United Kingdom"}, {2, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4700, "1/1/2020", "Australia"}, {3, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 2000, "1/1/2020", "NewZealand"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "United States"}, {5, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4000, "1/1/2020", "Germany"}, {6, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 4200, "1/1/2020", "Canada"}, {7, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 3700, "1/1/2020", "France"}, {8, "Mi LED 40inch", "Consumer Electronics", 1500, "1/1/2020", "United Kingdom"}, {9, "Mi LED 40inch", "Consumer Electronics", 2100, "1/1/2020", "Australia"}, {10, "Mi LED 40inch", "Consumer Electronics", 3100, "1/1/2020", "NewZealand"}, {11, "Mi LED 40inch", "Consumer Electronics", 3200, "1/1/2020", "United States"}, {12, "Mi LED 40inch", "Consumer Electronics", 4800, "1/1/2020", "Germany"}, {13, "Mi LED 40inch", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {14, "Mi LED 40inch", "Consumer Electronics", 700, "1/1/2020", "France"}, {15, "Sennheiser HD 4.40-BT", "Consumer Electronics", 900, "1/1/2020", "United Kingdom"}, {16, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3600, "1/1/2020", "Australia"}, {17, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4300, "1/1/2020", "NewZealand"}, {18, "Sennheiser HD 4.40-BT", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {19, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3800, "1/1/2020", "Germany"}, {20, "Sennheiser HD 4.40-BT", "Consumer Electronics", 2700, "1/1/2020", "Canada"}, {21, "Sennheiser HD 4.40-BT", "Consumer Electronics", 1200, "1/1/2020", "France"}, {22, "Iphone XR", "Mobile", 4600, "1/1/2020", "United Kingdom"}, {23, "Iphone XR", "Mobile", 3900, "1/1/2020", "Australia"}, {24, "Iphone XR", "Mobile", 2100, "1/1/2020", "NewZealand"}, {25, "Iphone XR", "Mobile", 2000, "1/1/2020", "United States"}, {26, "Iphone XR", "Mobile", 4000, "1/1/2020", "Germany"}, {27, "Iphone XR", "Mobile", 2900, "1/1/2020", "Canada"}, {28, "Iphone XR", "Mobile", 2200, "1/1/2020", "France"}, {29, "Samsung S9", "Mobile", 1200, "1/1/2020", "United Kingdom"}, {30, "Samsung S9", "Mobile", 2800, "1/1/2020", "Australia"}, {31, "Samsung S9", "Mobile", 1700, "1/1/2020", "NewZealand"}, {32, "Samsung S9", "Mobile", 500, "1/1/2020", "United States"}, {33, "Samsung S9", "Mobile", 200, "1/1/2020", "Germany"}, {34, "Samsung S9", "Mobile", 3900, "1/1/2020", "Canada"}, {35, "Samsung S9", "Mobile", 4800, "1/1/2020", "France"}, {36, "OnePlus 7Pro", "Mobile", 3600, "1/1/2020", "United Kingdom"}, {37, "OnePlus 7Pro", "Mobile", 2700, "1/1/2020", "Australia"}, {38, "OnePlus 7Pro", "Mobile", 1800, "1/1/2020", "NewZealand"}, {39, "OnePlus 7Pro", "Mobile", 2200, "1/1/2020", "United States"}, {40, "OnePlus 7Pro", "Mobile", 900, "1/1/2020", "Germany"}, {41, "OnePlus 7Pro", "Mobile", 600, "1/1/2020", "Canada"}, {42, "OnePlus 7Pro", "Mobile", 2500, "1/1/2020", "France"}, {43, "Redmi 7", "Mobile", 4200, "1/1/2020", "United Kingdom"}, {44, "Redmi 7", "Mobile", 4200, "1/1/2020", "Australia"}, {45, "Redmi 7", "Mobile", 100, "1/1/2020", "NewZealand"}, {46, "Redmi 7", "Mobile", 3300, "1/1/2020", "United States"}, {47, "Redmi 7", "Mobile", 4200, "1/1/2020", "Germany"}, {48, "Redmi 7", "Mobile", 3900, "1/1/2020", "Canada"}, {49, "Redmi 7", "Mobile", 1700, "1/1/2020", "France"}, {50, "Bose 785593-0050", "Consumer Electronics", 2800, "1/1/2020", "United Kingdom"}, {51, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Australia"}, {52, "Bose 785593-0050", "Consumer Electronics", 400, "1/1/2020", "NewZealand"}, {53, "Bose 785593-0050", "Consumer Electronics", 4500, "1/1/2020", "United States"}, {54, "Bose 785593-0050", "Consumer Electronics", 3400, "1/1/2020", "Germany"}, {55, "Bose 785593-0050", "Consumer Electronics", 200, "1/1/2020", "Canada"}, {56, "Bose 785593-0050", "Consumer Electronics", 2700, "1/1/2020", "France"}, {57, "Canon EOS 1500D", "Consumer Electronics", 800, "1/1/2020", "United Kingdom"}, {58, "Canon EOS 1500D", "Consumer Electronics", 4800, "1/1/2020", "Australia"}, {59, "Canon EOS 1500D", "Consumer Electronics", 1700, "1/1/2020", "NewZealand"}, {60, "Canon EOS 1500D", "Consumer Electronics", 2500, "1/1/2020", "United States"}, {61, "Canon EOS 1500D", "Consumer Electronics", 1300, "1/1/2020", "Germany"}, {62, "Canon EOS 1500D", "Consumer Electronics", 1500, "1/1/2020", "Canada"}, {63, "Canon EOS 1500D", "Consumer Electronics", 200, "1/1/2020", "France"}, {64, "Haier 394L 4Star", "Consumer Electronics", 4300, "1/1/2020", "United Kingdom"}, {65, "Haier 394L 4Star", "Consumer Electronics", 1300, "1/1/2020", "Australia"}, {66, "Haier 394L 4Star", "Consumer Electronics", 2600, "1/1/2020", "NewZealand"}, {67, "Haier 394L 4Star", "Consumer Electronics", 4600, "1/1/2020", "United States"}, {68, "Haier 394L 4Star", "Consumer Electronics", 1200, "1/1/2020", "Germany"}, {69, "Haier 394L 4Star", "Consumer Electronics", 800, "1/1/2020", "Canada"}, {70, "Haier 394L 4Star", "Consumer Electronics", 2200, "1/1/2020", "France"}} Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Name = "Source" worksheet.Range("A1:F71").Value = data worksheet.Range("E2:E71").NumberFormat = "m/d/yyyy;@" worksheet.Range("D2:D71").NumberFormat = "$#,##0_);($#,##0)" Dim calculatedItemSheet As IWorksheet = workbook.Worksheets.Add() calculatedItemSheet.Name = "CalculatedItem" Dim pivotCache As IPivotCache = workbook.PivotCaches.Create(worksheet.Range("A1:F71")) Dim calculatedItemTable As IPivotTable = calculatedItemSheet.PivotTables.Add(pivotCache, calculatedItemSheet.Range("A1")) calculatedItemTable.PivotFields("Product").Orientation = PivotFieldOrientation.RowField calculatedItemTable.PivotFields("Country").Orientation = PivotFieldOrientation.ColumnField calculatedItemTable.PivotFields("Amount").Orientation = PivotFieldOrientation.DataField calculatedItemTable.DataFields(0).NumberFormat = "$#,##0_);($#,##0)" Dim countryCalcItems As ICalculatedItems = calculatedItemTable.PivotFields("Country").CalculatedItems() Dim productCalcItems As ICalculatedItems = calculatedItemTable.PivotFields("Product").CalculatedItems() countryCalcItems.Add("Oceania", "=Australia+NewZealand") countryCalcItems.Add("Europe", "=France+Germany") countryCalcItems.Add("America", "=Canada") productCalcItems.Add("IPhone 13", "=2500") Dim countrys As IPivotItems = calculatedItemTable.PivotFields("Country").PivotItems countrys("United Kingdom").Visible = False countrys("United States").Visible = False Dim products As IPivotItems = calculatedItemTable.PivotFields("Product").PivotItems products("IPhone 13").Visible = False calculatedItemSheet.Range("A:I").AutoFit() calculatedItemSheet.Activate() ' save to an excel file workbook.Save("CalculatedItem.xlsx")