Skip to main content

Data Export数据导出

Writing Workbooks编写工作簿​

API​

Generate spreadsheet bytes (file) from data从数据生成电子表格字节（文件）

vardata=XLSX.write(workbook,opts);

The write method attempts to package data from the workbook into a file in memory. write方法尝试将工作簿中的数据打包到内存中的文件中。By default, XLSX files are generated, but that can be controlled with the bookType property of the opts argument. 默认情况下，会生成XLSX文件，但这可以通过opts参数的bookType属性进行控制。Based on the type option, the data can be stored as a "binary string", JS string, Uint8Array or Buffer.根据type选项，数据可以存储为“二进制字符串”、JS字符串、Uint8Array或缓冲区。

The second opts argument is required. 第二个opts参数是必需的。"Writing Options"“写入选项” covers the supported properties and behaviors.涵盖支持的属性和行为。

Generate and attempt to save file生成并尝试保存文件

XLSX.writeFile(workbook,filename,opts);

The writeFile method packages the data and attempts to save the new file. writeFile方法打包数据并尝试保存新文件。The export file format is determined by the extension of filename (SheetJS.xlsx signals XLSX export, SheetJS.xlsb signals XLSB export, etc).导出文件格式由filename的扩展名决定（SheetJS.xlsx信号XLSX导出、SheetJS.xlsb信号XLSB导出等）。

The second opts argument is optional. 第二个opts参数是可选的。"Writing Options"“写入选项” covers the supported properties and behaviors.涵盖支持的属性和行为。

Generate and attempt to save an XLSX file生成并尝试保存XLSX文件

XLSX.writeFileXLSX(workbook,filename,opts);

The writeFile method embeds a number of different export functions. writeFile方法嵌入了许多不同的导出函数。This is great for developer experience but not amenable to tree shaking using the current developer tools. 这对开发人员体验很好，但不适合使用当前的开发人员工具进行树摇动。 When only XLSX exports are needed, this method avoids referencing the other export functions.当只需要XLSX导出时，此方法避免引用其他导出函数。

The second opts argument is optional. 第二个opts参数是可选的。"Writing Options"“写入选项” covers the supported properties and behaviors.涵盖支持的属性和行为。

note

The writeFile and writeFileXLSX methods uses platform-specific APIs to save files. writeFile和writeFileXLSX方法使用特定于平台的API来保存文件。The APIs do not generally provide feedback on whether files were created.API通常不提供是否创建了文件的反馈。

Examples​

Here are a few common scenarios (click on each subtitle to see the code).以下是一些常见的场景（单击每个子标题以查看代码）。

The demos cover special deployments in more detail.演示更详细地介绍了特殊部署。

Example: Local File示例：本地文件​

XLSX.writeFile supports writing local files in platforms like NodeJS. 支持在Node.js等平台中编写本地文件。In other platforms like React Native, XLSX.write should be called with file data.在其他平台（如React Native）中，应使用文件数据调用XLSX.write。

XLSX.writeFile wraps a few techniques for triggering a file save:总结了触发文件保存的几种技术：

  • URL browser API creates an object URL for the file, which the library uses by creating a link and forcing a click. 浏览器API为文件创建对象URL，库通过创建链接并强制单击来使用该URL。It is supported in modern browsers.现代浏览器支持它。
  • msSaveBlob is an IE10+ API for triggering a file save.是用于触发文件保存的IE10+API。
  • IE_FileSave uses VBScript and ActiveX to write a file in IE6+ for Windows XP and Windows 7. 使用VBScript和ActiveX在适用于Windows XP和Windows 7的IE6+中编写文件。The shim must be included in the containing HTML page.填充程序必须包含在包含HTML的页面中。

There is no standard way to determine if the actual file has been downloaded.没有标准方法来确定实际文件是否已下载。

/* output format determined by filename */
XLSX.writeFile(workbook,"out.xlsb");
/* at this point, out.xlsb will have been downloaded */
SWF workaround for Windows 95+Windows 95的SWF解决方案+ (click to show)
warning警告

Each moving part in this solution has been deprecated years ago:多年前，此解决方案中的每个移动部件都已被弃用：

  • Adobe stopped supporting Flash Player at the end of 20202020年底，Adobe停止支持Flash Player
  • Microsoft stopped supporting IE8 in 2019 and stopped supporting IE9 in 20202019年，微软停止支持IE8，2020年，微软停止支持IE9
  • Downloadify support ended in 2010 and SWFObject support ended in 2016支持结束于2010年，SWFObject支持结束于2016年

