DIV CSS 佈局教程網

在 Node.js 中產生並下載 Excel 檔的實作
編輯:JavaScript綜合知識     

後台管理介面常有「匯出成 Excel」的需求。在 Node.js 裡,做法是先產生檔案、再以串流回傳給瀏覽器下載。以下示範以 excel-export 這類套件搭配自寫的檔案下載模組,並處理中文檔名與斷點續傳。

excelHelper.js

var extend = require("extend");
var fs = require("fs");
var path = require("path");
var excelExport = require('excel-export');
var guid = require('guid');

var excel = function () {
  this.req = null;
  this.resp = null;
};

/** 產生 Excel 檔案 */
excel.prototype.createExcel = function (params) {
  var setting = { savePath: "uploadFile/excel/" };
  setting = extend({}, setting, params);

  var uuid = guid.create();
  var result = excelExport.execute(params.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 m = /^bytes=([0-9]+)-$/.exec(range);
    startPos = Number(m[1]);
  }
  return startPos;
};

excel.prototype._configHeader = function (config) {
  var startPos = config.startPos, fileSize = config.fileSize, resp = this.resp;

  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 = {}, self = this;
  fs.stat(filePath, function (error, state) {
    if (error) throw error;
    config.fileSize = state.size;
    config.startPos = self._calStartPosition(self.req.headers.range);
    self.config = config;
    self._configHeader(config);
    down();
  });
};

/** 下載檔案;isDeleted 為 true 時於傳完後刪除暫存檔 */
excel.prototype.download = function (filePath, req, res, isDeleted) {
  var self = this;
  self.req = req;
  self.resp = res;

  fs.access(filePath, function (err) {
    if (err) { console.log('檔案不存在!'); return; }

    self._init(filePath, function () {
      var config = self.config, 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 () {}); }
        resp.end();
      });
    });
  });
};

module.exports = new excel();

呼叫方式

var excel = require('../lib/excelHelper.js');

exports.exportExcel = function (req, res) {
  var conf = {};
  conf.cols = [
    { caption: '字串', type: 'string' },
    { caption: '日期', type: 'string' },
    { caption: '布林', type: 'bool'   },
    { caption: '數字', 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);
    }
  });
}

幾個值得修正的地方

中文檔名要符合 RFC 5987。只寫 filename= 加 encode,在部分瀏覽器會出現亂碼。建議同時給 filename 與 filename*:

res.setHeader('Content-Disposition',
  "attachment; filename=\"export.xlsx\"; filename*=UTF-8''" +
  encodeURIComponent(filename));

一律回 206 是錯的。只有用戶端帶了 Range 標頭才該回 206 Partial Content,一般請求應回 200 並省略 Content-Range,否則某些用戶端會誤判。

fs.writeFile 要檢查 err。原範例直接呼叫 setting.cb(filePath),寫檔失敗時會把不存在的路徑傳下去。

path.exists 已被移除。這是 Node 0.x 的舊 API,請改用 fs.access 或 fs.promises.access。

建議改用 Promise/async 寫法。回呼巢狀在錯誤處理上很容易漏:

const fs = require('fs/promises');
const content = await fs.readFile(filePath);
res.setHeader('Content-Type',
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.send(content);
await fs.unlink(filePath);

另外,excel-export 這類套件已多年未更新。新專案可考慮 ExcelJS(支援樣式、公式、串流寫入)或 SheetJS,它們的 API 更完整,對大型資料集的記憶體表現也更好。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved