Bulk Input of Large Data — setValueArray / setFormulaArray

# bulk-input.py — same verification as the Node.js sample (pyosbxl)
import json
import time

import pyosbxl

app = pyosbxl.App()
wb = app.createWorkBook("bulk-input-py.xlsx")
ws = wb.openWorkSheet("Sheet1")

# ---------- 1. write mixed types in one shot ----------
date = pyosbxl.dto.DateTimeObject()
date.setYMD(2024, 3, 20)

values = []

def add_string(a1, val):
    o = pyosbxl.dto.InputValueObject()
    o.setStringValue(a1, val)
    values.append(o)

# Trailing optional arguments are treated as omitted when not passed
def add_number(a1, val, fmt=None):
    o = pyosbxl.dto.InputValueObject()
    if fmt is None:
        o.setNumberValue(a1, val)
    else:
        o.setNumberValue(a1, val, False, fmt)
    values.append(o)

add_string("A1", "Product")
add_number("B1", 1234.5)
add_number("C1", 0.25, "0.0%")
# Date: omitting the format auto-sets a default format (same as Range.setDateValue)
date_val = pyosbxl.dto.InputValueObject()
date_val.setDateTimeValue("D1", date, False, "yyyy-mm-dd")   # explicit format
values.append(date_val)
date_val2 = pyosbxl.dto.InputValueObject()
date_val2.setDateTimeValue("D2", date)                       # omitted → auto
values.append(date_val2)

ws.setValueArray(values)
print("[1] bulk input with mixed types")
for a1 in ["A1", "B1", "C1", "D1", "D2"]:
    r = ws.getRange(f"{a1}:{a1}")
    v = json.dumps(r.getValue()[a1], ensure_ascii=False)
    fmt = json.dumps(r.getNumberFormat()[a1], ensure_ascii=False)
    print(f"    {a1}: display={v} format={fmt}")

# ---------- 2. clearing cells is bulk input too ----------
ws.getRange("A2:A2").setValue("temporary entry")
empty = pyosbxl.dto.InputValueObject()
empty.setEmptyVal("A2")
ws.setValueArray([empty])
print("[2] after setEmptyVal, A2 =", json.dumps(ws.getRange("A2:A2").getValue()["A2"], ensure_ascii=False))

# ---------- 3. bulk-input formulas with relative references shifted per row ----------
# Range.setFormula() does not expand relative references, so use this instead
for r in range(1, 4):
    ws.getRange(f"F{r}:F{r}").setNumberValue(r * 10)
formulas = []
for r in range(1, 4):
    f = pyosbxl.dto.InputFormulaObject()
    f.setA1C1(app.convertFromRowColNumber(r, 7))   # G1, G2, G3
    f.setFormula(f"F{r}*2")                        # different relative reference per row (no leading "=")
    formulas.append(f)
ws.setFormulaArray(formulas)
print("[3] after setFormulaArray")
fm = ws.getRange("G1:G3").getFormula()
vm = ws.getRange("G1:G3").getValue(True)
keys = ["G1", "G2", "G3"]
print("    formulas =", json.dumps({k: fm[k] for k in keys}, ensure_ascii=False, separators=(",", ":")))
print("    values   =", json.dumps({k: vm[k] for k in keys}, ensure_ascii=False, separators=(",", ":")))

# ---------- 4. speed comparison (2,000 rows x 5 cols = 10,000 cells) ----------
ROWS, COLS = 2000, 5

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)

ws_bulk = wb.addWorkSheet("Bulk")
bulk = []
t = time.perf_counter()
for r in range(1, ROWS + 1):
    for c in range(1, COLS + 1):
        o = pyosbxl.dto.InputValueObject()
        o.setNumberValue(app.convertFromRowColNumber(r, c), r * 100 + c)
        bulk.append(o)
ms_build = int((time.perf_counter() - t) * 1000)
t = time.perf_counter()
ws_bulk.setValueArray(bulk)
ms_apply = int((time.perf_counter() - t) * 1000)

