前端导入解析excel数据，上传文件（图片，excel，zip,word等）

import XLSX from 'xlsx'
import ExcelJS from 'exceljs'
import { Trim } from '@/utils'
import { sheetColumn } from '@/utils/excelHeader.js'
import lodash from 'lodash'


export const createExcelFileExcelJS = async (arr, fileName = `下载文件.xlsx`) => {

  // 组装数据
  let list = lodash.cloneDeep(arr || [])
  let sheetData = []
  for (let i = 0, len = list.length; i < len; i++) {
    let v = list[i]
    let obj = {
      Id: 1,
      BillNo: 2,
      ServiceCode: 3,
      CustomerGroupName: 4,
      ForecastNumber: 5,
      TrackingNumber: 6,
      CustomerOrderNumber: 7,
      SystemMoney: 8,
      ErrataFeeItemName: '',
      ErrataFee: '',
      ErrataTotalFee: '',
      ErrataNote: 9,
      items: `111`
    }
    sheetData.push(obj)
  }

  // 生成sheet
  const wb = new ExcelJS.Workbook()
  const sheet = wb.addWorksheet('Sheet1')
  sheet.columns = sheetColumn
  sheet.addRows(sheetData)
  for (let i = 0, len = sheetData.length; i < len; i++) {
    sheet.getCell(i + 1, 9).dataValidation = {
      type: 'list',
      formulae: ['"One,Two,Three,Four"']
    }
  }
  const buffer = await wb.xlsx.writeBuffer()
  var blob = new Blob([buffer], {
    type: 'application/octet-stream'
  })
  var url = blob
  if (typeof url == 'object' && url instanceof Blob) {
    url = URL.createObjectURL(url) // 创建blob地址
  }
  var aLink = document.createElement('a')
  aLink.href = url
  aLink.download = fileName || '' // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
  var event
  if (window.MouseEvent) event = new MouseEvent('click')
  else {
    event = document.createEvent('MouseEvents')
    event.initMouseEvent(
      'click',
      true,
      false,
      window,
      0,
      0,
      0,
      0,
      0,
      false,
      false,
      false,
      false,
      0,
      null
    )
  }
  aLink.dispatchEvent(event)
  return
}


/*
  FileReader共有4种读取方法：
  1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。
  2.readAsBinaryString(file)：将文件读取为二进制字符串
  3.readAsDataURL(file)：将文件读取为Data URL
  4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8'
*/

/**
 *
 * @param {*} file  文件
 * @param {*} colnumData  第一张表 提交给后台的数据和列名 默认只操作第一张表
 * @param {*} colnumData2  第二张表 提交给后台的数据和列名
 * @param {*} compareField  排序字段
 * @returns  返回要提交后台的数组 第一张表和第二章表的集合
 */
export function importExcel(
  file,
  colnumData,
  colnumData2 = undefined,
  compareField = undefined
) {
  let rABS = false //是否将文件读取为二进制字符串
  const types = file.name.split('.').pop()
  let dataList = []
  const fileType = ['xlsx', 'xls'].some(item => item === types)
  if (!fileType) {
    return
  }
  return new Promise(function (resolve, reject) {
    file2Xce(file).then(tabJson => {
      if (tabJson && tabJson.length > 0) {
        let columnMapping = colnumData
        const colnumName = Object.values(columnMapping)
        let isArea = false
        //第一张
        let dataArr = tabJson[0].sheet
        let billWeight = 0
        dataArr.forEach(item => {
          let itemMap = new Map()
          for (let key in item) {
            if (colnumName.includes(Trim(key))) {
              if (Trim(key) === '对比方式' && Trim(item[key]) === '区间') {
                isArea = true
              }
              if (Trim(key) === '对账内容' && Trim(item[key]) === '计费重') {
                billWeight++
              }
              itemMap.set(Trim(key), Trim(item[key]))
            } else {
              reject(key + '  列名不存在，请核对！')
              return
            }
          }
          dataList.push(itemMap)
        })

        if (billWeight >= 2) {
          reject('计费重最多显示一条，请核对！')
          return
        }
        let list = map2json(dataList, colnumData)
        // 存在区间才去 处理第二张表
        let dataList2 = []
        let list2 = []
        if (isArea && colnumData2) {
          //第二张
          let dataArr2 = tabJson[1].sheet
          let columnMapping = colnumData2
          const colnumName = Object.values(columnMapping)
          dataArr2.forEach(item => {
            let itemMap = new Map()
            for (let key in item) {
              if (colnumName.includes(Trim(key))) {
                itemMap.set(Trim(key), Trim(item[key]))
              } else {
                reject(key + '  列名不存在，请核对！')
                return
              }
            }
            dataList2.push(itemMap)
          })
          list2 = map2json2(dataList2, colnumData2)
        }
        if (isArea && list2.length === 0) {
          reject('对比方式含有区间，区间值 不能为空哦')
          return
        }

        let data = null
        let arr = []
        list.forEach(item => {
          if (item.CompareType === 1) {
            data = item
          } else {
            arr.push(item)
          }
        })
        let arr2 = []
        list2.forEach(item => {
          if (
            Object.prototype.toString.call(item.DifferStart) ===
            '[object Undefined]' ||
            Object.prototype.toString.call(item.DifferEnd) ===
            '[object Undefined]'
          ) {
            reject('对比方式含有区间，区间值 不能为空哦')
            return
          }
          arr2.push({ ...data, ...item })
        })
        list = [...arr, ...arr2]
        if (compareField) {
          list.sort((a, b) => a[compareField].localeCompare(b[compareField]))
        }
        resolve(list)
      } else {
        reject('EXCEL文件没有数据！')
      }
    })
  })
}

/**
 *
 * @param {*} file  文件
 * @param {*} colnumData  提交给后台的数据和列名
 * @param {*} sheetIndex  0 默认取第一张表， 1代表取第二张表
 * @param {*} compareField  排序字段
 * @returns 返回要提交后台的数组/第一张表和第二章表的集合
 */
export function importExcelSingle(
  file,
  colnumData,
  sheetIndex = 0,
  compareField = undefined
) {
  const types = file.name.split('.').pop()
  let dataList = []
  const fileType = ['xlsx', 'xls'].some(item => item === types)
  if (!fileType) {
    return
  }

  return new Promise(function (resolve, reject) {
    file2XceSingle(file).then(tabJson => {
      if (tabJson && tabJson.length > 0) {
        let columnMapping = colnumData
        const colnumName = Object.values(columnMapping)

        //第一张
        let dataArr = tabJson[sheetIndex].sheet
        dataArr.forEach(item => {
          let itemMap = new Map()
          for (let key in item) {
            if (colnumName.includes(Trim(key))) {
              itemMap.set(Trim(key), Trim(item[key]))
            } else {
              reject(key + '  列名不存在，请核对！')
              return
            }
          }
          dataList.push(itemMap)
        })
        let list = map2json(dataList, colnumData)
        if (compareField) {
          list.sort(
            (a, b) =>
              a[compareField] && a[compareField].localeCompare(b[compareField])
          )
        }
        resolve(list)
      } else {
        reject('EXCEL文件没有数据！')
      }
    })
  })
}

function file2XceSingle(file) {
  return new Promise(function (resolve, reject) {
    const reader = new FileReader()
    reader.onload = function (e) {
      const data = e.target.result
      // rABS
      this.wb = XLSX.read(data, {
        // type: 'array'
        // type: 'base64' 文件流
        type: 'binary'
      })
      const result = []
      this.wb.SheetNames.forEach(sheetName => {
        result.push({
          sheetName: sheetName,
          sheet: XLSX.utils.sheet_to_json(this.wb.Sheets[sheetName])
        })
      })
      resolve(result)
    }
    reader.readAsBinaryString(file)
  })
}

function map2json2(list, colnumData, list2, colnumData2) {
  let datalist = []
  let columnMapping = colnumData
  const obj = Object.entries(columnMapping)
  list.forEach(element => {
    let json = {}
    for (let [k, v] of element) {
      for (let [j, z] of obj) {
        if (z === k) {
          if (j === 'FieldStauts') {
            json[j] = v === '启用' ? 2 : 1
          } else if (j === 'CompareType') {
            json[j] = v === '差值' ? 2 : 1
          } else {
            json[Trim(j)] = Trim(v)
          }
        }
      }
    }
    datalist.push(json)
  })
  return datalist
}

function file2Xce(file) {
  return new Promise(function (resolve, reject) {
    const reader = new FileReader()
    reader.onload = function (e) {
      const data = e.target.result
      this.wb = XLSX.read(data, {
        // type: 'array'
        type: 'binary'
      })
      const result = []
      this.wb.SheetNames.forEach(sheetName => {
        result.push({
          sheetName: sheetName,
          sheet: XLSX.utils.sheet_to_json(this.wb.Sheets[sheetName])
        })
      })
      resolve(result)
    }
    reader.readAsBinaryString(file)
  })
}

function map2json(list, colnumData) {
  let datalist = []
  let columnMapping = colnumData
  const obj = Object.entries(columnMapping)
  list.forEach(element => {
    let json = {}
    for (let [k, v] of element) {
      for (let [j, z] of obj) {
        if (z === k) {
          if (j === 'FieldStauts') {
            json[j] = v === '启用' ? 2 : 1
          } else if (j === 'CompareType') {
            json[j] = v === '差值' ? 2 : 1
          } else {
            json[Trim(j)] = Trim(v)
          }
        }
      }
    }

    datalist.push(json)
  })
  return datalist
}

