数字格式

File Format Support (click to show)

Modern applications separate "content" from "presentation". A value like $3.50 is typically stored as the underlying value (3.50) with a format ($0.00). Parsers are expected to render values using the respective number formats.

Text-based file formats like CSV and HTML mix content and presentation. $3.50 is stored as the formatted value. The formatted values can be generated from many different values and number formats. SheetJS parsers expose options to control value parsing and number format speculation.

Formats	Basic	Storage Representation
XLSX / XLSM	✔	Number Format Code
XLSB	✔	Number Format Code
XLS	✔	Number Format Code
XLML	✔	Number Format Code
SYLK	R	Number Format Code
ODS / FODS / UOS	✔	XML Tokens
NUMBERS		Binary encoding
WK1	+	Fixed set of formats
WK3 / WK4		Binary encoding
WKS Lotus	+	Fixed set of formats
WKS Works	+	Fixed set of formats
WQ1	+	Fixed set of formats
WQ2		Binary encoding
WB1 / WB2 / WB3		Binary encoding
QPW	+	Binary encoding
DBF		Implied by field types
HTML	!	Special override
CSV	*	N/A
PRN	*	N/A
DIF	*	N/A
RTF	*	N/A

(+) mark formats with limited support. The QPW (Quattro Pro Workbooks) parser supports the built-in date and built-in time formats but does not support custom number formats. Date and Time support in modern Excel formats requires limited number format support to distinguish date or time codes from standard numeric data.

Asterisks (*) mark formats that mix content and presentation. Writers will use formatted values if cell objects include formatted text or number formats. Parsers may guess number formats for special values.

The letter R (R) marks features parsed but not written in the format.

(!) HTML mixes content and presentation. The HTML DOM parser supports special attributes to override number formats

通常，电子表格将包含格式化文本，例如货币 ($3.50) 或带有千位分隔符 (7,262) 或百分比 (2.19%) 的大数字。

¥Typically spreadsheets will include formatted text such as currencies ($3.50) or large numbers with thousands separators (7,262) or percentages (2.19%).

为了简化编辑，应用将单独存储基础值和数字格式。例如，$3.50 将表示为值 3.5，其数字格式要求 $ 印记和 2 位小数。

¥To simplify editing, the applications will store the underlying values and the number formats separately. For example, $3.50 will be represented as the value 3.5 with a number format that mandates a $ sigil and 2 decimal places.

SheetJS 单元格对象的 z 属性存储数字格式元数据：

¥The z property of SheetJS cell objects stores the number format metadata:

/* set the format of cell B2 to "0.00%" */
worksheet["B2"].z = "0.00%";

当请求时，格式化文本将存储在 w 属性中。

¥When requested, the formatted text will be stored in the w property.

在线演示

¥Live Demo

此示例生成具有常见数字格式的工作表。数字格式已明确指定：

¥This example generates a worksheet with common number formats. The number formats are explicitly assigned:

/* assign number formats */
ws["B2"].z = '"$"#,##0.00_);\\("$"#,##0.00\\)'; // Currency format
ws["B3"].z = '#,##0'; // Number with thousands separator
ws["B4"].z = "0.00%"; // Percentage with up to 2 decimal places

sheet_to_html 在生成 HTML 表时使用数字格式和值来计算格式化文本。

¥sheet_to_html uses the number formats and values to compute the formatted text when generating the HTML table.

"导出" 按钮将编写具有数字格式的工作簿。该文件可以在 Excel 或其他电子表格编辑器中打开。B 列中的值将是具有指定数字格式的正确数字。

¥The "Export" button will write a workbook with number formats. The file can be opened in Excel or another spreadsheet editor. The values in column B will be proper numbers with the assigned number formats.

Result

Live Editor

