// bulk-input.js — 2次元配列による値と数式の一括高速投入 (nodeosbxl)
const { nodeosbxl } = require("nodeosbxl");
const app = new nodeosbxl.App();
const wb = app.createWorkBook("bulk-input.xlsx");
const ws = wb.openWorkSheet("Sheet1");
// ---------- 1. 2次元配列による値の一括高速投入 (setRangeData) ----------
// 各行・各列の値を文字列の2次元配列として用意し、左上セルを指定して一度に流し込みます。
// 数値形式の文字列 ("100" 等) は自動的に数値セルとして正しく認識・保存されます。
const data = [
["商品名", "単価", "数量", "金額"],
["りんご", "100", "3"],
["みかん", "50", "6"],
["ぶどう", "150", "2"],
];
ws.setRangeData("A1", data);
console.log("[1] setRangeData 投入後:");
for (const a1 of ["A1", "B2", "C2", "D1"]) {
console.log(` ${a1} = ${JSON.stringify(ws.getRange(a1).getValue()[a1])}`);
}
// ---------- 2. 2次元配列による数式の一括高速投入 (setRangeFormulas) ----------
// 計算列の数式群を2次元配列で一度に流し込みます(先頭の "=" は不要)。
// ヘッダー行(D1)を除いた D2 セルから数式を展開します。
const formulas = [
["B2*C2"],
["B3*C3"],
["B4*C4"],
];
ws.setRangeFormulas("D2", formulas);
console.log("[2] setRangeFormulas 投入後:");
console.log(" 数式 D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getFormula()));
console.log(" 計算値 D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getValue(true)));
// ---------- 3. 速度比較(2,000 行 x 5 列 = 10,000 セル)----------
const ROWS = 2000, COLS = 5;
// セルごとに 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;
// 2次元配列で setRangeData() 投入 (最高速)
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] ${ROWS * COLS} セルの投入速度比較:`);
console.log(` セルごと getCells().setNumberValue() : ${msCell} ms`);
console.log(` setRangeData (構築${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 === Number(b) ? b : `不一致 ${a} / ${b}`}`);
wb.save();
wb.close();
# bulk-input.py — 2次元配列による値と数式の一括高速投入 (pyosbxl)
import json
import time
import pyosbxl
app = pyosbxl.App()
wb = app.createWorkBook("bulk-input.xlsx")
ws = wb.openWorkSheet("Sheet1")
# ---------- 1. 2次元配列による値の一括高速投入 (setRangeData) ----------
data = [
["商品名", "単価", "数量", "金額"],
["りんご", "100", "3"],
["みかん", "50", "6"],
["ぶどう", "150", "2"],
]
ws.setRangeData("A1", data)
print("[1] setRangeData 投入後:")
for a1 in ["A1", "B2", "C2", "D1"]:
print(f" {a1} = {json.dumps(ws.getRange(a1).getValue()[a1], ensure_ascii=False)}")
# ---------- 2. 2次元配列による数式の一括高速投入 (setRangeFormulas) ----------
formulas = [
["B2*C2"],
["B3*C3"],
["B4*C4"],
]
ws.setRangeFormulas("D2", formulas)
print("[2] setRangeFormulas 投入後:")
print(" 数式 D2:D4 =", json.dumps(ws.getRange("D2:D4").getFormula(), ensure_ascii=False))
print(" 計算値 D2:D4 =", json.dumps(ws.getRange("D2:D4").getValue(True), ensure_ascii=False))
# ---------- 3. 速度比較(2,000 行 x 5 列 = 10,000 セル)----------
ROWS, COLS = 2000, 5
# セルごとに 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)
# 2次元配列で setRangeData() 投入 (最高速)
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] {ROWS * COLS} セルの投入速度比較:")
print(f" セルごと getCells().setNumberValue() : {ms_cell} ms")
print(f" setRangeData (構築{ms_build} + 投入{ms_apply}) : {ms_build + ms_apply} ms (最速)")
a = ws_cell.getRange("E2000:E2000").getValue(True)["E2000"]
b = ws_bulk.getRange("E2000:E2000").getValue(True)["E2000"]
print(f"[検証] 両方式とも E2000 = {b if a == float(b) else f'不一致 {a} / {b}'}")
wb.save()
wb.close()
// bulk-input.java — 2次元配列による値と数式の一括高速投入 (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. 2次元配列による値の一括高速投入 (setRangeData) ----------
List> data = Arrays.asList(
Arrays.asList("商品名", "単価", "数量", "金額"),
Arrays.asList("りんご", "100", "3"),
Arrays.asList("みかん", "50", "6"),
Arrays.asList("ぶどう", "150", "2")
);
ws.setRangeData("A1", data);
System.out.println("[1] setRangeData 投入後:");
for (String a1 : Arrays.asList("A1", "B2", "C2", "D1")) {
System.out.println(" " + a1 + " = " + ws.getRange(a1).getValue().get(a1));
}
// ---------- 2. 2次元配列による数式の一括高速投入 (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] setRangeFormulas 投入後:");
System.out.println(" 数式 D2:D4 = " + ws.getRange("D2:D4").getFormula());
System.out.println(" 計算値 D2:D4 = " + ws.getRange("D2:D4").getValue(true));
// ---------- 3. 速度比較(2,000 行 x 5 列 = 10,000 セル)----------
final int ROWS = 2000, COLS = 5;
// セルごとに 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;
// 2次元配列で setRangeData() 投入 (最高速)
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] " + (ROWS * COLS) + " セルの投入速度比較:");
System.out.println(" セルごと getCells().setNumberValue() : " + msCell + " ms");
System.out.println(" setRangeData (構築" + msBuild + " + 投入" + msApply + ") : " + (msBuild + msApply) + " ms (最速)");
String a = wsCell.getRange("E2000:E2000").getValue(true).get("E2000");
String b = wsBulk.getRange("E2000:E2000").getValue(true).get("E2000");
System.out.println("[検証] 両方式とも E2000 = " + (a.equals(b) ? b : "不一致 " + a + " / " + b));
wb.save();
wb.close();
}
}
// bulk-input.php — 2次元配列による値と数式の一括高速投入 (phposbxl)
$app = new \phposbxl\App();
$wb = $app->createWorkBook("bulk-input.xlsx");
$ws = $wb->openWorkSheet("Sheet1");
$now = fn(): int => (int)(hrtime(true) / 1000000); // ミリ秒
// ---------- 1. 2次元配列による値の一括高速投入 (setRangeData) ----------
$data = [
["商品名", "単価", "数量", "金額"],
["りんご", "100", "3"],
["みかん", "50", "6"],
["ぶどう", "150", "2"],
];
$ws->setRangeData("A1", $data);
echo "[1] setRangeData 投入後:\n";
foreach (["A1", "B2", "C2", "D1"] as $a1) {
echo " {$a1} = ", json_encode($ws->getRange($a1)->getValue()[$a1], JSON_UNESCAPED_UNICODE), "\n";
}
// ---------- 2. 2次元配列による数式の一括高速投入 (setRangeFormulas) ----------
$formulas = [
["B2*C2"],
["B3*C3"],
["B4*C4"],
];
$ws->setRangeFormulas("D2", $formulas);
echo "[2] setRangeFormulas 投入後:\n";
echo " 数式 D2:D4 = ", json_encode($ws->getRange("D2:D4")->getFormula(), JSON_UNESCAPED_UNICODE), "\n";
echo " 計算値 D2:D4 = ", json_encode($ws->getRange("D2:D4")->getValue(true), JSON_UNESCAPED_UNICODE), "\n";
// ---------- 3. 速度比較(2,000 行 x 5 列 = 10,000 セル)----------
$ROWS = 2000;
$COLS = 5;
// セルごとに getCells() して投入
$wsCell = $wb->addWorkSheet("CellByCell");
$t = $now();
for ($r = 1; $r <= $ROWS; $r++) {
for ($c = 1; $c <= $COLS; $c++) $wsCell->getCells($r, $c)->setNumberValue($r * 100 + $c);
}
$msCell = $now() - $t;
// 2次元配列で setRangeData() 投入 (最高速)
$wsBulk = $wb->addWorkSheet("Bulk");
$t = $now();
$matrix = [];
for ($r = 1; $r <= $ROWS; $r++) {
$row = [];
for ($c = 1; $c <= $COLS; $c++) $row[] = (string)($r * 100 + $c);
$matrix[] = $row;
}
$msBuild = $now() - $t;
$t = $now();
$wsBulk->setRangeData("A1", $matrix);
$msApply = $now() - $t;
echo "[3] ", $ROWS * $COLS, " セルの投入速度比較:\n";
echo " セルごと getCells().setNumberValue() : {$msCell} ms\n";
echo " setRangeData (構築{$msBuild} + 投入{$msApply}) : ", $msBuild + $msApply, " ms (最速)\n";
$a = $wsCell->getRange("E2000:E2000")->getValue(true)["E2000"];
$b = $wsBulk->getRange("E2000:E2000")->getValue(true)["E2000"];
echo "[検証] 両方式とも E2000 = ", $a === (int)$b ? $b : "不一致 {$a} / {$b}", "\n";
$wb->save();
$wb->close();
// bulk-input.wasm.js — 2次元配列による値と数式の一括高速投入 (wasmosbxl / embind)
const app = new osbxl.App();
const wb = app.createWorkBook("bulk-input.xlsx");
const ws = wb.openWorkSheet("Sheet1");
// ---------- 1. 2次元配列による値の一括高速投入 (setRangeData) ----------
const data = [
["商品名", "単価", "数量", "金額"],
["りんご", "100", "3"],
["みかん", "50", "6"],
["ぶどう", "150", "2"],
];
ws.setRangeData("A1", data);
console.log("[1] setRangeData 投入後:");
for (const a1 of ["A1", "B2", "C2", "D1"]) {
console.log(` ${a1} = ${JSON.stringify(ws.getRange(a1).getValue()[a1])}`);
}
// ---------- 2. 2次元配列による数式の一括高速投入 (setRangeFormulas) ----------
const formulas = [
["B2*C2"],
["B3*C3"],
["B4*C4"],
];
ws.setRangeFormulas("D2", formulas);
console.log("[2] setRangeFormulas 投入後:");
console.log(" 数式 D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getFormula()));
console.log(" 計算値 D2:D4 =", JSON.stringify(ws.getRange("D2:D4").getValue(true)));
// ---------- 3. 速度比較(2,000 行 x 5 列 = 10,000 セル)----------
const ROWS = 2000, COLS = 5;
// セルごとに 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;
// 2次元配列で setRangeData() 投入 (最高速)
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] ${ROWS * COLS} セルの投入速度比較:`);
console.log(` セルごと getCells().setNumberValue() : ${msCell} ms`);
console.log(` setRangeData (構築${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 === Number(b) ? b : `不一致 ${a} / ${b}`}`);
wb.save();
wb.close();