ExpressJS 中的工作表

ExpressJS 是一个用于构建服务器端应用的轻量级 NodeJS 框架。

¥ExpressJS is a lightweight NodeJS framework for building server-side applications.

SheetJS 是一个用于从电子表格读取和写入数据的 JavaScript 库。

¥SheetJS is a JavaScript library for reading and writing data from spreadsheets.

本演示使用 ExpressJS 和 SheetJS 来读写数据。我们将探讨如何在 POST 请求处理程序中解析上传的文件并使用可下载的电子表格响应 GET 请求。

¥This demo uses ExpressJS and SheetJS to read and write data. We'll explore how to parse uploaded files in a POST request handler and respond to GET requests with downloadable spreadsheets.

"完整示例" 部分包括一个完整的服务器。

¥The "Complete Example" section includes a complete server.

测试部署

该演示于 2024 年 3 月 11 日使用 express-formidable@1.2.0 和 ExpressJS 4.18.3 进行了测试

¥This demo was tested on 2024 March 11 using express-formidable@1.2.0 and ExpressJS 4.18.3

集成详情

¥Integration Details

SheetJS NodeJS 模块可以从使用 ExpressJS 的脚本导入。

¥The SheetJS NodeJS module can be imported from scripts that use ExpressJS.

将数据导出到工作簿 (GET)

¥Exporting Data to Workbooks (GET)

带有选项 type: "buffer" 的 SheetJS write 方法 [^1] 生成包含原始文件数据的 NodeJS Buffer 对象。

¥The SheetJS write method[^1] with the option type: "buffer" generates NodeJS Buffer objects containing the raw file data.

ExpressJS 可以直接处理 Response#end[^2] 中的 Buffer 数据。

¥ExpressJS can directly handle Buffer data in Response#end[^2].

导出的文件名可以使用 Response#attachment[^3] 指定。

¥The exported filename can be specified using Response#attachment[^3].

以下演示 ExpressJS 服务器将使用 XLSX 电子表格响应对 /download 的 GET 请求。在此示例中，SheetJS aoa_to_sheet 方法 [^4] 生成工作表对象，book_new 和 book_append_sheet 辅助程序 [^5] 构建工作簿对象。

¥The following demo ExpressJS server will respond to GET requests to /download with a XLSX spreadsheet. In this example, the SheetJS aoa_to_sheet method[^4] generates a sheet object and the book_new and book_append_sheet helpers[^5] build the workbook object.

var XLSX = require('xlsx'), express = require('express');

/* create app */
var app = express();

app.get('/download', function(req, res) {
  /* generate workbook object */
  var ws = XLSX.utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = XLSX.write(wb, {type: "buffer", bookType: "xlsx"});
  /* set headers */
  res.attachment("SheetJSExpress.xlsx");
  /* respond with file data */
  res.status(200).end(buf);
});
app.listen(+process.env.PORT||3000);

解析上传的文件（POST）

¥Parsing Uploaded Files (POST)

express-formidable 中间件由 formidable 解析器提供支持。它将 files 对象添加到请求中。

¥The express-formidable middleware is powered by the formidable parser. It adds a files object to the request.

files 对象中的每个值都有一个 path 属性，该属性表示文件系统中文件的路径。SheetJS readFile 方法 [^6] 可以读取文件并生成工作簿对象 [^7]。

¥Each value in the files object has a path property which represents the path to the file in the filesystem. The SheetJS readFile method[^6] can read the file and generate a workbook object[^7].

以下演示 ExpressJS 服务器将响应对 /upload 的 POST 请求。假设表单数据的 upload 字段是文件，SheetJS read 方法将解析该文件。CSV 行是使用 SheetJS sheet_to_csv 方法 [^8] 从第一个工作表生成的。

¥The following demo ExpressJS server will respond to POST requests to /upload. Assuming the upload field of the form data is the file, the SheetJS read method will parse the file. CSV rows are generated from the first worksheet using the SheetJS sheet_to_csv method[^8].

var XLSX = require('xlsx'), express = require('express');

/* create app */
var app = express();
/* add express-formidable middleware */
app.use(require('express-formidable')());
/* route for handling uploaded data */
app.post('/upload', function(req, res) {
  var f = req.files["upload"]; // <input type="file" id="upload" name="upload">
  var wb = XLSX.readFile(f.path);
  /* respond with CSV data from the first sheet */
  res.status(200).end(XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
});
app.listen(+process.env.PORT||3000);

完整示例

¥Complete Example

将代码示例保存到 SheetJSExpressCSV.js：

¥Save the code sample to SheetJSExpressCSV.js:

SheetJSExpressCSV.js
var XLSX = require('xlsx'), express = require('express');

/* create app */
var app = express();
/* add express-formidable middleware */
app.use(require('express-formidable')());
/* route for handling uploaded data */
app.post('/upload', function(req, res) {
  var f = req.files["upload"]; // <input type="file" id="upload" name="upload">
  var wb = XLSX.readFile(f.path);
  /* respond with CSV data from the first sheet */
  res.status(200).end(XLSX.utils.sheet_to_csv(wb.Sheets[wb.SheetNames[0]]));
});
app.get('/download', function(req, res) {
  /* generate workbook object */
  var ws = XLSX.utils.aoa_to_sheet(["SheetJS".split(""), [5,4,3,3,7,9,5]]);
  var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Data");
  /* generate buffer */
  var buf = XLSX.write(wb, {type: "buffer", bookType: "xlsx"});
  /* set headers */
  res.attachment("SheetJSExpress.xlsx");
  /* respond with file data */
  res.status(200).end(buf);
});
app.listen(+process.env.PORT||3000);

安装依赖：

¥Install dependencies:

npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz express@4.18.3 express-formidable@1.2.0

启动服务器（注意：运行时不会向控制台打印任何内容）

¥Start server (note: it will not print anything to console when running)

node SheetJSExpressCSV.js

使用 https://xlsx.nodejs.cn/pres.numbers 测试 POST 请求。这些命令应在新的终端窗口中运行：

¥Test POST requests using https://xlsx.nodejs.cn/pres.numbers . The commands should be run in a new terminal window:

curl -LO https://xlsx.nodejs.cn/pres.numbers
curl -X POST -F upload=@pres.numbers http://localhost:3000/upload

响应应以 CSV 行显示数据。

¥The response should show the data in CSV rows.

通过在浏览器中打开 http://localhost:3000/download 来测试 GET 请求。

¥Test GET requests by opening http://localhost:3000/download in your browser.

应该会提示下载 SheetJSExpress.xlsx

¥It should prompt to download SheetJSExpress.xlsx

[^1]: 见 write 于 "写入文件"

¥See write in "Writing Files"

[^2]: 请参阅 ExpressJS 文档中的 res.end。

¥See res.end in the ExpressJS documentation.

[^3]: 请参阅 ExpressJS 文档中的 res.attachment。

¥See res.attachment in the ExpressJS documentation.

[^4]: 见 aoa_to_sheet 于 "实用工具"

¥See aoa_to_sheet in "Utilities"

[^5]: 有关 book_new 和 book_append_sheet 的详细信息，请参阅 "工作簿助手" 于 "实用工具"。

¥See "Workbook Helpers" in "Utilities" for details on book_new and book_append_sheet.

[^6]: 见 readFile 于 "读取文件"

¥See readFile in "Reading Files"

[^7]: 见 "工作簿对象"

¥See "Workbook Object"

[^8]: 见 sheet_to_csv 于 "CSV 和文本"

¥See sheet_to_csv in "CSV and Text"

集成详情​

将数据导出到工作簿 (GET)​

解析上传的文件（POST）​

完整示例​

集成详情

将数据导出到工作簿 (GET)

解析上传的文件（POST）

完整示例