HTML

HTML is a common format for presenting data in the web. While the general read functions (XLSX.read and XLSX.readFile) can parse HTML strings and the write functions (XLSX.write and XLSX.writeFile) can generate HTML strings, the utility functions in this section can use DOM features.HTML是在网络中显示数据的常见格式。虽然通用读取函数（XLSX.read和XLSX.readFile）可以解析HTML字符串，而写入函数（XLSX.write和XLSX.writeFile）可以生成HTML字符串，但本节中的实用程序函数可以使用DOM功能。

SheetJS CE primarily focuses on data and number formatting.SheetJS CE主要关注数据和数字格式。

SheetJS Pro supports CSS text and cell styles in the HTML format and HTML table utilities.支持HTML格式和HTML表实用程序中的CSS文本和单元格样式。

HTML Table OutputHTML表格输出

Display worksheet data in a HTML table在HTML表中显示工作表数据

var html = XLSX.utils.sheet_to_html(ws, opts);

As an alternative to the writeFile HTML type, XLSX.utils.sheet_to_html also produces HTML output. 作为writeFile HTML类型的替代方案，XLSX.utils.sheet_to_html还生成HTML输出。The function takes an options argument:该函数采用一个选项参数：

Option Name选项名称	Default默认值	Description描述
id		Specify the id attribute for the TABLE element指定TABLE元素的id属性
editable	false	If true, set contenteditable="true" for every TD如果为true，则为每个TD设置contenteditable="true"
header		Override header覆盖标头
footer		Override footer覆盖页脚

Starting from the sample file pres.numbers:从样本文件pres.numbers开始：

Result

Loading...

Live Editor

function SheetJSHTML() { const url = "https://sheetjs.com/pres.numbers"; const [__html, setHTML] = React.useState(""); React.useEffect(() => { (async() => { /* download file and parse */ const wb = XLSX.read(await (await fetch(url)).arrayBuffer()); /* get the first worksheet */ const ws = wb.Sheets[wb.SheetNames[0]]; /* generate HTML */ const html = XLSX.utils.sheet_to_html(ws); setHTML(html); })(); }, []); return ( <> <b>XLSX.utils.sheet_to_html(ws)</b> <div dangerouslySetInnerHTML={{__html}}/> </> ); }

function SheetJSHTML() {
  const url = "https://sheetjs.com/pres.numbers";
  const [__html, setHTML] = React.useState("");
  React.useEffect(() => { (async() => {
    /* download file and parse */
    const wb = XLSX.read(await (await fetch(url)).arrayBuffer());
    /* get the first worksheet */
    const ws = wb.Sheets[wb.SheetNames[0]];

    /* generate HTML */
    const html = XLSX.utils.sheet_to_html(ws);

    setHTML(html);
  })(); }, []);
  return ( <>
    <b>XLSX.utils.sheet_to_html(ws)</b>
    <div dangerouslySetInnerHTML={{__html}}/>
  </> );
}
 

Implementation Details实施细节

The generated table will include special data attributes on each TD element:生成的表格将包括每个TD元素的特殊数据属性：

Attribute属性	Description描述
data-t	Override 覆盖Cell Type单元格类型
data-v	Override Cell Value覆盖单元格值
data-z	Override Number Format替代数字格式

External cell links will be written as A tags wrapping the cell contents.外部单元格链接将被写为包装单元格内容的A标记。

HTML Table InputHTML表格输入

Create New Sheet创建新工作表

Create a worksheet or workbook from a TABLE element从TABLE元素创建工作表或工作簿

var ws = XLSX.utils.table_to_sheet(elt, opts);
var wb = XLSX.utils.table_to_book(elt, opts);

XLSX.utils.table_to_sheet takes a table DOM element and returns a worksheet resembling the input table. 获取一个表DOM元素并返回一个类似于输入表的工作表。Numbers are parsed. All other data will be stored as strings.对数字进行解析。所有其他数据将存储为字符串。

XLSX.utils.table_to_book produces a minimal workbook based on the worksheet.基于工作表生成最小工作簿。

Both functions accept options arguments:两个函数都接受选项参数：

Option Name选项名称	Default默认值	Description描述
raw		If true, every cell will hold raw strings如果为true，则每个单元格都将包含原始字符串
dateNF	FMT 14	Use specified date format in string output在字符串输出中使用指定的日期格式
cellDates	false	Store dates as type d (default is n)将日期存储为类型d（默认为n）
sheetRows	0	If >0, read the first sheetRows rows of the table如果>0，则读取表格的前sheetRows行
display	false	If true, hidden rows and cells will not be parsed如果为true，则不会分析隐藏的行和单元格
UTC	false	If true, dates are interpreted as UTC如果为true，则日期解释为UTC **

