论如何优雅的用Vue从前台导入excel（并处理单元格合并问题）

57 阅读 0 评论 38 点赞

我是靠谱客的博主谨慎白猫，最近开发中收集的这篇文章主要介绍论如何优雅的用Vue从前台导入excel（并处理单元格合并问题），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近接到一个需求导入excel后还要进行编辑再保存，所以采用了前台导入的方式，使用的xlsx

假设表格是这样的

第一步使用input组件进行文件上传

<span>
    <input ref="fileInput" type="file" @change="fileChangeHandle" style="display: none;">
    <el-button type="primary" size="small" icon="el-icon-folder-add" @click="clickHandle"><slot></slot></el-button>
</span>

clickHandle() {
    this.$refs.fileInput.click()
},
fileChangeHandle(e) {
    let file = this.$refs.fileInput.files[0];// 通过DOM取文件数据
    let formData = new FormData();//new一个formData事件
    formData.append("file", file)
    this.$emit('changeHandle', {data: formData, clear: () => e.target.value = null, file: file})
}

这样就拿到了上传的文件

第二步使用xlsx读取文件，并对合并的单元格进行处理

read(sheet, cbk) {
    //拿到所导入文件的名字
    const fileName = this.file
    //定义reader，存放文件读取方法
    const reader = new FileReader()
    //启动函数
    reader.readAsBinaryString(fileName)
    //onload在文件被读取时自动触发
    reader.onload = (e) => {
      //workbook存放excel的所有基本信息
      const workbook = XLSX.read(e.target.result, {type: 'binary'});
      //定义sheetList中存放excel表格的sheet表，就是最下方的tab
      let worksheet = workbook.Sheets[workbook.SheetNames[sheet]]; // 只能通过工作表名称来获取指定工作表
      this.excelData = worksheet
      this.initMerge() //初始化合并单元格信息
      cbk(this)
    }
  }

initMerge() {
    const merges = this.excelData['!merges'];
    merges.forEach(p => {
      let columnIndex = p['s']['c']
      const endColumn = p['e']['c']
      let rowIndex = p['s']['r']
      const endRow = p['e']['r']
      const startColumn = String.fromCharCode(A + p['s']['c'])
      const startRow = p['s']['r'] + 1
      while (columnIndex <= endColumn) {
        rowIndex = p['s']['r']
        while (rowIndex <= endRow) {
          let column = String.fromCharCode(A + columnIndex);
          let row = rowIndex + 1
          this.mergeMap[column + row] = this.excelData[startColumn + startRow]['v']
          rowIndex++
        }
        columnIndex++
      }

    })
  }

excel 默认合并的单元格只会保留第一格的值，进行处理后将所有值存入了变量mergeMap

打印一下处理好的合并单元格map

第三步数据提取

参数：

titleLine: 标题行

startRow：数据开始行

startColumn：数据开始列

alias：别名传入一个AliasMap对象

toArray(titleLine = 1, startRow = 1, startColumn = 'A', alias = null) {
    if (this.excelData == null || !this.excelData) {
      return []
    }
    const resultArr = []

    const length = this.excelData['!rows'].length
    for (let i = startRow; i < length; i++) {
      const item = {}
      let column = startColumn
      do {
        //拿到属性名 如果没有就去合并单元格里面找
        let attrName = ''
        if(this.excelData[column + titleLine]) {
          attrName = this.excelData[column + titleLine]['v']
        } else {
          attrName = this.mergeMap[column + titleLine]
        }

        //如果设置了别名
        if (alias != null) {
          let prev = ''
          if (alias.prevLine && alias.prevLine > 0) {
            prev = this.mergeMap[column + alias.prevLine] + '-'
          }
          attrName = alias.getAlias(prev + attrName)
        }
        if(this.excelData[column + i]) {
          item[attrName] = this.excelData[column + i]['v']
        } else {
          item[attrName] = ''
        }
        column = this.chartAdd(column)
      } while (this.excelData[column + titleLine])
      resultArr.push(item)
    }
    return resultArr

  }

这里我进行了取别名的操作因为excel里面读取的是汉字将属性名映射为对应的字段名

const map = {
          '班级': 'banji',
          '语文': 'yuwen',
          '数学': 'shuxue',
          '英语': 'yingyu',
          '计算机基础': 'jisuanjijichu',
          '算法': 'suanfa',
          '数据结构': 'shujujiegou'
        }
 e.toArray(3, 4, 'A', new AliasMap(map));

