//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); IWorksheet sheet = workbook.ActiveSheet; 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.Range["A1:G" + data.GetLength(0)].Value = data; sheet.Range["A1:G1"].Interior.Color = Color.FromArgb(200, 214, 239); sheet.Range["J1"].Value = "The usage with non-adjacent columns and multiple aggregations"; sheet.Range["J2"].Formula = "=FORMULATEXT(J3)"; sheet.Range["J3"].Formula2 = "=PIVOTBY(A1:B62,D1:D62,CHOOSECOLS(E1:G62,1,3),HSTACK(PERCENTOF,MAX),3,2)"; var pivotConditionalFormatting1 = sheet.Range["J3:Q51"].FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.None, "$J3=\"Year\"") as IFormatCondition; pivotConditionalFormatting1.Interior.Color = Color.FromArgb(217, 226, 243); var pivotConditionalFormatting2 = sheet.Range["J3:Q51"].FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.None, "$K3=\"\"") as IFormatCondition; pivotConditionalFormatting2.Interior.Color = Color.FromArgb(198, 217, 240); pivotConditionalFormatting2.Font.Bold = true; sheet.Range["J3:K51"].Font.Bold = true; sheet.Range["J3:K51"].Interior.Color = Color.FromArgb(164, 194, 231); sheet.Range["A:AD"].ColumnWidth = 12.5; // Save to an excel file workbook.Save("PivotbyFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim sheet As IWorksheet = workbook.ActiveSheet Dim data As 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.Range("A1:G" & data.GetLength(0)).Value = data sheet.Range("A1:G1").Interior.Color = Color.FromArgb(200, 214, 239) sheet.Range("J1").Value = "The usage with non-adjacent columns and multiple aggregations" sheet.Range("J2").Formula = "=FORMULATEXT(J3)" sheet.Range("J3").Formula2 = "=PIVOTBY(A1:B62,D1:D62,CHOOSECOLS(E1:G62,1,3),HSTACK(PERCENTOF,MAX),3,2)" Dim pivotConditionalFormatting1 = TryCast(sheet.Range("J3:Q51").FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.None, "$J3=""Year"""), IFormatCondition) pivotConditionalFormatting1.Interior.Color = Color.FromArgb(217, 226, 243) Dim pivotConditionalFormatting2 = TryCast(sheet.Range("J3:Q51").FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.None, "$K3="""""), IFormatCondition) pivotConditionalFormatting2.Interior.Color = Color.FromArgb(198, 217, 240) pivotConditionalFormatting2.Font.Bold = True sheet.Range("J3:K51").Font.Bold = True sheet.Range("J3:K51").Interior.Color = Color.FromArgb(164, 194, 231) sheet.Range("A:AD").ColumnWidth = 12.5 ' save to an excel file workbook.Save("PivotbyFunction.xlsx")