Node.js 後端導出 Excel 是報表、數據導出常見需求。早期 excel-export 將所有資料載入記憶體再生成檔案,大數據易 OOM。本文推薦 exceljs 流式寫入,支援百萬列導出、樣式豐富、記憶體恆定,並示範 Express/koa 下載響應與斷點續傳。
npm install exceljs
const ExcelJS = require('exceljs');
const fs = require('fs');
async function streamExport() {
const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
filename: './export-large.xlsx',
useStyles: true,
useSharedStrings: true // 共享字串表,減少記憶體
});
const sheet = workbook.addWorksheet('Report', {
// 預設欄寬
properties: { defaultColWidth: 20 },
// 頁面設定
pageSetup: { fitToPage: true, orientation: 'landscape' }
});
// 定義欄位與樣式
sheet.columns = [
{ header: 'ID', key: 'id', width: 10, style: { numFmt: '0' } },
{ header: '姓名', key: 'name', width: 30 },
{ header: '電子郵件', key: 'email', width: 40 },
{ header: '註冊時間', key: 'createdAt', width: 25, style: { numFmt: 'yyyy-mm-dd hh:mm:ss' } },
{ header: '狀態', key: 'status', width: 15 }
];
// 標題列樣式
sheet.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } };
sheet.getRow(1).fill = {
type: 'pattern', pattern: 'solid', fgColor: { argb: 'FF4472C4' }
};
sheet.getRow(1).alignment = { horizontal: 'center' };
// 模擬大量資料分批寫入(從 DB 游標/Generator 讀取)
async function* dataGenerator() {
for (let i = 1; i <= 100000; i++) {
yield {
id: i,
name: `User ${i}`,
email: `user${i}@example.com`,
createdAt: new Date(Date.now() - Math.random() * 1e12),
status: i % 2 ? '啟用' : '停用'
};
if (i % 10000 === 0) await new Promise(r => setImmediate(r)); // 讓出事件循環
}
}
for await (const row of dataGenerator()) {
sheet.addRow(row).commit(); // commit() 立即寫入流,釋放記憶體
}
await workbook.commit();
console.log('導出完成');
}
streamExport().catch(console.error);
const express = require('express');
const app = express();
app.get('/export/users', async (req, res) => {
const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
stream: res, // 直接寫入 HTTP 響應流
useStyles: true,
useSharedStrings: true
});
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
res.setHeader('Content-Disposition', 'attachment; filename*=UTF-8\'\'users-' + Date.now() + '.xlsx');
const sheet = workbook.addWorksheet('Users');
sheet.columns = [
{ header: 'ID', key: 'id', width: 10 },
{ header: '姓名', key: 'name', width: 30 },
{ header: 'Email', key: 'email', width: 40 }
];
sheet.getRow(1).font = { bold: true };
// 從資料庫流式讀取(以 pg 為例)
const { Client } = require('pg');
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
const cursor = client.query(new Cursor('SELECT id, name, email FROM users WHERE active = true'));
for await (const row of cursor) { // 需支援 async iterator 的 cursor 實現
sheet.addRow(row).commit();
}
await client.end();
await workbook.commit(); // 關閉流,結束響應
});
// 手動控制背壓:當寫入流緩衝區滿時暫停讀取
sheet.addRow(row).commit(); // 返回 boolean,true=緩衝區未滿,false=需等待 drain 事件
// 完整背壓處理範例
async function writeWithBackpressure(sheet, dataIterable) {
for await (const row of dataIterable) {
const ok = sheet.addRow(row).commit();
if (!ok) {
await new Promise(resolve => sheet.stream.once('drain', resolve));
}
}
}
// 參考原文思路:先生成暫存檔,再支援 Range 下載
const fs = require('fs');
const path = require('path');
app.get('/export/download/:filename', (req, res) => {
const filePath = path.join(__dirname, 'temp', req.params.filename);
if (!fs.existsSync(filePath)) return res.status(404).send('Not found');
const stat = fs.statSync(filePath);
const range = req.headers.range;
if (range) {
const [start, end] = range.replace(/bytes=/, '').split('-').map(Number);
const chunkSize = (end || stat.size - 1) - start + 1;
res.writeHead(206, {
'Content-Range': `bytes ${start}-${end || stat.size - 1}/${stat.size}`,
'Accept-Ranges': 'bytes',
'Content-Length': chunkSize,
'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
fs.createReadStream(filePath, { start, end }).pipe(res);
} else {
res.setHeader('Content-Length', stat.size);
res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
fs.createReadStream(filePath).pipe(res);
}
});
// 條件格式:狀態欄位紅/綠
sheet.getColumn('status').eachCell((cell, rowNumber) => {
if (rowNumber === 1) return; // 跳過標題
cell.fill = cell.value === '啟用'
? { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFC6EFCE' } }
: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFFC7CE' } };
cell.font = { color: { argb: cell.value === '啟用' ? 'FF006100' : 'FF9C0006' } };
});
// 合併儲存格
sheet.mergeCells('A1:E1');
sheet.getCell('A1').value = '用戶統計報表';
sheet.getCell('A1').alignment = { horizontal: 'center' };
// 資料驗證(下拉選單)
sheet.dataValidations.add('E2:E100000', {
type: 'list',
allowBlank: true,
formulae: ['"啟用,停用,審核中"'],
showErrorMessage: true,
errorStyle: 'stop',
errorTitle: '無效狀態',
error: '請選擇有效狀態'
});