後台系統裡「查詢 → 匯出 Excel」幾乎是標配需求。Node.js 的做法是先在伺服器上產生 .xlsx 檔案,再把它以串流方式回傳給瀏覽器下載。本文以 excel-export 套件為例,順便把「支援 Range 斷點續傳」的細節一起做完。
var extend = require("extend");
var fs = require("fs");
var excelExport = require('excel-export');
var guid = require('guid');
var path = require('path');
var excel = function () {
this.req = null;
this.resp = null;
};
/**
* 產生 Excel 檔案
* @param params
*/
excel.prototype.createExcel = function (params) {
var setting = { savePath: "uploadFile/excel/" };
setting = extend({}, setting, params);
var uuid = guid.create();
var data = params.data || "";
var result = excelExport.execute(data);
var name = 'excel' + uuid + '.xlsx';
var filePath = path.resolve(setting.savePath, name);
fs.writeFile(filePath, result, 'binary', function (err) {
setting.cb(filePath);
});
};
/**
* 解析 Range 標頭,取得續傳起始位置
*/
excel.prototype._calStartPosition = function (range) {
var startPos = 0;
if (typeof range != 'undefined') {
var startPosMatch = /^bytes=([0-9]+)-$/.exec(range);
startPos = Number(startPosMatch[1]);
}
return startPos;
};
excel.prototype._configHeader = function (config) {
var startPos = config.startPos,
fileSize = config.fileSize,
resp = this.resp;
// startPos 為 0 代表從頭下載,否則為斷點續傳
if (startPos == 0) {
resp.setHeader('Accept-Range', 'bytes');
} else {
resp.setHeader('Content-Range', 'bytes ' + startPos + '-' + (fileSize - 1) + '/' + fileSize);
}
resp.writeHead(206, 'Partial Content', {
'Content-Type': 'application/octet-stream'
});
};
excel.prototype._init = function (filePath, down) {
var config = {};
var self = this;
fs.stat(filePath, function (error, state) {
if (error) throw error;
config.fileSize = state.size;
var range = self.req.headers.range;
config.startPos = self._calStartPosition(range);
self.config = config;
self._configHeader(config);
down();
});
};
/**
* 下載檔案
* @param filePath 檔案路徑
* @param req
* @param res
* @param isDeleted 下載完成後是否刪除檔案
*/
excel.prototype.download = function (filePath, req, res, isDeleted) {
var self = this;
self.req = req;
self.resp = res;
path.exists(filePath, function (exist) {
if (exist) {
self._init(filePath, function () {
var config = self.config;
var resp = self.resp;
var fReadStream = fs.createReadStream(filePath, {
encoding: 'binary',
bufferSize: 1024 * 1024,
start: config.startPos,
end: config.fileSize
});
fReadStream.on('data', function (chunk) {
resp.write(chunk, 'binary');
});
fReadStream.on('end', function () {
if (isDeleted) {
fs.unlink(filePath, function (err, res) {});
}
resp.end();
});
});
} else {
console.log('檔案不存在!');
return;
}
});
};
module.exports = new excel();
var excel = require('../lib/excelHelper.js');
exports.exportExcel = function (req, res) {
var conf = {};
conf.cols = [
{ caption: 'string', type: 'string' },
{ caption: 'date', type: 'string' },
{ caption: 'bool', type: 'bool' },
{ caption: 'number', type: 'number' }
];
conf.rows = [
['pi', '2015-06-29', true, 3.14],
['e', '2015-06-29', false, 2.7182]
];
var filename = "導出excel.xlsx";
res.setHeader('Content-Disposition', 'attachment; filename=' + encodeURIComponent(filename));
excel.createExcel({
data: conf,
savePath: "uploadFile/excel/",
cb: function (path) {
excel.download(path, req, res, true);
}
});
}
filename*=UTF-8''... 形式。isDeleted 為 true 時會在下載完畢後刪檔;若使用者中途取消下載,end 不會觸發,需要靠 req.on('close') 之類的機制善後,否則暫存區遲早被塞滿。