第二种 可能是一样的

/*
  FileReader共有4种读取方法：
  1.readAsArrayBuffer(file)：将文件读取为ArrayBuffer。
  2.readAsBinaryString(file)：将文件读取为二进制字符串
  3.readAsDataURL(file)：将文件读取为Data URL
  4.readAsText(file, [encoding])：将文件读取为文本，encoding缺省值为'UTF-8'
*/

/**
 *
 * @param {*} file  文件
 * @param {*} colnumData  第一张表 提交给后台的数据和列名 默认只操作第一张表
 * @param {*} colnumData2  第二张表 提交给后台的数据和列名
 * @param {*} compareField  排序字段
 * @returns  返回要提交后台的数组 第一张表和第二章表的集合
 */
export function importExcel(
    file,
    colnumData,
    colnumData2 = undefined,
    compareField = undefined
) {
    let rABS = false //是否将文件读取为二进制字符串
    const types = file.name.split('.').pop()
    let dataList = []
    const fileType = ['xlsx', 'xls'].some(item => item === types)
    if (!fileType) {
        return
    }
    return new Promise(function(resolve, reject) {
        file2Xce(file).then(tabJson => {
            if (tabJson && tabJson.length > 0) {
                let columnMapping = colnumData
                const colnumName = Object.values(columnMapping)
                let isArea = false
                    //第一张
                let dataArr = tabJson[0].sheet
                let billWeight = 0
                dataArr.forEach(item => {
                    let itemMap = new Map()
                    for (let key in item) {
                        if (colnumName.includes(Trim(key))) {
                            if (Trim(key) === '对比方式' && Trim(item[key]) === '区间') {
                                isArea = true
                            }
                            if (Trim(key) === '对账内容' && Trim(item[key]) === '计费重') {
                                billWeight++
                            }
                            itemMap.set(Trim(key), Trim(item[key]))
                        } else {
                            reject(key + '  列名不存在，请核对！')
                            return
                        }
                    }
                    dataList.push(itemMap)
                })

                if (billWeight >= 2) {
                    reject('计费重最多显示一条，请核对！')
                    return
                }
                let list = map2json(dataList, colnumData)

                // 存在区间才去 处理第二张表
                let dataList2 = []
                let list2 = []
                if (isArea && colnumData2) {
                    //第二张
                    let dataArr2 = tabJson[1].sheet
                    let columnMapping = colnumData2
                    const colnumName = Object.values(columnMapping)
                    dataArr2.forEach(item => {
                        let itemMap = new Map()
                        for (let key in item) {
                            if (colnumName.includes(Trim(key))) {
                                itemMap.set(Trim(key), Trim(item[key]))
                            } else {
                                reject(key + '  列名不存在，请核对！')
                                return
                            }
                        }
                        dataList2.push(itemMap)
                    })
                    list2 = map2json2(dataList2, colnumData2)
                }
                if (isArea && list2.length === 0) {
                    reject('对比方式含有区间，区间值 不能为空哦')
                    return
                }

                let data = null
                let arr = []
                list.forEach(item => {
                    if (item.CompareType === 1) {
                        data = item
                    } else {
                        arr.push(item)
                    }
                })
                let arr2 = []
                list2.forEach(item => {
                    if (
                        Object.prototype.toString.call(item.DifferStart) ===
                        '[object Undefined]' ||
                        Object.prototype.toString.call(item.DifferEnd) ===
                        '[object Undefined]'
                    ) {
                        reject('对比方式含有区间，区间值 不能为空哦')
                        return
                    }
                    arr2.push({...data, ...item })
                })
                list = [...arr, ...arr2]
                    //排序有问题
                    // if (compareField) {
                    //   list.sort((a, b) => a[compareField].localeCompare(b[compareField]))
                    // }
                resolve(list)
            } else {
                reject('EXCEL文件没有数据！')
            }
        })
    })
}

/**
 *
 * @param {*} file  文件
 * @param {*} colnumData  提交给后台的数据和列名 存数组
 * @param {*} sheetIndex  0 默认取第一张表， 1代表取第二张表
 * @param {*} compareField  排序字段
 * @returns 返回要提交后台的数组/第一张表和第二章表的集合
 */
export function importExcelSingle(
    file,
    colnumData,
    sheetIndex = 0,
    compareField = undefined,
    isVer = true,
    sortColnum = []
) {
    const types = file.name.split('.').pop()
    let dataList = []
    const fileType = ['xlsx', 'xls'].some(item => item === types)
    if (!fileType) {
        return
    }

    return new Promise(function(resolve, reject) {
        file2XceSingle(file).then(tabJson => {
            if (tabJson && tabJson.length > 0) {
                let columnMapping = colnumData
                let colnum = Object.values(columnMapping)
                let colnumName = []
                for (let i = 0, len = colnum.length; i < len; i++) {
                    let item = colnum[i]
                    colnumName.push(item.replace(/\s*/g, ''))
                }

                //第一张
                let dataArr = tabJson[sheetIndex].sheet

                dataArr.forEach((item, i) => {
                    let itemMap = new Map()
                    let colName = []
                    for (let key in item) {
                        //
                        // 是否校验
                        if (isVer) {
                            if (colnumName.includes(Trim(key))) {
                                if (!colName.includes(Trim(key))) {
                                    colName.push(Trim(key))
                                }

                                itemMap.set(Trim(key), Trim(item[key]))
                            } else {
                                if (key === '__EMPTY') {
                                    reject('列名为空那列，存在多余数据，请核对！')
                                    return
                                }
                                reject(key + '  列名不存在，请核对！')
                                return
                            }
                        } else {
                            colnumName.forEach((v, i) => {
                                let str = `${v}`
                                let str2 = `${Trim(key)}`
                                if (str2 == str) {
                                    itemMap.set(Trim(key), Trim(item[key]))
                                }
                            })
                        }
                    }
                    //

                    if (isVer && sortColnum.join('') !== colName.join('')) {
                        reject(
                            `${sortColnum.join(
                ''
              )} 等列，导入失败，请检查模版字段或者字段顺序是否符合`
                        )
                        return
                    }
                    dataList.push(itemMap)
                })
                let list = map2json(dataList, colnumData)

                resolve(list)
            } else {
                reject('EXCEL文件没有数据！')
            }
        })
    })
}

function file2XceSingle(file) {
    return new Promise(function(resolve, reject) {
        const reader = new FileReader()
        reader.onload = function(e) {
            const data = e.target.result
                // rABS
            this.wb = XLSX.read(data, {
                // type: 'array'
                // type: 'base64' 文件流
                type: 'binary'
            })
            const result = []
            const sheet2JSONOpts = {
                /** Default value for null/undefined values */
                defval: '' // 给defval赋值为空的字符串
            }
            this.wb.SheetNames.forEach(sheetName => {
                result.push({
                    sheetName: sheetName,
                    sheet: XLSX.utils.sheet_to_json(
                        this.wb.Sheets[sheetName],
                        sheet2JSONOpts
                    )
                })
            })

            resolve(result)
        }
        reader.readAsBinaryString(file)
            // reader.readAsBinaryString(file.raw)
            // reader.readAsArrayBuffer(file)
    })
}

function map2json2(list, colnumData, list2, colnumData2) {
    let datalist = []
    let columnMapping = colnumData
    const obj = Object.entries(columnMapping)
    list.forEach(element => {
        let json = {}
        for (let [k, v] of element) {
            for (let [j, z] of obj) {
                if (z === k) {
                    if (j === 'FieldStauts') {
                        json[j] = v === '启用' ? 2 : 1
                    } else if (j === 'CompareType') {
                        json[j] = v === '差值' ? 2 : 1
                    } else {
                        json[Trim(j)] = Trim(v)
                    }
                }
            }
        }
        datalist.push(json)
    })
    return datalist
}

function file2Xce(file) {
    return new Promise(function(resolve, reject) {
        const reader = new FileReader()
        reader.onload = function(e) {
            const data = e.target.result
            this.wb = XLSX.read(data, {
                // type: 'array'
                type: 'binary'
            })
            const result = []
            const sheet2JSONOpts = {
                /** Default value for null/undefined values */
                defval: '' // 给defval赋值为空的字符串
            }
            this.wb.SheetNames.forEach(sheetName => {
                result.push({
                    sheetName: sheetName,
                    sheet: XLSX.utils.sheet_to_json(
                        this.wb.Sheets[sheetName],
                        sheet2JSONOpts
                    )
                })
            })
            resolve(result)
        }
        reader.readAsBinaryString(file)
            // reader.readAsBinaryString(file.raw)
            // reader.readAsArrayBuffer(file)
    })
}

function map2json(list, colnumData) {
    let datalist = []
    let columnMapping = colnumData
    const obj = Object.entries(columnMapping)

    list.forEach(element => {
        let json = {}
        for (let [k, v] of element) {
            for (let [j, z] of obj) {
                if (z === k) {
                    if (j === 'FieldStauts') {
                        json[j] = v === '启用' ? 2 : 1
                    } else if (j === 'CompareType') {
                        json[j] = v === '差值' ? 2 : 1
                    } else {
                        json[Trim(j)] = Trim(v)
                    }
                }
            }
        }

        datalist.push(json)
    })
    return datalist
}