功能
主要实现excel导入导出功能，同时具有合并单元格，美化单元格等功能，引用依赖包exceljs、file-saver，好像也没有什么要介绍的，可以看看官网文档然后直接使用了
导入依赖包
pnpm install exceljs file-saver @types/file-saver封装Excel帮助类（excelHelper.js）
import ExcelJS from 'exceljs'
import FileSaver from 'file-saver'
import { cloneDeep } from 'lodash'/*** 读取excel文件* @param {object} file 文件* @param {String} sheetName 获取工作表名称* @returns */
export async function readExcelFile(file, sheetName) {const arrayBuffer = file.arrayBuffer()const result = []const workbook = new ExcelJS.Workbook()await workbook.xlsx.load(arrayBuffer)const worksheet = workbook.getWorksheet(sheetName || 1)worksheet.eachRow((row, rowNumber) => {console.log(rowNumber)const rowData = []result.push(rowData)row.eachCell((cell, colNumber) => {rowData.push(cell.value)console.log(colNumber)})})return result
}
/*** * 数据导出excel* @param {String} excelOpt.fileName 导出文件名* @param {String} excelOpt.configs.sheetName 工作表名称，默认从sheet1开始* @param {Array[Array]} excelOpt.configs.headers 表格头部 eg:[['标题1','标题2'],['标题3'，'标题4']]* @param {Array[Array]} excelOpt.configs.data 表格数据* @param {Array[Object]} excelOpt.configs.merges 合并信息 eg:[row: 0, col: 0, rowspan: 3, colspan: 1]* @param {Array[object]} excelOpt.configs.views 工作表视图配置* @param {Array[Number]} config.columnsWidth 每个字段列对应的宽度* @param {Object} excelOpt.configs.protect 工作表保护【此配置会保护全表，一般推荐只针对单元格进行保护配置】* @param {Array} excelOpt.configs.fields 辅助导出顺序* */
export function exportExcel(excelOpt) {if (!excelOpt) returnconst opt = cloneDeep(excelOpt)console.dir(excelOpt.sheetInfos[0])const dataOptions = {fileName: opt.fileName || `导出excel文件【${Date.now()}】.xlsx`,worksheets: []}if (!Array.isArray(opt.sheetInfos)) {opt.sheetInfos = [opt.sheetInfos]}opt.sheetInfos.forEach(item => {//对象根据fields进行组装数组数据let data = item.data.map(obj => {return item.fields.map(key => {return obj[key]})})let excelData = [].concat(item.headers).concat(data)let merges = item.merges.map(m => {return [m.row + 1, m.col + 1, m.row + m.rowspan, m.col + m.colspan]})let excelAttrs = item.attrs.map(attr => {attr.rowStart += 1;attr.rowEnd += 1;attr.colStart += 1;attr.colEnd += 1;return attr})dataOptions.worksheets.push({data: excelData,merges: merges,attrs: excelAttrs,views: item.views,columnsWidth: item.columnsWidth,protect: item.protect,sheetName: item.sheetName})})createExcel(dataOptions)
}
// 创建Excel文件方法
async function 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);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)})
}使用（导出，导入）
//导出
const exportExcelFn = () => {const data = [{index: 1,title1: 'title1-1',title2: 'title1-2',title3: 'title1-3',title4: 'title1-4',title5: 'title1-5',title6: 'title1-6',title7: 'title1-7',title8: 'title1-8',title9: 'title1-9',title10: 'title1-10',},{index: 2,title1: 'title2-1',title2: 'title2-2',title3: 'title2-3',title4: 'title2-4',title5: 'title2-5',title6: 'title2-6',title7: 'title2-7',title8: 'title2-8',title9: 'title2-9',title10: 'title2-10',}]const opt = {fileName: '积分明细.xlsx',sheetInfos: [{fields: ['index', 'title1', 'title2', 'title3', 'title4', 'title5', 'title6', 'title7', 'title8', 'title9', 'title10'],headers: [['序号', '标题1', '标题2', '标题3', '标题4', '标题5', '标题6', '标题7', '标题8', '标题9', '标题10']],data: data,merges: [// { row: 1, col: 1, rowspan: 11, colspan: 1 },],attrs: [],columnsWidth: [20, 30, 30, 30, 30, 30, 30, 30, 30, 30, 30],sheetName: '导出数据测试'}]}//设置单元格样式opt.sheetInfos.map(item => {item.attrs.push({rowStart: 0,rowEnd: item.headers.length + item.data.length,colStart: 0,colEnd: item.headers[0].length - 1,attr: {alignment: { vertical: "middle", horizontal: "center" },// border: {// top: { style: "thin" },// left: { style: "thin" },// bottom: { style: "thin" },// right: { style: "thin" }// }}})// 设置表头填充颜色，字体加粗item.attrs.push({rowStart: 0,rowEnd: item.headers?.length - 1,colStart: 0,colEnd: item.headers[0].length - 1,attr: {fill: {type: "pattern",pattern: "solid",fgColor: { argb: "99CCFF" }},font: {bold: true}}})})exportExcel(opt)
}
<script setup>
import { readExcelFile } from '@/utils/excelHelper'const fileUploadFn = async (file) => {const excelData = await readExcelFile(file)console.log('Excel Data:', excelData)return false;
}
</script><template><div><el-upload :before-upload="fileUploadFn" accept=".xlsx, .xls"><el-button type="primary" class="upload">上传</el-button></el-upload></div>
</template>