// Create a new workbook Workbook workbook = new Workbook(); // Use English (US) culture to apply styles workbook.setCulture(Locale.US); IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1"); sheet1.setName("ImportDataTable"); sheet1.getRange("A1").setValue("Tour budget table"); // Create a DataTable and define its columns. // ResultSetDataTable is used for testing purpose only. // You need to replace it with ResultSet of DB queries in real-world applications. ResultSetDataTable dataTable = new ResultSetDataTable(); dataTable.getColumns().add("EmployeeID", Integer.class); dataTable.getColumns().add("Adults", Integer.class); dataTable.getColumns().add("Children", Integer.class); dataTable.getColumns().add("Rooms", Integer.class); dataTable.getColumns().add("Spa", Double.class); dataTable.getColumns().add("Hotpot", Integer.class); dataTable.getColumns().add("Budget", Double.class); // Generate test data. To get the same data, you can set the seed value of the random number generator. Random rnd = new Random(1234); final int rowCount = 20; for (int i = 0; i < rowCount; i++) { int employeeId = rnd.nextInt(89999) + 10000; int adults = rnd.nextInt(3) + 1; int children = rnd.nextInt(3); int rooms = (int) Math.floor((adults + children) / 2.0); double spa = Math.ceil((adults + children) * rnd.nextDouble()); int hotpot = adults + children; double budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750; // Add the row to the DataTable dataTable.getRows().add(employeeId, adults, children, rooms, spa, hotpot, budget); } // Import the DataTable into the worksheet DataImportResult result = sheet1.getRange("A2").importData(dataTable); sheet1.getRange(1, 0, 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("Heading 3")); sheet1.getRange(2, 0, result.getRowsImported() - 1, result.getColumnsImported()).setStyle(workbook.getStyles().get("20% - Accent1")); sheet1.getRange(1, 0, result.getRowsImported(), result.getColumnsImported()).getEntireColumn().autoFit(); sheet1.getRange("A1").setStyle(workbook.getStyles().get("Heading 1")); sheet1.getRange("1:2").autoFit(); sheet1.getRange(2, 6, result.getRowsImported(), 1).setNumberFormat("$#,##0.00"); sheet1.getRange(2, 6, result.getRowsImported(), 1).getEntireColumn().setColumnWidth(10); // Save to an excel file workbook.save("ImportDataTable.xlsx");
// Create a new workbook var workbook = Workbook() // Use English (US) culture to apply styles workbook.culture = Locale.US val sheet1 = workbook.worksheets["Sheet1"] sheet1.name = "ImportDataTable" sheet1.getRange("A1").value = "Tour budget table" // Create a DataTable and define its columns. // ResultSetDataTable is used for testing purpose only. // You need to replace it with ResultSet of DB queries in real-world applications. val dataTable = ResultSetDataTable() dataTable.columns.add("EmployeeID", Int::class.javaObjectType) dataTable.columns.add("Adults", Int::class.javaObjectType) dataTable.columns.add("Children", Int::class.javaObjectType) dataTable.columns.add("Rooms", Int::class.javaObjectType) dataTable.columns.add("Spa", Double::class.javaObjectType) dataTable.columns.add("Hotpot", Int::class.javaObjectType) dataTable.columns.add("Budget", Double::class.javaObjectType) // Generate test data. To get the same data, you can set the seed value of the random number generator. val rnd = Random(1234) val rowCount = 20 for (i in 0 until rowCount) { val employeeId = rnd.nextInt(89999) + 10000 val adults = rnd.nextInt(3) + 1 val children = rnd.nextInt(3) val rooms = floor((adults + children) / 2.0).toInt() val spa = ceil((adults + children) * rnd.nextDouble()) val hotpot = adults + children val budget = adults * 200 + children * 100 + rooms * 400 + spa * 188 + hotpot * 233 + 750 // Add the row to the DataTable dataTable.rows.add(employeeId, adults, children, rooms, spa, hotpot, budget) } // Import the DataTable into the worksheet val result = sheet1.getRange("A2").importData(dataTable) sheet1.getRange(1, 0, 1, result.columnsImported).style = workbook.styles["Heading 3"] sheet1.getRange(2, 0, result.rowsImported - 1, result.columnsImported).style = workbook.styles["20% - Accent1"] sheet1.getRange(1, 0, result.rowsImported, result.columnsImported).entireColumn.autoFit() sheet1.getRange("A1").style = workbook.styles["Heading 1"] sheet1.getRange("1:2").autoFit() sheet1.getRange(2, 6, result.rowsImported, 1).numberFormat = "$#,##0.00" sheet1.getRange(2, 6, result.rowsImported, 1).entireColumn.columnWidth = 10.0 // Save to an excel file workbook.save("ImportDataTable.xlsx")