Skip to main content

公式

File Format Support (click to show)

The parser will translate from the storage representation to A1-Style strings, while the writer will translate from A1-Style strings to the file format.

FormatsParseWriteArrayDynamicStorage Representation
XLSX / XLSM✔✔✔✔A1-Style strings
XLSB✔✔✔BIFF parsed tokens
XLS✔✔✕BIFF parsed tokens
XLML✔✔✔✕RC-style strings
SYLK✔✔✕A1/RC-style strings
CSV / TXT✔✔✕✕A1-Style strings
ODS / FODS / UOS✔✔✕OpenFormula strings
WK*✔✕Lotus parsed tokens
WQ* / WB* / QPW✕Quattro Pro tokens
NUMBERS✕Numbers parsed tokens

X (✕) marks features that are not supported by the file formats. There is no way to mark a dynamic array formula in the XLS file format.

SheetJS 支持多种文件格式的读取和写入公式。如果支持，公式将始终被导出。

¥SheetJS supports reading and writing formulae for a number of file formats. When supported, formulae will always be exported.

默认情况下，并不总是导入公式。为了确保公式解析，应将选项 cellFormula: true 传递给解析器。

¥By default, formulae are not always imported. To ensure formula parsing, the option cellFormula: true should be passed to the parser.

通常，文件数据将以 ArrayBuffer 形式提供，可以通过 fetch / XMLHttpRequest 下载，也可以由用户通过文件输入元素提交。cellFormula: true 应添加到第二个选项参数中：

¥Typically file data will be available as an ArrayBuffer, either downloaded with fetch / XMLHttpRequest or user-submitted with a File Input element. cellFormula: true should be added to the second options argument:

/* using read in the browser, `cellFormula` is in the second argument */
const ab = await (await fetch("test.xlsx")).arrayBuffer();
const workbook = XLSX.read(ab, { cellFormula: true });
// ------------------------------^^^^^^^^^^^^^^^^^

A1-Style 公式​

¥A1-Style Formulae

A1 样式公式字符串存储在单元格对象的 f 字段中。电子表格软件通常表示带有前导 = 符号的公式，但 SheetJS 公式省略了 =。

¥The A1-Style formula string is stored in the f field of the cell object. Spreadsheet software typically represent formulae with a leading = sign, but SheetJS formulae omit the =.

"A1-Style" 更详细地描述了 A1-Style。

¥"A1-Style" describes A1-Style in more detail.

Live Example (click to hide)

例如，考虑 这个测试文件：

¥For example, consider this test file:

Screenshot

以下代码块获取文件、解析并打印单元格 D1 上的信息：

¥The following code block fetches the file, parses and prints info on cell D1:

Result
Loading...
Live Editor
/* The live editor requires this function wrapper */
function ConcatFormula(props) {
  const [ws, setWS] = React.useState({"!ref":"A1"});
  const [addr, setAddr] = React.useState("D1");
  const setaddr = React.useCallback((evt)=>{ setAddr(evt.target.value) });

  /* Process ArrayBuffer */
  const process_ab = (ab) => {
    const wb = XLSX.read(ab, {cellFormula: true, sheetStubs: true});
    setWS(wb.Sheets[wb.SheetNames[0]]);
  };

  /* Fetch sample file */
  React.useEffect(() => {(async() => {
    process_ab(await (await fetch("/files/concat.xlsx")).arrayBuffer());
  })(); }, []);
  const process_file = async(e) => {
    process_ab(await e.target.files[0].arrayBuffer());
  };
  return ( <>
    <input type="file" onChange={process_file}/><br/>
    <b>Cell: </b><input type="text" value={addr} onChange={setaddr} size="6"/>
    {!ws[addr] ? ( <b>Cell {addr} not found</b> ) : ( <table>
      <tr><td>Formula</td><td><code>{ws[addr].f}</code></td></tr>
      <tr><td>Value</td><td><code>{ws[addr].v}</code></td></tr>
      <tr><td>Cell Type</td><td><code>{ws[addr].t}</code></td></tr>
    </table> )}
  </> );
}

