Original link: https://blog.lipux.cn/archives/337.html
foreword
Based on the need to export database information as an excel table for users to download, this article roughly introduces a powerful excel table parsing module node-xlsx in node.js. Among them, express is a powerful web server framework, so I won’t introduce it too much here.
Install the module
form generation module
npm install node-xlsx
web server framework module
npm install express
Build a server to listen for requests
// 导入服务器模块const express = require('express'); // 创建服务器对象const app = express(); // 绑定路由app.get('/getExcel', (req,res)=>{ // 路由处理函数}) // 启动服务器,监听85端口app.listen(85, () => { console.log('服务器启动成功！') })
The main function that generates the form
// 导入excel生成模块const xlsx = require("node-xlsx"); const list = [{ name: "sheet", data: [ ["data1", "data2", "data3"], ["data1", "data2", "data3"], ["data1", "data2", "data3"], ], }, ]; // 得到一个表格文件流const buffer = xlsx.build(list);
where list is the table data to be generated, and name is the name of the table
The buffer is a file stream. If you want to save the table to the local, you can use the write file method of the fs module, and use the buffer as the content of the written file to save it locally.
set response headers
Declare that this is a file that needs to be downloaded
res.set({ 'Content-Type': 'application/octet-stream', // 告诉浏览器这是一个二进制文件'Content-Disposition': 'attachment; filename=Asnull.xlsx' // 告诉浏览器这是一个需要下载的文件并且文件名为Asnull.xlsx });
Respond to client requests
res.send(buffer)
overall code
// 导入服务器模块const express = require('express'); // 导入excel生成模块const xlsx = require("node-xlsx"); // 创建服务器对象const app = express(); // 绑定路由app.get('/getExcel', (req, res) => { // 路由处理函数// 表格数据const list = [{ name: "Asnull", data: [ ["data1", "data2", "data3"], ["data1", "data2", "data3"], ["data1", "data2", "data3"], ], }, ]; // 得到一个表格文件流const buffer = xlsx.build(list); res.set({ 'Content-Type': 'application/octet-stream', // 告诉浏览器这是一个二进制文件'Content-Disposition': 'attachment; filename=Asnull.xlsx' // 告诉浏览器这是一个需要下载的文件并且文件名为Asnull.xlsx }); // 响应客户端请求进行下载res.send(buffer) }) // 启动服务器,监听85端口app.listen(85, () => { console.log('服务器启动成功！') })
We access the listening address http://localhost :85/getExcel, we can call up the download, the effect is as follows 
The list in the above code is the data source that needs to be exported
list is an array containing objects, each element of the list will become a sheet of the excel table, the name is the title of the sheet, and the structure of data is a two-dimensional array, so before exporting the data, it is necessary to process the exported data and turn it into two The form of a dimensional array.
If you want to export a table with multiple sheets, the list can be written like this
const list = [ { name: "sheet1", data: [ ["data1", "data2", "data3"], ["data1", "data2", "data3"], ["data1", "data2", "data3"], ], }, { name: "sheet2", data: [ ["data1", "data2", "data3"], ["data1", "data2", "data3"], ["data1", "data2", "data3"], ], }, { name: "sheet3", data: [ ["data1", "data2", "data3"], ["data1", "data2", "data3"], ["data1", "data2", "data3"], ], }, ];
refer to:
- https://blog.csdn.net/weixin_46108101/article/details/122342566
- https://blog.csdn.net/cnds123321/article/details/121874673
- https://segmentfault.com/a/1190000040614995?sort=votes
This article is reproduced from: https://blog.lipux.cn/archives/337.html
This site is for inclusion only, and the copyright belongs to the original author.