function SheetJSSimpleNF(props) {
  const [ws, setWS] = React.useState();
  const [__html, setHTML] = React.useState("");
  const fmt = React.useRef(null);

  /* when the page is loaded, create worksheet and show table */
  React.useEffect(() => {
    /* Create worksheet from simple data */
    const ws = XLSX.utils.aoa_to_sheet([
      ["General",   54337      ],
      ["Currency",      3.5    ],
      ["Thousands",  7262      ],
      ["Percent",       0.0219 ],
    ]);

    /* assign number formats */
    ws["B2"].z = '"$"#,##0.00_);\\("$"#,##0.00\\)';
    ws["B3"].z = '#,##0';
    ws["B4"].z = "0.00%";

    /* save worksheet object for the export */
    setWS(ws);
    /* generate the HTML table */
    setHTML(XLSX.utils.sheet_to_html(ws));
  }, []);

  const xport = (fmt) => {
    /* Export to file (start a download) */
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Formats");
    XLSX.writeFile(wb, `SheetJSSimpleNF.${fmt}`);
  };

  const fmts = ["xlsx", "xls", "csv", "xlsb", "html", "ods"];
  return ( <>
    <b>File format: </b>
    <select ref={fmt}>{fmts.map(f=>(<option value={f}>{f}</option>))}</select>
    <br/><button onClick={()=>xport(fmt.current.value)}><b>Export!</b></button>
    <div dangerouslySetInnerHTML={{__html}}/>
  </> );
}

SheetJS 表示

¥SheetJS Representation

数字格式和值附加到单元格。使用以下键：

¥Number formats and values are attached to cells. The following keys are used:

键	描述
`v`	原始值（数字、字符串、日期对象、布尔值）
`z`	与单元格关联的数字格式字符串（如果需要）
`w`	格式化文本（如果适用）

每个单元格的 cell.w 格式文本是根据 cell.v 和 cell.z 格式生成的。如果未指定格式，则使用 Excel General 格式。

¥The cell.w formatted text for each cell is produced from cell.v and cell.z format. If the format is not specified, the Excel General format is used.

默认情况下，解析器不会将数字格式附加到单元格。cellNF 选项指示 XLSX.read 或 XLSX.readFile 保存格式。

¥By default, parsers do not attach number formats to cells. The cellNF option instructs XLSX.read or XLSX.readFile to save the formats.

数字格式字符串

¥Number Format Strings

z 格式字符串遵循 ECMA-376 18.8.31（数字格式）[^1] 中所述的 Excel 持久性规则

¥The z format string follows the Excel persistence rules as described in ECMA-376 18.8.31 (Number Formats)[^1]

这些规则与 Excel 显示自定义数字格式的方式略有不同。特别是，字面量字符必须用双引号括起来或前面有反斜杠。

¥The rules are slightly different from how Excel displays custom number formats. In particular, literal characters must be wrapped in double quotes or preceded by a backslash.

以下示例从用户指定的文件中打印数字格式：

¥The following example prints number formats from a user-specified file:

Result

Live Editor

function SheetJSExtractNF(props) {
  const [rows, setRows] = React.useState([])

  return ( <>
    <input type="file" onChange={async(e) => {
      /* parse workbook with cellNF: true */
      const wb = XLSX.read(await e.target.files[0].arrayBuffer(), {cellNF: true});

      /* look at each cell in each worksheet */
      const formats = {};
      wb.SheetNames.forEach(n => {
        var ws = wb.Sheets[n]; if(!ws || !ws["!ref"]) return;
        var ref = XLSX.utils.decode_range(ws["!ref"]);
        for(var R = 0; R <= ref.e.r; ++R) for(var C = 0; C <= ref.e.c; ++C) {
          var addr = XLSX.utils.encode_cell({r:R,c:C});
          if(!ws[addr] || !ws[addr].z || formats[ws[addr].z]) continue;
          /* when a new format is found, save the address */
          formats[ws[addr].z] = `'${n}'!${addr}`;
          setRows(Object.entries(formats));
        }
      });
    }}/>
    <table><tr><th>Format</th><th>JSON</th><th>Example Cell</th></tr>
    {rows.map((r,R) => ( <tr key={R}>
      <td><code>{r[0]}</code></td>
      <td><code>{JSON.stringify(r[0])}</code></td>
      <td>{r[1]}</td>
    </tr> ))}
    </table>
  </> );
}

值和格式

¥Values and Formatting

日期和时间

¥Dates and Times

在 XLS 和扩展 Lotus 1-2-3 工作表文件格式的其他文件格式中，日期和时间存储为数字代码。应用使用数字格式来确定是否应将值解释为日期。

¥In XLS and other file formats that extended the Lotus 1-2-3 worksheet file format, dates and times are stored as numeric codes. The application uses the number format to determine whether the value should be interpreted as a date.

日期代码的解释包含在 "日期和时间" 中。

¥Interpretation of date codes is covered in "Dates and Times".

以下可重复标记强制进行日期解释：