单元格公式​

¥Single-Cell Formulae

对于简单的公式，可以将所需单元格的 f 键设置为实际的公式文本。此工作表代表 A1=1、A2=2 和 A3=A1+A2：

¥For simple formulae, the f key of the desired cell can be set to the actual formula text. This worksheet represents A1=1, A2=2, and A3=A1+A2:

var worksheet = {
  "!ref": "A1:A3", // Worksheet range A1:A3
  A1: { t: "n", v: 1 }, // A1 is a number (1)
  A2: { t: "n", v: 2 }, // A2 is a number (2)
  A3: { t: "n", v: 3, f: "A1+A2" } // A3 =A1+A2
};

像 aoa_to_sheet 这样的实用程序将接受单元格对象来代替值：

¥Utilities like aoa_to_sheet will accept cell objects in lieu of values:

var worksheet = XLSX.utils.aoa_to_sheet([
  [ 1 ], // A1
  [ 2 ], // A2
  [ {t: "n", v: 3, f: "A1+A2"} ] // A3
]);
Live Example (click to hide)

此演示创建一个工作表，其中 A1=1、A2=2 和 A3=A1+A2。

¥This demo creates a worksheet where A1=1, A2=2, and A3=A1+A2.

Result
Loading...
Live Editor
/* The live editor requires this function wrapper */
function ExportSimpleFormula(props) {

  /* Callback invoked when the button is clicked */
  const xport = React.useCallback(() => {
    /* Create worksheet with A1=1, A2=2, A3=A1+A2 */
    var ws = XLSX.utils.aoa_to_sheet([
      [ 1 ], // A1
      [ 2 ], // A2
      [ {t: "n", v: 3, f: "A1+A2"} ] // A3
    ]);

    /* Export to file (start a download) */
    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSFormula1.xlsx");
  });

  return (<button onClick={xport}><b>Export XLSX!</b></button>);
}

包含公式条目但没有值的单元格将以 Excel 和其他电子表格工具可以识别的方式进行序列化。该库不会自动计算公式结果！例如，以下工作表将包含 BESSELJ 函数，但结果在 JavaScript 中不可用：

¥Cells with formula entries but no value will be serialized in a way that Excel and other spreadsheet tools will recognize. This library will not automatically compute formula results! For example, the following worksheet will include the BESSELJ function but the result will not be available in JavaScript:

var worksheet = XLSX.utils.aoa_to_sheet([
  [ 3.14159, 2 ], // Row "1"
  [ { t: "n", f: "BESSELJ(A1,B1)" } ] // Row "2" will be calculated on file open
])

如果 JS 中需要实际结果，SheetJS 专业版 提供了一个公式计算器组件，用于计算表达式、更新值和依赖单元格以及刷新整个工作簿。

¥If the actual results are needed in JS, SheetJS Pro offers a formula calculator component for evaluating expressions, updating values and dependent cells, and refreshing entire workbooks.

数组公式​

¥Array Formulae

分配数组公式

¥Assign an array formula

XLSX.utils.sheet_set_array_formula(worksheet, range, formula);

数组公式存储在数组块的左上角单元格中。数组公式的所有单元格都有一个与范围相对应的 F 字段。单单元格公式可以通过 F 字段的存在来与普通公式区分开来。

¥Array formulae are stored in the top-left cell of the array block. All cells of an array formula have a F field corresponding to the range. A single-cell formula can be distinguished from a plain formula by the presence of F field.

以下代码片段将单元格 C1 设置为数组公式 {=SUM(A1:A3*B1:B3)}：

¥The following snippet sets cell C1 to the array formula {=SUM(A1:A3*B1:B3)}:

// API function
XLSX.utils.sheet_set_array_formula(worksheet, "C1", "SUM(A1:A3*B1:B3)");

