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.
Approach
What to use
Characteristics
Per-cell input
ws.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
Method
Description
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();
# bulk-input.py — high-speed bulk input of values and formulas with 2D array (pyosbxl)
import json
import time
import pyosbxl
app = pyosbxl.App()
wb = app.createWorkBook("bulk-input.xlsx")
ws = wb.openWorkSheet("Sheet1")
# ---------- 1. Bulk value input with 2D array (setRangeData) ----------
data = [
["Product", "Price", "Qty", "Amount"],
["Apple", "100", "3"],
["Orange", "50", "6"],
["Grape", "150", "2"],
]
ws.setRangeData("A1", data)
print("[1] After setRangeData:")
for a1 in ["A1", "B2", "C2", "D1"]:
print(f" {a1} = {json.dumps(ws.getRange(a1).getValue()[a1], ensure_ascii=False)}")
# ---------- 2. Bulk formula input with 2D array (setRangeFormulas) ----------
formulas = [
["B2*C2"],
["B3*C3"],
["B4*C4"],
]
ws.setRangeFormulas("D2", formulas)
print("[2] After setRangeFormulas:")
print(" Formulas D2:D4 =", json.dumps(ws.getRange("D2:D4").getFormula(), ensure_ascii=False))
print(" Values D2:D4 =", json.dumps(ws.getRange("D2:D4").getValue(True), ensure_ascii=False))
# ---------- 3. Performance comparison (2,000 rows x 5 cols = 10,000 cells) ----------
ROWS, COLS = 2000, 5
# Per-cell input via getCells()
ws_cell = wb.addWorkSheet("CellByCell")
t = time.perf_counter()
for r in range(1, ROWS + 1):
for c in range(1, COLS + 1):
ws_cell.getCells(r, c).setNumberValue(r * 100 + c)
ms_cell = int((time.perf_counter() - t) * 1000)
# 2D array input via setRangeData() (Fastest)
ws_bulk = wb.addWorkSheet("Bulk")
t = time.perf_counter()
matrix = [[str(r * 100 + c) for c in range(1, COLS + 1)] for r in range(1, ROWS + 1)]
ms_build = int((time.perf_counter() - t) * 1000)
t = time.perf_counter()
ws_bulk.setRangeData("A1", matrix)
ms_apply = int((time.perf_counter() - t) * 1000)
print(f"[3] Performance for {ROWS * COLS} cells:")
print(f" Per-cell getCells().setNumberValue() : {ms_cell} ms")
print(f" setRangeData (build {ms_build} + write {ms_apply}) : {ms_build + ms_apply} ms (fastest)")
a = ws_cell.getRange("E2000:E2000").getValue(True)["E2000"]
b = ws_bulk.getRange("E2000:E2000").getValue(True)["E2000"]
print(f"[Verification] Both match E2000 = {b if a == float(b) else f'mismatch {a} / {b}'}")
wb.save()
wb.close()
// bulk-input.java — high-speed bulk input of values and formulas with 2D array (javaosbxl)
import com.osboffice.osbxl.*;
import java.util.*;
public class Main {
public static void main(String[] args) {
AppWrapper app = new AppWrapper();
WorkBookWrapper wb = app.createWorkBook("bulk-input.xlsx");
WorkSheetWrapper ws = wb.openWorkSheet("Sheet1");
// ---------- 1. Bulk value input with 2D array (setRangeData) ----------
List> data = Arrays.asList(
Arrays.asList("Product", "Price", "Qty", "Amount"),
Arrays.asList("Apple", "100", "3"),
Arrays.asList("Orange", "50", "6"),
Arrays.asList("Grape", "150", "2")
);
ws.setRangeData("A1", data);
System.out.println("[1] After setRangeData:");
for (String a1 : Arrays.asList("A1", "B2", "C2", "D1")) {
System.out.println(" " + a1 + " = " + ws.getRange(a1).getValue().get(a1));
}
// ---------- 2. Bulk formula input with 2D array (setRangeFormulas) ----------
List> formulas = Arrays.asList(
Collections.singletonList("B2*C2"),
Collections.singletonList("B3*C3"),
Collections.singletonList("B4*C4")
);
ws.setRangeFormulas("D2", formulas);
System.out.println("[2] After setRangeFormulas:");
System.out.println(" Formulas D2:D4 = " + ws.getRange("D2:D4").getFormula());
System.out.println(" Values D2:D4 = " + ws.getRange("D2:D4").getValue(true));
// ---------- 3. Performance comparison (2,000 rows x 5 cols = 10,000 cells) ----------
final int ROWS = 2000, COLS = 5;
// Per-cell input via getCells()
WorkSheetWrapper wsCell = wb.addWorkSheet("CellByCell");
long t = System.currentTimeMillis();
for (int r = 1; r <= ROWS; r++) {
for (int c = 1; c <= COLS; c++) wsCell.getCells(r, c).setNumberValue(r * 100 + c);
}
long msCell = System.currentTimeMillis() - t;
// 2D array input via setRangeData() (Fastest)
WorkSheetWrapper wsBulk = wb.addWorkSheet("Bulk");
t = System.currentTimeMillis();
List> matrix = new ArrayList<>();
for (int r = 1; r <= ROWS; r++) {
List row = new ArrayList<>();
for (int c = 1; c <= COLS; c++) row.add(String.valueOf(r * 100 + c));
matrix.add(row);
}
long msBuild = System.currentTimeMillis() - t;
t = System.currentTimeMillis();
wsBulk.setRangeData("A1", matrix);
long msApply = System.currentTimeMillis() - t;
System.out.println("[3] Performance for " + (ROWS * COLS) + " cells:");
System.out.println(" Per-cell getCells().setNumberValue() : " + msCell + " ms");
System.out.println(" setRangeData (build " + msBuild + " + write " + msApply + ") : " + (msBuild + msApply) + " ms (fastest)");
String a = wsCell.getRange("E2000:E2000").getValue(true).get("E2000");
String b = wsBulk.getRange("E2000:E2000").getValue(true).get("E2000");
System.out.println("[Verification] Both match E2000 = " + (a.equals(b) ? b : "mismatch " + a + " / " + b));
wb.save();
wb.close();
}
}
// bulk-input.wasm.js — high-speed bulk input of values and formulas with 2D array (wasmosbxl / embind)
const app = new osbxl.App();
const wb = app.createWorkBook("bulk-input.xlsx");
const ws = wb.openWorkSheet("Sheet1");
// ---------- 1. Bulk value input with 2D array (setRangeData) ----------
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) ----------
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)
Speedup
Practical Advantage
10,000 cells 2,000 rows x 5 cols
~72 – 185 ms
~27 – 95 ms (incl. array build)
~2x – 2.7x faster
Web 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 faster
Populate 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 faster
Completes 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
Populating CSV / Database Records — Pass a 2D array of rows and columns directly into ws.setRangeData("A1", matrix).
Generating Calculation Columns — Stream formulas row by row with ws.setRangeFormulas("D2", [["B2*C2"], ["B3*C3"], ...]).
When Writing Hundreds or More Cells — Using setRangeData() / setRangeFormulas() is strongly recommended over per-cell setters.
Caveats
Specify the top-left start cell. The first argument (e.g. "A1" or "B5") acts as the anchor point from which the 2D matrix expands across rows and columns.
Numeric strings are automatically saved as numeric cells. Strings that represent valid numbers (e.g., "100", "-12.5") are recognized as numeric cells in Excel and can be calculated by functions like SUM.
Do not prefix formulas with =. Write setRangeFormulas("D2", [["B2*C2"]]).