print(f"[4] writing {ROWS * COLS} cells")
print(f"    per-cell getCells().setNumberValue() : {ms_cell} ms")
print(f"    setValueArray (build {ms_build} + write {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"[check] both methods agree: E2000 = {a if a == b else f'mismatch {a} / {b}'}")

wb.save()
wb.close()

// bulk-input.java — same verification as the Node.js sample (javaosbxl)
import com.osboffice.osbxl.*;
import com.osboffice.osbxl.dto.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Main {
    // Quoted string, equivalent to JSON.stringify
    static String q(String s) { return "\"" + s + "\""; }

    static void addString(List<InputValueObjectWrapper> values, String a1, String val) {
        InputValueObjectWrapper o = new InputValueObjectWrapper();
        o.setStringValue(a1, val);
        values.add(o);
    }

    // If fmt == null, do not pass the optional arguments (treated as omitted)
    static void addNumber(List<InputValueObjectWrapper> values, String a1, double val, String fmt) {
        InputValueObjectWrapper o = new InputValueObjectWrapper();
        if (fmt == null) {
            o.setNumberValue(a1, val);
        } else {
            o.setNumberValue(a1, val, false, fmt);
        }
        values.add(o);
    }

    public static void main(String[] args) {
        AppWrapper app = new AppWrapper();
        WorkBookWrapper wb = app.createWorkBook("bulk-input-java.xlsx");
        WorkSheetWrapper ws = wb.openWorkSheet("Sheet1");

        // ---------- 1. write mixed types in one shot ----------
        DateTimeObjectWrapper date = new DateTimeObjectWrapper();
        date.setYMD(2024, 3, 20);

        List<InputValueObjectWrapper> values = new ArrayList<>();
        addString(values, "A1", "Product");
        addNumber(values, "B1", 1234.5, null);
        addNumber(values, "C1", 0.25, "0.0%");
        // Date: omitting the format auto-sets a default format (same as Range.setDateValue)
        InputValueObjectWrapper dateVal = new InputValueObjectWrapper();
        dateVal.setDateTimeValue("D1", date, false, "yyyy-mm-dd");   // explicit format
        values.add(dateVal);
        InputValueObjectWrapper dateVal2 = new InputValueObjectWrapper();
        dateVal2.setDateTimeValue("D2", date);                       // omitted → auto
        values.add(dateVal2);

        ws.setValueArray(values);
        System.out.println("[1] bulk input with mixed types");
        for (String a1 : new String[]{"A1", "B1", "C1", "D1", "D2"}) {
            RangeWrapper r = ws.getRange(a1 + ":" + a1);
            System.out.println("    " + a1 + ": display=" + q(r.getValue().get(a1))
                    + " format=" + q(r.getNumberFormat().get(a1)));
        }

        // ---------- 2. clearing cells is bulk input too ----------
        ws.getRange("A2:A2").setValue("temporary entry");
        InputValueObjectWrapper empty = new InputValueObjectWrapper();
        empty.setEmptyVal("A2");
        List<InputValueObjectWrapper> emptyList = new ArrayList<>();
        emptyList.add(empty);
        ws.setValueArray(emptyList);
        System.out.println("[2] after setEmptyVal, A2 = " + q(ws.getRange("A2:A2").getValue().get("A2")));

        // ---------- 3. bulk-input formulas with relative references shifted per row ----------
        // Range.setFormula() does not expand relative references, so use this instead
        for (int r = 1; r <= 3; r++) ws.getRange("F" + r + ":F" + r).setNumberValue(r * 10);
        List<InputFormulaObjectWrapper> formulas = new ArrayList<>();
        for (int r = 1; r <= 3; r++) {
            InputFormulaObjectWrapper f = new InputFormulaObjectWrapper();
            f.setA1C1(app.convertFromRowColNumber(r, 7));   // G1, G2, G3
            f.setFormula("F" + r + "*2");                   // different relative reference per row (no leading "=")
            formulas.add(f);
        }
        ws.setFormulaArray(formulas);
        System.out.println("[3] after setFormulaArray");
        Map<String, String> fm = ws.getRange("G1:G3").getFormula();
        Map<String, String> vm = ws.getRange("G1:G3").getValue(true);
        System.out.println("    formulas = {\"G1\":" + q(fm.get("G1")) + ",\"G2\":" + q(fm.get("G2"))
                + ",\"G3\":" + q(fm.get("G3")) + "}");
        System.out.println("    values   = {\"G1\":" + q(vm.get("G1")) + ",\"G2\":" + q(vm.get("G2"))
                + ",\"G3\":" + q(vm.get("G3")) + "}");

        // ---------- 4. speed comparison (2,000 rows x 5 cols = 10,000 cells) ----------
        final int ROWS = 2000, COLS = 5;

        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;

        WorkSheetWrapper wsBulk = wb.addWorkSheet("Bulk");
        List<InputValueObjectWrapper> bulk = new ArrayList<>();
        t = System.currentTimeMillis();
        for (int r = 1; r <= ROWS; r++) {
            for (int c = 1; c <= COLS; c++) {
                InputValueObjectWrapper o = new InputValueObjectWrapper();
                o.setNumberValue(app.convertFromRowColNumber(r, c), r * 100 + c);
                bulk.add(o);
            }
        }
        long msBuild = System.currentTimeMillis() - t;
        t = System.currentTimeMillis();
        wsBulk.setValueArray(bulk);
        long msApply = System.currentTimeMillis() - t;

        System.out.println("[4] writing " + (ROWS * COLS) + " cells");
        System.out.println("    per-cell getCells().setNumberValue() : " + msCell + " ms");
        System.out.println("    setValueArray (build " + msBuild + " + write " + 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("[check] both methods agree: E2000 = " + (a.equals(b) ? a : "mismatch " + a + " / " + b));

        wb.save();
        wb.close();
    }
}

// bulk-input.wasm.js — same verification as the Node.js sample (wasmosbxl / embind)
// Note (WebAssembly build): load the module first with createOsbxl(createModule).
// osbxl / dto / chart / enums used below are the four namespaces it returns
// (the equivalent of require("nodeosbxl") in the Node.js build).
const app = new osbxl.App();
const wb = app.createWorkBook("bulk-input-wasm.xlsx");
const ws = wb.openWorkSheet("Sheet1");

// ---------- 1. write mixed types in one shot ----------
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);
};
// The wasm build registers a separate overload per argument count; unlike node,
// passing undefined for a trailing optional argument throws a "Cannot pass non-string"
// exception. To omit an argument, call the overload with fewer arguments.
const addNumber = (a1, val, fmt) => {
    const o = new dto.InputValueObject();
    if (fmt === undefined) o.setNumberValue(a1, val);
    else o.setNumberValue(a1, val, false, fmt);
    values.push(o);
};