// ... OR raw operations
worksheet["C1"] = { t: "n", f: "SUM(A1:A3*B1:B3)", F: "C1:C1" };

对于多单元格数组公式，每个单元格具有相同的数组范围，但只有第一个单元格指定公式。考虑 D1:D3=A1:A3*B1:B3：

¥For a multi-cell array formula, every cell has the same array range but only the first cell specifies the formula. Consider D1:D3=A1:A3*B1:B3:

// API function
XLSX.utils.sheet_set_array_formula(worksheet, "D1:D3", "A1:A3*B1:B3");

// ... OR raw operations
worksheet["D1"] = { t: "n", F: "D1:D3", f: "A1:A3*B1:B3" };
worksheet["D2"] = { t: "n", F: "D1:D3" };
worksheet["D3"] = { t: "n", F: "D1:D3" };

实用程序和编写者应检查 F 字段是否存在，并忽略起始单元格以外的单元格中任何可能的公式元素 f。他们不应该对公式进行验证！

¥Utilities and writers are expected to check for the presence of a F field and ignore any possible formula element f in cells other than the starting cell. They are not expected to perform validation of the formulae!

Live Example (click to show)
Result
Loading...
Live Editor
/* The live editor requires this function wrapper */
function ExportArrayFormulae(props) {

  /* Callback invoked when the button is clicked */
  const xport = React.useCallback(() => {
    /* Starting data */
    var D = [
      [ "A", "B", , "Cell AF", "Expected", , "Range AF", "Expected" ],
      [  1,   2 ],
      [  3,   4 ],
      [  5,   6 ]
    ];
    /* Add expected values */
    let sum = 0;
    for(let i = 1; i < D.length; ++i) sum += (D[i][7] = D[i][0] * D[i][1]);
    D[1][4] = sum;

    /* Create worksheet */
    var ws = XLSX.utils.aoa_to_sheet(D);

    /* D2 single-cell array formula */
    XLSX.utils.sheet_set_array_formula(ws, "D2", "SUM(A2:A4*B2:B4)");

    /* G2:G4 range array formula */
    XLSX.utils.sheet_set_array_formula(ws, "G2:G4", "A2:A4*B2:B4");

    /* Export to file (start a download) */
    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSArrayFormulae.xlsx");
  });

  return (<button onClick={xport}><b>Export XLSX!</b></button>);
}

动态数组公式​

¥Dynamic Array Formulae

分配动态数组公式

¥Assign a dynamic array formula

XLSX.utils.sheet_set_array_formula(worksheet, range, formula, true);

动态数组公式于 2020 年发布，支持 XLSX/XLSM 和 XLSB 文件格式。它们的表示方式与普通数组公式类似，但具有特殊的单元格元数据，指示应允许公式调整范围。

¥Released in 2020, Dynamic Array Formulae are supported in the XLSX/XLSM and XLSB file formats. They are represented like normal array formulae but have special cell metadata indicating that the formula should be allowed to adjust the range.

通过将单元格 D 属性设置为 true，可以将数组公式标记为动态。F 范围是预期的，但可以设置为当前单元格：

¥An array formula can be marked as dynamic by setting the cell D property to true. The F range is expected but can be the set to the current cell:

// API function
XLSX.utils.sheet_set_array_formula(worksheet, "C1", "_xlfn.UNIQUE(A1:A3)", 1);

