Building a Table with Values and Formulas

level: basic / verified: 2026-10-04 / tabs: Node.js・Python・Java・PHP・WebAssembly / Back to Index / 日本語

What you want to do

Create a new xlsx, write a table (headers, strings, numbers, formulas) and save the file. This applies the basic flow from the overview to real table building.

Resulting image

Sheet1 of sales.xlsx will contain this table:

ABCD
1ProductUnit PriceQtyAmount
2Apple1003300
3Orange506300
4Grape1502300
5Total900

Column D holds the formula B*C; D5 holds SUM(D2:D4).

Prerequisites

Download the module for your language from the download page and install it (entry points are listed in the overview Prerequisites table).

For detailed per-OS PHP installation steps (Linux / macOS / Windows, including when Composer is not installed), see the Quickstart.

The WebAssembly build uses MEMORY64 (Wasm 3.0), so the runtime must support it (Chrome/Edge 133+, Firefox 134+, Node.js 24+. Safari is not supported yet).

If you omit the directory in the path ("sales.xlsx"), the file is written to the process current working directory.

npm install ./nodeosbxl-2.0.1.tgz

Code

Output:

// values-formulas.js — create a book, write values and formulas, save
const { nodeosbxl, dto } = require("nodeosbxl");

const app = new nodeosbxl.App();
const wb = app.createWorkBook("sales.xlsx");   // new workbook
const ws = wb.openWorkSheet("Sheet1");         // open the default sheet

// --- header (row 1) ---
// app.convertFromRowColNumber(row, col) gives the A1 address for (1,1) -> "A1"
["Product", "Unit Price", "Qty", "Amount"].forEach((text, i) => {
    const cell = app.convertFromRowColNumber(1, i + 1);
    ws.getRange(`${cell}:${cell}`).setValue(text);
});

// --- detail rows (2-4) ---
const data = [["Apple", 100, 3], ["Orange", 50, 6], ["Grape", 150, 2]];
data.forEach(([name, price, qty], i) => {
    const row = i + 2;
    ws.getRange(`A${row}:A${row}`).setValue(name);
    ws.getRange(`B${row}:B${row}`).setNumberValue(price);
    ws.getRange(`C${row}:C${row}`).setNumberValue(qty);
});

// --- amount column (D2:D4) ---
// Each row needs its own relative reference, so use setRangeFormulas.
// Formula strings do NOT take a leading "=".
const formulas = data.map((_, i) => [`B${i + 2}*C${i + 2}`]);
ws.setRangeFormulas("D2", formulas);

// --- total (D5) ---
ws.getRange("C5:C5").setValue("Total");
ws.getRange("D5:D5").setFormula("SUM(D2:D4)");

// --- read back ---
// getValue(true) returns raw values = formula results
const values = ws.getRange("A1:D5").getValue(true);
console.log("Amount D2:D4 =", [values["D2"], values["D3"], values["D4"]].join(", "));
console.log("Total:", values["D5"]);

// --- save and close ---
wb.save();
wb.close();
Amount D2:D4 = 300, 300, 300
Total: 900

Notes

Caveats

See also