表格上传包括单元格合并类

表格上传封装

此处包含未合并的单元格或者合并过的单元格表格上传

封装类：

/*
 * @Author: menxiaojin
 * @Date: 2023-04-18 11:11:36
 * @LastEditors: menxiaojin
 * @LastEditTime: 2023-04-20 16:45:52
 */
import * as XLSX from 'xlsx'
import {message} from './message';

export default class ExcelReader {
    constructor(file) {
        this.excelData = null
        this.mergeMap = {}
        // this.read(file)
    }

    read(file) {
        var that = this
        return new Promise(function(resolve, reject) {
            const reader = new FileReader()
            reader.onload = function(e) {
            // console.log('==',e)
            const data = e.target.result
            let datajson = XLSX.read(data, {
                type: 'binary',
                cellDates: true
            })

            let worksheet = datajson.Sheets[datajson.SheetNames[0]]; // 只能通过工作表名称来获取指定工作表
            that.excelData = worksheet
            that.initMerge() //初始化合并单元格信息

            const result = []
            datajson.SheetNames.forEach(sheetName => {
                result.push({
                sheetName: sheetName,
                // sheet: XLSX.utils.sheet_to_json(datajson.Sheets[sheetName])
                sheet: XLSX.utils.sheet_to_json(that.excelData)  //打印出所需的数据
                })
            })
            // console.log('===>>>>',datajson)

            resolve(result)
            }

            reader.readAsBinaryString(file.file)
        })
        // return '123'
    }

    /**
     * 初始合并单元格
     * 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) {
                    console.log(rowIndex,endRow)
                    let column = createCol(columnIndex);
                    let row = rowIndex + 1
                    if(this.excelData[startColumn + startRow]==undefined){
                        message({
                            message: '表格格式不正确，请勿包含空行或空列',
                            type: 'error'
                          });
                    }else{
                        this.mergeMap[column + row] = this.excelData[startColumn + startRow]['v']
                        //相关表格数据展示
                        this.excelData[column + row]={t:'s',v:this.excelData[startColumn + startRow]['v'],w:this.excelData[startColumn + startRow]['v']}
                    }
                    rowIndex++
                }
                columnIndex++
            }

        })
    }

}

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
}

 

页面引用：

import ExcelReader from '@/api/uploadExcel';

 

在相应的上传方法上做如下操作：

getExcel(file, datatype) {
     let lastindex = file.file.name.lastIndexOf('.');
     const types = file.file.name.slice(lastindex+1,file.file.name.length) //获取文件后缀
     const fileType = ['xlsx','xls'].indexOf(types);
     if (fileType == -1) {
       this.$modal.msgError("格式错误！请重新选择");
       return
     }
     const b = new ExcelReader(file);
     var aa = b.read(file)
     //这里的aa就是打印出的相关表格的数据，后续操作根据自己的实际需要进行展示
 }