//create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); // ***************************Set RowHeight & Width**************************** worksheet.setStandardHeight(30); worksheet.getRange("3:4").setRowHeight(30.25); worksheet.getRange("1:1").setRowHeight(103.50); worksheet.getRange("2:2").setRowHeight(38.25); worksheet.getRange("A:A").setColumnWidth(2.625); worksheet.getRange("B:B").setColumnWidth(22.25); worksheet.getRange("C:E").setColumnWidth(17.25); worksheet.getRange("F:F").setColumnWidth(31.875); worksheet.getRange("G:G").setColumnWidth(22.625); worksheet.getRange("H:H").setColumnWidth(30); worksheet.getRange("I:I").setColumnWidth(20.25); worksheet.getRange("J:J").setColumnWidth(17.625); worksheet.getRange("K:K").setColumnWidth(12.625); worksheet.getRange("L:L").setColumnWidth(37.25); worksheet.getRange("M:M").setColumnWidth(2.625); // *******************************Set Table Value & // Formulas************************************* ITable table = worksheet.getTables().add(worksheet.getRange("B2:L4"), true); worksheet.getRange("B2:L4").setValue(new Object[][]{ {"NAME", "WORK", "CELL", "HOME", "EMAIL", "BIRTHDAY", "ADDRESS", "CITY", "STATE", "ZIP", "NOTE"}, {"Kim Abercrombie", 1235550123, 1235550123, 1235550123, "someone@example.com", null, "123 N. Maple", "Cherryville", "WA", 98031, ""}, {"John Smith", 3215550123L, "", "", "someone@example.com", null, "456 E. Aspen", "", "", "", ""},}); worksheet.getRange("B1").setValue("ADDRESS BOOK"); worksheet.getRange("G3").setFormula("=TODAY()"); worksheet.getRange("G4").setFormula("=TODAY()+5"); // ****************************Set Table Style******************************** ITableStyle tableStyle = workbook.getTableStyles().add("Personal Address Book"); workbook.setDefaultTableStyle("Personal Address Book"); // Set WholeTable element style. tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().setColor(Color.FromArgb(179, 35, 23)); tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.Thin); tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.Thin); tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Thin); tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().get(BordersIndex.InsideVertical).setLineStyle(BorderLineStyle.Thin); tableStyle.getTableStyleElements().get(TableStyleElementType.WholeTable).getBorders().get(BordersIndex.InsideHorizontal).setLineStyle(BorderLineStyle.Thin); // Set FirstColumn element style. tableStyle.getTableStyleElements().get(TableStyleElementType.FirstColumn).getFont().setBold(true); // Set SecondColumns element style. tableStyle.getTableStyleElements().get(TableStyleElementType.HeaderRow).getBorders().setColor(Color.FromArgb(179, 35, 23)); tableStyle.getTableStyleElements().get(TableStyleElementType.HeaderRow).getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.Thick); tableStyle.getTableStyleElements().get(TableStyleElementType.HeaderRow).getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Thick); // ***********************************Set Named // Styles***************************** IStyle normalStyle = workbook.getStyles().get("Normal"); normalStyle.getFont().setName("Arial"); normalStyle.getFont().setColor(Color.FromArgb(179, 35, 23)); normalStyle.setHorizontalAlignment(HorizontalAlignment.Left); normalStyle.setIndentLevel(1); normalStyle.setVerticalAlignment(VerticalAlignment.Center); normalStyle.setWrapText(true); IStyle titleStyle = workbook.getStyles().get("Title"); titleStyle.setIncludeAlignment(true); titleStyle.setHorizontalAlignment(HorizontalAlignment.Left); titleStyle.setVerticalAlignment(VerticalAlignment.Center); titleStyle.getFont().setName("Arial"); titleStyle.getFont().setBold(true); titleStyle.getFont().setSize(72); titleStyle.getFont().setColor(Color.FromArgb(179, 35, 23)); IStyle heading1Style = workbook.getStyles().get("Heading 1"); heading1Style.setIncludeBorder(false); heading1Style.getFont().setName("Arial"); heading1Style.getFont().setSize(18); heading1Style.getFont().setColor(Color.FromArgb(179, 35, 23)); IStyle dataStyle = workbook.getStyles().add("Data"); dataStyle.setIncludeNumber(true); dataStyle.setNumberFormat("m/d/yyyy"); IStyle phoneStyle = workbook.getStyles().add("Phone"); phoneStyle.setIncludeNumber(true); phoneStyle.setNumberFormat("[<=9999999]###-####;(###) ###-####"); // ****************************************Use // NamedStyle************************** worksheet.getSheetView().setDisplayGridlines(false); worksheet.getRange("B2:L2").getInterior().setColor(Color.FromArgb(217, 217, 217)); worksheet.getRange("B3:B4").getFont().setBold(true); worksheet.getRange("2:2").setHorizontalAlignment(HorizontalAlignment.Left); table.setTableStyle(tableStyle); worksheet.getRange("B1").setStyle(titleStyle); worksheet.getRange("B2:L2").setStyle(heading1Style); worksheet.getRange("C3:E4").setStyle(phoneStyle); worksheet.getRange("G3:G4").setStyle(dataStyle); //save to an excel file workbook.save("PersonalAddressBook.xlsx");
//create a new workbook var workbook = Workbook() val worksheet = workbook.worksheets.get(0) // ***************************Set RowHeight & Width**************************** worksheet.standardHeight = 30.0 worksheet.getRange("3:4").rowHeight = 30.25 worksheet.getRange("1:1").rowHeight = 103.50 worksheet.getRange("2:2").rowHeight = 38.25 worksheet.getRange("A:A").columnWidth = 2.625 worksheet.getRange("B:B").columnWidth = 22.25 worksheet.getRange("C:E").columnWidth = 17.25 worksheet.getRange("F:F").columnWidth = 31.875 worksheet.getRange("G:G").columnWidth = 22.625 worksheet.getRange("H:H").columnWidth = 30.0 worksheet.getRange("I:I").columnWidth = 20.25 worksheet.getRange("J:J").columnWidth = 17.625 worksheet.getRange("K:K").columnWidth = 12.625 worksheet.getRange("L:L").columnWidth = 37.25 worksheet.getRange("M:M").columnWidth = 2.625 // *******************************Set Table Value & // Formulas************************************* val table = worksheet.tables.add(worksheet.getRange("B2:L4"), true) worksheet.getRange("B2:L4").value = arrayOf(arrayOf("NAME", "WORK", "CELL", "HOME", "EMAIL", "BIRTHDAY", "ADDRESS", "CITY", "STATE", "ZIP", "NOTE"), arrayOf("Kim Abercrombie", 1235550123, 1235550123, 1235550123, "someone@example.com", null, "123 N. Maple", "Cherryville", "WA", 98031, ""), arrayOf("John Smith", 3215550123L, "", "", "someone@example.com", null, "456 E. Aspen", "", "", "", "")) worksheet.getRange("B1").value = "ADDRESS BOOK" worksheet.getRange("G3").formula = "=TODAY()" worksheet.getRange("G4").formula = "=TODAY()+5" // ****************************Set Table Style******************************** val tableStyle = workbook.tableStyles.add("Personal Address Book") workbook.defaultTableStyle = "Personal Address Book" // Set WholeTable element style. tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.color = Color.FromArgb(179, 35, 23) tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.get(BordersIndex.EdgeLeft).lineStyle = BorderLineStyle.Thin tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.get(BordersIndex.EdgeRight).lineStyle = BorderLineStyle.Thin tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Thin tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.get(BordersIndex.InsideVertical).lineStyle = BorderLineStyle.Thin tableStyle.tableStyleElements.get(TableStyleElementType.WholeTable).borders.get(BordersIndex.InsideHorizontal).lineStyle = BorderLineStyle.Thin // Set FirstColumn element style. tableStyle.tableStyleElements.get(TableStyleElementType.FirstColumn).font.bold = true // Set SecondColumns element style. tableStyle.tableStyleElements.get(TableStyleElementType.HeaderRow).borders.color = Color.FromArgb(179, 35, 23) tableStyle.tableStyleElements.get(TableStyleElementType.HeaderRow).borders.get(BordersIndex.EdgeTop).lineStyle = BorderLineStyle.Thick tableStyle.tableStyleElements.get(TableStyleElementType.HeaderRow).borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Thick // ***********************************Set Named // Styles***************************** val normalStyle = workbook.styles.get("Normal") normalStyle.font.name = "Arial" normalStyle.font.color = Color.FromArgb(179, 35, 23) normalStyle.horizontalAlignment = HorizontalAlignment.Left normalStyle.indentLevel = 1 normalStyle.verticalAlignment = VerticalAlignment.Center normalStyle.wrapText = true val titleStyle = workbook.styles.get("Title") titleStyle.includeAlignment = true titleStyle.horizontalAlignment = HorizontalAlignment.Left titleStyle.verticalAlignment = VerticalAlignment.Center titleStyle.font.name = "Arial" titleStyle.font.bold = true titleStyle.font.size = 72.0 titleStyle.font.color = Color.FromArgb(179, 35, 23) val heading1Style = workbook.styles.get("Heading 1") heading1Style.includeBorder = false heading1Style.font.name = "Arial" heading1Style.font.size = 18.0 heading1Style.font.color = Color.FromArgb(179, 35, 23) val dataStyle = workbook.styles.add("Data") dataStyle.includeNumber = true dataStyle.numberFormat = "m/d/yyyy" val phoneStyle = workbook.styles.add("Phone") phoneStyle.includeNumber = true phoneStyle.numberFormat = "[<=9999999]###-####;(###) ###-####" // ****************************************Use // NamedStyle************************** worksheet.sheetView.displayGridlines = false worksheet.getRange("B2:L2").interior.color = Color.FromArgb(217, 217, 217) worksheet.getRange("B3:B4").font.bold = true worksheet.getRange("2:2").horizontalAlignment = HorizontalAlignment.Left table.tableStyle = tableStyle worksheet.getRange("B1").style = titleStyle worksheet.getRange("B2:L2").style = heading1Style worksheet.getRange("C3:E4").style = phoneStyle worksheet.getRange("G3:G4").style = dataStyle //save to an excel file workbook.save("PersonalAddressBook.xlsx")