New projects should strongly consider requiring modern browsers. 新项目应该强烈考虑需要现代浏览器。This info is provided on an "as is" basis and there is no realistic way to provide support given that every related vendor stopped providing support for their software.这些信息是在“按现状”的基础上提供的，鉴于每个相关供应商都停止了对其软件的支持，因此没有实际的方法来提供支持。

XLSX.writeFile techniques work for most modern browsers as well as older IE. 这些技术适用于大多数现代浏览器和旧的IE。For much older browsers, there are workarounds implemented by wrapper libraries.对于更老的浏览器，有一些由包装器库实现的变通方法。

Downloadify uses a Flash SWF button to generate local files, suitable for environments where ActiveX is unavailable:使用Flash SWF按钮生成本地文件，适用于ActiveX不可用的环境：

Downloadify.create(id,{
/* other options are required! read the downloadify docs for more info */
filename:"test.xlsx",
data:function(){returnXLSX.write(wb,{bookType:"xlsx",type:"base64"});},
append:false,
dataType:"base64"
});

The oldie demo shows an IE-compatible fallback scenario.oldie演示展示了一个与IE兼容的回退场景。

Example: Remote File示例：远程文件​

This example focuses on uploading files ("Ajax" in browser parlance) using APIs like XMLHttpRequest and fetch as well as third-party libraries.本例重点介绍使用XMLHttpRequest和fetch等API以及第三方库上载文件（用浏览器的说法是“Ajax”）。

caution

Some platforms like Azure and AWS will attempt to parse POST request bodies as UTF-8 strings before user code can see the data. 一些平台，如Azure和AWS，将尝试在用户代码可以看到数据之前，将POST请求主体解析为UTF-8字符串。This will result in corrupt data parsed by the server. 这将导致服务器解析的数据损坏。There are some workarounds, but the safest approach is to adjust the server process or Lambda function to accept Base64 strings.有一些解决方法，但最安全的方法是调整服务器进程或Lambda函数以接受Base64字符串。

A complete example using XHR is included in the XHR demo, along with examples for fetch and wrapper libraries. XHR演示中包含了使用XHR的完整示例，以及获取和包装库的示例。This example assumes the server can handle Base64-encoded files (see the demo for a basic nodejs server):本例假设服务器可以处理Base64编码的文件（请参阅基本Node.js服务器的演示）：

/* in this example, send a base64 string to the server */
varwbout=XLSX.write(workbook,{bookType:"xlsx",type:"base64"});

/* prepare data for POST */
varformdata=newFormData();
formdata.append("file","test.xlsx");// <-- server expects `file` to hold name
formdata.append("data",wbout);// <-- `data` holds the base64-encoded data

/* perform POST request */
varreq=newXMLHttpRequest();
req.open("POST","/upload",true);
req.send(formdata);

For servers that do not parse POST request bodies as UTF-8 strings, a Blob can be generated from the array output:对于不将POST请求正文解析为UTF-8字符串的服务器，可以从array输出生成Blob：

/* in this example, send a Blob to the server */
varwbout=XLSX.write(workbook,{bookType:"xlsx",type:"array"});

/* prepare data for POST */
varblob=newBlob([newUint8Array(wbout)],{type:"application/octet-stream"});
varformdata=newFormData();
formdata.append("file",blob,"test.xlsx");

/* perform POST request */
fetch("/upload",{method:'POST',body:formdata});

Generating JSON and JS Data生成JSON和JS数据​

JSON and JS data tend to represent single worksheets. JSON和JS数据往往表示单个工作表。The utility functions in this section work with single worksheets.本节中的实用程序功能适用于单个工作表。

The "Common Spreadsheet Format" section describes the object structure in more detail. “通用电子表格格式”部分更详细地描述了对象结构。 workbook.SheetNames is an ordered list of the worksheet names. 是工作表名称的有序列表。workbook.Sheets is an object whose keys are sheet names and whose values are worksheet objects.是一个对象，其键为工作表名称，其值为工作表对象。

The "first worksheet" is stored at workbook.Sheets[workbook.SheetNames[0]].“第一张工作表”存储在workbook.Sheets[workbook.SheetNames[0]]中。

API​

Create an array of JS objects from a worksheet从工作表创建JS对象数组

varjsa=XLSX.utils.sheet_to_json(worksheet,opts);

Create an array of arrays of JS values from a worksheet从工作表中创建JS值数组

varaoa=XLSX.utils.sheet_to_json(worksheet,{...opts,header:1});

The sheet_to_json utility function walks a workbook in row-major order, generating an array of objects. sheet_to_json实用程序函数按行主顺序遍历工作簿，生成一个对象数组。The second opts argument controls a number of export decisions including the type of values (JS values or formatted text). 第二个参数opts控制许多导出决策，包括值的类型（JS值或格式化文本）。The "JSON" section describes the argument in more detail.“JSON”部分更详细地描述了参数。

By default, sheet_to_json scans the first row and uses the values as headers. 默认情况下，sheet_to_json扫描第一行，并将值用作标题。With the header: 1 option, the function exports an array of arrays of values.使用header:1选项，该函数导出值数组。

Examples示例​

Example: Data Grids示例：数据网格​

x-spreadsheet is an interactive data grid for previewing and modifying structured data in the web browser. 是一种交互式数据网格，用于在web浏览器中预览和修改结构化数据。The demo includes a sample script with the stox function for converting from a workbook to x-spreadsheet. 演示包括一个带有stox函数的示例脚本，用于将工作簿转换为x-spreadsheet。Live Demo: 现场演示：https://oss.sheetjs.com/sheetjs/x-spreadsheet

Example: Data Loading示例：数据加载​

Populating a database (SQL or no-SQL)填充数据库（SQL或无SQL） (click to show)

The database demo includes examples of working with databases and query results.database演示包括使用数据库和查询结果的示例。

Numerical Computations with TensorFlow.jsTensorFlowjs的数值计算 (click to show)

@tensorflow/tfjs and other libraries expect data in simple arrays, well-suited for worksheets where each column is a data vector. 其他库希望数据以简单数组形式存在，非常适合于每列都是数据向量的工作表。 That is the transpose of how most people use spreadsheets, where each row is a vector.这是大多数人使用电子表格方式的转换，其中每一行都是一个向量。

A single Array#map can pull individual named rows from sheet_to_json export:单个Array#map可以将单个命名行从sheet_to_json导出：

constXLSX=require("xlsx");
consttf=require('@tensorflow/tfjs');

constkey="age";// this is the field we want to pull
constages=XLSX.utils.sheet_to_json(worksheet).map(r=>r[key]);
consttf_data=tf.tensor1d(ages);

All fields can be processed at once using a transpose of the 2D tensor generated with the sheet_to_json export with header: 1. 所有字段都可以使用sheet_to_json配合header: 1导出生成的2D张量转置立即处理。The first row, if it contains header labels, should be removed with a slice:如果第一行包含标题标签，则应使用切片删除：

constXLSX=require("xlsx");
consttf=require('@tensorflow/tfjs');

/* array of arrays of the data starting on the second row */
constaoa=XLSX.utils.sheet_to_json(worksheet,{header:1}).slice(1);
/* dataset in the "correct orientation" */
consttf_dataset=tf.tensor2d(aoa).transpose();
/* pull out each dataset with a slice */
consttf_field0=tf_dataset.slice([0,0],[1,tensor.shape[1]]).flatten();
consttf_field1=tf_dataset.slice([1,0],[1,tensor.shape[1]]).flatten();

The array demo shows a complete example.array演示展示了一个完整的示例。

Generating HTML Tables生成HTML表​

API​

Generate HTML Table from Worksheet从工作表生成HTML表

varhtml=XLSX.utils.sheet_to_html(worksheet);

The sheet_to_html utility function generates HTML code based on the worksheet data. sheet_to_html实用程序函数根据工作表数据生成html代码。Each cell in the worksheet is mapped to a <TD> element. 工作表中的每个单元格都映射到一个<TD>元素。Merged cells in the worksheet are serialized by setting colspan and rowspan attributes.工作表中的合并单元格通过设置colspan和rowspan属性进行序列化。

Examples示例​

The sheet_to_html utility function generates HTML code that can be added to any DOM element by setting the innerHTML:sheet_to_html实用程序函数生成html代码，可以通过设置innerHTML将其添加到任何DOM元素：

varcontainer=document.getElementById("tavolo");
container.innerHTML=XLSX.utils.sheet_to_html(worksheet);

