Skip to main content

Array of Formulae

Extract all formulae from a worksheet

var fmla_arr = XLSX.utils.sheet_to_formulae(ws);

XLSX.utils.sheet_to_formulae generates an array of commands that represent how a person would enter data into an application.

Live Preview

After choosing a file, the demo will extract and display all formulae.

Result
Loading...
Live Editor

Cell Processing

Cells are analyzed in "row-major order" (starting from the first row).

Cells without formulae

Cells without formulae are written as A1-cell-address=value:

A1=1                   // A1 is the numeric value 1
B1=TRUE // B1 is the logical value TRUE

String literals are prefixed with a ' in accordance with Excel:

A5='A4+A3              // A5 is the string "A4+A3"

Cells with formulae

Cells with formulae are written as A1-cell-address=formula:

A5=A4+A3               // A5 is a cell with formula =A4+A3

Array formulae

Array formulae are written as A1-range=formula. They do not include the displayed curly braces:

A4:B4=A2:B2*A3:B3      // A4:B4 array formula {=A2:B2*A3:B3}

Single-cell array formulae are written with single-cell ranges:

C4:C4=SUM(A2:A3*B2:B3) // C4 array formula {=SUM(A2:A3*B2:B3)}

Demo

This example constructs a workbook including cells with no formulae, cells with normal formulae, single-cell array formulae and array formulae spanning ranges.

For verification, the button writes a workbook whose formulae can be inspected.

Result
Loading...
Live Editor