UTC option is explained in "Dates"UTC选项在“日期”中有说明

Exporting a table to a spreadsheet file in the web browser involves 3 steps: "find the table", "generate a workbook object", and "export to file".在web浏览器中将表格导出到电子表格文件涉及3个步骤：“查找表格”、“生成工作簿对象”和“导出到文件”。

For example, if the HTML table has id attribute set to sheetjs:例如，如果HTML表的id属性设置为sheetjs：

<table id="sheetjs">
  <tr><th>Name</th><th>Index</th></tr>
  <tr><td>Barack Obama</td><td>44</td></tr>
  <tr><td>Donald Trump</td><td>45</td></tr>
  <tr><td>Joseph Biden</td><td>46</td></tr>
</table>

document.getElementById("sheetjs") is a live reference to the table.是对该表的实时引用。

/* find the table element in the page在页面中查找表元素 */
var tbl = document.getElementById('sheetjs');
/* create a workbook创建工作簿 */
var wb = XLSX.utils.table_to_book(tbl);
/* export to file导出到文件 */
XLSX.writeFile(wb, "SheetJSTable.xlsx");

Demo (click to hide)

This HTML table has id set to sheetjs:此HTML表的id设置为sheetjs：

Name	Index
Barack Obama	44
Donald Trump	45
Joseph Biden	46

Result

Loading...

Live Editor

function SheetJSExportTable() { return ( <button onClick={() => { /* find the table element in the page */ var tbl = document.getElementById('sheetjs'); /* create a workbook */ var wb = XLSX.utils.table_to_book(tbl); /* export to file */ XLSX.writeFile(wb, "SheetJSTable.xlsx"); }}><b>Export XLSX!</b></button> ); }

function SheetJSExportTable() { return ( <button onClick={() => {
  /* find the table element in the page在页面中查找表元素 */
  var tbl = document.getElementById('sheetjs');
  /* create a workbook */
  var wb = XLSX.utils.table_to_book(tbl);
  /* export to file */
  XLSX.writeFile(wb, "SheetJSTable.xlsx");
}}><b>Export XLSX!</b></button> ); }
 

Add to Sheet添加到工作表

Add data from a TABLE element to an existing worksheet将TABLE元素中的数据添加到现有工作表中

XLSX.utils.sheet_add_dom(ws, elt, opts);

XLSX.utils.sheet_add_dom takes a table DOM element and updates an existing worksheet object. 获取一个表DOM元素并更新现有的工作表对象。It follows the same process as table_to_sheet and accepts an options argument:它遵循与table_to_sheet相同的过程，并接受一个options参数：

Option Name选项名称	Default	Description描述
raw		If true, every cell will hold raw strings如果为true，则每个单元格都将包含原始字符串
dateNF	FMT 14	Use specified date format in string output在字符串输出中使用指定的日期格式
cellDates	false	Store dates as type d (default is n)将日期存储为类型d（默认为n）
sheetRows	0	If >0, read the first sheetRows rows of the table如果>0，则读取表格的前sheetRows行
display	false	If true, hidden rows and cells will not be parsed如果为true，则不会分析隐藏的行和单元格
UTC	false	If true, dates are interpreted as UTC如果为true，则日期解释为UTC **

UTC option is explained in "Dates"UTC选项在“日期”中有说明

origin is expected to be one of:预计是以下其中之一：

origin	Description描述
(cell object)	Use specified cell (cell object)使用指定的单元格（单元格对象）
(string)	Use specified cell (A1-Style cell)使用指定的单元格（A1样式单元格）
(number >= 0)	Start from the first column at specified row (0-indexed)从指定行的第一列开始（0索引）
-1	Append to bottom of worksheet starting on first column从第一列开始追加到工作表底部
(default)	Start from cell A1

A common use case for sheet_add_dom involves adding multiple tables to a single worksheet, usually with a few blank rows in between each table:sheet_add_dom的一个常见用例涉及将多个表添加到一个工作表中，通常每个表之间有几行空白：

/* get "table1" and create worksheet */
const table1 = document.getElementById('table1');
const ws = XLSX.utils.table_to_sheet(table1);

/* get "table2" and append to the worksheet */
const table2 = document.getElementById('table2');
XLSX.utils.sheet_add_dom(ws, table2, {origin: -1});

Multi-table Export Example (click to show)

This demo creates a worksheet that should look like the screenshot below:这个演示创建了一个工作表，看起来应该像下面的屏幕截图：

The create_gap_rows helper function expands the worksheet range, adding blank rows between the data tables.create_gap_rows辅助函数扩展工作表范围，在数据表之间添加空行。

