High-Speed Bulk Input — setRangeData / setRangeFormulas

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

What you want to do

Write values and formulas into thousands or tens of thousands of cells in one shot as a 2D matrix array instead of calling getCells() per cell. Populate tabular data from CSV files or database query results into worksheets with maximum speed and minimal memory overhead.

ApproachWhat to useCharacteristics
Per-cell inputws.getCells(r, c).setNumberValue(...)Suitable for a few cells. Crossing language/C++ boundaries per cell incurs heavy overhead.
2D array value input (Fastest, Recommended)ws.setRangeData(startA1, data)Fastest & Recommended. Zero DTO creation overhead. Numeric strings are automatically saved as numeric cells.
2D array formula input (Fastest, Recommended)ws.setRangeFormulas(startA1, formulas)Fastest & Recommended. Stream formulas across calculation columns at once (no leading "=" needed).

Classes covered

WorkSheet

MethodDescription
setRangeData(startA1, data)Bulk enters 2D cell values (array of string rows/columns) from the top-left start cell (e.g., "A1").
setRangeFormulas(startA1, formulas)Bulk enters 2D formulas (array of formula string rows/columns, no "=") from the top-left start cell (e.g., "D2").

Code

Output:

// bulk-input.js — high-speed bulk input of values and formulas with 2D array (nodeosbxl)
const { nodeosbxl } = require("nodeosbxl");

const app = new nodeosbxl.App();
const wb = app.createWorkBook("bulk-input.xlsx");
const ws = wb.openWorkSheet("Sheet1");

// ---------- 1. Bulk value input with 2D array (setRangeData) ----------
// Prepare a 2D array of rows and columns as strings and write all at once starting from top-left cell.
// Numeric strings like "100" are automatically stored as Excel numbers.
const data = [
    ["Product", "Price", "Qty", "Amount"],
    ["Apple", "100", "3"],
    ["Orange", "50", "6"],
    ["Grape", "150", "2"],
];
ws.setRangeData("A1", data);

console.log("[1] After setRangeData:");
for (const a1 of ["A1", "B2", "C2", "D1"]) {
    console.log(`    ${a1} = ${JSON.stringify(ws.getRange(a1).getValue()[a1])}`);
}

// ---------- 2. Bulk formula input with 2D array (setRangeFormulas) ----------
// Bulk input formulas across calculation columns (no leading "=" needed).
// Populate formulas starting at row 2 (D2), leaving header (D1) intact.
const formulas = [
    ["B2*C2"],
    ["B3*C3"],
    ["B4*C4"],
];
ws.setRangeFormulas("D2", formulas);

console.log("[2] After setRangeFormulas:");
console.log("    Formulas D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getFormula()));
console.log("    Values   D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getValue(true)));

// ---------- 3. Performance comparison (2,000 rows x 5 cols = 10,000 cells) ----------
const ROWS = 2000, COLS = 5;

// Per-cell input via getCells()
const wsCell = wb.addWorkSheet("CellByCell");
let t = Date.now();
for (let r = 1; r <= ROWS; r++) {
    for (let c = 1; c <= COLS; c++) wsCell.getCells(r, c).setNumberValue(r * 100 + c);
}
const msCell = Date.now() - t;

// 2D array input via setRangeData() (Fastest)
const wsBulk = wb.addWorkSheet("Bulk");
t = Date.now();
const matrix = [];
for (let r = 1; r <= ROWS; r++) {
    const row = [];
    for (let c = 1; c <= COLS; c++) row.push(String(r * 100 + c));
    matrix.push(row);
}
const msBuild = Date.now() - t;
t = Date.now();
wsBulk.setRangeData("A1", matrix);
const msApply = Date.now() - t;

console.log(`[3] Performance for ${ROWS * COLS} cells:`);
console.log(`    Per-cell getCells().setNumberValue() : ${msCell} ms`);
console.log(`    setRangeData (build ${msBuild} + write ${msApply}) : ${msBuild + msApply} ms (fastest)`);

const a = wsCell.getRange("E2000:E2000").getValue(true)["E2000"];
const b = wsBulk.getRange("E2000:E2000").getValue(true)["E2000"];
console.log(`[Verification] Both match E2000 = ${a === Number(b) ? b : `mismatch ${a} / ${b}`}`);

wb.save();
wb.close();
[1] After setRangeData:
    A1 = "Product"
    B2 = 100
    C2 = 3
    D1 = "Amount"
[2] After setRangeFormulas:
    Formulas D2:D4 = {"D2":"B2*C2","D3":"B3*C3","D4":"B4*C4"}
    Values   D2:D4 = {"D2":300,"D3":300,"D4":300}
[3] Performance for 10000 cells:
    Per-cell getCells().setNumberValue() : 72 ms
    setRangeData (build 2 + write 25) : 27 ms (fastest)
[Verification] Both match E2000 = 200005

[3] execution time varies depending on the runtime environment (the above is an actual sample run on hardware).

Notes

Speed and Performance

Measured input performance under identical environment (Linux x86_64 / Intel Xeon / Python and PHP benchmark test runs):

Scale (Cells)Per-cell getCells().setNumberValue()setRangeData() (2D Bulk Array)SpeedupPractical Advantage
10,000 cells
2,000 rows x 5 cols
~72 – 185 ms~27 – 95 ms
(incl. array build)
~2x – 2.7x fasterWeb API downloads complete in under 0.1 seconds without user wait.
100,000 cells
20,000 rows x 5 cols
~960 – 1,300 ms~280 – 350 ms~3x – 4.7x fasterPopulate massive datasets of 100k cells in ~0.3 seconds.
200,000 cells
50,000 rows x 4 cols
~2,200 – 2,400 ms~570 – 630 ms~3.5x – 4.1x fasterCompletes safely with low memory where legacy OSS libraries crash with OOM.

※ For full platform benchmark reports (GCP C3/C4, macOS M4, Windows 11) including file write and formula recalculation, see the Cross-Platform Benchmark Report.

setRangeData() minimizes language-boundary crossing overhead and performs direct SST (Shared String Table) registration and cell placement inside C++, widening the performance gap as cell count grows.

Best Practices

Caveats

See also