Vue里如何实现excel转json的功能「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

前言：
因为做微信小程序云开发，在云开发导入数据需要json格式的，然鹅我们市场部的小姐姐给我的文档是excel文件，导致我需要去手动录入，后来在网上搜了下。
有通过复制excel文件内容粘贴后生成的：http://www.jsonla.com/excel2json/
有通过上传excel文件后生成的json文件下载却需要付费的：http://www.yzcopen.com/doc/exceljson

搞得那么难受不如自己写一个，效果图如下：

使用地址：https://test-mars.qtshe.com/tool/excelToJson

首先 我们推荐下两个插件，
一个是复制的插件：vue-clipboard2
一个处理xlsx的插件：XLSX

先给它安排上：

 cnpm i xlsx --save-dev 

安装后在当前页面引入：

import XLSX from "xlsx";   //引入

这里我使用了iview的框架，就是为了好看用，没其他效果。
我们先编写页面布局样式：

<template>
<div>
  <Card title="导入EXCEL">
    <Row>
      <Upload action="" :before-upload="handleBeforeUpload" accept=".xls, .xlsx">
        <Button icon="ios-cloud-upload-outline" :loading="uploadLoading" @click="handleUploadFile">上传文件</Button>
      </Upload>
    </Row>
    <Row>
      <div class="ivu-upload-list-file" v-if="file !== null">
        <Icon type="ios-stats"></Icon>
        {
  
  { file.name }}
        <Icon v-show="showRemoveFile" type="ios-close" class="ivu-upload-list-remove" @click.native="handleRemove()"></Icon>
      </div>
    </Row>
    <Row>
      <transition name="fade">
        <Progress v-if="showProgress" :percent="progressPercent" :stroke-width="2">
          <div v-if="progressPercent == 100">
            <Icon type="ios-checkmark-circle"></Icon>
            <span>成功</span>
          </div>
        </Progress>
      </transition>
    </Row>
  </Card>
  <Row class="margin-top-10">
    <Table :columns="tableTitle" :height="300" :data="tableData" :loading="tableLoading"></Table>
  </Row>
  <Row class="margin-top-10" v-if="content">
    <Input v-model="content" :rows="10" type="textarea" placeholder="Enter something..." />
    <Button type="info" style="margin-top: 20px;" @click="copy(content)">复制</Button>
    <Button type="success" style="margin-top: 20px; margin-left: 10px;" @click="saveJSON">点击下载</Button>
  </Row>
</div>
</template>

引入excel.js文件，以下代码为该文件内容
import excel from ‘@/libs/excel’

/* eslint-disable */
import XLSX from 'xlsx';

function auto_width(ws, data){
    /*set worksheet max width per col*/
    const colWidth = data.map(row => row.map(val => {
        /*if null/undefined*/
        if (val == null) {
            return {'wch': 10};
        }
        /*if chinese*/
        else if (val.toString().charCodeAt(0) > 255) {
            return {'wch': val.toString().length * 2};
        } else {
            return {'wch': val.toString().length};
        }
    }))
    /*start in the first row*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
        for (let j = 0; j < colWidth[i].length; j++) {
            if (result[j]['wch'] < colWidth[i][j]['wch']) {
                result[j]['wch'] = colWidth[i][j]['wch'];
            }
        }
    }
    ws['!cols'] = result;
}

function json_to_array(key, jsonData){
    return jsonData.map(v => key.map(j => { return v[j] }));
}

// fix data,return string
function fixdata(data) {
    let o = ''
    let l = 0
    const w = 10240
    for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)))
    o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
    return o
}

// get head from excel file,return array
function get_header_row(sheet) {
    const headers = []
    const range = XLSX.utils.decode_range(sheet['!ref'])
    let C
    const R = range.s.r /* start in the first row */
    for (C = range.s.c; C <= range.e.c; ++C) { /* walk every column in the range */
        var cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })] /* find the cell in the first row */
        var hdr = 'UNKNOWN ' + C // <-- replace with your desired default
        if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
        headers.push(hdr)
    }
    return headers
}

export const export_table_to_excel= (id, filename) => {
    const table = document.getElementById(id);
    const wb = XLSX.utils.table_to_book(table);
    XLSX.writeFile(wb, filename);

    /* the second way */
    // const table = document.getElementById(id);
    // const wb = XLSX.utils.book_new();
    // const ws = XLSX.utils.table_to_sheet(table);
    // XLSX.utils.book_append_sheet(wb, ws, filename);
    // XLSX.writeFile(wb, filename);
}