打印一下读取结果

但是这还不够，有时候会有这样的表格

这样他的表头会有重复的时候，只需要构建别名时加入前缀即可

读取结果：

完整代码：

import XLSX from "xlsx";

/**
 * Excel读取类
 * @author: Savitar
 * @datetime: 2021-10-14
 */

const A = 65

export class ExcelReader {
    constructor(file, sheet, cbk) {
        this.file = file
        this.excelData = null
        this.mergeMap = {}
        this.read(sheet, cbk)
    }

    read(sheet, cbk) {
        //拿到所导入文件的名字
        const fileName = this.file
        //定义reader，存放文件读取方法
        const reader = new FileReader()
        //启动函数
        reader.readAsBinaryString(fileName)
        //onload在文件被读取时自动触发
        reader.onload = (e) => {
            //workbook存放excel的所有基本信息
            const workbook = XLSX.read(e.target.result, {type: 'binary'});
            //定义sheetList中存放excel表格的sheet表，就是最下方的tab
            let worksheet = workbook.Sheets[workbook.SheetNames[sheet]]; // 只能通过工作表名称来获取指定工作表
            this.excelData = worksheet
            this.initMerge()
            cbk(this)
        }
    }

    /**
     * 初始合并单元格
     * excel合并单元格后默认只有左上角的单元格保留值 此方法会将所有合并的单元格都赋值
     */



    initMerge() {
        const merges = this.excelData['!merges'];
        if(!merges) return
        merges.forEach(p => {
            let columnIndex = p['s']['c']
            const endColumn = p['e']['c']
            let rowIndex = p['s']['r']
            const endRow = p['e']['r']
            const startColumn = createCol(p['s']['c'])
            const startRow = p['s']['r'] + 1
            while (columnIndex <= endColumn) {
                rowIndex = p['s']['r']
                while (rowIndex <= endRow) {
                    let column = createCol(columnIndex);
                    let row = rowIndex + 1
                    this.mergeMap[column + row] = this.excelData[startColumn + startRow]['v']
                    rowIndex++
                }
                columnIndex++
            }

        })
    }

    toArray(titleLine = 1, startRow = 1, startColumn = 'A', alias = null, endRow = 999) {
        if (this.excelData == null || !this.excelData) {
            return []
        }
        const resultArr = []
        let length;
        if(this.excelData['!rows'] && this.excelData['!rows'].length) {
            length = this.excelData['!rows'].length
        } else {
            length = endRow;
        }

        for (let i = startRow; i <= length; i++) {
            const item = {}
            let column = startColumn
            let columnIndex = startColumn.charCodeAt()
            do {
                //拿到属性名
                let attrName = ''
                if (this.excelData[column + titleLine]) {
                    attrName = this.excelData[column + titleLine]['v']
                } else {
                    attrName = this.mergeMap[column + titleLine]
                }

                //如果设置了别名
                if (alias != null) {
                    let prev = ''
                    if (alias.prevLine && alias.prevLine > 0) {
                        prev = this.mergeMap[column + alias.prevLine] + '-'
                    }
                    attrName = alias.getAlias(prev + attrName)
                }
                if (this.excelData[column + i]) {
                    item[attrName] = this.excelData[column + i]['v']
                } else {
                    item[attrName] = ''
                }
                column = this.chartAdd(columnIndex)
                columnIndex++
            } while (this.excelData[column + titleLine])
            resultArr.push(item)
        }
        return resultArr

    }

    chartAdd(columnIndex) {
        return createCol(columnIndex - A)
    }
}

function createCol(n) {
    const ordA = 'A'.charCodeAt(0)
    const ordZ = 'Z'.charCodeAt(0)
    const len = ordZ - ordA + 1
    let str = ""
    while (n >= 0) {
        str = String.fromCharCode(n % len + ordA) + str
        n = Math.floor(n / len) - 1
    }
    return str
}

/***
 *
 * @param file
 * @param sheet
 * @param cbk
 */

/***
 * 别名map
 */
export class AliasMap {
    constructor(map = {}, prevLine) {
        this.map = map;
        this.prevLine = prevLine
    }

    getAlias(name) {
        return this.map[name] || name
    }

}