Template: form controls

Excel gives form controls no name or id. You find them by the cell they are anchored to.

A control object has these properties:

Property	Meaning
loc	the top-left anchor cell, { r, c }
type	"Checkbox", "Radio", "Button" and so on
raw	the drawing text of the control. Do not edit it
path	the part of the file that holds it

Functions

• template_get_ctrls(wb, sheet) returns every control of a sheet.
• template_find_ctrls(wb, sheet, cell) returns the controls anchored at cell. The cell is { r, c } or "B2".
• template_set_ctrl_prop(wb, ctrl, prop, value) sets a property. For a Checkbox, Checked is 0 for unchecked, 1 for checked and 2 for mixed.
• Radio buttons are not grouped in the file, so the group is inferred. template_get_radio_group(wb, sheet, ctrl) returns the controls in the same group. template_set_radio_sel(wb, group, index) deselects all of them and selects the one at index (zero-based).

Note

These five functions exist at runtime and are in the bundled type declarations. The original type declarations did not list them.

Example

These functions need a workbook with form-control drawings. Excel writes them when you add controls. The example below builds such a file with the !controls sheet property so that it runs on its own.

const XLSX = require("@agent-sheet/wasm");

// Build a file that has one check box and three radio buttons.
const pos = (r) => ({ r, c: 1, x: 0, y: 0, w: 100, h: 20 });
const ws = XLSX.utils.aoa_to_sheet([["Controls"]]);
ws["!controls"] = [
  { "!pos": pos(1), "!type": "Checkbox", t: "Check me" },
  { "!pos": pos(3), "!type": "Radio", t: "A" },
  { "!pos": pos(4), "!type": "Radio", t: "B" },
  { "!pos": pos(5), "!type": "Radio", t: "C" },
];
const src = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(src, ws, "Form");
const file = XLSX.write(src, { type: "buffer", bookType: "xlsx" });

// Edit it in template mode.
const wb = XLSX.read(file, { template: true });
const sheet = "Form";
const all = XLSX.utils.template_get_ctrls(wb, sheet);
console.log(all.map((c) => c.type));

const box = XLSX.utils.template_find_ctrls(wb, sheet, all[0].loc)[0];
XLSX.utils.template_set_ctrl_prop(wb, box, "Checked", 1);

const radio = all.find((c) => c.type === "Radio");
const group = XLSX.utils.template_get_radio_group(wb, sheet, radio);
XLSX.utils.template_set_radio_sel(wb, group, 1);

const bytes = XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true });
console.log(group.length, bytes.length > 0);

Output

[ 'Checkbox', 'Radio', 'Radio', 'Radio' ]
3 true

For your own file, replace the build step with XLSX.readFile("form.xlsx", { template: true }).

On a sheet without control drawings, template_get_ctrls and template_find_ctrls throw a TypeError.