export const export_json_to_excel = ({data, key, title, filename, autoWidth}) => {
    const wb = XLSX.utils.book_new();
    data.unshift(title);
    const ws = XLSX.utils.json_to_sheet(data, {header: key, skipHeader: true});
    if(autoWidth){
        const arr = json_to_array(key, data);
        auto_width(ws, arr);
    }
    XLSX.utils.book_append_sheet(wb, ws, filename);
    XLSX.writeFile(wb, filename + '.xlsx');
}

export const export_array_to_excel = ({key, data, title, filename, autoWidth}) => {
    const wb = XLSX.utils.book_new();
    const arr = json_to_array(key, data);
    arr.unshift(title);
    const ws = XLSX.utils.aoa_to_sheet(arr);
    if(autoWidth){
        auto_width(ws, arr);
    }
    XLSX.utils.book_append_sheet(wb, ws, filename);
    XLSX.writeFile(wb, filename + '.xlsx');
}

export const read = (data, type) => {
    /* if type == 'base64' must fix data first */
    // const fixedData = fixdata(data)
    // const workbook = XLSX.read(btoa(fixedData), { type: 'base64' })
    const workbook = XLSX.read(data, { type: type });
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];
    const header = get_header_row(worksheet);
    const results = XLSX.utils.sheet_to_json(worksheet);
    return {header, results};
}

export default {
  export_table_to_excel,
  export_array_to_excel,
  export_json_to_excel,
  read
}

点击上传前校验下文件格式，如果格式正确则进行转换操作：

handleBeforeUpload(file) {
      const fileExt = file.name.split('.').pop().toLocaleLowerCase()
      if (fileExt === 'xlsx' || fileExt === 'xls') {
        this.readFile(file)
        this.readExcel(file)
        this.file = file
      } else {
        this.$Notice.warning({
          title: '文件类型错误',
          desc: '文件：' + file.name + '不是EXCEL文件，请选择后缀为.xlsx或者.xls的EXCEL文件。'
        })
      }
      return false
    },

接着咱们开始写点击上传后读取的操作：

// 读取文件
    readFile(file) {
      const reader = new FileReader()
      reader.readAsArrayBuffer(file)
      reader.onloadstart = e => {
        this.uploadLoading = true
        this.tableLoading = true
        this.showProgress = true
      }
      reader.onprogress = e => {
        this.progressPercent = Math.round(e.loaded / e.total * 100)
      }
      reader.onerror = e => {
        this.$Message.error('文件读取出错')
      }
      reader.onload = e => {
        this.$Message.info('文件读取成功')
        const data = e.target.result
        const {
          header,
          results
        } = excel.read(data, 'array')
        const tableTitle = header.map(item => {
          return {
            title: item,
            key: item
          }
        })
        this.tableData = results
        this.tableTitle = tableTitle
        this.uploadLoading = false
        this.tableLoading = false
        this.showRemoveFile = true
      }
    },
    readExcel(file) {
      // 表格导入
      const fileReader = new FileReader()
      fileReader.onload = ev => {
        try {
          const data = ev.target.result
          const workbook = XLSX.read(data, {
            type: "binary"
          })
          const wsname = workbook.SheetNames[0] // 取第一张表
          const ws = XLSX.utils.sheet_to_json(workbook.Sheets[wsname]) // 生成json表格内容
          this.jsonData = ws
          this.content = JSON.stringify(ws)
        } catch (e) {
          return false
        }
      }
      fileReader.readAsBinaryString(file)
    },

最后加上复制的操作，以及点击下载生成后的json文件。

copy(content) {
      this.$copyText(content).then((e) => {
        this.$Message.success('复制成功')
      }, function(e) {
        this.$Message.error('复制失败，请手动复制')
      })
    },
 saveJSON() {
      if (!this.jsonData) {
        alert('data is null')
        return
      }
      let filename = 'data.json'
      if (typeof this.jsonData === 'object') {
        this.jsonData = JSON.stringify(this.jsonData, undefined, 4)
      }
      var blob = new Blob([this.jsonData], {
        type: 'text/json'
      })
      var e = document.createEvent('MouseEvents')
      var a = document.createElement('a')
      a.download = filename
      a.href = window.URL.createObjectURL(blob)
      a.dataset.downloadurl = ['text/json', a.download, a.href].join(':')
      e.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null)
      a.dispatchEvent(e)
    }

以上就是一个完整的在vue里实现excel转json文件的方案，如有不懂的可以下面留言，可提供完整代码。