Number Formats数字格式

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). 现代应用程序将“内容”与“演示”分开。像$3.50这样的值通常以格式（$0.00）存储为基础值（3.50）。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. 基于文本的文件格式，如CSV和HTML，混合了内容和演示。$3.50被存储为格式化的值。格式化后的值可以由许多不同的值和数字格式生成。SheetJS parsers expose options to control value parsing and number format speculation.SheetJS解析器公开了控制值解析和数字格式推测的选项。

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. QPW(Quattro Pro Workbooks)解析器支持内置日期和内置时间格式，但不支持自定义数字格式。Date and Time support in modern Excel formats requires limited number format support to distinguish date or time codes from standard numeric data.现代Excel格式中的日期和时间支持要求有限的数字格式支持，以区分日期或时间代码与标准数字数据。

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.字母R(R)标记已解析但未按格式编写的特征。

(!) HTML mixes content and presentation. The HTML DOM parser supports special attributes to override number formatsHTML混合了内容和演示。HTML DOM解析器支持覆盖数字格式的特殊属性

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）或带有数千个分隔符的大数字（7,262）或百分比（2.19%）。

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.为了简化编辑，应用程序将分别存储基础值和数字格式。例如，$3.50将表示为值3.5，其数字格式要求使用$sigil和2位小数。

Number format metadata can be attached to each cell object in the z property:数字格式元数据可以附加到z属性中的每个单元格对象：

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

When requested, the cell formatted text will be stored in the w property.当请求时，单元格格式的文本将存储在w属性中。

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 uses the number formats and values to compute the formatted text when generating the HTML table.在生成HTML表时，使用数字格式和值来计算格式化文本。

The "Export" button will write a workbook with number formats. The file can be opened in Excel or another spreadsheet editor. “导出”按钮将使用数字格式编写工作簿。该文件可以在Excel或其他电子表格编辑器中打开。The values in column B will be proper numbers with the assigned number formats.B列中的值将是具有指定数字格式的正确数字。

Result

Loading...

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

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生成HTML表 */
    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 RepresentationSheetJS表示

Number formats and values are attached to cells. The following keys are used:数字格式和值附加到单元格中。使用以下密钥：

Key	Description描述
v	raw value (number, string, Date object, boolean)原始值（数字、字符串、日期对象、布尔值）
z	number format string associated with the cell (if requested)与单元格关联的数字格式字符串（如果请求）
w	formatted text (if applicable)格式化文本（如果适用）

The cell.w formatted text for each cell is produced from cell.v and cell.z format. 每个单元格的cell.w格式文本由cell.v和cell.z格式生成。If the format is not specified, the Excel General format is used.如果未指定格式，则使用ExcelGeneral格式。

By default, parsers do not attach number formats to cells. 默认情况下，解析器不会将数字格式附加到单元格。The cellNF option instructs XLSX.read or XLSX.readFile to save the formats.cellNF选项指示XLSX.read或XLSX.readFile保存格式。

Number Format Strings数字格式字符串

The z format string follows the Excel persistence rules as described in ECMA-376 18.8.31 (Number Formats)z格式字符串遵循ECMA-376 18.8.31（数字格式）中描述的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.这些规则与Excel显示自定义数字格式的方式略有不同。特别是，文字字符必须用双引号括起来或前面加一个反斜杠。

The following example prints number formats from a user-specified file:以下示例打印用户指定文件中的数字格式：

Result

Loading...

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

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日期和时间

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.在扩展了Lotus 1-2-3工作表文件格式的XLS和其他文件格式中，日期和时间存储为数字代码。应用程序使用数字格式来确定是否应将该值解释为日期。

Interpretation of date codes is covered in "Dates and Times".日期代码的解释见“日期和时间”。

The following repeatable tokens force a date interpretation:以下可重复标记强制进行日期解释：

Tokens	Description描述
Y	Year年
M	Month or Minute (contextual)月或分钟（上下文）
D	Day天
H	Hours (0-23 normally, but 1-12 if meridiem is present)小时数（通常为0-23，但如果存在子午线，则为1-12）
S	Seconds秒
A/P or AM/PM	Meridiem
[h] or [hh]	Absolute hours (duration)绝对小时数（持续时间）
[m] or [mm]	Absolute minutes (duration)绝对分钟数（持续时间）
[s] or [ss]	Absolute seconds (duration)绝对秒数（持续时间）
B1 or B2	Use Gregorian Calendar (B1) or Hijri Calendar (B2)使用公历（B1）或回历（B2）
E	"Era Year" or standard year depending on locale“时代年”或标准年，具体取决于地区
G	"Era" modifier or empty string depending on locale“Era”修饰符或空字符串，具体取决于区域设置

If a format is detected to be a date, the decimal tokens .0, .00 and .000 represent the sub-second portion of the time.如果检测到一种格式是日期，则十进制标记.0、.00和.000表示时间的亚秒部分。

Percentages百分比

Percentage formats automatically scale values by 100. Multiple percent symbols repeat the effect. 百分比格式会自动将值缩放100。多个百分比符号重复效果。For example, a cell with value 2.19% is typically stored as a numeric cell with value 0.0219 and number format 0.00%例如，值为2.19%的单元格通常存储为值为0.0219、数字格式为0.00%的数字单元格

The following table uses the en-US locale (. as the decimal point symbol). Formatted text is rendered using the embedded SheetJS SSF formatting library.下表使用en-US语言环境（.作为小数点符号）。格式化后的文本使用嵌入的SheetJS SSF格式库进行呈现。

Result

Loading...

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

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.从数学角度来看，最优解是“Mediant”方法。在最坏的情况下，这种算法可能非常慢，因此电子表格应用程序倾向于使用连续分数方法。

The common algorithm produces unexpected results for "Up to one digit":常见的算法会为“最多一位数”产生意外结果：

Value	Mediant	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杂项

The default formats are listed in ECMA-376 18.8.30: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	@

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. 格式14（m/d/yy）由Excel本地化：即使文件指定了数字格式，也会根据系统设置进行不同的绘制。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.为了避免这种歧义，解析函数接受dateNF选项来覆盖对特定格式字符串的解释。

Excel Format CategoriesExcel格式类别

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:Excel正式将一小部分格式识别为“货币”，另一组格式识别为”会计“。以下列出了en-US的确切格式：

Currency

JS String	Decimal Places小數位	Negative Color负片颜色
'"$"#,##0_);\\("$"#,##0\\)'	0	Black
'"$"#,##0_);[Red]\\("$"#,##0\\)'	0	Red
'"$"#,##0.00_);\\("$"#,##0.00\\)'	2	Black
'"$"#,##0.00_);[Red]\\("$"#,##0.00\\)'	2	Red

Accounting会计

JS String	Decimal十进制的	Sigil
'_(* #,##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 OverrideHTML覆盖

This feature is discussed in the HTML utilities sectionHTML实用程序部分将讨论此功能

Plaintext Export明文导出

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. 使用格式化文本的内置实用程序（如CSV导出器）将使用w文本（如果可用）。以编程方式更改值时，应在尝试导出之前删除w文本。Utilities will regenerate the w text from the number format (cell.z) and the raw value if possible.如果可能的话，实用程序将从数字格式（cell.z）和原始值重新生成w文本。

---

1. On 2023 November 04, the "Review guidelines for customizing a number format" page in the Excel documentation covered custom number format minutiae.2023年11月4日，Excel文档中的“自定义数字格式的审查指南”页面涵盖了自定义数字格式细节。↩