vueJS（XLSX）前端实现表格导出（带样式）
1、安装依赖
pnpm add xlsx xlsx-js-style -D
2、具体实现
import XLSX from "xlsx";
import xlsxStyle from 'xlsx-js-style'
exportExcel() {
const dataMerges = [
{
s: {c: 0, r: 0}, e: {c: 7, r: 0}, style: {
fill: {patternType: 'solid', fgColor: {rgb: 'd4e6fd'}},
border: {
top: {style: 'thin', color: {rgb: '000000'}},
bottom: {style: 'thin', color: {rgb: '000000'}},
left: {style: 'thin', color: {rgb: '000000'}},
right: {style: 'thin', color: {rgb: '000000'}}
},
font: {
sz: 16,
name: '宋体'
},
bold: true,
alignment: {horizontal: 'center', vertical: 'center', wrapText: 1}
}
}, //合并第一行 第1个至第8个
{
s: {c: 0, r: 1}, e: {c: 7, r: 1}, style: {
fill: {patternType: 'solid', fgColor: {rgb: 'd4e6fd'}},
font: {
sz: 12,
name: '宋体'
},
bold: true,
border: {
top: {style: 'thin', color: {rgb: '000000'}},
bottom: {style: 'thin', color: {rgb: '000000'}},
left: {style: 'thin', color: {rgb: '000000'}},
right: {style: 'thin', color: {rgb: '000000'}}
},
alignment: {horizontal: 'center', vertical: 'center', wrapText: 1}
}
},
];
// 导出表格的逻辑
const headerData = [["日报统计", "", "", "", "", "", "", "", ""], ['起始时间：' + this.formInline.startTime + " 结束时间：" + this.formInline.endTime, "", "", "", "", "", "", "", ""], ["XXXXX", "单位部门", "XXXX", "XXXXX", "XXXXX", "项目数", "XXXXX", "XXXX"]]
const rows = this.tableData.map(item => (Object.values({
transportName: item.transportName,
organName: this.formatDepartment(item.organCode),
tranaportType: item.tranaportType,
plateNumber: item.plateNumber,
deviceCode: item.deviceCode,
appNum: item.appNum,
distanceNum: item.distanceNum,
alarmNum: item.alarmNum
})));
// 构建Excel数据
const data = [
...headerData,
...rows
];
// 创建一个Workbook对象
const workbook = XLSX.utils.book_new();
// 将数据转换为Worksheet
const worksheet = XLSX.utils.aoa_to_sheet(data);
// 添加表头
// XLSX.utils.sheet_add_aoa(worksheet, headerData);
// 合并表头
//worksheet["!merges"] = dataMerges;
// 设置单元格样式
const borderStyle = {
border: {
top: {style: 'thin', color: {rgb: '000000'}},
bottom: {style: 'thin', color: {rgb: '000000'}},
left: {style: 'thin', color: {rgb: '000000'}},
right: {style: 'thin', color: {rgb: '000000'}},
},
alignment: {
horizontal: 'center',
vertical: 'center',
},
font: {
sz: 12,
name: '宋体'
},
};
// 设置单元格边框样式
const range = XLSX.utils.decode_range(worksheet['!ref']);
for (let R = range.s.r; R <= range.e.r; ++R) {
if (R < dataMerges.length) continue
for (let C = range.s.c; C <= range.e.c; ++C) {
const cellAddress = {c: C, r: R};
const cellRef = XLSX.utils.encode_cell(cellAddress);
if (!worksheet[cellRef]) continue;
worksheet[cellRef].s = borderStyle;
worksheet[cellRef].z = '0'; // 设置为自动调整列宽
worksheet[cellRef].t = 's'; // 设置为字符串类型
}
}
if (dataMerges.length > 0) {
if (!worksheet['!merges']) worksheet['!merges'] = [];
dataMerges.forEach(item => {
// 处理合并行
for (let i = item.s.c; i <= item.e.c; i++) {
const cellAddress = {c: i, r: item.e.r};
const cellRef = XLSX.utils.encode_cell(cellAddress);
worksheet[cellRef].s = item.style || borderStyle;
worksheet[cellRef].z = '0'; // 设置为自动调整列宽
worksheet[cellRef].t = 's'; // 设置为字符串类型
}
worksheet['!merges'].push(XLSX.utils.decode_range(
XLSX.utils.encode_cell(item.s) + ':' + XLSX.utils.encode_cell(item.e)
));
});
}
// 设置第一行样式
worksheet['!cols'] = [{width: 25}, {width: 25,}, {width: 15}, {width: 15}, {width: 15}, {width: 15}, {width: 15}, {width: 10}];
worksheet['!rows'] = [{hpx: 30}, {hpx: 30}, null, null, null]; // 设置第一行高度为 30 像素
// 将Worksheet添加到Workbook
XLSX.utils.book_append_sheet(workbook, worksheet, 'XXXXX统计');
// 将Workbook导出为Excel文件
xlsxStyle.writeFile(workbook, 'XXXXX统计-' + new Date().getTime() + '.xlsx');
},
实现效果
[复制链接]
发表于 2024-5-16 09:36
感觉比django难