import * as XLSX from "xlsx";
import FileSaver from "file-saver";
一、单个table导出到一个excel工作表中
function handleExport() {
const ws = XLSX.utils.table_to_sheet(
document.getElementById("incomeInfo-table")
);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); //添加工作表并命名
//导出Excel文件
const wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: false,
type: "binary"
});
function s2ab(s) {
//将字符串转为ArrayBuffer
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i !== s.length; i++) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
//创建Blob对象并下载
FileSaver.saveAs(
new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
"收入信息.xlsx"
);
}
二、多个table导出到同一个excel工作簿的一个工作表中
function formatDate(dateObjOrString) {
if (typeof dateObjOrString === "number") {
return XLSX.SSF.format("yyyy-mm-dd", dateObjOrString);
} else if (dateObjOrString instanceof Date) {
return XLSX.SSF.format(format, dateObjOrString.getTime() / 1000); // 转换为Excel序列数后格式化
} else {
return dateObjOrString;
}
}
function handleTableExport() {
const ws1 = XLSX.utils.table_to_sheet(
document.getElementById("incomeInfo-table")
);
const ws2 = XLSX.utils.table_to_sheet(
document.getElementById("constInfo-table")
);
//使用XLSX.utils.sheet_to_json后,不会保留数据格式
//合并数据
const ws1Data = XLSX.utils.sheet_to_json(ws1, { header: 1, raw: true });
const ws2Data = XLSX.utils.sheet_to_json(ws2, { header: 1, raw: true });
//日期格式
for (let key in ws1Data[0]) {
ws1Data[0][key] = this.formatDate(ws1Data[0][key]);
}
for (let key in ws2Data[0]) {
ws2Data[0][key] = this.formatDate(ws2Data[0][key]);
}
//百分比格式
for (let key in ws2Data[ws2Data.length - 1]) {
ws2Data[ws2Data.length - 1][key] =
typeof ws2Data[ws2Data.length - 1][key] === "number"
? ws2Data[ws2Data.length - 1][key] * 100 + "%"
: ws2Data[ws2Data.length - 1][key];
}
const combinedData = ws1Data.concat(ws2Data);
//创建工作薄和工作表
const wb = XLSX.utils.book_new();
const combinedWs = XLSX.utils.aoa_to_sheet(combinedData, {
cellStyles: true
});
//提取合并信息
const merges1 = ws1["!merges"] || [];
const merges2 = ws2["!merges"] || [];
//调整合并范围以适应合并后的新数据起始位置
const mergeOffset = ws1Data.length; //假设ws1之后直接ws2,无额外行
const adjustedMerges2 = merges2.map(merge => ({
//表示合并单元格的起始位置(start),包含行号(r)和列号(c)
s: { r: merge.s.r + mergeOffset, c: merge.s.c },
//表示合并单元格的结束位置(end),同样包含行号和列号
e: { r: merge.e.r + mergeOffset, c: merge.e.c }
}));
//合并合并信息
const combinedMerges = merges1.concat(adjustedMerges2);
//应用合并信息到新工作表
combinedWs["!merges"] = combinedMerges;
XLSX.utils.book_append_sheet(wb, combinedWs, "combinedSheet");
//导出Excel文件
const wbout = XLSX.write(wb, {
bookType: "xlsx",
bookSST: false,
type: "binary"
});
function s2ab(s) {
//将字符串转为ArrayBuffer
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i = 0; i !== s.length; i++) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
//创建Blob对象并下载
FileSaver.saveAs(
new Blob([s2ab(wbout)], { type: "application/octet-stream" }),
"详情.xlsx"
);
}