¥The following repeatable tokens force a date interpretation:

令牌	描述
`Y`	年
`M`	月或分钟（上下文）
`D`	天
`H`	小时（通常为 0-23，但如果有子午线，则为 1-12）
`S`	秒数
`A/P` 或 `AM/PM`	子午线
`[h]` 或 `[hh]`	绝对小时数（持续时间）
`[m]` 或 `[mm]`	绝对分钟（持续时间）
`[s]` 或 `[ss]`	绝对秒（持续时间）
`B1` 或 `B2`	使用公历 (`B1`) 或回历 (`B2`)
`E`	"时代年份" 或标准年份，具体取决于区域设置
`G`	"时代" 修饰符或空字符串，具体取决于区域设置

如果检测到格式为日期，则十进制标记 .0、.00 和 .000 表示时间的亚秒部分。

¥If a format is detected to be a date, the decimal tokens .0, .00 and .000 represent the sub-second portion of the time.

百分比

¥Percentages

百分比格式会自动将值缩放 100。多个百分比符号重复效果。例如，具有值 2.19% 的单元格通常存储为具有值 0.0219 和数字格式 0.00% 的数字单元格

¥Percentage formats automatically scale values by 100. Multiple percent symbols repeat the effect. For example, a cell with value 2.19% is typically stored as a numeric cell with value 0.0219 and number format 0.00%

下表使用 en-US 区域设置（. 作为小数点符号）。格式化文本使用嵌入式 SheetJS SSF 格式化库渲染。

¥The following table uses the en-US locale (. as the decimal point symbol). Formatted text is rendered using the embedded SheetJS SSF formatting library.

Result

Live Editor

function SheetJSPCT() {
  const data = [
    { n: 0.0219, z: "0.00%"},
    { n: 2.19,   z: "0.00%"},
    { n: 0.0219, z: "0.00%%"},
    { n: 2.19,   z: "0.00%%"},
  ];
  return ( <table><tr><th>Number</th><th>Format</th><th>Text</th></tr>
    {data.map(r => (<tr>
      <td><code>{r.n}</code></td>
      <td><code>{r.z}</code></td>
      <td><code>{XLSX.SSF.format(r.z, r.n)}</code></td>
    </tr>))}
  </table> );
}

分数

¥Fractions

某些应用支持以小数形式显示数字。

¥Some applications support displaying numbers in fractional form.

具有固定分母的分数是通过对数字的小数部分进行缩放和四舍五入来计算的。

¥Fractions with a fixed denominator are calculated by scaling and rounding the fractional part of the number.

具有可变分母的分数通常由分母中的位数指定（例如，"最多一位数"）。

¥Fractions with a variable denominator are typically specified by the number of digits in the denominator (for example, "Up to one digit").

从数学角度来看，最优解是 "中音" 方法。在最坏的情况下，该算法可能非常慢，因此电子表格应用倾向于使用连分数方法。

¥The optimal solution from a mathematical perspective is the "Mediant" method. This algorithm can be very slow in the worst case, so spreadsheet applications tend to use a continued fraction approach.

通用算法对 "最多一位数" 产生意想不到的结果：

¥The common algorithm produces unexpected results for "Up to one digit":

值	中音	Excel 2019
`0.3`	`2/7`	`2/7`
`1.3`	`1 2/7`	`1 1/3`
`2.3`	`2 2/7`	`2 2/7`
`3.3`	`3 2/7`	`3 2/7`

杂项

¥Miscellany

ECMA-376 18.8.30 中列出了默认格式：

¥The default formats are listed in ECMA-376 18.8.30:

Default Number Formats (click to show)

ID	Format
0	`General`
1	`0`
2	`0.00`
3	`#,##0`
4	`#,##0.00`
9	`0%`
10	`0.00%`
11	`0.00E+00`
12	`# ?/?`
13	`# ??/??`
14	`m/d/yy` (see below)
15	`d-mmm-yy`
16	`d-mmm`
17	`mmm-yy`
18	`h:mm AM/PM`
19	`h:mm:ss AM/PM`
20	`h:mm`
21	`h:mm:ss`
22	`m/d/yy h:mm`
37	`#,##0 ;(#,##0)`
38	`#,##0 ;[Red](#,##0)`
39	`#,##0.00;(#,##0.00)`
40	`#,##0.00;[Red](#,##0.00)`
45	`mm:ss`
46	`[h]:mm:ss`
47	`mmss.0`
48	`##0.0E+0`
49	`@`

