导入导出并将excel表格数据展示

npm install xlsx
npm install --save file-saver xlsx

<template>
  <div>
    <div class="inline-block">

      <el-upload
        class="upload-demo"
        action="https://jsonplaceholder.typicode.com/posts/"
        :on-change="handleChange"
        :file-list="fileList"
        list-type="picture"
        multiple
        show-file-list
        accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
      >
        <el-button slot="trigger" type="primary" icon="el-icon-upload">导入</el-button>
        <el-button type="primary" icon="el-icon-download" style="margin-left: 10px" @click="handleExport">导出</el-button>
      </el-upload>

    </div>

    <el-table id="tables" :data="tableData" border>
      <el-table-column prop="Project" label="姓名" />
      <el-table-column prop="ERS1" label="性别" />
      <el-table-column prop="CategoryRel" label="民族" />
      <el-table-column prop="CategoryRel2" label="身份证号" />
      <el-table-column prop="CategoryRel3" label="出生时间" />
      <el-table-column prop="CategoryRel4" label="电话" />
      <el-table-column label="操作" />
    </el-table>
  </div>
</template>
// 数据
<script>
import { excel } from '@/api/excel'
import xlsx from 'xlsx'
export default {
  data() {
    return {
      tableData: [],
      fileList: [],
      fileTemp: null, // 关键！！！！存放组件上传的excel file 用于实现读取数据
      outdata: []
    }
  },

  methods: {
    handleExport(event) {
      const loading = this.$loading({
        lock: true,
        text: '拼命加载中请稍等...',
        spinner: 'el-icon-loading',
        background: 'rgba(0, 0, 0, 0.7)'
      })
      setTimeout(() => {
        this.$confirm('确定导出列表文件?', '提示', {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'warning'
        })
          .then(() => {
            loading.close()
            excel('tables', '测试表格导出')
          })
          .catch(() => {
            loading.close()
          })
      }, 1000)
    },

    handleChange(file) {
      const _this = this

      const reader = new FileReader()
      // 提取excel中文件内容
      reader.readAsArrayBuffer(file.raw)
      reader.onload = function() {
        const buffer = reader.result
        const bytes = new Uint8Array(buffer)
        const length = bytes.byteLength
        let binary = ''
        for (let i = 0; i < length; i++) {
          binary += String.fromCharCode(bytes[i])
        }
        const XLSX = require('xlsx') // import xlsx from 'xlsx'

        // 转换二进制
        const wb = XLSX.read(binary, {
          type: 'binary'
        })
        _this.outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
        console.log(_this.outdata)
      }

      // 这里for循环将excel表格数据转化成json数据
      _this.outdata.forEach((i) => {
        const obj = {
          Project: i.姓名,
          ERS1: i.性别,
          CategoryRel: i.民族,
          CategoryRel2: i.身份证号,
          CategoryRel3: i.出生时间,
          CategoryRel4: i.电话
        }
        // 连接后台接口添加到后台数据库
        // _this
        //   .$http({
        //     method: "post",
        //     url: "/ERS/InsertERS",
        //     data: obj,
        //   })
        //   .then((response) => {
        //     if (response.data["header"]["code"] == 0) 
        //       _this.getData();

        //     } else {
        //       _this.$message.error(response.data["header"]["message"]);
        //     }
        //   })
        //   .catch((error) => {
        //     _this.$message.error(error.toString());
        //   });
        _this.tableData.push(obj)
        console.log(_this.tableData)
      })
    }
  }
}
</script>
<style>
.inline-block {
  display: inline-block;
  margin: 20px;
}
</style>


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

//二、导出需要的excel.js代码如下

// 引入依赖
import FileSaver from 'file-saver'
import * as XLSX from 'xlsx'

// id绑定的id，title表格名称
export const excel = (id, title) => {
  /* generate workbook object from table */
  //  判断要导出的节点中是否有fixed的表格，如果有，转换excel时先将该dom移除，然后append回去，
  const fix = document.querySelector('.el-table__fixed')
  let wb
  if (fix) {
    wb = XLSX.utils.table_to_book(
      document.querySelector('#' + id).removeChild(fix)
    )
    document.querySelector('#' + id).appendChild(fix)
  } else {
    wb = XLSX.utils.table_to_book(document.querySelector('#' + id))
  }

  // 网上wb = XLSX.utils.table_to_book(document.querySelector('#'+id));直接这样写，如果存在固定列，导出的excel表格会重复两遍

  /* get binary string as output */
  console.log(wb)
  const wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
  try {
    FileSaver.saveAs(
      new Blob([wbout], { type: 'application/octet-stream' }),
      title + '.xlsx'
    )
  } catch (e) {
    if (typeof console !== 'undefined') console.log(e, wbout)
  }
  return wbout
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

参考：https://blog.csdn.net/qq_39593196/article/details/126008880