Excel API Library for Java - Sample Browser | Document Solutions | Use table formulas
[
フレーム]
src="bundle.js">
Table formula refers to a formula that is used as a structured reference in the worksheet instead of using it as an explicit cell reference. Refer to the following example code to set table formula in your spreadsheets.
// 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")