// 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");
// Create a new workbook var workbook = Workbook() val sheet = workbook.getActiveSheet() val data = arrayOf?>( arrayOf("Year", "Category", "Product", "Status", "Sales", "Rating", "Profit"), arrayOf(2023, "Electronics", "Smart TV", "Active", 15000, 4.5, 250), arrayOf(2023, "Fashion", "Designer Jeans", "Discontinued", 8000, 4.7, 150), arrayOf(2024, "Food", "Organic Granola", "Active", 5000, 4.8, 100), arrayOf(2023, "Books", "Bestseller Novel", "Active", 12000, 4.6, 180), arrayOf(2024, "Electronics", "Laptop", "Active", 20000, 4.9, 300), arrayOf(2023, "Beauty", "Skincare Set", "Active", 7000, 4.4, 120), arrayOf(2023, "Home & Garden", "Garden Tools", "Active", 6500, 4.3, 130), arrayOf(2024, "Health", "Fitness Tracker", "Active", 9500, 4.6, 190), arrayOf(2023, "Toys", "Action Figure", "Active", 4800, 4.7, 95), arrayOf(2024, "Automotive", "Car Accessories", "Active", 3200, 4.5, 65), arrayOf(2023, "Sports", "Basketball", "Active", 7600, 4.8, 150), arrayOf(2024, "Office Supplies", "Notebooks", "Active", 11000, 4.4, 220), arrayOf(2023, "Pet Supplies", "Dog Food", "Active", 5600, 4.6, 110), arrayOf(2024, "Music", "Headphones", "Active", 13000, 4.9, 260), arrayOf(2023, "Outdoor", "Camping Tent", "Active", 4400, 4.5, 88), arrayOf(2024, "Jewelry", "Silver Necklace", "Active", 2800, 4.7, 56), arrayOf(2023, "Tools", "Power Drill", "Active", 3900, 4.4, 78), arrayOf(2024, "Baby", "Stroller", "Active", 1700, 4.6, 34), arrayOf(2023, "Kitchen", "Blender", "Active", 2500, 4.8, 50), arrayOf(2024, "Clothing", "Casual Shirt", "Active", 6200, 4.5, 124), arrayOf(2023, "Art", "Oil Paintings", "Active", 1900, 4.7, 38), arrayOf(2024, "Hobbies", "Model Trains", "Active", 3100, 4.4, 62), arrayOf(2023, "Tech Gadgets", "Smart Watch", "Active", 7300, 4.6, 146), arrayOf(2024, "Travel", "Luggage", "Active", 4600, 4.8, 92), arrayOf(2023, "Home Decor", "Wall Clock", "Active", 2200, 4.5, 44), arrayOf(2024, "Fitness", "Yoga Mat", "Active", 5700, 4.7, 114), arrayOf(2023, "Gardening", "Plant Pots", "Active", 3400, 4.4, 68), arrayOf(2024, "Audio", "Speakers", "Active", 8900, 4.6, 178), arrayOf(2023, "Lighting", "LED Bulbs", "Active", 4300, 4.8, 86), arrayOf(2024, "Gaming", "Video Games", "Active", 16000, 4.5, 320), arrayOf(2023, "Furniture", "Sofa", "Active", 5500, 4.7, 110), arrayOf(2024, "Health", "Vitamins", "Active", 7100, 4.4, 142), arrayOf(2023, "Office", "Desk Chair", "Active", 6300, 4.6, 126), arrayOf(2024, "Books", "Educational Textbooks", "Active", 9200, 4.8, 184), arrayOf(2023, "Tools", "Screwdriver Set", "Active", 3700, 4.5, 74), arrayOf(2024, "Home & Garden", "Patio Furniture", "Active", 4900, 4.7, 98), arrayOf(2023, "Automotive", "Car Seat Covers", "Active", 2600, 4.4, 52), arrayOf(2024, "Electronics", "Smart Home System", "Active", 10500, 4.6, 210), arrayOf(2023, "Health", "Blood Pressure Monitor", "Active", 3800, 4.8, 76), arrayOf(2024, "Beauty", "Makeup Kit", "Active", 5900, 4.5, 118), arrayOf(2023, "Music", "Turntable", "Active", 2100, 4.7, 42), arrayOf(2024, "Toys", "Board Games", "Active", 3300, 4.4, 66), arrayOf(2023, "Clothing", "Winter Coat", "Active", 4700, 4.6, 94), arrayOf(2024, "Food", "Gourmet Chocolates", "Active", 7400, 4.8, 148), arrayOf(2023, "Home Decor", "Curtains", "Active", 2900, 4.5, 58), arrayOf(2024, "Pet Supplies", "Cat Litter", "Active", 6100, 4.7, 122), arrayOf(2023, "Outdoor", "Hiking Boots", "Active", 3500, 4.4, 70), arrayOf(2024, "Kitchen", "Slow Cooker", "Active", 8200, 4.6, 164), arrayOf(2023, "Toys", "Dolls", "Active", 4400, 4.8, 88), arrayOf(2024, "Books", "Poetry Collection", "Active", 5300, 4.5, 106), arrayOf(2023, "Health", "Massage Chair", "Active", 6600, 4.7, 132), arrayOf(2024, "Home & Garden", "Fire Pit", "Active", 7700, 4.4, 154), arrayOf(2023, "Automotive", "Car Wash Kit", "Active", 4000, 4.6, 80), arrayOf(2024, "Office Supplies", "Highlighters", "Active", 5200, 4.8, 104), arrayOf(2023, "Tools", "Hammer", "Active", 3200, 4.5, 64), arrayOf(2024, "Beauty", "Hair Dryer", "Active", 6800, 4.7, 136), arrayOf(2023, "Kitchen", "Coffee Maker", "Active", 4600, 4.4, 92), arrayOf(2024, "Toys", "Remote Control Cars", "Active", 7900, 4.6, 158), arrayOf(2023, "Home Decor", "Picture Frames", "Active", 3300, 4.8, 66), arrayOf(2024, "Electronics", "Router", "Active", 8500, 4.5, 170), arrayOf(2023, "Clothing", "Suit", "Active", 5700, 4.7, 114) ) sheet.getRange("A1:G" + data.size).setValue(data) sheet.getRange("A1:G1").getInterior().setColor(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)") val pivotConditionalFormatting1 = sheet.getRange("J3:Q51").getFormatConditions().add( FormatConditionType.Expression, FormatConditionOperator.None, "\$J3=\"Year\"", null ) as IFormatCondition pivotConditionalFormatting1.getInterior().setColor(Color.FromArgb(217, 226, 243)) val pivotConditionalFormatting2 = sheet.getRange("J3:Q51").getFormatConditions().add( FormatConditionType.Expression, FormatConditionOperator.None, "\$K3=\"\"", null ) as IFormatCondition pivotConditionalFormatting2.getInterior().setColor(Color.FromArgb(198, 217, 240)) pivotConditionalFormatting2.getFont().setBold(true) sheet.getRange("J3:K51").getFont().setBold(true) sheet.getRange("J3:K51").getInterior().setColor(Color.FromArgb(164, 194, 231)) sheet.getRange("A:AD").setColumnWidth(12.5) // Save to an excel file workbook.save("PivotbyFunction.xlsx")