导入XLSX:
import XLSX from ‘xlsx’;
import XLSXS from ‘xlsx-style’;
/**
* 多sheet页导出
* dataList: [{
* sheetData:[],
* sheetTitle:[],
* sheetName:'',
* }]; sheetData:单sheet页全部数据,sheetTitle:单sheet页表头(label【表头名称】,prop【列绑定属性】定义的column),sheetName:单sheet页名字
*/
export async function exportMuliExcel(dataList, fileName, download = true) {
const wb = XLSX.utils.book_new();
// 格式化数据
for (let i = 0; i < dataList.length; i++) {
const item = dataList[i];
const sheetData = [];
item.sheetData.forEach((item2) => {
let keyValue = {};
item.sheetTitle.forEach((item1) => {
if (item1.options) item1.type = 'select';
const column = { params: item1, property: item1.prop };
keyValue[item1.label] = {
d: item1.digit ? item1.digit : 2, // 数值类型精度处理
t: item1.type === 'number' ? 'n' : 's', // 根据表头type设置单元格格式
};
});
sheetData.push(keyValue);
});
const header = [];
item.sheetTitle.forEach((v) => {
header.push(v.label);
});
const sheet = XLSX.utils.json_to_sheet(sheetData, { header: header });
for (const key in sheet) {
if (key.length < 4 && (key.slice(1) == 1 || (isNaN(key.slice(1, 2)) && key.slice(2) == 1))) {
//设置第一行【表头】样式
sheet[key].s = {
alignment: {
vertical: 'center', // 垂直对齐,值为”bottom”、”center”或 “top”
horizontal: 'center', //水平对齐,值为”left”、”center”或 “right”
wrapText: false, //自动换行
},
border: {
top: { style: 'thin', color: { rgb: 'FFD4D4D4' } },
left: { style: 'thin', color: { rgb: 'FFD4D4D4' } },
bottom: { style: 'thin', color: { rgb: 'FFD4D4D4' } },
right: { style: 'thin', color: { rgb: 'FFD4D4D4' } },
},
font: {
bold: true,
},
fill: {
fgColor: { rgb: 'FFF4F5FA' },
},
}; // 设置xlsx单元格样式
}
const d = sheet[key].v?.d ? sheet[key].v.d : 2;
if (typeof sheet[key].v == 'object') {
sheet[key].t = sheet[key].v.t;
sheet[key].v = sheet[key].v.v;
}
if (sheet[key].v === null) sheet[key].v = ''; // 处理数据为null的情况
if (sheet[key].t === 'n') {
sheet[key].s = { numFmt: '#,##0.' + '0'.repeat(d) };
sheet[key].v = removeComma(sheet[key].v); // 取消数字中的千分符
} // 处理数据为null的情况
}
sheet['!cols'] = calculatorWidth(sheet);
XLSX.utils.book_append_sheet(wb, sheet, item.sheetName);
}
openDownloadDialog(sheetMuli2blobX(wb), fileName + new Date().format('yyyyMMddhhmmss') + '.xlsx');
}
function sheetMuli2blobX(wb) {
// 生成excel的配置项
const wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary',
};
const wbout = XLSXS.write(wb, wopts);
const blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' });
return blob;
}
export function s2ab(s) {
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;
}
/**
* 生成个 a 标签,然后点击一下,模拟实现
*
* @param url
* @param saveName
*/
export function openDownloadDialog(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
const aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
let event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
去除千分符:
// 去除千分位中的‘,’
export const removeComma = function (num) {
if (!num) {
return num;
}
num = num.toString();
num = num.replace(/,/gi, '');
return num;
};