公式数组
从工作表中提取所有公式
¥Extract all formulae from a worksheet
var fmla_arr = XLSX.utils.sheet_to_formulae(ws);
XLSX.utils.sheet_to_formulae
生成一系列命令,表示人们如何将数据输入到应用中。
¥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.
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
没有公式的单元格写为 A1-cell-address=value
:
¥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
根据 Excel,字符串字面量以 '
为前缀:
¥String literals are prefixed with a '
in accordance with Excel:
A5='A4+A3 // A5 is the string "A4+A3"
带有公式的单元格
¥Cells with formulae
有公式的单元格写为 A1-cell-address=formula
:
¥Cells with formulae are written as A1-cell-address=formula
:
A5=A4+A3 // A5 is a cell with formula =A4+A3
数组公式
¥Array formulae
数组公式写为 A1-range=formula
。它们不包括显示的大括号:
¥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.
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> ); }