// Create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A:E").setColumnWidth(15); worksheet.getRange("A1:E3").setValue(new Object[][]{ {"SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt"}, {"Joe", "North", 260, 0.10, null}, {"Nia", "South", 660, 0.15, null}, }); worksheet.getTables().add(worksheet.getRange("A1:E3"), true); worksheet.getTables().get(0).setName("DeptSales"); worksheet.getTables().get(0).getColumns().get("ComPct").getDataBodyRange().setNumberFormat("0%"); //Use table formula in table range. worksheet.getTables().get(0).getColumns().get("ComAmt").getDataBodyRange().setFormula("=[@ComPct]*[@SalesAmount]"); //Use table formula out of table range. worksheet.getRange("F2").setFormula("=SUM(DeptSales[@SalesAmount])"); worksheet.getRange("G2").setFormula("=SUM(DeptSales[[#Data],[SalesAmount]])"); worksheet.getRange("H2").setFormula("=SUM(DeptSales[SalesAmount])"); worksheet.getRange("I2").setFormula("=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])"); //judge if Range F2:I2 have formula. for (int i = 5; i <= 8; i++) { if (worksheet.getRange(1, i).getHasFormula()) { worksheet.getRange(1, i).getInterior().setColor(Color.GetLightBlue()); } } // Save to an excel file workbook.save("UseTableFormula.xlsx");
// Create a new workbook var workbook = Workbook() val worksheet = workbook.worksheets.get(0) worksheet.getRange("A:E").columnWidth = 15.0 worksheet.getRange("A1:E3").value = arrayOf(arrayOf("SalesPerson", "Region", "SalesAmount", "ComPct", "ComAmt"), arrayOf("Joe", "North", 260, 0.10, null), arrayOf("Nia", "South", 660, 0.15, null)) worksheet.tables.add(worksheet.getRange("A1:E3"), true) worksheet.tables.get(0).name = "DeptSales" worksheet.tables.get(0).columns.get("ComPct").dataBodyRange.numberFormat = "0%" //Use table formula in table range. worksheet.tables.get(0).columns.get("ComAmt").dataBodyRange.formula = "=[@ComPct]*[@SalesAmount]" //Use table formula out of table range. worksheet.getRange("F2").formula = "=SUM(DeptSales[@SalesAmount])" worksheet.getRange("G2").formula = "=SUM(DeptSales[[#Data],[SalesAmount]])" worksheet.getRange("H2").formula = "=SUM(DeptSales[SalesAmount])" worksheet.getRange("I2").formula = "=SUM(DeptSales[@ComPct], DeptSales[@ComAmt])" //judge if Range F2:I2 have formula. for (i in 5..8) { if (worksheet.getRange(1, i).hasFormula) { worksheet.getRange(1, i).interior.color = Color.GetLightBlue() } } // Save to an excel file workbook.save("UseTableFormula.xlsx")