当前位置：首页 > news >正文

vue中使用exceljs和file-saver插件实现纯前端表格导出Excel（支持样式配置，多级表头）

news 来源：原创 2024/9/28 14:09:17

实现：使用Excel.js库创建excel文件，然后再使用 file-saver库将 Excel 文件保存到用户的本地计算机。

1.安装，可以使用npm，yarn

npm install exceljs
npm install file-saver

2.封装生成excel的方法

// 封装exceljs
const ExcelJS = require('exceljs');
const FileSaver = require('file-saver');exportDataToExcel(config, fileName) {if (!config) return;const options = {fileName: fileName || `导出excel文件【${Date.now()}】.xlsx`,worksheets: []}if(!Array.isArray(config)) {config = [config]}config.forEach((item) => {// 深拷贝data【JSON.stringify有缺陷，可自行换成_.cloneDeep】const data = JSON.parse(JSON.stringify(item.data));const results = data.map(obj => {return item.fields.map(key => {return obj[key]})})// 生成完整excel数据let excelData = [];excelData = excelData.concat(item.headers).concat(results);// 单元格合并处理【excel数据的第一行/列是从1开始】let excelMerges = [];excelMerges = item.merges.map(m => {return [m.row + 1, m.col + 1, m.row + m.rowspan, m.col + m.colspan]})// 单元格配置处理 excel数据的第一行/列是从1开始】let excelAttrs = [];excelAttrs = item.attrs.map(attr => {attr.rowStart += 1;attr.rowEnd += 1;attr.colStart += 1;attr.colEnd += 1;return attr})options.worksheets.push({data: excelData,merges: excelMerges,attrs: excelAttrs,views: item.views,columnsWidth: item.columnsWidth,protect: item.protect,sheetName: item.sheetName})})this.createExcel(options)
},// 创建Excel文件方法
async createExcel(options) {if (!options.worksheets.length) return;// 创建工作簿const workbook = new ExcelJS.Workbook();for (let i = 0; i < options.worksheets.length; i++) {const sheetOption = options.worksheets[i];// 创建工作表const sheet = workbook.addWorksheet(sheetOption.sheetName || 'sheet' + (i + 1));// 添加数据行sheet.addRows(sheetOption.data);// 配置视图sheet.views = sheetOption.views;// 单元格合并处理【开始行，开始列，结束行，结束列】if (sheetOption.merges){sheetOption.merges.forEach((item) => {sheet.mergeCells(item) });}// 工作表保 if (sheetOption.protect) {const res = await sheet.protect(sheetOption.protect.password, sheetOption.protect.options);}// 单元格样式处理if (sheetOption.attrs.length) {sheetOption.attrs.forEach((item) => {const attr = item.attr || {};// 获取开始行-结束行; 开始列-结束列const rowStart = item.rowStart;const rowEnd = item.rowEnd;const colStart = item.colStart;const colEnd = item.colEnd;if (rowStart) { // 设置行for (let r = rowStart; r <= rowEnd; r++) {// 获取当前行const row = sheet.getRow(r);if (colStart) { // 列设置for (let c = colStart; c <= colEnd; c++) {// 获取当前单元格const cell = row.getCell(c);Object.keys(attr).forEach((key) => {// 给当前单元格设置定义的样式cell[key] = attr[key];});}} else {// 未设置列，整行设置【大纲级别】Object.keys(attr).forEach((key) => {row[key] = attr[key];});}}} else if (colStart) { // 未设置行，只设置了列for (let c = colStart; c <= colEnd; c++) {// 获取当前列，整列设置【大纲级别】const column = sheet.getColumn(c);Object.keys(attr).forEach((key) => {column[key] = attr[key];});}} else {// 没有设置具体的行列，则为整表设置Object.keys(attr).forEach((key) => {sheet[key] = attr[key];});}})}// 列宽设置if (sheetOption.columnsWidth) {for (let i = 0; i < sheet.columns.length; i++) {sheet.columns[i].width = sheetOption.columnsWidth[i]}}}// 生成excel文件workbook.xlsx.writeBuffer().then(buffer => {// application/octet-stream 二进制数据FileSaver.saveAs(new Blob([buffer], { type: 'application/octet-stream' }), options.fileName)})}

3.在项目中使用

exportExcel(){const header = [["所在部门","隐患总数","已整改隐患数","整改中隐患数","整改率","一般隐患","","","","较大隐患","","","","重大隐患","","",""],["","","","","","隐患数","已整改数","整改中数","整改率(%)","隐患数","已整改数","整改中数","整改率(%)","隐患数","已整改数","整改中数","整改率(%)"]]const fields = ["name","count","count_yzg","count_zgz","zgl","count_common","count_common_yzg","count_common_zgz","ybyh_zgl","count_bigger","count_bigger_yzg","count_bigger_zgz","jdyh_zgl","count_biggest","count_biggest_yzg","count_biggest_zgz","zdyh_zgl"]const merges = [//导出表格的第一行第一列，行合并2个单元格，列就用自己的一个；{row: 0, col: 0, rowspan: 2, colspan: 1},{row: 0, col: 1, rowspan: 2, colspan: 1},{row: 0, col: 2, rowspan: 2, colspan: 1},{row: 0, col: 3, rowspan: 2, colspan: 1},{row: 0, col: 4, rowspan: 2, colspan: 1},{row: 0, col: 5, rowspan: 1, colspan: 4},{row: 0, col: 9, rowspan: 1, colspan: 4},{row: 0, col: 13, rowspan: 1, colspan: 4},]const config = this.exportConfig(header,fields,merges,this.tabledata[this.type]);//配置this.$utils.exportDataToExcel(config, "隐患治理情况统计表.xlsx");},//导出表格配置exportConfig(header = [], fields = [], merges = [], tableData = []){// console.log(header, fields, merges, tableData );// return//配置表头header1为一级表头，header2为二级表头，被合并的单元格为空写占位符""：// 如果导出前要处理数据，需要深克隆一份表格数据，然后进行处理const exportTableData = JSON.parse(JSON.stringify(tableData));const config = {data: exportTableData,//exportTableData为表格数据，为空的话，导出表格只显示表头fields: fields,headers: header,merges: merges,attrs: [],view: [],columnsWidth: [20, 20, 20, 20, 20,20, 20,20, 20, 20, 20, 20,20, 20,20,20],//每行列的宽// protect: {},sheetName: "个人信息"};// 设置全表单元格边框，居中布局config.attrs.push({rowStart: 0,rowEnd: config.data.length + 1,//表格表头多几层，就加几个colStart: 0,colEnd: config.fields.length - 1,attr: {alignment: { vertical: "middle", horizontal: "center" },border: {top: { style: "thin" },left: { style: "thin" },bottom: { style: "thin" },right: { style: "thin" }}}});// 设置表头填充颜色，字体加粗config.attrs.push({rowStart: 0,rowEnd: 1,//表格表头多几层，就写几colStart: 0,colEnd: config.fields.length - 1,attr: {fill: {type: "pattern",pattern: "solid",fgColor: { argb: "c5c8ce" }},font: {bold: true}}});return config;}

/**

* 导出数据到Excel 传参参数

* config.data 表格数据

* config.fields 字段列表

* config.headers excel表头列表[[]]，可以是多级表头[['A1'，'B1'],['A2'，'B2']]

* config.merges 需要合并的单元格，需要考虑表头的行数[{row:1, col:1, rowspan: 1, colspan: 2}]

* config.attrs 单元格样式配置

* config.views 工作表视图配置

* columnsWidth 每个字段列对应的宽度

* config.protect 工作表保护【此配置会保护全表，一般推荐只针对单元格进行保护配置】

* sheetName 工作表名称，默认从sheet1开始

* fileName excel文件名称

*/