大量データの一括投入 — setValueArray / setFormulaArray

level: intermediate ／ verified: 2026-10-01 ／ 4言語タブ対応 ／ English

やりたいこと

何千・何万セルもの値や数式を、セルごとに getRange() するのではなく 配列にまとめて一度に投入する。あわせて相対参照を行ごとにずらした数式を一括入力する。

やり方使うもの
セルごとに投入ws.getCells(r, c).setNumberValue(...)
値を一括投入ws.setValueArray(Array<dto.InputValueObject>)
数式を一括投入ws.setFormulaArray(Array<dto.InputFormulaObject>)

対象クラス

dto.InputValueObject

メソッド説明
setStringValue(A1C1, val, numberFormat?)文字列値を設定します。
setNumberValue(A1C1, val, forceString?, numberFormat?)数値を設定します。
setBooleanValue(A1C1, val, forceString?, numberFormat?)真偽値を設定します。
setDateTimeValue(A1C1, val, forceString?, numberFormat?)日付時刻値を設定します。
setEmptyVal(A1C1)セル範囲を空に設定します。
getA1C1() / getInputType() / getStringValue() / getNumberValue() / getBooleanValue() / getDateTimeValue() / getNumberFormat() / getForceString()設定内容の取得。

dto.InputFormulaObject

メソッド説明
setA1C1(A1C1)対象セルを設定します。
setFormula(formula)数式を設定します（先頭に = は付けない）。
getA1C1() / getFormula()設定内容の取得。

コード

実行結果:

// bulk-input.js — 値と数式の一括投入、セルごと投入との速度比較
const { nodeosbxl, dto } = require("nodeosbxl");

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

// ---------- 1. いろいろな型を一度に投入する ----------
const date = new dto.DateTimeObject();
date.setYMD(2024, 3, 20);

const values = [];
const addString = (a1, val) => {
    const o = new dto.InputValueObject();
    o.setStringValue(a1, val);
    values.push(o);
};
// 末尾の省略可能引数は undefined のまま渡せます（省略として扱われます）
const addNumber = (a1, val, fmt) => {
    const o = new dto.InputValueObject();
    o.setNumberValue(a1, val, false, fmt);
    values.push(o);
};

addString("A1", "商品名");
addNumber("B1", 1234.5);
addNumber("C1", 0.25, "0.0%");
// 日付: 書式を省略すると既定書式が自動設定されます（Range.setDateValue と同じ）
const dateVal = new dto.InputValueObject();
dateVal.setDateTimeValue("D1", date, false, "yyyy-mm-dd");   // 書式を明示
values.push(dateVal);
const dateVal2 = new dto.InputValueObject();
dateVal2.setDateTimeValue("D2", date);                       // 省略 → 自動
values.push(dateVal2);

ws.setValueArray(values);
console.log("[1] 型混在の一括投入");
for (const a1 of ["A1", "B1", "C1", "D1", "D2"]) {
    const r = ws.getRange(`${a1}:${a1}`);
    console.log(`    ${a1}: 表示値=${JSON.stringify(r.getValue()[a1])} 書式=${JSON.stringify(r.getNumberFormat()[a1])}`);
}

// ---------- 2. 空にするのも一括投入 ----------
ws.getRange("A2:A2").setValue("いったん入力");
const empty = new dto.InputValueObject();
empty.setEmptyVal("A2");
ws.setValueArray([empty]);
console.log("[2] setEmptyVal 後 A2 =", JSON.stringify(ws.getRange("A2:A2").getValue()["A2"]));

// ---------- 3. 相対参照を行ごとにずらした数式を一括入力 ----------
// Range.setFormula() では相対参照が展開されないので、こちらを使う
for (let r = 1; r <= 3; r++) ws.getRange(`F${r}:F${r}`).setNumberValue(r * 10);
const formulas = [];
for (let r = 1; r <= 3; r++) {
    const f = new dto.InputFormulaObject();
    f.setA1C1(app.convertFromRowColNumber(r, 7));   // G1, G2, G3
    f.setFormula(`F${r}*2`);                        // 行ごとに異なる相対参照
    formulas.push(f);
}
ws.setFormulaArray(formulas);
console.log("[3] setFormulaArray 後");
console.log("    数式 =", JSON.stringify(ws.getRange("G1:G3").getFormula()));
console.log("    値   =", JSON.stringify(ws.getRange("G1:G3").getValue(true)));

// ---------- 4. 速度比較（2,000 行 x 5 列 = 10,000 セル）----------
const ROWS = 2000, COLS = 5;

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;

const wsBulk = wb.addWorkSheet("Bulk");
const bulk = [];
t = Date.now();
for (let r = 1; r <= ROWS; r++) {
    for (let c = 1; c <= COLS; c++) {
        const o = new dto.InputValueObject();
        o.setNumberValue(app.convertFromRowColNumber(r, c), r * 100 + c);
        bulk.push(o);
    }
}
const msBuild = Date.now() - t;
t = Date.now();
wsBulk.setValueArray(bulk);
const msApply = Date.now() - t;

console.log(`[4] ${ROWS * COLS} セルの投入`);
console.log(`    セルごと getCells().setNumberValue() : ${msCell} ms`);
console.log(`    setValueArray (構築${msBuild} + 投入${msApply}) : ${msBuild + msApply} ms`);

const a = wsCell.getRange("E2000:E2000").getValue(true)["E2000"];
const b = wsBulk.getRange("E2000:E2000").getValue(true)["E2000"];
console.log(`[検証] 両方式とも E2000 = ${a === b ? a : `不一致 ${a} / ${b}`}`);

wb.save();
wb.close();
[1] 型混在の一括投入
    A1: 表示値="商品名" 書式="General"
    B1: 表示値="1234.5" 書式="General"
    C1: 表示値="25.0%" 書式="0.0%"
    D1: 表示値="2024-03-20" 書式="yyyy-mm-dd"
    D2: 表示値="2024/3/20" 書式="yyyy/m/d;@"
[2] setEmptyVal 後 A2 = ""
[3] setFormulaArray 後
    数式 = {"G1":"F1*2","G2":"F2*2","G3":"F3*2"}
    値   = {"G1":"20","G2":"40","G3":"60"}
[4] 10000 セルの投入
    セルごと getCells().setNumberValue() : 102 ms
    setValueArray (構築18 + 投入58) : 76 ms
[検証] 両方式とも E2000 = 200005

[4] の ms は実行のたびに変わります（上記は 1 回の実行例）。

解説

速度

セル数セルごと getCells().setNumberValue()setValueArray()差
10,000（2,000 行 x 5 列）約 110 ms76 ms（構築 18 + 投入 58）約 1.4 倍
100,000（20,000 行 x 5 列）1,390 ms770 ms（構築 130 + 投入 640）約 1.8 倍

いずれも osbxl 1.5.0 / Node.js v21 での 1 回の実測例で、環境と実行のたびに変わります。 傾向としてセル数が増えるほど差が開きます。 JavaScript 側で InputValueObject を組み立てる時間も無視できないので、 上記では「構築」と「投入」を分けて計測しています。 数万セルを超えるなら setValueArray() を使ってください。

使いどころ

注意点

関連