Combining with fetch, constructing a site from a workbook is straightforward:结合fetch，从工作簿构建站点非常简单：

This example assigns the innerHTML of a DIV element:本例分配DIV元素的innerHTML：

<body>
<style>TABLE{border-collapse:collapse;}TD{border:1pxsolid;}</style>
<divid="tavolo"></div>
<scriptsrc="https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js"></script>
<scripttype="text/javascript">
(async()=>{
/* fetch and parse workbook -- see the fetch example for details */
constworkbook=XLSX.read(await(awaitfetch("sheetjs.xlsx")).arrayBuffer());

letoutput=[];
/* loop through the worksheet names in order */
workbook.SheetNames.forEach(name=>{

/* generate HTML from the corresponding worksheets */
constworksheet=workbook.Sheets[name];
consthtml=XLSX.utils.sheet_to_html(worksheet);

/* add a header with the title name followed by the table */
output.push(`<H3>${name}</H3>${html}`);
});
/* write to the DOM at the end */
tavolo.innerHTML=output.join("\n");
})();
</script>
</body>

Generating Single-Worksheet Snapshots生成单个工作表快照​

The sheet_to_* functions accept a worksheet object.sheet_to_*函数接受工作表对象。

API​

Generate a CSV from a single worksheet从单个工作表生成CSV

varcsv=XLSX.utils.sheet_to_csv(worksheet,opts);

This snapshot is designed to replicate the "CSV UTF8 (.csv)" output type. 此快照旨在复制“CSV UTF8（.csv）”输出类型。"Delimiter-Separated Output"“分隔符分隔的输出” describes the function and the optional opts argument in more detail.更详细地描述了函数和可选opts参数。

Generate "Text" from a single worksheet从单个工作表生成“文本”

vartxt=XLSX.utils.sheet_to_txt(worksheet,opts);

This snapshot is designed to replicate the "UTF16 Text (.txt)" output type. 此快照旨在复制“UTF16文本（.txt）”输出类型。"Delimiter-Separated Output"“分隔符分隔的输出” describes the function and the optional opts argument in more detail.更详细地描述了函数和可选opts参数。

Generate a list of formulae from a single worksheet从单个工作表生成公式列表

varfmla=XLSX.utils.sheet_to_formulae(worksheet);

This snapshot generates an array of entries representing the embedded formulae. 此快照生成表示嵌入公式的条目数组。Array formulae are rendered in the form range=formula while plain cells are rendered in the form cell=formula or value. 数组公式以range=formula的形式呈现，而普通单元格以cell=formula or value的形式呈现。String literals are prefixed with an apostrophe ', consistent with Excel's formula bar display.字符串文字的前缀为撇号'，与Excel的公式栏显示一致。

"Formulae Output"“公式输出” describes the function in more detail.更详细地描述了该功能。

Streaming Write流式写入​

The streaming write functions are available in the XLSX.stream object. XLSX.stream对象中提供了流写入功能。They take the same arguments as the normal write functions but return a NodeJS Readable Stream.它们采用与普通写函数相同的参数，但返回Node.js可读流。

  • XLSX.stream.to_csv is the streaming version of XLSX.utils.sheet_to_csv.是XLSX.utils.sheet_to_csv的流式版本。
  • XLSX.stream.to_html is the streaming version of XLSX.utils.sheet_to_html.是XLSX.utils.sheet_to_html的流式版本。
  • XLSX.stream.to_json is the streaming version of XLSX.utils.sheet_to_json.是XLSX.utils.sheet_to_json的流式版本。
nodejs convert to CSV and write fileNode.js转换为CSV并写入文件 (click to show) 
varoutput_file_name="out.csv";
varstream=XLSX.stream.to_csv(worksheet);
stream.pipe(fs.createWriteStream(output_file_name));
nodejs write JSON stream to screenNode.js将JSON流写入屏幕 (click to show) 
/* to_json returns an object-mode stream */
varstream=XLSX.stream.to_json(worksheet,{raw:true});

/* the following stream converts JS objects to text via JSON.stringify */
varconv=newTransform({writableObjectMode:true});
conv._transform=function(obj,e,cb){cb(null,JSON.stringify(obj)+"\n");};

stream.pipe(conv);conv.pipe(process.stdout);

https://github.com/sheetjs/sheetaki pipes write streams to nodejs response.管道将流写入Node.js响应。