// Create a new workbook Workbook workbook = new Workbook(); IWorksheet sheet = workbook.getActiveSheet(); Object[][] data = { {"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 usage with non-adjacent columns and multiple aggregations"); sheet.getRange("J2").setFormula("=FORMULATEXT(J3)"); sheet.getRange("J3").setFormula2("=PIVOTBY(A1:B62,D1:D62,CHOOSECOLS(E1:G62,1,3),HSTACK(PERCENTOF,MAX),3,2)"); IFormatCondition pivotConditionalFormatting1 = (IFormatCondition)sheet.getRange("J3:Q51").getFormatConditions().add( FormatConditionType.Expression, FormatConditionOperator.None, "$J3=\"Year\"", null); pivotConditionalFormatting1.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(217, 226, 243)); IFormatCondition pivotConditionalFormatting2 = (IFormatCondition)sheet.getRange("J3:Q51").getFormatConditions().add( FormatConditionType.Expression, FormatConditionOperator.None, "$K3=\"\"", null); pivotConditionalFormatting2.getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(198, 217, 240)); pivotConditionalFormatting2.getFont().setBold(true); sheet.getRange("J3:K51").getFont().setBold(true); sheet.getRange("J3:K51").getInterior().setColor(com.grapecity.documents.excel.Color.FromArgb(164, 194, 231)); sheet.getRange("A:AD").setColumnWidth(12.5); // Save to an excel file workbook.save("PivotbyFunction.xlsx");