// Create a new workbook Workbook workbook = new Workbook(); IWorksheet ws1 = workbook.getWorksheets().get("Sheet1"); ws1.setName("Lookup result address"); //Set value. ws1.getRange("A1:B7").setValue(new Object[][] { { "Item", "Sales"}, { "Apples", 100}, { "Bananas", 190}, { "Lemons", 150}, { "Oranges", 130}, { "Grapes", 220}, { "Limes", 200} }); ws1.getRange("D1").setValue("Item"); ws1.getRange("D2").setValue("Sales"); ws1.getRange("D3").setValue("Address"); ws1.getRange("E1").setValue("Oranges"); ws1.getRange("F2").setValue("=INDEX(B2:B7,MATCH(E1,A2:A7,0))"); ws1.getRange("F3").setValue("=CELL(\"address\",INDEX(B2:B7,MATCH(E1,A2:A7,0)))"); //Add table. ITable ws1Table = ws1.getTables().add(ws1.getRange("A1:B7"),true); //Set styles. ws1.getRange("B5:EB7").setNumberFormat("$#,##0"); ws1.getRange("E2").setNumberFormat("$#,##0"); ws1.getRange("D1:D3").getFont().setBold(true); ws1.getRange("E2:F3").getFont().setThemeColor(ThemeColor.Accent1); ws1.getRange("F2:J2").merge(); ws1.getRange("F3:K3").merge(); ws1.getRange("E1:E3").setHorizontalAlignment(HorizontalAlignment.Right); ws1.getRange("F2:F3").setHorizontalAlignment(HorizontalAlignment.Left); //Add formula //Returns the sales corresponding to "Oranges". ws1.getRange("E2").setFormula2("=INDEX(B2:B7,MATCH(E1,A2:A7,0))"); //Returns the above result address. ws1.getRange("E3").setFormula2("=CELL(\"address\",INDEX(B2:B7,MATCH(E1,A2:A7,0)))"); IWorksheet ws2 = workbook.getWorksheets().add(); ws2.setName("Number formats"); //Set value. ws2.getRange("A1:E30").setValue(new Object[][] { { "Actual value", "Display value", "Format", "Result"}, { "Numbers", null, null, null}, { 5000.555, 5000.555, "General", null}, { null, 5000.555, "0", null}, { null, 5000.555, "#,##0", null}, { null, 5000.555, "0.00", null}, { null, 5000.555, "#,##0.00", null}, { "Currency", null, null, null}, { -5000.555, -5000.555, "$#,##0_);($#,##0)", null}, { null, -5000.555, "$#,##0_);[Red]($#,##0)", null}, { null, -5000.555, "$#,##0.00_);($#,##0.00)", null}, { null, -5000.555, "$#,##0.00_);[Red]($#,##0.00)", null}, { "Percentage", null, null, null}, { 0.0555, 0.0555, "0%", null}, { null, 0.0555, "0.00%", null}, { "Scientific format", null, null, null}, { 5000.55500055, 5000.55500055, "0.00E+00", null}, { "Fractions", null, null, null}, { 50.555, 50.555, "# ?/?", null}, { "Dates", null, null, null}, { new GregorianCalendar(2019, 2, 15), new GregorianCalendar(2019, 2, 15), "m/d/yy", null}, { null, new GregorianCalendar(2019, 2, 15), "d-mmm-yy", null}, { null, new GregorianCalendar(2019, 2, 15), "d-mmm", null}, { null, new GregorianCalendar(2019, 2, 15), "mmm-yy", null}, { null, new GregorianCalendar(2019, 2, 15), "mm/dd", null}, { "Times", null, null, null}, { new GregorianCalendar(1899, 11, 30, 16, 30, 15), new GregorianCalendar(1899, 11, 30, 16, 30, 15), "h:mm AM/PM", null}, { null, new GregorianCalendar(1899, 11, 30, 16, 30, 15), "h:mm:ss AM/PM", null}, { null, new GregorianCalendar(1899, 11, 30, 16, 30, 15), "h:mm", null}, { null, new GregorianCalendar(1899, 11, 30, 16, 30, 15), "h:mm:ss", null} }); //Set styles. ws2.getRange("A1:D1").getInterior().setColor(Color.FromArgb(217, 225, 242)); ws2.getRange("A2").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A8").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A13").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A16").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A18").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A20").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getRange("A26").getInterior().setColor(Color.FromArgb(242, 242, 242)); ws2.getColumns().get(0).setColumnWidth(17); ws2.getColumns().get(1).setColumnWidth(17); ws2.getColumns().get(2).setColumnWidth(25); ws2.getColumns().get(3).setColumnWidth(9); ws2.getRange("A1:D1").getFont().setBold(true); ws2.getRange("E1:H1").merge(); ws2.getRange("D3:D30").getFont().setBold(true); //Numbers ws2.getRange("A2").getFont().setBold(true); ws2.getRange("A2:D2").merge(); ws2.getRange("A3:A7").merge(); ws2.getRange("A2").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A3").setVerticalAlignment(VerticalAlignment.Center); ws2.getRange("A3:D7").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B3").setNumberFormat("General"); ws2.getRange("B4").setNumberFormat("0"); ws2.getRange("B5").setNumberFormat("#,##0"); ws2.getRange("B6").setNumberFormat("0.00"); ws2.getRange("B7").setNumberFormat("#,##0.00"); ws2.getRange("C3:C7").setNumberFormat("@"); //Currency ws2.getRange("A8").getFont().setBold(true); ws2.getRange("A8:D8").merge(); ws2.getRange("A9:A12").merge(); ws2.getRange("A8").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A9").setVerticalAlignment(VerticalAlignment.Center); ws2.getRange("A9:D12").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B9").setNumberFormat("$#,##0_);($#,##0)"); ws2.getRange("B10").setNumberFormat("$#,##0_);[Red]($#,##0)"); ws2.getRange("B11").setNumberFormat("$#,##0.00_);($#,##0.00)"); ws2.getRange("B12").setNumberFormat("$#,##0.00_);[Red]($#,##0.00)"); //Percentage ws2.getRange("A13").getFont().setBold(true); ws2.getRange("A13:D13").merge(); ws2.getRange("A14:A15").merge(); ws2.getRange("A13").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A14").setVerticalAlignment(VerticalAlignment.Center); ws2.getRange("A14:D15").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B14").setNumberFormat("0%"); ws2.getRange("B15").setNumberFormat("0.00%"); //Scientific format ws2.getRange("A16").getFont().setBold(true); ws2.getRange("A16:D16").merge(); ws2.getRange("A16").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A17:D17").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B17").setNumberFormat("0.00E+00"); //Fractions ws2.getRange("A18").getFont().setBold(true); ws2.getRange("A18:D18").merge(); ws2.getRange("A18").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A19:D19").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B19").setNumberFormat("# ?/?"); //Dates ws2.getRange("A20").getFont().setBold(true); ws2.getRange("A20:D20").merge(); ws2.getRange("A21:A25").merge(); ws2.getRange("A20").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A21").setVerticalAlignment(VerticalAlignment.Center); ws2.getRange("A21:D25").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("A21").setNumberFormat("[$-en-US]mmmm d, yyyy;@"); ws2.getRange("B21").setNumberFormat("m/d/yy"); ws2.getRange("B22").setNumberFormat("d-mmm-yy"); ws2.getRange("B23").setNumberFormat("d-mmm"); ws2.getRange("B24").setNumberFormat("mmm-yy"); ws2.getRange("B25").setNumberFormat("mm/dd"); //Times ws2.getRange("A26").getFont().setBold(true); ws2.getRange("A26:D26").merge(); ws2.getRange("A27:A30").merge(); ws2.getRange("A26").setHorizontalAlignment(HorizontalAlignment.Center); ws2.getRange("A27").setVerticalAlignment(VerticalAlignment.Center); ws2.getRange("A27:D30").setHorizontalAlignment(HorizontalAlignment.Left); ws2.getRange("B27").setNumberFormat("h:mm AM/PM"); ws2.getRange("B28").setNumberFormat("h:mm:ss AM/PM"); ws2.getRange("B29").setNumberFormat("h:mm"); ws2.getRange("B30").setNumberFormat("h:mm:ss"); //Set formula. ws2.getRange("D3:D30").setFormula2("=CELL(\"format\",B3)"); // Save to an excel file workbook.save("CellFunction.xlsx");