// ... OR raw operations
worksheet["C1"] = { t: "s", f: "_xlfn.UNIQUE(A1:A3)", F:"C1", D: 1 }; // dynamic
Live Example (click to show)
Result
Loading...
Live Editor
/* The live editor requires this function wrapper */
function ExportDynamicArrayFormulae(props) {

  /* Callback invoked when the button is clicked */
  const xport = React.useCallback(() => {
    /* Starting data */
    var D = [
      [ "A", , "Static", "Expected", , "Dynamic", "Expected" ],
      [  1,  ,         , 1         , ,          ,          1 ],
      [  2,  ,         ,           , ,          ,          2 ],
      [  1, ]
    ];

    /* Create worksheet */
    var ws = XLSX.utils.aoa_to_sheet(D);

    /* C2 static formula */
    XLSX.utils.sheet_set_array_formula(ws, "C2", "_xlfn.UNIQUE(A2:A4)");

    /* F2 dynamic formula */
    XLSX.utils.sheet_set_array_formula(ws, "F2", "_xlfn.UNIQUE(A2:A4)", 1);

    /* Export to file (start a download) */
    var wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    XLSX.writeFile(wb, "SheetJSDynamicFormulae.xlsx");
  });

  return (<button onClick={xport}><b>Export XLSX!</b></button>);
}

本地化​

¥Localization

SheetJS 在文件级别运行。Excel 使用英语（美国）函数名称存储公式表达式。对于非英语用户，Excel 使用一组本地化的函数名称。

¥SheetJS operates at the file level. Excel stores formula expressions using the English (United States) function names. For non-English users, Excel uses a localized set of function names.

例如，当计算机语言和区域设置为西班牙语时，Excel 将 =CONTAR(A1:C3) 解释为 CONTAR 是 COUNT 函数。然而，在实际文件中，Excel 存储的是 COUNT(A1:C3)。

¥For example, when the computer language and region is set to Spanish, Excel interprets =CONTAR(A1:C3) as if CONTAR is the COUNT function. However, in the actual file, Excel stores COUNT(A1:C3).

函数参数用逗号分隔。例如，西班牙语 Excel 公式 =CONTAR(A1:C3;B4:D6) 相当于 SheetJS 公式字符串 COUNT(A1:A3,B4:D6)

¥Function arguments are separated with commas. For example, the Spanish Excel formula =CONTAR(A1:C3;B4:D6) is equivalent to the SheetJS formula string COUNT(A1:A3,B4:D6)

JSON 翻译表。

¥JSON Translation table.

Function Name Translator (click to hide)
Result
Loading...
Live Editor
/* The live editor requires this function wrapper */
function Translator(props) {
  const [locales, setLocales] = React.useState([]);
  const [data, setData] = React.useState({});
  const [names, setNames] = React.useState([]);
  const [name, setName] = React.useState("Enter a function name");
  /* Fetch and display formula */
  React.useEffect(() => { (async() => {
    /* Fetch data */
    const json = await (await fetch("/fmla/table.json")).json();
    setLocales(Object.keys(json));
    setData(json);
    setNames(json.en);
    setName(json.es[0])
  })(); }, []);

  const update_name = React.useCallback(() => {
    const nameelt = document.getElementById("fmla");
    const idx = nameelt.options[nameelt.selectedIndex].value;
    const toelt = document.getElementById("tolocale");
    const tovalue = toelt.options[toelt.selectedIndex].value;
    setName(data[tovalue][idx]);
  });

  const update_from = React.useCallback(() => {
    const fromelt = document.getElementById("fromlocale");
    const fromvalue = fromelt.options[fromelt.selectedIndex].value;
    setNames(data[fromvalue]);
  });

  return ( <>
    <b>Name: </b><select id="fmla" onChange={update_name}>
    {names.map((n, idx) => (<option value={idx}>{n}</option>))}
    </select><br/>
    <b>From: </b><select id="fromlocale" onChange={update_from}>
    {locales.map(l => (<option value={l} selected={l=="en"}>{l}</option>))}
    </select>
    <b> To: </b><select id="tolocale" onChange={update_name}>
    {locales.map(l => (<option value={l} selected={l=="es"}>{l}</option>))}
    </select><br/>
    <b> Translation: </b><pre id="out">{name}</pre>
  </> );
}

前缀 "未来的功能"​

¥Prefixed "Future Functions"

