//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // Basic exact match IWorksheet ws1 = workbook.Worksheets["Sheet1"]; ws1.Name = "Exact match"; ws1.Range["$B$2:$B$3"].Value = new object[,] { { "#1 - basic exact match"}, { "=XMATCH(H5,B6:B10)"} }; ws1.Range["$B$5:$E$10"].Value = new object[,] { { "Movie", "Year", "Rank", "Sales"}, { "Fargo", 1996d, 5d, 61d}, { "L.A. Confidential", 1997d, 4d, 126d}, { "The Sixth Sense", 1999d, 1d, 673d}, { "Toy Story", 1995d, 2d, 362d}, { "Unforgiven", 1992d, 3d, 159d} }; ws1.Range["$G$5:$G$6"].Value = new object[,] { { "Movie"}, { "Position"} }; // Apply table style. ITable ws1Table = ws1.Tables.Add(ws1.Range["B5:E10"], true); ws1Table.ConvertToRange(); ws1.Range["G5"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws1.Range["G6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws1.Range["G5:G6"].Font.Color = System.Drawing.Color.White; ws1.Range["B:B"].ColumnWidth = 14; ws1.Range["$H$5"].Value = "Toy Story"; ws1.Range["$H$6"].Formula = "=XMATCH(H5,B6:B10)"; // Basic approximate match IWorksheet ws2 = workbook.Worksheets.Add(); ws2.Name = "Approximate match"; ws2.Range["$B$2:$B$3"].Value = (new object[,] { { "#2 - basic approximate match"}, { "=XMATCH(H5,E6:E10,1)"} }); ws2.Range["$B$5:$E$10"].Value = (new object[,] { { "Movie", "Year", "Rank", "Sales"}, { "Fargo", 1996d, 5d, 61d}, { "L.A. Confidential", 1997d, 4d, 126d}, { "The Sixth Sense", 1999d, 1d, 673d}, { "Toy Story", 1995d, 2d, 362d}, { "Unforgiven", 1992d, 3d, 159d} }); ws2.Range["$G$5:$G$6"].Value = (new object[,] { { "Sales"}, { "Position"} }); // Apply table style. ITable ws2Table = ws2.Tables.Add(ws2.Range["B5:E10"], true); ws2Table.ConvertToRange(); ws2.Range["G5"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws2.Range["G6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws2.Range["G5:G6"].Font.Color = System.Drawing.Color.White; ws2.Range["B:B"].ColumnWidth = 14; ws2.Range["$H$5"].Value = 400d; ws2.Range["$H$6"].Formula = "=XMATCH(H5,E6:E10,1)"; // Multiple values IWorksheet ws3 = workbook.Worksheets.Add(); ws3.Name = "Multiple values"; ws3.Range["$B$2:$B$3"].Value = (new object[,] { { "#3 - multiple values"}, { "=XMATCH({5,4,1},D6:D10)"} }); ws3.Range["$B$5:$E$10"].Value = (new object[,] { { "Movie", "Year", "Rank", "Sales"}, { "Fargo", 1996d, 5d, 61d}, { "L.A. Confidential", 1997d, 4d, 126d}, { "The Sixth Sense", 1999d, 1d, 673d}, { "Toy Story", 1995d, 2d, 362d}, { "Unforgiven", 1992d, 3d, 159d} }); ws3.Range["$G$5:$G$6"].Value = (new object[,] { { "Rank"}, { "Position"} }); // Apply table style. ITable ws3Table = ws3.Tables.Add(ws3.Range["B5:E10"], true); ws3Table.ConvertToRange(); ws3.Range["G5"].Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196); ws3.Range["G6"].Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230); ws3.Range["G5:G6"].Font.Color = System.Drawing.Color.White; ws3.Range["B:B"].ColumnWidth = 14; ws3.Range["$H$5"].Value = "{5,4,1}"; ws3.Range["$H$6"].Formula2 = "=XMATCH({5,4,1},D6:D10)"; // Save to an excel file workbook.Save("XMatchFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook ' Basic exact match Dim ws1 As IWorksheet = workbook.Worksheets("Sheet1") ws1.Name = "Exact match" ws1.Range("$B$2:$B$3").Value = New Object(,) { {"#1 - basic exact match"}, {"=XMATCH(H5,B6:B10)"} } ws1.Range("$B$5:$E$10").Value = New Object(,) { {"Movie", "Year", "Rank", "Sales"}, {"Fargo", 1996.0R, 5.0R, 61.0R}, {"L.A. Confidential", 1997.0R, 4.0R, 126.0R}, {"The Sixth Sense", 1999.0R, 1.0R, 673.0R}, {"Toy Story", 1995.0R, 2.0R, 362.0R}, {"Unforgiven", 1992.0R, 3.0R, 159.0R} } ws1.Range("$G$5:$G$6").Value = New Object(,) { {"Movie"}, {"Position"} } ' Apply table style. Dim ws1Table As ITable = ws1.Tables.Add(ws1.Range("B5:E10"), True) ws1Table.ConvertToRange() ws1.Range("G5").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws1.Range("G6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws1.Range("G5:G6").Font.Color = System.Drawing.Color.White ws1.Range("B:B").ColumnWidth = 14 ws1.Range!H5.Value = "Toy Story" ws1.Range!H6.Formula = "=XMATCH(H5,B6:B10)" ' Basic approximate match Dim ws2 As IWorksheet = workbook.Worksheets.Add() ws2.Name = "Approximate match" ws2.Range("$B$2:$B$3").Value = New Object(,) { {"#2 - basic approximate match"}, {"=XMATCH(H5,E6:E10,1)"} } ws2.Range("$B$5:$E$10").Value = New Object(,) { {"Movie", "Year", "Rank", "Sales"}, {"Fargo", 1996.0R, 5.0R, 61.0R}, {"L.A. Confidential", 1997.0R, 4.0R, 126.0R}, {"The Sixth Sense", 1999.0R, 1.0R, 673.0R}, {"Toy Story", 1995.0R, 2.0R, 362.0R}, {"Unforgiven", 1992.0R, 3.0R, 159.0R} } ws2.Range("$G$5:$G$6").Value = New Object(,) { {"Sales"}, {"Position"} } ' Apply table style. Dim ws2Table As ITable = ws2.Tables.Add(ws2.Range("B5:E10"), True) ws2Table.ConvertToRange() ws2.Range("G5").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws2.Range("G6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws2.Range("G5:G6").Font.Color = System.Drawing.Color.White ws2.Range("B:B").ColumnWidth = 14 ws2.Range!H5.Value = 400.0R ws2.Range!H6.Formula = "=XMATCH(H5,E6:E10,1)" ' Multiple values Dim ws3 As IWorksheet = workbook.Worksheets.Add() ws3.Name = "Multiple values" ws3.Range("$B$2:$B$3").Value = New Object(,) { {"#3 - multiple values"}, {"=XMATCH({5,4,1},D6:D10)"} } ws3.Range("$B$5:$E$10").Value = New Object(,) { {"Movie", "Year", "Rank", "Sales"}, {"Fargo", 1996.0R, 5.0R, 61.0R}, {"L.A. Confidential", 1997.0R, 4.0R, 126.0R}, {"The Sixth Sense", 1999.0R, 1.0R, 673.0R}, {"Toy Story", 1995.0R, 2.0R, 362.0R}, {"Unforgiven", 1992.0R, 3.0R, 159.0R} } ws3.Range("$G$5:$G$6").Value = New Object(,) { {"Rank"}, {"Position"} } ' Apply table style. Dim ws3Table As ITable = ws3.Tables.Add(ws3.Range("B5:E10"), True) ws3Table.ConvertToRange() ws3.Range("G5").Interior.Color = System.Drawing.Color.FromArgb(68, 114, 196) ws3.Range("G6").Interior.Color = System.Drawing.Color.FromArgb(155, 194, 230) ws3.Range("G5:G6").Font.Color = System.Drawing.Color.White ws3.Range("B:B").ColumnWidth = 14 ws3.Range!H5.Value = "{5,4,1}" ws3.Range!H6.Formula2 = "=XMATCH({5,4,1},D6:D10)" ' save to an excel file workbook.Save("XMatchFunction.xlsx")