//create a new workbook var workbook = new GrapeCity.Documents.Excel.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" }, }; IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["J1:O16"].Value = sourceData; worksheet.Range["J:O"].ColumnWidth = 15; IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["J1:O16"]); IWorksheet worksheet2 = workbook.Worksheets.Add(); IPivotTable pivottable = worksheet2.PivotTables.Add(pivotcache, worksheet2.Range["A1"], "pivottable1"); worksheet.Range["L1:L16"].NumberFormat = "$#,##0.00"; //config pivot table's fields IPivotField field_Category = pivottable.PivotFields["Category"]; field_Category.Orientation = PivotFieldOrientation.ColumnField; IPivotField field_Country = pivottable.PivotFields["Country"]; field_Country.Orientation = PivotFieldOrientation.RowField; IPivotField field_Amount = pivottable.PivotFields["Amount"]; field_Amount.Orientation = PivotFieldOrientation.DataField; field_Amount.NumberFormat = "$#,##0.00"; IPivotField field_Date = pivottable.PivotFields["Date"]; field_Date.Orientation = PivotFieldOrientation.PageField; IPivotField field_Product = pivottable.PivotFields["Product"]; field_Product.Orientation = PivotFieldOrientation.RowField; worksheet2.Select(); worksheet2.Range["A:D"].EntireColumn.AutoFit(); worksheet2.Range["F1"].Value = "Usage of GETPIVOTDATA and IRange.GenerateGetPivotDataFunction()."; worksheet2.Range["F3"].Value = "Return the Canon EOS 1500D Amount in Consumer Electronics in Canada."; worksheet2.Range["F4"].Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."; worksheet2.Range["F5"].Value = worksheet2.Range["B8"].GenerateGetPivotDataFunction(); worksheet2.Range["F6"].Formula = worksheet2.Range["B8"].GenerateGetPivotDataFunction(); worksheet2.Range["F8"].Value = "Return the Amount of all Consumer Electronics in Canada."; worksheet2.Range["F9"].Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."; worksheet2.Range["F10"].Value = worksheet2.Range["B7"].GenerateGetPivotDataFunction(); worksheet2.Range["F11"].Formula = worksheet2.Range["B7"].GenerateGetPivotDataFunction(); worksheet2.Range["F13"].Value = "Return to the Amount of Redmi 7 in Mobile in France."; worksheet2.Range["F14"].Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."; worksheet2.Range["F15"].Value = worksheet2.Range["C13"].GenerateGetPivotDataFunction(); worksheet2.Range["F16"].Formula = worksheet2.Range["C13"].GenerateGetPivotDataFunction(); worksheet2.Range["F18"].Value = "Return the Grand Total of all Products in Germany."; worksheet2.Range["F19"].Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."; worksheet2.Range["F20"].Value = worksheet2.Range["D14"].GenerateGetPivotDataFunction(); worksheet2.Range["F21"].Formula = worksheet2.Range["D14"].GenerateGetPivotDataFunction(); worksheet2.Range["F23"].Value = "Return the Grand Total of all Country's Mobile Amount."; worksheet2.Range["F24"].Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."; worksheet2.Range["F25"].Value = worksheet2.Range["C27"].GenerateGetPivotDataFunction(); worksheet2.Range["F26"].Formula = worksheet2.Range["C27"].GenerateGetPivotDataFunction(); worksheet2.Range["F28"].Value = "Return to the Amount of Redmi 7/Haier 394L 4Star in Consumer Electronics in France/United States."; worksheet2.Range["F29"].Value = @"=GETPIVOTDATA(""Amount"",$A$3,""Product"",A12:A13,""Category"",""Consumer Electronics"",""Country"",{""France"",""United States""})"; worksheet2.Range["F30"].Formula2 = @"=GETPIVOTDATA(""Amount"",$A$3,""Product"",A12:A13,""Category"",""Consumer Electronics"",""Country"",{""France"",""United States""})"; worksheet2.Range["F1, F3, F4, F8, F9, F13, F14, F18, F19, F23, F24, F28"].Font.Bold = true; worksheet2.Range["F5, F10, F15, F20, F25, F29"].Font.Color = Color.DarkRed; worksheet2.Range["F6, F11, F16, F21, F26, F30:G31"].HorizontalAlignment = HorizontalAlignment.Left; // Save to an excel file workbook.Save("GetPivotDataFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim sourceData As Object(,) = 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"} } Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.Range("J1:O16").Value = sourceData worksheet.Range("J:O").ColumnWidth = 15 Dim pivotcache As IPivotCache = workbook.PivotCaches.Create(worksheet.Range("J1:O16")) Dim worksheet2 As IWorksheet = workbook.Worksheets.Add() Dim pivottable As IPivotTable = worksheet2.PivotTables.Add(pivotcache, worksheet2.Range("A1"), "pivottable1") worksheet.Range("L1:L16").NumberFormat = "$#,##0.00" Dim field_Category As IPivotField = pivottable.PivotFields("Category") field_Category.Orientation = PivotFieldOrientation.ColumnField Dim field_Country As IPivotField = pivottable.PivotFields("Country") field_Country.Orientation = PivotFieldOrientation.RowField Dim field_Amount As IPivotField = pivottable.PivotFields("Amount") field_Amount.Orientation = PivotFieldOrientation.DataField field_Amount.NumberFormat = "$#,##0.00" Dim field_Date As IPivotField = pivottable.PivotFields("Date") field_Date.Orientation = PivotFieldOrientation.PageField Dim field_Product As IPivotField = pivottable.PivotFields("Product") field_Product.Orientation = PivotFieldOrientation.RowField worksheet2.[Select]() worksheet2.Range("A:D").EntireColumn.AutoFit() worksheet2.Range("F1").Value = "Usage of GETPIVOTDATA and IRange.GenerateGetPivotDataFunction()." worksheet2.Range("F3").Value = "Return the Canon EOS 1500D Amount in Consumer Electronics in Canada." worksheet2.Range("F4").Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function." worksheet2.Range("F5").Value = worksheet2.Range("B8").GenerateGetPivotDataFunction() worksheet2.Range("F6").Formula = worksheet2.Range("B8").GenerateGetPivotDataFunction() worksheet2.Range("F8").Value = "Return the Amount of all Consumer Electronics in Canada." worksheet2.Range("F9").Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function." worksheet2.Range("F10").Value = worksheet2.Range("B7").GenerateGetPivotDataFunction() worksheet2.Range("F11").Formula = worksheet2.Range("B7").GenerateGetPivotDataFunction() worksheet2.Range("F13").Value = "Return to the Amount of Redmi 7 in Mobile in France." worksheet2.Range("F14").Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function." worksheet2.Range("F15").Value = worksheet2.Range("C13").GenerateGetPivotDataFunction() worksheet2.Range("F16").Formula = worksheet2.Range("C13").GenerateGetPivotDataFunction() worksheet2.Range("F18").Value = "Return the Grand Total of all Products in Germany." worksheet2.Range("F19").Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function." worksheet2.Range("F20").Value = worksheet2.Range("D14").GenerateGetPivotDataFunction() worksheet2.Range("F21").Formula = worksheet2.Range("D14").GenerateGetPivotDataFunction() worksheet2.Range("F23").Value = "Return the Grand Total of all Country's Mobile Amount." worksheet2.Range("F24").Value = "Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function." worksheet2.Range("F25").Value = worksheet2.Range("C27").GenerateGetPivotDataFunction() worksheet2.Range("F26").Formula = worksheet2.Range("C27").GenerateGetPivotDataFunction() worksheet2.Range("F28").Value = "Return to the Amount of Redmi 7/Haier 394L 4Star in Consumer Electronics in France/United States." worksheet2.Range("F29").Value = "=GETPIVOTDATA(""Amount"",$A$3,""Product"",A12:A13,""Category"",""Consumer Electronics"",""Country"",{""France"",""United States""})" worksheet2.Range("F30").Formula2 = "=GETPIVOTDATA(""Amount"",$A$3,""Product"",A12:A13,""Category"",""Consumer Electronics"",""Country"",{""France"",""United States""})" worksheet2.Range("F1, F3, F4, F8, F9, F13, F14, F18, F19, F23, F24, F28").Font.Bold = True worksheet2.Range("F5, F10, F15, F20, F25, F29").Font.Color = Color.DarkRed worksheet2.Range("F6, F11, F16, F21, F26, F30:G31").HorizontalAlignment = HorizontalAlignment.Left ' save to an excel file workbook.Save("GetPivotDataFunction.xlsx")