Formulae公式

~~Formulae 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.~~解析器将从存储表示转换为A1样式字符串，而编写器将从A1样式字符串转换为文件格式。

~~Formats~~格式	~~Parse~~作语法分析	~~Write~~写	~~Array~~数组	~~Dynamic~~动态	~~Storage Representation~~存储表示
XLSX / XLSM	✔	✔	✔	✔	A1-style strings
XLSB	✔		✔	✔	~~BIFF parsed tokens~~BIFF解析令牌
XLS	✔		✔		~~BIFF parsed tokens~~BIFF解析令牌
XLML	✔	✔	✔		~~RC-style strings~~RC样式字符串
SYLK	✔	✔			~~A1 / RC-style strings~~A1/RC样式字符串
CSV / TXT	✔	✔			~~A1-style strings~~A1样式字符串
ODS / FODS / UOS	✔	✔			~~OpenFormula strings~~OpenFormula字符串
WK*	✔				~~Lotus parsed tokens~~Lotus解析令牌
WQ* / WB* / QPW					~~Quattro Pro tokens~~Quattro Pro代币
NUMBERS					~~Numbers parsed tokens~~数字解析令牌

~~SheetJS supports reading and writing formulae for a number of file formats. When supported, formulae will always be exported.~~SheetJS支持读取和写入多种文件格式的公式。支持时，将始终导出公式。

~~By default, formulae are not always imported.~~ 默认情况下，并不总是导入公式。~~To ensure formula parsing, the option cellFormula: true should be passed to the parser.~~为了确保公式解析，应将选项cellFormula:true传递给解析器。

Browser
NodeJS
Deno

~~Typically file data will be available as an ArrayBuffer, either downloaded with fetch / XMLHttpRequest or user-submitted with a File Input element.~~ 通常，文件数据将作为ArrayBuffer提供，可以通过fetch/XMLHttpRequest下载，也可以通过文件输入元素提交给用户。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 });
// ------------------------------^^^^^^^^^^^^^^^^^

~~Typically file data will be available as a Buffer from a network request / API or stored in the filesystem.~~ 通常，文件数据将作为网络请求/API的Buffer或存储在文件系统中。cellFormula: true ~~should be added to the second options argument to read or readFile:~~应添加到第二个选项参数中，为read或readFile：

XLSX.read

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

XLSX.readFile

/* using readFile in NodeJS, add `cellFormula` to the second argument */
const workbook = XLSX.readFile("test.xlsx", { cellFormula: true });
// -------------------------------------------^^^^^^^^^^^^^^^^^

~~Typically file data will be available as a Uint8Array / ArrayBuffer from an API or stored in the filesystem.~~ 通常，文件数据将以Uint8Array/ArrayBuffer的形式从API中提供，或存储在文件系统中。cellFormula: true ~~should be added to the second options argument to read or readFile:~~应添加到第二个选项参数中为read或readFile：

XLSX.read

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

XLSX.readFile

/* using readFile in Deno, add `cellFormula` to the second argument */
const workbook = XLSX.readFile("test.xlsx", { cellFormula: true });
// -------------------------------------------^^^^^^^^^^^^^^^^^

A1-Style FormulaeA1样式公式

~~The A1-style formula string is stored in the f field of the cell object.~~ A1样式的公式字符串存储在单元格对象的f字段中。~~Spreadsheet software typically represent formulae with a leading = sign, but SheetJS formulae omit the =.~~电子表格软件通常用前导的=符号表示公式，但SheetJS公式忽略了=。

"A1-Style" ~~describes A1 style in more detail.~~更详细地描述A1样式。

~~For example, consider this test file:~~例如，考虑以下测试文件：

D1=CONCAT("Sheet", "JS")

Result

Live Editor

/* The live editor requires this function wrapper */
function ConcatFormula(props) {
  const [text, setText] = React.useState([]);

  /* Fetch and display formula */
  React.useEffect(async() => {
    /* Fetch file */
    const ab = await (await fetch("/files/concat.xlsx")).arrayBuffer();

    /* Parse file */
    const wb = XLSX.read(ab, {cellFormula: true});
    const ws = wb.Sheets[wb.SheetNames[0]];

    /* Look at cell D1 */
    const addr = "D1";
    const { t, v, f } = ws[addr];
    setText(`\
CELL ADDRESS: ${addr}\n\
CELL FORMULA: ${f}\n\
VALUE (TYPE): "${v}" ("${t}")\n\
`);
  }, []);

  return (<pre>{text}</pre>);
}

/* The live editor requires this function wrapper */
function ConcatFormula(props) {
  const [text, setText] = React.useState([]);

  /* Fetch and display formula */
  React.useEffect(async() => {
    /* Fetch file */
    const ab = await (await fetch("/files/concat.xlsx")).arrayBuffer();

    /* Parse file */
    const wb = XLSX.read(ab, {cellFormula: true});
    const ws = wb.Sheets[wb.SheetNames[0]];

    /* Look at cell D1 */
    const addr = "D1";
    const { t, v, f } = ws[addr];
    setText(`\
CELL ADDRESS: ${addr}\n\
CELL FORMULA: ${f}\n\
VALUE (TYPE): "${v}" ("${t}")\n\
`);
  }, []);

  return (<pre>{text}</pre>);
}

Single-Cell Formulae单个单元格公式

~~For simple formulae, the f key of the desired cell can be set to the actual formula text.~~ 对于简单公式，可以将所需单元格的f键设置为实际公式文本。~~This worksheet represents A1=1, A2=2, and A3=A1+A2:~~此工作表表示A1=1、A2=2和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
};

~~Utilities like aoa_to_sheet will accept cell objects in lieu of values:~~像aoa_to_sheet这样的实用程序将接受单元格对象来代替值：

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

~~Live Example~~实例 (click to show)

Result

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>
  </>);
}

/* 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>
  </>);
}

~~Cells with formula entries but no value will be serialized in a way that Excel and other spreadsheet tools will recognize.~~ 带有公式条目但没有值的单元格将以Excel和其他电子表格工具可以识别的方式序列化。~~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:~~例如，以下工作表将包括BESSELJ函数，但结果在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
])

~~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.~~如果JS中需要实际结果，SheetJS Pro提供了一个公式计算器组件，用于计算表达式、更新值和相关单元格，以及刷新整个工作簿。

Array Formulae数组公式

~~Assign an array formula~~指定数组公式

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

~~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.~~ 数组公式的所有单元格都有一个对应于范围的F字段。~~A single-cell formula can be distinguished from a plain formula by the presence of F field.~~通过F字段的存在，可以将单个单元格公式与普通公式区分开来。

~~The following snippet sets cell C1 to the array formula {=SUM(A1:A3*B1:B3)}:~~以下代码段将单元格C1设置为数组公式{=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" };

~~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:~~考虑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" };

~~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.~~ 实用程序和编写器应检查是否存在F字段，并忽略除起始单元格外的单元格中任何可能的公式元素f。~~They are not expected to perform validation of the formulae!~~他们不需要对公式进行验证！

Dynamic Array Formulae动态数组公式

~~Assign a dynamic array formula~~指定动态数组公式

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

~~Released in 2020, Dynamic Array Formulae are supported in the XLSX/XLSM and XLSB file formats.~~ 2020年发布的动态数组公式支持XLSX/XLSM和XLSB文件格式。~~They are represented like normal array formulae but have special cell metadata indicating that the formula should be allowed to adjust the range.~~它们像普通数组公式一样表示，但具有特殊的单元元数据，表明应允许公式调整范围。

~~An array formula can be marked as dynamic by setting the cell D property to true.~~ 通过将单元格D属性设置为true，可以将数组公式标记为动态。~~The F range is expected but can be the set to the current cell:~~F范围是预期的，但可以设置为当前单元格：

// 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

Localization本地化

~~SheetJS operates at the file level. Excel stores formula expressions using the English (United States) function names.~~ SheetJS在文件级运行。Excel使用英语（美国）函数名存储公式表达式。~~For non-English users, Excel uses a localized set of function names.~~对于非英语用户，Excel使用一组本地化的函数名。

~~For example, when the computer language and region is set to Spanish, Excel interprets =CONTAR(A1:C3) as if CONTAR is the COUNT function.~~ 例如，当计算机语言和区域设置为西班牙语时，Excel会将=CONTAR(A1:C3)解释为CONTAR是COUNT函数。~~However, in the actual file, Excel stores COUNT(A1:C3).~~然而，在实际文件中，Excel存储COUNT(A1:C3)。

~~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)~~例如，西班牙语Excel公式=CONTAR(A1:C3;B4:D6)等效于SheetJS公式字符串COUNT(A1:A3,B4:D6)

~~JSON Translation table~~JSON转换表.。

~~Function Name Translator~~函数名转换器 (click to show)

Result

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("https://oss.sheetjs.com/notes/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>
  </>);
}

/* 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("https://oss.sheetjs.com/notes/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"前缀为“未来函数”

~~Functions introduced in newer versions of Excel are prefixed with _xlfn. when stored in files.~~ 当存储在文件中时，新版本的Excel中引入的函数前缀为_xlfn.。~~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);

~~When reading a file, the xlfn option preserves the prefixes.~~读取文件时，xlfn选项保留前缀。

~~Functions requiring `_xlfn.` prefix~~需要`_xlfn.`的函数前缀 (click to show)

~~This list is growing with each Excel release.~~该列表随着每个Excel版本的发布而增加。

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
CONFIDENCE.NORM
CONFIDENCE.T
COT
COTH
COVARIANCE.P
COVARIANCE.S
CSC
CSCH
DAYS
DECIMAL
ERF.PRECISE
ERFC.PRECISE
EXPON.DIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FIELDVALUE
FILTERXML
FLOOR.MATH
FLOOR.PRECISE
FORMULATEXT
GAMMA
GAMMA.DIST
GAMMA.INV
GAMMALN.PRECISE
GAUSS
HYPGEOM.DIST
IFNA
IMCOSH
IMCOT
IMCSC
IMCSCH
IMSEC
IMSECH
IMSINH
IMTAN
ISFORMULA
ISOMITTED
ISOWEEKNUM
LAMBDA
LET
LOGNORM.DIST
LOGNORM.INV
MAKEARRAY
MAP
MODE.MULT
MODE.SNGL
MUNIT
NEGBINOM.DIST
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
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
UNICHAR
UNICODE
UNIQUE
VAR.P
VAR.S
WEBSERVICE
WEIBULL.DIST
XLOOKUP
XOR
Z.TEST

Formulae公式

A1-Style FormulaeA1样式公式​

Single-Cell Formulae单个单元格公式​

Array Formulae数组公式​

Dynamic Array Formulae动态数组公式​

Localization本地化​

Prefixed "Future Functions"前缀为“未来函数”​

A1-Style FormulaeA1样式公式

Single-Cell Formulae单个单元格公式

Array Formulae数组公式

Dynamic Array Formulae动态数组公式

Localization本地化

Prefixed "Future Functions"前缀为“未来函数”