网站上的聊天框怎么做的,网站建设哪家好,盐城优化办,网站建设需要公司在vue3项目中使用xlsx插件实现Excel表格的导出和解析 1、xlsx插件包官方 xlsx插件包官方 2、FileReader官方文档#xff1a;FileReader官方文档 安装xlsx和xlsx-style-vite、file-saver
npm install xlsx
npm install xlsx-style-vite
npm install file-saverpackage.json中查…在vue3项目中使用xlsx插件实现Excel表格的导出和解析 1、xlsx插件包官方 xlsx插件包官方 2、FileReader官方文档FileReader官方文档 安装xlsx和xlsx-style-vite、file-saver
npm install xlsx
npm install xlsx-style-vite
npm install file-saverpackage.json中查看安装版本
在utils文件夹下新建excel.js
import { saveAs } from file-saver;export function exportExcel(ids,filename,excelProps,excelList,dictOptions {}
) {const newExcelProps JSON.parse(JSON.stringify(excelProps));import(/utils/export2Excel).then((excel) {// 顺序表按值删除for (var i newExcelProps.length - 1; i 0; i--) {if (newExcelProps[i].visible false ||newExcelProps[i].field operation) {newExcelProps.splice(i, 1);}}const propLength newExcelProps.length;const title [filename];const tHeader newExcelProps.map((item) item[name]); // 表头// 需要过滤的导出表头const toRemove [操作];const filterHeader tHeader.filter((item) !toRemove.includes(item));tHeader.length 0;tHeader.push(...filterHeader);// 如果ids为空或者undefined导出默认的数据否则筛选数据let fileData;if (ids undefined || ids.length 0) {fileData excelList;} else if (ids ids.length 0) {let arr JSON.parse(JSON.stringify(excelList));let arrMap new Map(arr.map((obj) [obj.id, obj]));let resultArr ids.map((id) arrMap.get(id));fileData resultArr;}fileData.forEach((item) {newExcelProps.forEach((v) {// 如果props 中type为dict 且 scopedSlots存在content 则会对每行导出数据进行下拉筛选if (v.type dict v.scopedSlots.content) {let dictField v.dictField || [dictValue, dictLabel];const filterData dictOptions[v.field].filter((f) item[v.field] f[dictField[0]]);item[v.field] !filterData.length ? : filterData[0][dictField[1]];}});});const data Array.from(fileData, (v) newExcelProps.map(({ field }) v[field]));//单元格值判空操作for (let i 0; i data.length; i) {const item data[i];for (let index 0; index item.length; index) {item[index] item[index] ||item[index] null ||item[index] undefined? : item[index];}}const merges [A1:${numToEng(propLength)}1];excel.export_json_to_excel({title,header: tHeader,data,merges,filename,autoWidth: true,bookType: xlsx,});});
}
// 转换英文字符
export function numToEng(num) {const A_Z []for (let i 65; i 91; i) {A_Z.push(String.fromCharCode(i))}return A_Z[num - 1]
}
在utils文件夹下新建export2Excel.js
/* eslint-disable */
// require(script-loader!file-saver);
import * as XLSX from xlsx;
import * as XLSX_STYLE from xlsx-style-vite; // Vue3 Vitefunction generateArray(table) {var out [];var rows table.querySelectorAll(tr);var ranges [];for (var R 0; R rows.length; R) {var outRow [];var row rows[R];var columns row.querySelectorAll(td);for (var C 0; C columns.length; C) {var cell columns[C];var colspan cell.getAttribute(colspan);var rowspan cell.getAttribute(rowspan);var cellValue cell.innerText;if (cellValue ! cellValue cellValue) cellValue cellValue;//Skip rangesranges.forEach(function (range) {if (R range.s.r R range.e.r outRow.length range.s.c outRow.length range.e.c) {for (var i 0; i range.e.c - range.s.c; i) outRow.push(null);}});//Handle Row Spanif (rowspan || colspan) {rowspan rowspan || 1;colspan colspan || 1;ranges.push({s: {r: R,c: outRow.length,},e: {r: R rowspan - 1,c: outRow.length colspan - 1,},});}//Handle ValueoutRow.push(cellValue ! ? cellValue : null);//Handle Colspanif (colspan) for (var k 0; k colspan - 1; k) outRow.push(null);}out.push(outRow);}return [out, ranges];
}function datenum(v, date1904) {if (date1904) v 1462;var epoch Date.parse(v);return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}function sheet_from_array_of_arrays(data, opts) {var ws {};var range {s: {c: 10000000,r: 10000000,},e: {c: 0,r: 0,},};for (var R 0; R ! data.length; R) {for (var C 0; C ! data[R].length; C) {if (range.s.r R) range.s.r R;if (range.s.c C) range.s.c C;if (range.e.r R) range.e.r R;if (range.e.c C) range.e.c C;var cell {v: data[R][C],};if (cell.v null) continue;var cell_ref XLSX_STYLE.utils.encode_cell({c: C,r: R,});if (typeof cell.v number) cell.t n;else if (typeof cell.v boolean) cell.t b;else if (cell.v instanceof Date) {cell.t n;cell.z XLSX_STYLE.SSF._table[14];cell.v datenum(cell.v);} else cell.t s;ws[cell_ref] cell;}}if (range.s.c 10000000) ws[!ref] XLSX_STYLE.utils.encode_range(range);return ws;
}function Workbook() {if (!(this instanceof Workbook)) return new Workbook();this.SheetNames [];this.Sheets {};
}function s2ab(s) {var buf new ArrayBuffer(s.length);var view new Uint8Array(buf);for (var i 0; i ! s.length; i) view[i] s.charCodeAt(i) 0xff;return buf;
}export function export_table_to_excel(id) {var theTable document.getElementById(id);var oo generateArray(theTable);var ranges oo[1];/* original data */var data oo[0];var ws_name SheetJS;var wb new Workbook(),ws sheet_from_array_of_arrays(data);/* add ranges to worksheet */ws[!merges] ranges;/* add worksheet to workbook */wb.SheetNames.push(ws_name);wb.Sheets[ws_name] ws;var wbout XLSX_STYLE.write(wb, {bookType: xlsx,bookSST: false,type: binary,});saveAs(new Blob([s2ab(wbout)], {type: application/octet-stream,}),test.xlsx);
}// 生成器
function* generateHeaderString() {let index 0;const letterString ABCDEFGHIJKLMNOPQRSTUVWXYZ;while (true) {let str ;let n index;while (n 0) {str letterString[n % 26] str;n Math.floor(n / 26) - 1;}yield str;index;}
}// 迭代生成单元格表头数组
function generateHeaderArr(length) {const result [];const generator generateHeaderString();for (let i 0; i length; i) {const nextString generator.next().value;result.push(nextString);}return result;
}export function export_json_to_excel({title,multiHeader [],header,data,filename,merges [],autoWidth true,bookType xlsx,
} {}) {/* original data */filename filename || excel-list;data [...data];// data.unshift(header);data.unshift(title);for (let i multiHeader.length - 1; i -1; i--) {data.unshift(multiHeader[i]);}var ws_name filename || SheetJS;var wb new Workbook(),ws sheet_from_array_of_arrays(data);if (merges.length 0) {if (!ws[!merges]) ws[!merges] [];merges.forEach((item) {ws[!merges].push(XLSX_STYLE.utils.decode_range(item));});}if (autoWidth) {/*设置worksheet每列的最大宽度*/const colWidth data.map((row) row.map((val) {/*先判断是否为null/undefined*/if (val null || val ) {return {wch: 20,};} else if (val.toString().charCodeAt(0) 255) {/*再判断是否为中文*/return {wch: val.toString().length * 10,};} else {return {wch: val.toString().length,};}}));/*以第一行为初始值*/let result colWidth[0];for (let i 1; i colWidth.length; i) {for (let j 0; j colWidth[i].length; j) {if (result[0][wch] colWidth[i][j][wch]) {result[0][wch] colWidth[i][j][wch] 5;} else {}}}result.push({ wch: 55 });ws[!cols] result;}/* add worksheet to workbook */wb.SheetNames.push(ws_name);wb.Sheets[ws_name] ws;var dataInfo wb.Sheets[wb.SheetNames[0]];//单元格外侧框线const borderAll {top: {style: thin,},bottom: {style: thin,},left: {style: thin,},right: {style: thin,},};//给单元格加上边框let row [!ref, !merges, !cols];for (var i in dataInfo) {if (row.indexOf(i) -1) {dataInfo[i ].s {border: borderAll,alignment: {horizontal: center,vertical: center,},};}}// 根据表头长度生成单元格列数组const arr generateHeaderArr(header.length);// 表头加上样式arr.forEach((v) {let v2 v 3;if (dataInfo.hasOwnProperty(v2)) {dataInfo[v2].s {border: borderAll,font: {color: { rgb: FFFFFF },bold: false,italic: false,underline: false,},alignment: {horizontal: center,vertical: center,},fill: {fgColor: { rgb: 808080 },},};}});//设置主标题样式dataInfo[A1].s {font: {name: 微软雅黑,sz: 16,color: { rgb: 000000 },bold: false,italic: false,underline: false,},alignment: {horizontal: center,vertical: center,},};var wbout XLSX_STYLE.write(wb, {bookType: bookType,bookSST: false,type: binary,});saveAs(new Blob([s2ab(wbout)], {type: application/octet-stream,}),${filename}.${bookType});
}
在页面上使用
script setup
import Edit from ./components/Edit.vue;
import { onMounted, ref } from vue;
import axios from axios;
import { exportExcel } from ./utils/excel.js;
// TODO: 列表渲染
const list ref([]);
const ids ref([]);
const title ref(测试导出);
const column ref([{ field: name }, { field: place }]);
const getList async () {const res await axios.get(/list);list.value res.data;
};
const onDelete async (id) {await axios.delete(/del/{id});getList();
};
onMounted(() getList());
// TODO: 删除功能const editRef ref(null);
// TODO: 编辑功能
const onEdit (row) {editRef.value.open(row);
};
// 导出
const handleExport () {exportExcel(ids.value, title.value, column.value, list.value);
};
/scripttemplatediv classappel-button typeprimary clickhandleExport导出/el-buttonel-table :datalistel-table-column labelID propid/el-table-columnel-table-column label姓名 propname width150/el-table-columnel-table-column label籍贯 propplace/el-table-columnel-table-column label操作 width150template #default{ row }el-button typeprimary link clickonEdit(row)编辑/el-buttonel-button typedanger link clickonDelete(row.id)删除/el-button/template/el-table-column/el-table/divEdit refeditRef onUpdategetList /
/templatestyle scoped
.app {width: 980px;margin: 100px auto 0;
}
/style
实现效果
