在Vue项目中实现表格数据导出为Excel
1. 安装依赖包
npm install file-saver xlsx --save
npm install script-loader --save-dev2. 创建工具文件
在项目根目录的 src/vendor 文件夹下创建两个脚本文件：
2.1 Blob.js（兼容旧浏览器）
(function (view) {
"use strict";
view.URL = view.URL || view.webkitURL;
if (view.Blob && view.URL) {
try {
new Blob;
return;
} catch (e) {}
}
var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || function(view) {
var get_class = function(obj) {
return Object.prototype.toString.call(obj).match(/^\[object\s(.*)\]$/)[1];
};
var FakeBlobBuilder = function() {
this.parts = [];
};
var FakeBlob = function(data, type, encoding) {
this.data = data;
this.size = data.length;
this.type = type;
this.encoding = encoding;
};
var FBB_proto = FakeBlobBuilder.prototype;
var FB_proto = FakeBlob.prototype;
var FileReaderSync = view.FileReaderSync;
var FileException = function(type) {
this.code = this[this.name = type];
};
var codes = "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR".split(" ");
var i = codes.length;
while (i--) {
FileException.prototype[codes[i]] = i + 1;
}
var real_URL = view.URL || view.webkitURL || view;
var real_create = real_URL.createObjectURL;
var real_revoke = real_URL.revokeObjectURL;
var URL = real_URL;
var btoa = view.btoa;
var atob = view.atob;
var ArrayBuffer = view.ArrayBuffer;
var Uint8Array = view.Uint8Array;
FakeBlob.fake = true;
FBB_proto.append = function(data) {
var parts = this.parts;
if (Uint8Array && (data instanceof ArrayBuffer || data instanceof Uint8Array)) {
var str = "";
var buf = new Uint8Array(data);
for (var j = 0; j < buf.length; j++) {
str += String.fromCharCode(buf[j]);
}
parts.push(str);
} else if (get_class(data) === "Blob" || get_class(data) === "File") {
if (FileReaderSync) {
var reader = new FileReaderSync();
parts.push(reader.readAsBinaryString(data));
} else {
throw new FileException("NOT_READABLE_ERR");
}
} else if (data instanceof FakeBlob) {
if (data.encoding === "base64" && atob) {
parts.push(atob(data.data));
} else if (data.encoding === "URI") {
parts.push(decodeURIComponent(data.data));
} else if (data.encoding === "raw") {
parts.push(data.data);
}
} else {
parts.push(String(data));
}
};
FBB_proto.getBlob = function(type) {
return new FakeBlob(this.parts.join(""), type, "raw");
};
FB_proto.slice = function(start, end, type) {
var arr = this.data;
var slice = arr.slice(start, end || arr.length);
return new FakeBlob(slice, type, this.encoding);
};
FB_proto.close = function() {
this.size = this.data.length = 0;
};
URL.createObjectURL = function(blob) {
var type = blob.type || "application/octet-stream";
var header = "data:" + type;
if (blob instanceof FakeBlob) {
if (blob.encoding === "base64") {
return header + ";base64," + blob.data;
} else if (blob.encoding === "URI") {
return header + "," + decodeURIComponent(blob.data);
} else if (btoa) {
return header + ";base64," + btoa(blob.data);
} else {
return header + "," + encodeURIComponent(blob.data);
}
} else if (real_create) {
return real_create.call(real_URL, blob);
}
};
URL.revokeObjectURL = function(url) {
if (url.substring(0, 5) !== "data:" && real_revoke) {
real_revoke.call(real_URL, url);
}
};
return FakeBlobBuilder;
}(view);
view.Blob = function(parts, options) {
var type = options ? options.type || "" : "";
var builder = new BlobBuilder();
if (parts) {
for (var i = 0; i < parts.length; i++) {
builder.append(parts[i]);
}
}
return builder.getBlob(type);
};
}(typeof self !== "undefined" ? self : typeof window !== "undefined" ? window : this.content || this));
2.2 Export2Excel.js（核心导出逻辑）
require('script-loader!file-saver');
require('script-loader!@/vendor/Blob');
require('script-loader!xlsx/dist/xlsx.core.min');
function extractTableData(tableElement) {
const rows = tableElement.querySelectorAll('tr');
const result = [];
const mergedCells = [];
for (let rowIndex = 0; rowIndex < rows.length; rowIndex++) {
const row = rows[rowIndex];
const cells = row.querySelectorAll('td, th');
const rowData = [];
// 处理合并单元格区域
mergedCells.forEach(range => {
if (rowIndex >= range.startRow && rowIndex <= range.endRow &&
rowData.length >= range.startCol && rowData.length <= range.endCol) {
for (let i = 0; i <= range.endCol - range.startCol; i++) {
rowData.push(null);
}
}
});
for (let colIndex = 0; colIndex < cells.length; colIndex++) {
const cell = cells[colIndex];
const colspan = parseInt(cell.getAttribute('colspan') || 1);
const rowspan = parseInt(cell.getAttribute('rowspan') || 1);
const value = cell.textContent.trim();
// 值转换：数字类型自动识别
let cellValue = value === '' ? null : (value === Number(value) ? Number(value) : value);
// 标记合并区域
if (rowspan > 1 || colspan > 1) {
mergedCells.push({
startRow: rowIndex,
endRow: rowIndex + rowspan - 1,
startCol: rowData.length,
endCol: rowData.length + colspan - 1
});
}
rowData.push(cellValue);
// 补充横向空值
for (let i = 1; i < colspan; i++) {
rowData.push(null);
}
}
result.push(rowData);
}
return { data: result, merges: mergedCells };
}
function dateToNumber(dateStr, is1904) {
const baseDate = is1904 ? new Date(Date.UTC(1904, 0, 1)) : new Date(Date.UTC(1899, 11, 30));
const targetDate = new Date(dateStr);
const diffMs = targetDate.getTime() - baseDate.getTime();
return diffMs / (24 * 60 * 60 * 1000);
}
function generateWorksheet(data, options = {}) {
const worksheet = {};
const range = { s: { c: Infinity, r: Infinity }, e: { c: -1, r: -1 } };
data.forEach((row, rowIndex) => {
row.forEach((cell, colIndex) => {
if (colIndex < range.s.c) range.s.c = colIndex;
if (rowIndex < range.s.r) range.s.r = rowIndex;
if (colIndex > range.e.c) range.e.c = colIndex;
if (rowIndex > range.e.r) range.e.r = rowIndex;
if (cell == null) return;
const cellRef = XLSX.utils.encode_cell({ c: colIndex, r: rowIndex });
const cellObj = { v: cell };
if (typeof cell === 'number') {
cellObj.t = 'n';
} else if (typeof cell === 'boolean') {
cellObj.t = 'b';
} else if (cell instanceof Date) {
cellObj.t = 'n';
cellObj.z = XLSX.SSF._table[14];
cellObj.v = dateToNumber(cell);
} else {
cellObj.t = 's';
}
worksheet[cellRef] = cellObj;
});
});
if (range.s.c !== Infinity) {
worksheet['!ref'] = XLSX.utils.encode_range(range);
}
return worksheet;
}
function createWorkbook() {
return {
SheetNames: [],
Sheets: {}
};
}
function stringToBinary(str) {
const buffer = new ArrayBuffer(str.length);
const view = new Uint8Array(buffer);
for (let i = 0; i < str.length; i++) {
view[i] = str.charCodeAt(i) & 0xFF;
}
return buffer;
}
export function exportTableToExcel(tableId, filename = 'export.xlsx') {
const table = document.getElementById(tableId);
if (!table) {
console.error(`Table with id "${tableId}" not found`);
return;
}
const { data, merges } = extractTableData(table);
const sheetName = 'Sheet1';
const workbook = createWorkbook();
const worksheet = generateWorksheet(data);
worksheet['!merges'] = merges;
workbook.SheetNames.push(sheetName);
workbook.Sheets[sheetName] = worksheet;
const output = XLSX.write(workbook, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([stringToBinary(output)], { type: 'application/octet-stream' }), filename);
}
export function exportJsonToExcel(headers, jsonData, title = '数据导出') {
const data = [headers, ...jsonData];
const sheetName = 'Sheet1';
const workbook = createWorkbook();
const worksheet = generateWorksheet(data);
workbook.SheetNames.push(sheetName);
workbook.Sheets[sheetName] = worksheet;
const output = XLSX.write(workbook, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([stringToBinary(output)], { type: 'application/octet-stream' }), `${title}.xlsx`);
}
3. 在 Vue 组件中使用
export default {
data() {
return {
list: [], // 原始数据
downloadLoading: false
};
},
methods: {
handleExport() {
this.downloadLoading = true;
require.ensure([], () => {
const { exportJsonToExcel } = require('@/vendor/Export2Excel');
const headers = ['订单编号', '应用名称', '会员姓名', '订单状态'];
const fields = ['orderId', 'appName', 'memberName', 'status'];
const formattedData = this.list.map(item => fields.map(field => item[field]));
exportJsonToExcel(headers, formattedData, '订单报表');
this.downloadLoading = false;
}, 'export');
},
formatList(fields, rawData) {
return rawData.map(item => fields.map(f => item[f]));
}
}
};