格式 14 (m/d/yy) 由 Excel 本地化：即使文件指定了数字格式，它也会根据系统设置以不同的方式绘制。当文件的生产者和消费者位于同一区域设置时这是有意义的，但在 Internet 上情况并非总是如此。为了解决这种歧义，解析函数接受 dateNF 选项来覆盖该特定格式字符串的解释。

¥Format 14 (m/d/yy) is localized by Excel: even though the file specifies that number format, it will be drawn differently based on system settings. It makes sense when the producer and consumer of files are in the same locale, but that is not always the case over the Internet. To get around this ambiguity, parse functions accept the dateNF option to override the interpretation of that specific format string.

Excel 格式类别

¥Excel Format Categories

Excel 正式将少数格式识别为 "货币"，将另一组格式识别为 "会计"。en-US 中的具体格式如下：

¥Excel officially recognizes a small number of formats as "Currency" and another set of formats as "Accounting". The exact formats in en-US are listed below:

货币

¥Currency

JS 字符串	小数位	负色
`'"$"#,##0_);\$"$"#,##0\$'`	0	黑色的
`'"$"#,##0_);[Red]\$"$"#,##0\$'`	0	红色的
`'"$"#,##0.00_);\$"$"#,##0.00\$'`	2	黑色的
`'"$"#,##0.00_);[Red]\$"$"#,##0.00\$'`	2	红色的

会计

¥Accounting

JS 字符串	十进制	印记
`'_(* #,##0_);_(* \$#,##0\$;_(* "-"_);_(@_)'`	0
`'_("$"* #,##0_);_("$"* \$#,##0\$;_("$"* "-"_);_(@_)'`	0	`$`
`'_(* #,##0.00_);_(* \$#,##0.00\$;_(* "-"??_);_(@_)'`	2
`'_("$"* #,##0.00_);_("$"* \$#,##0.00\$;_("$"* "-"??_);_(@_)'`	2	`$`

对于其他语言环境，可以通过创建具有所需格式的文件并使用数字格式字符串演示进行测试来发现格式

¥For other locales, the formats can be discovered by creating a file with the desired format and testing with the Number Format Strings demo

HTML 覆盖

¥HTML Override

此功能在 HTML 实用程序部分中讨论

¥This feature is discussed in the HTML utilities section

明文导出

¥Plaintext Export

使用格式化文本的内置实用程序（例如 CSV 导出器）将使用 w 文本（如果可用）。以编程方式更改值时，应在尝试导出之前删除 w 文本。如果可能，实用程序将从数字格式 (cell.z) 和原始值重新生成 w 文本。

¥Built-in utilities that use formatted text (such as the CSV exporter) will use the w text if available. When programmatically changing values, the w text should be deleted before attempting to export. Utilities will regenerate the w text from the number format (cell.z) and the raw value if possible.

[^1]: Excel 文档中的 "查看自定义数字格式的指南" 页涵盖了自定义数字格式的细节。

¥The "Review guidelines for customizing a number format" page in the Excel documentation covered custom number format minutiae.

JS 字符串	小数位	负色
`'"$"#,##0_);\\("$"#,##0\\)'`	0	黑色的
`'"$"#,##0_);[Red]\\("$"#,##0\\)'`	0	红色的
`'"$"#,##0.00_);\\("$"#,##0.00\\)'`	2	黑色的
`'"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'`	2	红色的

JS 字符串	十进制	印记
`'_(* #,##0_);_(* \\(#,##0\\);_(* "-"_);_(@_)'`	0
`'_("$"* #,##0_);_("$"* \\(#,##0\\);_("$"* "-"_);_(@_)'`	0	`$`
`'_(* #,##0.00_);_(* \\(#,##0.00\\);_(* "-"??_);_(@_)'`	2
`'_("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"* "-"??_);_(@_)'`	2	`$`

在线演示​

SheetJS 表示​

数字格式字符串​

值和格式​

日期和时间​

百分比​

分数​

杂项​

Excel 格式类别​

货币​

会计​

HTML 覆盖​

明文导出​

在线演示

SheetJS 表示

数字格式字符串

值和格式

日期和时间

百分比

分数

杂项

Excel 格式类别

货币

会计

HTML 覆盖

明文导出