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

function SheetJSFormulaPreview() { const [__html, setHTML] = React.useState("Select a file"); return ( <> <pre dangerouslySetInnerHTML={{ __html }}/> <input type="file" onChange={async(e) => { const wb = XLSX.read(await e.target.files[0].arrayBuffer()); var res = ""; wb.SheetNames.forEach((n, idx) => { const ws = wb.Sheets[n]; res += `<b>Sheet #${idx+1} (${n})</b>\n`; res += XLSX.utils.sheet_to_formulae(ws).join("\n") + "\n\n"; }); setHTML(res); }}/> </> ); }

function SheetJSFormulaPreview() {
  const [__html, setHTML] = React.useState("Select a file");

  return ( <>
    <pre dangerouslySetInnerHTML={{ __html }}/>
    <input type="file" onChange={async(e) => {
      const wb = XLSX.read(await e.target.files[0].arrayBuffer());
      var res = "";
      wb.SheetNames.forEach((n, idx) => {
        const ws = wb.Sheets[n];
        res += `<b>Sheet #${idx+1} (${n})</b>\n`;
        res += XLSX.utils.sheet_to_formulae(ws).join("\n") + "\n\n";
      });
      setHTML(res);
    }}/>
  </> );
}

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-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:根据Excel，字符串文字的前缀为'：

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

Cells with formulae带有公式的单元格

Cells with formulae are written as A1-cell-address=formula:带有公式的单元格被写成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. 数组公式被写成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

function SheetJSToFormulae() { var ws = XLSX.utils.aoa_to_sheet([ ["A", "B", "C"], [1, 2, { t: "n", f: "SUM(A2:B2)" }], [3, 4, { t: "n", f: "A3+B3" }] ]); XLSX.utils.sheet_set_array_formula(ws, "A4:B4", "A2:B2*A3:B3"); XLSX.utils.sheet_set_array_formula(ws, "C4", "SUM(A2:A3*B2:B3)"); const generate = () => { const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSFormulae.xlsx"); }; var __html = `\ <i>Values</i> [ ["A", "B", "C"], [ 1 , 2], [ 3 , 4] ] <i>Formulae</i> C2 =SUM(A2:B2) C3 =A3+B3 <i>Array Formulae</i> A4:B4 {=A2:B2*A3:B3} C4 {=SUM(A2:A3*B2:B3)}`; return ( <pre> <b>Original worksheet</b><br/> <button onClick={generate}><b>Export worksheet to XLSX</b></button> <div dangerouslySetInnerHTML={{__html}}/><br/> <b>XLSX.utils.sheet_to_formulae(ws).join("\n")</b><br/> <br/>{XLSX.utils.sheet_to_formulae(ws).join("\n")} </pre> ); }

function SheetJSToFormulae() {
  var ws = XLSX.utils.aoa_to_sheet([
    ["A", "B", "C"],
    [1, 2, { t: "n", f: "SUM(A2:B2)" }],
    [3, 4, { t: "n", f: "A3+B3" }]
  ]);
  XLSX.utils.sheet_set_array_formula(ws, "A4:B4", "A2:B2*A3:B3");
  XLSX.utils.sheet_set_array_formula(ws, "C4", "SUM(A2:A3*B2:B3)");
  const generate = () => {
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSFormulae.xlsx");
  };

  var __html = `\
<i>Values</i>
[
  ["A", "B", "C"],
  [ 1 ,  2],
  [ 3 ,  4]
]
<i>Formulae</i>
C2     =SUM(A2:B2)
C3     =A3+B3
<i>Array Formulae</i>
A4:B4  {=A2:B2*A3:B3}
C4     {=SUM(A2:A3*B2:B3)}`;

  return ( <pre>
    <b>Original worksheet</b><br/>
    <button onClick={generate}><b>Export worksheet to XLSX</b></button>
    <div dangerouslySetInnerHTML={{__html}}/><br/>
    <b>XLSX.utils.sheet_to_formulae(ws).join("\n")</b><br/>
    <br/>{XLSX.utils.sheet_to_formulae(ws).join("\n")}
  </pre> );
}