// Create a new workbook Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getActiveSheet(); Object[][] data = new Object[][]{ {"Year", "Category", "Product", "Status", "Sales", "Rating", "Profit"}, {2023, "Electronics", "Smart TV", "Active", 15000, 4.5, 250}, {2023, "Fashion", "Designer Jeans", "Discontinued", 8000, 4.7, 150}, {2024, "Food", "Organic Granola", "Active", 5000, 4.8, 100}, {2023, "Books", "Bestseller Novel", "Active", 12000, 4.6, 180}, {2024, "Electronics", "Laptop", "Active", 20000, 4.9, 300}, {2023, "Beauty", "Skincare Set", "Active", 7000, 4.4, 120}, {2023, "Home & Garden", "Garden Tools", "Active", 6500, 4.3, 130}, {2024, "Health", "Fitness Tracker", "Active", 9500, 4.6, 190}, {2023, "Toys", "Action Figure", "Active", 4800, 4.7, 95}, {2024, "Automotive", "Car Accessories", "Active", 3200, 4.5, 65}, {2023, "Sports", "Basketball", "Active", 7600, 4.8, 150}, {2024, "Office Supplies", "Notebooks", "Active", 11000, 4.4, 220}, {2023, "Pet Supplies", "Dog Food", "Active", 5600, 4.6, 110}, {2024, "Music", "Headphones", "Active", 13000, 4.9, 260}, {2023, "Outdoor", "Camping Tent", "Active", 4400, 4.5, 88}, {2024, "Jewelry", "Silver Necklace", "Active", 2800, 4.7, 56}, {2023, "Tools", "Power Drill", "Active", 3900, 4.4, 78}, {2024, "Baby", "Stroller", "Active", 1700, 4.6, 34}, {2023, "Kitchen", "Blender", "Active", 2500, 4.8, 50}, {2024, "Clothing", "Casual Shirt", "Active", 6200, 4.5, 124}, {2023, "Art", "Oil Paintings", "Active", 1900, 4.7, 38}, {2024, "Hobbies", "Model Trains", "Active", 3100, 4.4, 62}, {2023, "Tech Gadgets", "Smart Watch", "Active", 7300, 4.6, 146}, {2024, "Travel", "Luggage", "Active", 4600, 4.8, 92}, {2023, "Home Decor", "Wall Clock", "Active", 2200, 4.5, 44}, {2024, "Fitness", "Yoga Mat", "Active", 5700, 4.7, 114}, {2023, "Gardening", "Plant Pots", "Active", 3400, 4.4, 68}, {2024, "Audio", "Speakers", "Active", 8900, 4.6, 178}, {2023, "Lighting", "LED Bulbs", "Active", 4300, 4.8, 86}, {2024, "Gaming", "Video Games", "Active", 16000, 4.5, 320}, {2023, "Furniture", "Sofa", "Active", 5500, 4.7, 110}, {2024, "Health", "Vitamins", "Active", 7100, 4.4, 142}, {2023, "Office", "Desk Chair", "Active", 6300, 4.6, 126}, {2024, "Books", "Educational Textbooks", "Active", 9200, 4.8, 184}, {2023, "Tools", "Screwdriver Set", "Active", 3700, 4.5, 74}, {2024, "Home & Garden", "Patio Furniture", "Active", 4900, 4.7, 98}, {2023, "Automotive", "Car Seat Covers", "Active", 2600, 4.4, 52}, {2024, "Electronics", "Smart Home System", "Active", 10500, 4.6, 210}, {2023, "Health", "Blood Pressure Monitor", "Active", 3800, 4.8, 76}, {2024, "Beauty", "Makeup Kit", "Active", 5900, 4.5, 118}, {2023, "Music", "Turntable", "Active", 2100, 4.7, 42}, {2024, "Toys", "Board Games", "Active", 3300, 4.4, 66}, {2023, "Clothing", "Winter Coat", "Active", 4700, 4.6, 94}, {2024, "Food", "Gourmet Chocolates", "Active", 7400, 4.8, 148}, {2023, "Home Decor", "Curtains", "Active", 2900, 4.5, 58}, {2024, "Pet Supplies", "Cat Litter", "Active", 6100, 4.7, 122}, {2023, "Outdoor", "Hiking Boots", "Active", 3500, 4.4, 70}, {2024, "Kitchen", "Slow Cooker", "Active", 8200, 4.6, 164}, {2023, "Toys", "Dolls", "Active", 4400, 4.8, 88}, {2024, "Books", "Poetry Collection", "Active", 5300, 4.5, 106}, {2023, "Health", "Massage Chair", "Active", 6600, 4.7, 132}, {2024, "Home & Garden", "Fire Pit", "Active", 7700, 4.4, 154}, {2023, "Automotive", "Car Wash Kit", "Active", 4000, 4.6, 80}, {2024, "Office Supplies", "Highlighters", "Active", 5200, 4.8, 104}, {2023, "Tools", "Hammer", "Active", 3200, 4.5, 64}, {2024, "Beauty", "Hair Dryer", "Active", 6800, 4.7, 136}, {2023, "Kitchen", "Coffee Maker", "Active", 4600, 4.4, 92}, {2024, "Toys", "Remote Control Cars", "Active", 7900, 4.6, 158}, {2023, "Home Decor", "Picture Frames", "Active", 3300, 4.8, 66}, {2024, "Electronics", "Router", "Active", 8500, 4.5, 170}, {2023, "Clothing", "Suit", "Active", 5700, 4.7, 114} }; sheet.getRange("A1:G" + data.length).setValue(data); sheet.getRange("A1:G1").getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(200, 214, 239)); sheet.getRange("J1").setValue("The basic usage with conditional formatting"); sheet.getRange("J2").setFormula("=FORMULATEXT(J3)"); sheet.getRange("J3").setFormula2("=GROUPBY(A1:B62,E1:E62,SUM,3,2)"); IFormatCondition conditionalFormatting1 = (IFormatCondition) sheet.getRange("J3:L48").getFormatConditions() .add(FormatConditionType.Expression, FormatConditionOperator.None, "$J3=\"Year\"", null); conditionalFormatting1.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(183, 221, 121)); conditionalFormatting1.getFont().setBold(true); IFormatCondition conditionalFormatting2 = (IFormatCondition) sheet.getRange("J3:L48").getFormatConditions() .add(FormatConditionType.Expression, FormatConditionOperator.None, "$K3=\"\"", null); conditionalFormatting2.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(146, 208, 80)); conditionalFormatting2.getFont().setBold(true); sheet.getRange("P1").setValue("The non-adjacent columns and multiple aggregations with VSTACK"); sheet.getRange("P2").setFormula("=FORMULATEXT(P3)"); sheet.getRange("P3").setFormula2("=GROUPBY(B1:B62,E1:G62,VSTACK(COUNT,LAMBDA(a,IF(COUNT(a)>2,\"↑\",\"↓\")),PERCENTOF),,0)"); IFormatCondition conditionalFormatting3 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions() .add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"COUNT\"", null); conditionalFormatting3.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(180, 199, 231)); IFormatCondition conditionalFormatting4 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions() .add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"CUSTOM\"", null); conditionalFormatting4.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(216, 228, 188)); IFormatCondition conditionalFormatting5 = (IFormatCondition) sheet.getRange("P3:R95").getFormatConditions() .add(FormatConditionType.Expression, FormatConditionOperator.None, "$Q3=\"PERCENTOF\"", null); conditionalFormatting5.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(198, 224, 180)); sheet.getRange("A:AD").setColumnWidth(12.5); // Save to an excel file workbook.save("GroupbyFunction.xlsx");