Result

Loading...

Live Editor

function MultiTable() { const headers = ["Table 1", "Table2", "Table 3"]; /* Callback invoked when the button is clicked */ const xport = React.useCallback(async () => { /* This function creates gap rows */ function create_gap_rows(ws, nrows) { var ref = XLSX.utils.decode_range(ws["!ref"]); // get original range ref.e.r += nrows; // add to ending row ws["!ref"] = XLSX.utils.encode_range(ref); // reassign row } /* first table */ const ws = XLSX.utils.aoa_to_sheet([[headers[0]]]); XLSX.utils.sheet_add_dom(ws, document.getElementById('table1'), {origin: -1}); create_gap_rows(ws, 1); // one row gap after first table /* second table */ XLSX.utils.sheet_add_aoa(ws, [[headers[1]]], {origin: -1}); XLSX.utils.sheet_add_dom(ws, document.getElementById('table2'), {origin: -1}); create_gap_rows(ws, 2); // two rows gap after second table /* third table */ XLSX.utils.sheet_add_aoa(ws, [[headers[2]]], {origin: -1}); XLSX.utils.sheet_add_dom(ws, document.getElementById('table3'), {origin: -1}); /* create workbook and export */ const wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Export"); XLSX.writeFile(wb, "SheetJSMultiTablexport.xlsx"); }); return ( <> <button onClick={xport}><b>Export XLSX!</b></button><br/><br/> <b>{headers[0]}</b><br/> <table id="table1"> <tr><td>A2</td><td>B2</td></tr> <tr><td>A3</td><td>B3</td></tr> </table> <b>{headers[1]}</b><br/> <table id="table2"> <tr><td>A6</td><td>B6</td><td>C6</td></tr> <tr><td>A7</td><td>B7</td><td>C7</td></tr> </table> <br/> <b>{headers[2]}</b><br/> <table id="table3"> <tr><td>A11</td><td>B11</td></tr> <tr><td>A12</td><td>B12</td></tr> </table> </> ); }

function MultiTable() {
  const headers = ["Table 1", "Table2", "Table 3"];

  /* Callback invoked when the button is clicked */
  const xport = React.useCallback(async () => {
    /* This function creates gap rows */
    function create_gap_rows(ws, nrows) {
      var ref = XLSX.utils.decode_range(ws["!ref"]);       // get original range
      ref.e.r += nrows;                                    // add to ending row
      ws["!ref"] = XLSX.utils.encode_range(ref);           // reassign row
    }

    /* first table */
    const ws = XLSX.utils.aoa_to_sheet([[headers[0]]]);
    XLSX.utils.sheet_add_dom(ws, document.getElementById('table1'), {origin: -1});
    create_gap_rows(ws, 1); // one row gap after first table

    /* second table */
    XLSX.utils.sheet_add_aoa(ws, [[headers[1]]], {origin: -1});
    XLSX.utils.sheet_add_dom(ws, document.getElementById('table2'), {origin: -1});
    create_gap_rows(ws, 2); // two rows gap after second table

    /* third table */
    XLSX.utils.sheet_add_aoa(ws, [[headers[2]]], {origin: -1});
    XLSX.utils.sheet_add_dom(ws, document.getElementById('table3'), {origin: -1});

    /* create workbook and export */
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "Export");
    XLSX.writeFile(wb, "SheetJSMultiTablexport.xlsx");
  });

  return ( <>
    <button onClick={xport}><b>Export XLSX!</b></button><br/><br/>
    <b>{headers[0]}</b><br/>
    <table id="table1">
      <tr><td>A2</td><td>B2</td></tr>
      <tr><td>A3</td><td>B3</td></tr>
    </table>
    <b>{headers[1]}</b><br/>
    <table id="table2">
      <tr><td>A6</td><td>B6</td><td>C6</td></tr>
      <tr><td>A7</td><td>B7</td><td>C7</td></tr>
    </table>
    <br/>
    <b>{headers[2]}</b><br/>
    <table id="table3">
      <tr><td>A11</td><td>B11</td></tr>
      <tr><td>A12</td><td>B12</td></tr>
    </table>
  </> );
}
 

HTML Strings

Create a worksheet or workbook from HTML string从HTML字符串创建工作表或工作簿

table_to_book / table_to_sheet / sheet_add_dom act on HTML DOM elements. Starting from an HTML string, there are two parsing approaches:对HTML DOM元素执行操作。从HTML字符串开始，有两种解析方法：

A) Table Phantasm: create a DIV whose innerHTML is set to the HTML string, generate worksheet using the DOM element, then remove the DIV:表Phantasm：创建一个DIV，其innerHTML设置为HTML字符串，使用DOM元素生成工作表，然后删除DIV：

/* create element from the source */
var elt = document.createElement("div");
elt.innerHTML = html_source;
document.body.appendChild(elt);

/* generate worksheet */
var ws = XLSX.utils.table_to_sheet(elt.getElementsByTagName("TABLE")[0]);

/* remove element */
document.body.removeChild(elt);

Phantasm Demo (click to show)

The html variable in the demo is an editable HTML string演示中的html变量是一个可编辑的html字符串

Result

Loading...

Live Editor

function SheetJSTablePhantasm() { /* HTML stored as a string */ const html = `\ <table> <tr><th>Name</th><th>Index</th></tr> <tr><td>Barack Obama</td><td>44</td></tr> <tr><td>Donald Trump</td><td>45</td></tr> <tr><td>Joseph Biden</td><td>46</td></tr> </table> `; return ( <> <button onClick={() => { /* create element from the source */ var elt = document.createElement("div"); elt.innerHTML = html; document.body.appendChild(elt); /* generate workbook */ var tbl = elt.getElementsByTagName("TABLE")[0]; var wb = XLSX.utils.table_to_book(tbl); /* remove element */ document.body.removeChild(elt); /* generate file */ XLSX.writeFile(wb, "SheetJSTablePhantasm.xlsx"); }}><b>Export XLSX!</b></button> <pre><b>HTML:</b><br/>{html}</pre> </>); }

function SheetJSTablePhantasm() {
  /* HTML stored as a string */
  const html = `\
<table>
  <tr><th>Name</th><th>Index</th></tr>
  <tr><td>Barack Obama</td><td>44</td></tr>
  <tr><td>Donald Trump</td><td>45</td></tr>
  <tr><td>Joseph Biden</td><td>46</td></tr>
</table>
`;
  return ( <>
    <button onClick={() => {
      /* create element from the source */
      var elt = document.createElement("div");
      elt.innerHTML = html;
      document.body.appendChild(elt);

      /* generate workbook */
      var tbl = elt.getElementsByTagName("TABLE")[0];
      var wb = XLSX.utils.table_to_book(tbl);

      /* remove element */
      document.body.removeChild(elt);

      /* generate file */
      XLSX.writeFile(wb, "SheetJSTablePhantasm.xlsx");
    }}><b>Export XLSX!</b></button>
    <pre><b>HTML:</b><br/>{html}</pre>
  </>);
}
 

B) Raw HTML: use XLSX.read to read the text in the same manner as CSV.原始HTML：使用XLSX.read以与CSV相同的方式读取文本。

var wb = XLSX.read(html_source, { type: "string" });
var ws = wb.Sheets[wb.SheetNames[0]];

Raw HTML Demo (click to show)

The html variable in the demo is an editable HTML string演示中的html变量是一个可编辑的html字符串

Result

Loading...

Live Editor

function SheetJSRawHTMLToXLSX() { /* HTML stored as a string */ const html = `\ <table> <tr><th>Name</th><th>Index</th></tr> <tr><td>Barack Obama</td><td>44</td></tr> <tr><td>Donald Trump</td><td>45</td></tr> <tr><td>Joseph Biden</td><td>46</td></tr> </table> `; return ( <> <button onClick={() => { /* read HTML string */ var wb = XLSX.read(html, {type: "string"}); /* generate file */ XLSX.writeFile(wb, "SheetJSRawHTML.xlsx"); }}><b>Export XLSX!</b></button> <pre><b>HTML:</b><br/>{html}</pre> </>); }

function SheetJSRawHTMLToXLSX() {
  /* HTML stored as a string */
  const html = `\
<table>
  <tr><th>Name</th><th>Index</th></tr>
  <tr><td>Barack Obama</td><td>44</td></tr>
  <tr><td>Donald Trump</td><td>45</td></tr>
  <tr><td>Joseph Biden</td><td>46</td></tr>
</table>
`;
  return ( <>
    <button onClick={() => {
      /* read HTML string */
      var wb = XLSX.read(html, {type: "string"});

      /* generate file */
      XLSX.writeFile(wb, "SheetJSRawHTML.xlsx");
    }}><b>Export XLSX!</b></button>
    <pre><b>HTML:</b><br/>{html}</pre>
  </>);
}
 

Value Override值覆盖

When the raw: true option is specified, the parser will generate text cells. When the option is not specified or when it is set to false, the parser will try to interpret the text of each TD element.当指定了raw:true选项时，解析器将生成文本单元格。当未指定该选项或将其设置为false时，解析器将尝试解释每个TD元素的文本。

To override the conversion for a specific cell, the following data attributes can be added to the individual TD elements:要覆盖特定单元格的转换，可以将以下数据属性添加到各个TD元素：

Attribute属性	Description描述
data-t	Override 覆盖Cell Type单元格类型
data-v	Override Cell Value覆盖单元格值
data-z	Override 覆盖Number Format数字格式

For example:例如

<!-- Parser interprets value as `new Date("2012-12-03")` default date formatParser将值解释为`new Date("2012-12-03")`默认日期格式 -->
<td>2012-12-03</td>

<!-- String cell "2012-12-03" -->
<td data-t="s">2012-12-03</td>

<!-- Numeric cell with the correct date code and General format具有正确日期代码和常规格式的数字单元格 -->
<td data-t="n" data-v="41246">2012-12-03</td>

<!-- Traditional Excel Date 2012-12-03 with style yyyy-mm-dd传统Excel日期2012-12-03，样式为yyyy-mm-dd -->
<td data-t="n" data-v="41246" data-z="yyyy-mm-dd">2012-12-03</td>

HTML Value Examples (click to hide)

Result

Loading...

Live Editor

function SheetJSHTMLValueOverride() { /* HTML stored as a string */ const html = `\ <table> <tr><th>Cell</th><th>data-t</th><th>data-v</th><th>data-z</th></tr> <tr><td>2012-12-03</td><td/><td/><td/></tr> <tr><td data-t="s">2012-12-03</td><td>s</td><td/><td/></tr> <tr><td data-t="n" data-v="41246">2012-12-03</td><td>n</td><td>41246</td><td/></tr> <tr><td data-t="n" data-v="41246" data-z="yyyy-mm-dd">2012-12-03</td><td>n</td><td>41246</td><td>yyyy-mm-dd</td></tr> </table> `; return ( <> <button onClick={() => { /* create element from the source */ var elt = document.createElement("div"); elt.innerHTML = html; document.body.appendChild(elt); /* generate workbook */ var tbl = elt.getElementsByTagName("TABLE")[0]; var wb = XLSX.utils.table_to_book(tbl); /* remove element */ document.body.removeChild(elt); /* generate file */ XLSX.writeFile(wb, "SheetJSHTMLValueOverride.xlsx"); }}><b>Export XLSX!</b></button> <pre><b>HTML String:</b><br/>{html}<br/><b>TABLE:</b></pre> <div dangerouslySetInnerHTML={{__html: html}}/> </>); }

function SheetJSHTMLValueOverride() {
  /* HTML stored as a string */
  const html = `\
<table>
  <tr><th>Cell</th><th>data-t</th><th>data-v</th><th>data-z</th></tr>
  <tr><td>2012-12-03</td><td/><td/><td/></tr>
  <tr><td data-t="s">2012-12-03</td><td>s</td><td/><td/></tr>
  <tr><td data-t="n" data-v="41246">2012-12-03</td><td>n</td><td>41246</td><td/></tr>
  <tr><td data-t="n" data-v="41246" data-z="yyyy-mm-dd">2012-12-03</td><td>n</td><td>41246</td><td>yyyy-mm-dd</td></tr>
</table>
`;
  return ( <>
    <button onClick={() => {
      /* create element from the source */
      var elt = document.createElement("div");
      elt.innerHTML = html;
      document.body.appendChild(elt);

      /* generate workbook */
      var tbl = elt.getElementsByTagName("TABLE")[0];
      var wb = XLSX.utils.table_to_book(tbl);

      /* remove element */
      document.body.removeChild(elt);

      /* generate file */
      XLSX.writeFile(wb, "SheetJSHTMLValueOverride.xlsx");
    }}><b>Export XLSX!</b></button>
    <pre><b>HTML String:</b><br/>{html}<br/><b>TABLE:</b></pre>
    <div dangerouslySetInnerHTML={{__html: html}}/>
  </>);
}
 

Synthetic DOM合成DOM

table_to_book / table_to_sheet / sheet_add_dom act on HTML DOM elements. Traditionally there is no DOM in server-side environments including NodeJS.对HTML DOM元素执行操作。传统上，在包括NodeJS在内的服务器端环境中没有DOM。

The simplest approach for server-side processing is to automate a headless web browser. "Browser Automation" covers some browsers.服务器端处理最简单的方法是自动化无头web浏览器。“浏览器自动化”涵盖了一些浏览器。

Some ecosystems provide DOM-like frameworks that are compatible with SheetJS. 一些生态系统提供了与SheetJS兼容的类似DOM的框架。Examples are included in the "Synthetic DOM" demo示例包含在“Synthetic DOM”演示中