// 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 GregorianCalendar(2018, 0, 6), "United States"}, {2, "Canon EOS 1500D", "Consumer Electronics", 8239, new GregorianCalendar(2018, 0, 7), "United Kingdom"}, {3, "Haier 394L 4Star", "Consumer Electronics", 617, new GregorianCalendar(2018, 0, 8), "United States"}, {4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, new GregorianCalendar(2018, 0, 10), "Canada"}, {5, "Mi LED 40inch", "Consumer Electronics", 2626, new GregorianCalendar(2018, 0, 10), "Germany"}, {6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, new GregorianCalendar(2018, 0, 11), "United States"}, {7, "Iphone XR", "Mobile", 9062, new GregorianCalendar(2018, 0, 11), "Australia"}, {8, "OnePlus 7Pro", "Mobile", 6906, new GregorianCalendar(2018, 0, 16), "New Zealand"}, {9, "Redmi 7", "Mobile", 2417, new GregorianCalendar(2018, 0, 16), "France"}, {10, "Samsung S9", "Mobile", 7431, new GregorianCalendar(2018, 0, 16), "Canada"}, {11, "OnePlus 7Pro", "Mobile", 8250, new GregorianCalendar(2018, 0, 16), "Germany"}, {12, "Redmi 7", "Mobile", 7012, new GregorianCalendar(2018, 0, 18), "United States"}, {13, "Bose 785593-0050", "Consumer Electronics", 1903, new GregorianCalendar(2018, 0, 20), "Germany"}, {14, "Canon EOS 1500D", "Consumer Electronics", 2824, new GregorianCalendar(2018, 0, 22), "Canada"}, {15, "Haier 394L 4Star", "Consumer Electronics", 6946, new GregorianCalendar(2018, 0, 24), "France"}, }; IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("J1:O16").setValue(sourceData); worksheet.getRange("J:O").setColumnWidth(15); IPivotCache pivotcache = workbook.getPivotCaches().create(worksheet.getRange("J1:O16")); IWorksheet worksheet2 = workbook.getWorksheets().add(); IPivotTable pivottable = worksheet2.getPivotTables().add(pivotcache, worksheet2.getRange("A1"), "pivottable1"); worksheet.getRange("L1:L16").setNumberFormat("$#,##0.00"); //config pivot table's fields IPivotField field_Category = pivottable.getPivotFields().get("Category"); field_Category.setOrientation(PivotFieldOrientation.ColumnField); IPivotField field_Country = pivottable.getPivotFields().get("Country"); field_Country.setOrientation(PivotFieldOrientation.RowField); IPivotField field_Amount = pivottable.getPivotFields().get("Amount"); field_Amount.setOrientation(PivotFieldOrientation.DataField); field_Amount.setNumberFormat("$#,##0.00"); IPivotField field_Date = pivottable.getPivotFields().get("Date"); field_Date.setOrientation(PivotFieldOrientation.PageField); IPivotField field_Product = pivottable.getPivotFields().get("Product"); field_Product.setOrientation(PivotFieldOrientation.RowField); worksheet2.select(); worksheet2.getRange("A:D").getEntireColumn().autoFit(); worksheet2.getRange("F1").setValue("Usage of GETPIVOTDATA and IRange.GenerateGetPivotDataFunction()."); worksheet2.getRange("F3").setValue("Return the Canon EOS 1500D Amount in Consumer Electronics in Canada."); worksheet2.getRange("F4").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."); worksheet2.getRange("F5").setValue(worksheet2.getRange("B8").generateGetPivotDataFunction()); worksheet2.getRange("F6").setFormula(worksheet2.getRange("B8").generateGetPivotDataFunction()); worksheet2.getRange("F8").setValue("Return the Amount of all Consumer Electronics in Canada."); worksheet2.getRange("F9").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."); worksheet2.getRange("F10").setValue(worksheet2.getRange("B7").generateGetPivotDataFunction()); worksheet2.getRange("F11").setFormula(worksheet2.getRange("B7").generateGetPivotDataFunction()); worksheet2.getRange("F13").setValue("Return to the Amount of Redmi 7 in Mobile in France."); worksheet2.getRange("F14").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."); worksheet2.getRange("F15").setValue(worksheet2.getRange("C13").generateGetPivotDataFunction()); worksheet2.getRange("F16").setFormula(worksheet2.getRange("C13").generateGetPivotDataFunction()); worksheet2.getRange("F18").setValue("Return the Grand Total of all Products in Germany."); worksheet2.getRange("F19").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."); worksheet2.getRange("F20").setValue(worksheet2.getRange("D14").generateGetPivotDataFunction()); worksheet2.getRange("F21").setFormula(worksheet2.getRange("D14").generateGetPivotDataFunction()); worksheet2.getRange("F23").setValue("Return the Grand Total of all Country's Mobile Amount."); worksheet2.getRange("F24").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function."); worksheet2.getRange("F25").setValue(worksheet2.getRange("C27").generateGetPivotDataFunction()); worksheet2.getRange("F26").setFormula(worksheet2.getRange("C27").generateGetPivotDataFunction()); worksheet2.getRange("F28").setValue("Return to the Amount of Redmi 7/Haier 394L 4Star in Consumer Electronics in France/United States."); worksheet2.getRange("F29").setValue("=GETPIVOTDATA(\"Amount\",$A$3,\"Product\",A12:A13,\"Category\",\"Consumer Electronics\",\"Country\",{\"France\",\"United States\"})"); worksheet2.getRange("F30").setFormula2("=GETPIVOTDATA(\"Amount\",$A$3,\"Product\",A12:A13,\"Category\",\"Consumer Electronics\",\"Country\",{\"France\",\"United States\"})"); worksheet2.getRange("F1, F3, F4, F8, F9, F13, F14, F18, F19, F23, F24, F28").getFont().setBold(true); worksheet2.getRange("F5, F10, F15, F20, F25, F29").getFont().setColor(Color.GetDarkRed()); worksheet2.getRange("F6, F11, F16, F21, F26, F30:G31").setHorizontalAlignment(HorizontalAlignment.Left); // Save to an excel file workbook.save("GetPivotDataFunction.xlsx");
// Create a new workbook var workbook = Workbook() val sourceData: Any = arrayOf( arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Bose 785593-0050", "Consumer Electronics", 4270, GregorianCalendar(2018, 0, 6), "United States"), arrayOf(2, "Canon EOS 1500D", "Consumer Electronics", 8239, GregorianCalendar(2018, 0, 7), "United Kingdom"), arrayOf(3, "Haier 394L 4Star", "Consumer Electronics", 617, GregorianCalendar(2018, 0, 8), "United States"), arrayOf(4, "IFB 6.5 Kg FullyAuto", "Consumer Electronics", 8384, GregorianCalendar(2018, 0, 10), "Canada"), arrayOf(5, "Mi LED 40inch", "Consumer Electronics", 2626, GregorianCalendar(2018, 0, 10), "Germany"), arrayOf(6, "Sennheiser HD 4.40-BT", "Consumer Electronics", 3610, GregorianCalendar(2018, 0, 11), "United States"), arrayOf(7, "Iphone XR", "Mobile", 9062, GregorianCalendar(2018, 0, 11), "Australia"), arrayOf(8, "OnePlus 7Pro", "Mobile", 6906, GregorianCalendar(2018, 0, 16), "New Zealand"), arrayOf(9, "Redmi 7", "Mobile", 2417, GregorianCalendar(2018, 0, 16), "France"), arrayOf(10, "Samsung S9", "Mobile", 7431, GregorianCalendar(2018, 0, 16), "Canada"), arrayOf(11, "OnePlus 7Pro", "Mobile", 8250, GregorianCalendar(2018, 0, 16), "Germany"), arrayOf(12, "Redmi 7", "Mobile", 7012, GregorianCalendar(2018, 0, 18), "United States"), arrayOf(13, "Bose 785593-0050", "Consumer Electronics", 1903, GregorianCalendar(2018, 0, 20), "Germany"), arrayOf(14, "Canon EOS 1500D", "Consumer Electronics", 2824, GregorianCalendar(2018, 0, 22), "Canada"), arrayOf(15, "Haier 394L 4Star", "Consumer Electronics", 6946, GregorianCalendar(2018, 0, 24), "France")) val worksheet: IWorksheet = workbook.getWorksheets().get(0) worksheet.getRange("J1:O16").setValue(sourceData) worksheet.getRange("J:O").setColumnWidth(15.0) val pivotcache: IPivotCache = workbook.getPivotCaches().create(worksheet.getRange("J1:O16")) val worksheet2: IWorksheet = workbook.getWorksheets().add() val pivottable: IPivotTable = worksheet2.getPivotTables().add(pivotcache, worksheet2.getRange("A1"), "pivottable1") worksheet.getRange("L1:L16").setNumberFormat("$#,##0.00") //config pivot table's fields val field_Category: IPivotField = pivottable.getPivotFields().get("Category") field_Category.setOrientation(PivotFieldOrientation.ColumnField) val field_Country: IPivotField = pivottable.getPivotFields().get("Country") field_Country.setOrientation(PivotFieldOrientation.RowField) val field_Amount: IPivotField = pivottable.getPivotFields().get("Amount") field_Amount.setOrientation(PivotFieldOrientation.DataField) field_Amount.setNumberFormat("$#,##0.00") val field_Date: IPivotField = pivottable.getPivotFields().get("Date") field_Date.setOrientation(PivotFieldOrientation.PageField) val field_Product: IPivotField = pivottable.getPivotFields().get("Product") field_Product.setOrientation(PivotFieldOrientation.RowField) worksheet2.select() worksheet2.getRange("A:D").getEntireColumn().autoFit() worksheet2.getRange("F1").setValue("Usage of GETPIVOTDATA and IRange.GenerateGetPivotDataFunction().") worksheet2.getRange("F3").setValue("Return the Canon EOS 1500D Amount in Consumer Electronics in Canada.") worksheet2.getRange("F4").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function.") worksheet2.getRange("F5").setValue(worksheet2.getRange("B8").generateGetPivotDataFunction()) worksheet2.getRange("F6").setFormula(worksheet2.getRange("B8").generateGetPivotDataFunction()) worksheet2.getRange("F8").setValue("Return the Amount of all Consumer Electronics in Canada.") worksheet2.getRange("F9").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function.") worksheet2.getRange("F10").setValue(worksheet2.getRange("B7").generateGetPivotDataFunction()) worksheet2.getRange("F11").setFormula(worksheet2.getRange("B7").generateGetPivotDataFunction()) worksheet2.getRange("F13").setValue("Return to the Amount of Redmi 7 in Mobile in France.") worksheet2.getRange("F14").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function.") worksheet2.getRange("F15").setValue(worksheet2.getRange("C13").generateGetPivotDataFunction()) worksheet2.getRange("F16").setFormula(worksheet2.getRange("C13").generateGetPivotDataFunction()) worksheet2.getRange("F18").setValue("Return the Grand Total of all Products in Germany.") worksheet2.getRange("F19").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function.") worksheet2.getRange("F20").setValue(worksheet2.getRange("D14").generateGetPivotDataFunction()) worksheet2.getRange("F21").setFormula(worksheet2.getRange("D14").generateGetPivotDataFunction()) worksheet2.getRange("F23").setValue("Return the Grand Total of all Country's Mobile Amount.") worksheet2.getRange("F24").setValue("Use IRange.GenerateGetPivotDataFunction() to generate the corresponding function.") worksheet2.getRange("F25").setValue(worksheet2.getRange("C27").generateGetPivotDataFunction()) worksheet2.getRange("F26").setFormula(worksheet2.getRange("C27").generateGetPivotDataFunction()) worksheet2.getRange("F28").setValue("Return to the Amount of Redmi 7/Haier 394L 4Star in Consumer Electronics in France/United States.") worksheet2.getRange("F29").setValue("=GETPIVOTDATA(\"Amount\",\$A$3,\"Product\",A12:A13,\"Category\",\"Consumer Electronics\",\"Country\",{\"France\",\"United States\"})") worksheet2.getRange("F30").setFormula2("=GETPIVOTDATA(\"Amount\",\$A$3,\"Product\",A12:A13,\"Category\",\"Consumer Electronics\",\"Country\",{\"France\",\"United States\"})") worksheet2.getRange("F1, F3, F4, F8, F9, F13, F14, F18, F19, F23, F24, F28").getFont().setBold(true) worksheet2.getRange("F5, F10, F15, F20, F25, F29").getFont().setColor(com.grapecity.documents.excel.Color.GetDarkRed()) worksheet2.getRange("F6, F11, F16, F21, F26, F30:G31").setHorizontalAlignment(HorizontalAlignment.Left) // Save to an excel file workbook.save("GetPivotDataFunction.xlsx")