新版本 Excel 中引入的函数在存储在文件中时以 _xlfn. 为前缀。使用这些函数编写公式表达式时，需要前缀以实现最大兼容性：

¥Functions introduced in newer versions of Excel are prefixed with _xlfn. when stored in files. When writing formula expressions using these functions, the prefix is required for maximal compatibility:

// Broadest compatibility
XLSX.utils.sheet_set_array_formula(worksheet, "C1", "_xlfn.UNIQUE(A1:A3)", 1);

// Can cause errors in spreadsheet software
XLSX.utils.sheet_set_array_formula(worksheet, "C1", "UNIQUE(A1:A3)", 1);

读取文件时，xlfn 选项保留前缀。

¥When reading a file, the xlfn option preserves the prefixes.

Functions requiring _xlfn. prefix (click to show)

This list is growing with each Excel release.

ACOT
ACOTH
AGGREGATE
ARABIC
BASE
BETA.DIST
BETA.INV
BINOM.DIST
BINOM.DIST.RANGE
BINOM.INV
BITAND
BITLSHIFT
BITOR
BITRSHIFT
BITXOR
BYCOL
BYROW
CEILING.MATH
CEILING.PRECISE
CHISQ.DIST
CHISQ.DIST.RT
CHISQ.INV
CHISQ.INV.RT
CHISQ.TEST
COMBINA
CONCAT
CONFIDENCE.NORM
CONFIDENCE.T
COT
COTH
COVARIANCE.P
COVARIANCE.S
CSC
CSCH
DAYS
DECIMAL
ECMA.CEILING
ERF.PRECISE
ERFC.PRECISE
EXPON.DIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FIELDVALUE
FILTERXML
FLOOR.MATH
FLOOR.PRECISE
FORECAST.ETS
FORECAST.ETS.CONFINT
FORECAST.ETS.SEASONALITY
FORECAST.ETS.STAT
FORECAST.LINEAR
FORMULATEXT
GAMMA
GAMMA.DIST
GAMMA.INV
GAMMALN.PRECISE
GAUSS
HYPGEOM.DIST
IFNA
IFS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMSEC
IMSECH
IMSINH
IMTAN
ISFORMULA
ISO.CEILING
ISOMITTED
ISOWEEKNUM
LAMBDA
LET
LOGNORM.DIST
LOGNORM.INV
MAKEARRAY
MAP
MAXIFS
MINIFS
MODE.MULT
MODE.SNGL
MUNIT
NEGBINOM.DIST
NETWORKDAYS.INTL
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
NUMBERVALUE
PDURATION
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUTATIONA
PHI
POISSON.DIST
QUARTILE.EXC
QUARTILE.INC
QUERYSTRING
RANDARRAY
RANK.AVG
RANK.EQ
REDUCE
RRI
SCAN
SEC
SECH
SEQUENCE
SHEET
SHEETS
SKEW.P
SORTBY
STDEV.P
STDEV.S
SWITCH
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
TEXTJOIN
UNICHAR
UNICODE
UNIQUE
VAR.P
VAR.S
WEBSERVICE
WEIBULL.DIST
WORKDAY.INTL
XLOOKUP
XOR
Z.TEST

注意事项​

¥Caveats

在某些情况下，看似有效的公式可能会被电子表格软件拒绝。

¥In some cases, seemingly valid formulae may be rejected by spreadsheet software.

EVALUATE 是 WPS Office 中支持的功能。它在 Excel 的单元格公式中无效。导出为 XLSM 格式时，可以在 Excel 定义的名称中使用它，但不能在 XLSX 中使用。这是 Excel 的限制。由于 WPS Office 接受带有 EVALUATE 的文件，因此编写器不会触发警告或抛出错误。

¥EVALUATE is a supported function in WPS Office. It is not valid in a cell formula in Excel. It can be used in an Excel defined name when exporting to XLSM format but not XLSX. This is a limitation of Excel. Since WPS Office accepts files with EVALUATE, the writer does not warn or throw errors.