Vue3-导出导入excel

功能

主要实现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) return
    const 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>