// Create a new workbook Workbook workbook = new Workbook(); // Basic exact match IWorksheet ws1 = workbook.getWorksheets().get("Sheet1"); ws1.setName("Exact match"); ws1.getRange("$B$2:$B$3").setValue(new Object[][] { { "#1 - basic exact match" }, { "=XMATCH(H5,B6:B10)" } }); ws1.getRange("$B$5:$E$10") .setValue(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.getRange("$G$5:$G$6").setValue(new Object[][] { { "Movie" }, { "Position" } }); ws1.getRange("$H$5").setValue("Toy Story"); // Apply table style. ITable ws1Table = ws1.getTables().add(ws1.getRange("B5:E10"),true); ws1Table.convertToRange(); ws1.getRange("G5").getInterior().setColor(Color.FromArgb(68,114,196)); ws1.getRange("G6").getInterior().setColor(Color.FromArgb(155,194,230)); ws1.getRange("G5:G6").getFont().setColor(Color.GetWhite()); ws1.getRange("B:B").setColumnWidth(14); ws1.getRange("$H$6").setFormula("=XMATCH(H5,B6:B10)"); // Basic approximate match IWorksheet ws2 = workbook.getWorksheets().add(); ws2.setName("Approximate match"); ws2.getRange("$B$2:$B$3") .setValue(new Object[][] { { "#2 - basic approximate match" }, { "=XMATCH(H5,E6:E10,1)" } }); ws2.getRange("$B$5:$E$10") .setValue(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.getRange("$G$5:$G$6").setValue(new Object[][] { { "Sales" }, { "Position" } }); ws2.getRange("$H$5").setValue(400d); // Apply table style. ITable ws2Table = ws2.getTables().add(ws2.getRange("B5:E10"),true); ws2Table.convertToRange(); ws2.getRange("G5").getInterior().setColor(Color.FromArgb(68,114,196)); ws2.getRange("G6").getInterior().setColor(Color.FromArgb(155,194,230)); ws2.getRange("G5:G6").getFont().setColor(Color.GetWhite()); ws2.getRange("B:B").setColumnWidth(14); ws2.getRange("$H$6").setFormula("=XMATCH(H5,E6:E10,1)"); // Multiple values IWorksheet ws3 = workbook.getWorksheets().add(); ws3.setName("Multiple values"); ws3.getRange("$B$2:$B$3") .setValue(new Object[][] { { "#3 - multiple values" }, { "=XMATCH({5,4,1},D6:D10)" } }); ws3.getRange("$B$5:$E$10") .setValue(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.getRange("$G$5:$G$6").setValue(new Object[][] { { "Rank" }, { "Position" } }); ws3.getRange("$H$5").setValue("{5,4,1}"); // Apply table style. ITable ws3Table = ws3.getTables().add(ws3.getRange("B5:E10"),true); ws3Table.convertToRange(); ws3.getRange("G5").getInterior().setColor(Color.FromArgb(68,114,196)); ws3.getRange("G6").getInterior().setColor(Color.FromArgb(155,194,230)); ws3.getRange("G5:G6").getFont().setColor(Color.GetWhite()); ws3.getRange("B:B").setColumnWidth(14); ws3.getRange("$H$6").setFormula2("=XMATCH({5,4,1},D6:D10)"); // Save to an excel file workbook.save("XMatchFunction.xlsx");
// Create a new workbook var workbook = Workbook() // Basic exact match val ws1: IWorksheet = workbook.getWorksheets().get("Sheet1") ws1.setName("Exact match") ws1.getRange("\$B$2:\$B$3").setValue(arrayOf(arrayOf("#1 - basic exact match"), arrayOf("=XMATCH(H5,B6:B10)"))) ws1.getRange("\$B$5:\$E$10") .setValue(arrayOf(arrayOf("Movie", "Year", "Rank", "Sales"), arrayOf("Fargo", 1996.0, 5.0, 61.0), arrayOf("L.A. Confidential", 1997.0, 4.0, 126.0), arrayOf("The Sixth Sense", 1999.0, 1.0, 673.0), arrayOf("Toy Story", 1995.0, 2.0, 362.0), arrayOf("Unforgiven", 1992.0, 3.0, 159.0))) ws1.getRange("\$G$5:\$G$6").setValue(arrayOf(arrayOf("Movie"), arrayOf("Position"))) ws1.getRange("\$H$5").setValue("Toy Story") // Apply table style. val ws1Table: ITable = ws1.getTables().add(ws1.getRange("B5:E10"), true) ws1Table.convertToRange() ws1.getRange("G5").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws1.getRange("G6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws1.getRange("G5:G6").getFont().setColor(Color.GetWhite()) ws1.getRange("B:B").setColumnWidth(14.0) ws1.getRange("\$H$6").setFormula("=XMATCH(H5,B6:B10)") // Basic approximate match val ws2: IWorksheet = workbook.getWorksheets().add() ws2.setName("Approximate match") ws2.getRange("\$B$2:\$B$3") .setValue(arrayOf(arrayOf("#2 - basic approximate match"), arrayOf("=XMATCH(H5,E6:E10,1)"))) ws2.getRange("\$B$5:\$E$10") .setValue(arrayOf(arrayOf("Movie", "Year", "Rank", "Sales"), arrayOf("Fargo", 1996.0, 5.0, 61.0), arrayOf("L.A. Confidential", 1997.0, 4.0, 126.0), arrayOf("The Sixth Sense", 1999.0, 1.0, 673.0), arrayOf("Toy Story", 1995.0, 2.0, 362.0), arrayOf("Unforgiven", 1992.0, 3.0, 159.0))) ws2.getRange("\$G$5:\$G$6").setValue(arrayOf(arrayOf("Sales"), arrayOf("Position"))) ws2.getRange("\$H$5").setValue(400.0) // Apply table style. val ws2Table: ITable = ws2.getTables().add(ws2.getRange("B5:E10"), true) ws2Table.convertToRange() ws2.getRange("G5").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws2.getRange("G6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws2.getRange("G5:G6").getFont().setColor(Color.GetWhite()) ws2.getRange("B:B").setColumnWidth(14.0) ws2.getRange("\$H$6").setFormula("=XMATCH(H5,E6:E10,1)") // Multiple values val ws3: IWorksheet = workbook.getWorksheets().add() ws3.setName("Multiple values") ws3.getRange("\$B$2:\$B$3") .setValue(arrayOf(arrayOf("#3 - multiple values"), arrayOf("=XMATCH({5,4,1},D6:D10)"))) ws3.getRange("\$B$5:\$E$10") .setValue(arrayOf(arrayOf("Movie", "Year", "Rank", "Sales"), arrayOf("Fargo", 1996.0, 5.0, 61.0), arrayOf("L.A. Confidential", 1997.0, 4.0, 126.0), arrayOf("The Sixth Sense", 1999.0, 1.0, 673.0), arrayOf("Toy Story", 1995.0, 2.0, 362.0), arrayOf("Unforgiven", 1992.0, 3.0, 159.0))) ws3.getRange("\$G$5:\$G$6").setValue(arrayOf(arrayOf("Rank"), arrayOf("Position"))) ws3.getRange("\$H$5").setValue("{5,4,1}") // Apply table style. val ws3Table: ITable = ws3.getTables().add(ws3.getRange("B5:E10"), true) ws3Table.convertToRange() ws3.getRange("G5").getInterior().setColor(Color.FromArgb(68, 114, 196)) ws3.getRange("G6").getInterior().setColor(Color.FromArgb(155, 194, 230)) ws3.getRange("G5:G6").getFont().setColor(Color.GetWhite()) ws3.getRange("B:B").setColumnWidth(14.0) ws3.getRange("\$H$6").setFormula2("=XMATCH({5,4,1},D6:D10)") // Save to an excel file workbook.save("XMatchFunction.xlsx")