addString("A1", "Product");
addNumber("B1", 1234.5);
addNumber("C1", 0.25, "0.0%");
// Date: omitting the format auto-sets a default format (same as Range.setDateValue)
const dateVal = new dto.InputValueObject();
dateVal.setDateTimeValue("D1", date, false, "yyyy-mm-dd");   // explicit format
values.push(dateVal);
const dateVal2 = new dto.InputValueObject();
dateVal2.setDateTimeValue("D2", date);                       // omitted → auto
values.push(dateVal2);

ws.setValueArray(values);
console.log("[1] bulk input with mixed types");
for (const a1 of ["A1", "B1", "C1", "D1", "D2"]) {
    const r = ws.getRange(`${a1}:${a1}`);
    console.log(`    ${a1}: display=${JSON.stringify(r.getValue()[a1])} format=${JSON.stringify(r.getNumberFormat()[a1])}`);
}

// ---------- 2. clearing cells is bulk input too ----------
ws.getRange("A2:A2").setValue("temporary entry");
const empty = new dto.InputValueObject();
empty.setEmptyVal("A2");
ws.setValueArray([empty]);
console.log("[2] after setEmptyVal, A2 =", JSON.stringify(ws.getRange("A2:A2").getValue()["A2"]));

// ---------- 3. bulk-input formulas with relative references shifted per row ----------
// Range.setFormula() does not expand relative references, so use this instead
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`);                        // different relative reference per row
    formulas.push(f);
}
ws.setFormulaArray(formulas);
console.log("[3] after setFormulaArray");
console.log("    formulas =", JSON.stringify(ws.getRange("G1:G3").getFormula()));
console.log("    values   =", JSON.stringify(ws.getRange("G1:G3").getValue(true)));

// ---------- 4. speed comparison (2,000 rows x 5 cols = 10,000 cells) ----------
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] writing ${ROWS * COLS} cells`);
console.log(`    per-cell getCells().setNumberValue() : ${msCell} ms`);
console.log(`    setValueArray (build ${msBuild} + write ${msApply}) : ${msBuild + msApply} ms`);

const a = wsCell.getRange("E2000:E2000").getValue(true)["E2000"];
const b = wsBulk.getRange("E2000:E2000").getValue(true)["E2000"];
console.log(`[check] both methods agree: E2000 = ${a === b ? a : `mismatch ${a} / ${b}`}`);

wb.save();
wb.close();