vue实现纯前端导入与解析excel表格文件，导出Excel

凌儿~

2453人浏览 · 2022-12-01 15:32:00

凌儿~ · 2022-12-01 15:32:00 发布

一、安装相关依赖

npm install --save xlsx file-saver

二、使用

import * as XLSX from 'xlsx/xlsx.mjs'
const FileSaver = require('file-saver')
//import XLXS from "xlsx";import 语法引入模块，XLSX 对象为undefined

三、纯前端导入与解析excel表格文件

1、html部分

<div class="flex-center">
  <div class="container">
    {{ upload_file || "导入" }}
    <input
      type="file"
      accept=".xls,.xlsx"
      class="upload_file"
      @change="readExcel($event)"
    />
  </div>
</div>

2、JS部分

readExcel (e) {
  // 读取表格文件
  const that = this
  const files = e.target.files
  if (files.length <= 0) {
    return false
  } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
    this.$message({
      message: '上传格式不正确，请上传xls或者xlsx格式',
      type: 'warning'
    })
    return false
  } else {
    // 更新获取文件名
    that.upload_file = files[0].name
  }

  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表格内容
      that.lists = []
      // 从解析出来的数据中提取相应的数据
      ws.forEach(item => {
        that.lists.push({
          username: item['用户名'],
          phone_number: item['手机号']
        })
      })
    } catch (e) {
      return console.log(e)
    }
  }
  fileReader.readAsBinaryString(files[0])
}

四、el-table 数据导出Excel

1、html部分

<el-button class="m-t20" type="primary" @click="add()">添加数据</el-button>
<el-button class="m-t20" type="primary" @click="exportExcel()">导出数据</el-button>
<el-table
  id="out-table"
  :data="lists"
  border
  style="width:20%">
  <el-table-column
    prop="username"
    label="用户名">
  </el-table-column>
  <el-table-column
    prop="phone_number"
    label="手机号">
  </el-table-column>
  <el-table-column
    label="备注">
    <template>11</template>
  </el-table-column>
</el-table>

2、JS部分

exportExcel () {
      const tables = document.getElementById('out-table')
      const tablebook = XLSX.utils.table_to_book(tables)
      const tablewrite = XLSX.write(tablebook, {
        bookType: 'xlsx',
        bookSST: true,
        type: 'array'
      })
      try {
        FileSaver.saveAs(
          new Blob([tablewrite], { type: 'application/octet-stream' }),
          '下载的excel表单数据.xlsx'
        )
      } catch (e) {
        if (typeof console !== 'undefined') console.log(e, tablewrite)
      }
      return tablewrite
    },
    add () {
      const arr = [
        {
          username: '小一' + this.a++,
          phone_number: this.phone++
        },
        {
          username: '小二' + this.a++,
          phone_number: this.phone++
        }
      ]
      this.lists = [...arr, ...this.lists]
    },

五、样式

<style lang="less" scoped>
.container {
  border: none;
  border-radius: 4px;
  background-color: #409eff;
  height: 40px;
  margin-top: 8px;
  display: flex;
  align-items: center;
  justify-content: center;
  padding: 0 15px;
  min-width: 80px;
  *zoom: 1;
}

.upload_file {
  font-size: 20px;
  opacity: 0;
  position: absolute;
  filter: alpha(opacity=0);
  width: 60px;
}
</style>

六、完整代码

<template>
  <div class="">
    <div class="flex-center">
      <div class="container">
        {{ upload_file || "导入" }}
        <input
          type="file"
          accept=".xls,.xlsx"
          class="upload_file"
          @change="readExcel($event)"
        />
      </div>
    </div>
    <el-button class="m-t20" type="primary" @click="add()">添加数据</el-button>
    <el-button class="m-t20" type="primary" @click="exportExcel()">导出数据</el-button>
    <el-table
      id="out-table"
      :data="lists"
      border
      style="width:20%">
      <el-table-column
        prop="username"
        label="用户名">
      </el-table-column>
      <el-table-column
        prop="phone_number"
        label="手机号">
      </el-table-column>
      <el-table-column
        label="备注">
        <template>11</template>
      </el-table-column>
    </el-table>
  </div>
</template>

<script>
// const XLSX = require('xlsx')
import * as XLSX from 'xlsx/xlsx.mjs'
const FileSaver = require('file-saver') 
export default {
  data () {
    return {
      upload_file: '',
      lists: [],
      a: 0,
      phone: 13456765432
    }
  },
  methods: {
    exportExcel () {
      const tables = document.getElementById('out-table')
      const tablebook = XLSX.utils.table_to_book(tables)
      const tablewrite = XLSX.write(tablebook, {
        bookType: 'xlsx',
        bookSST: true,
        type: 'array'
      })
      try {
        FileSaver.saveAs(
          new Blob([tablewrite], { type: 'application/octet-stream' }),
          '下载的excel表单数据.xlsx'
        )
      } catch (e) {
        if (typeof console !== 'undefined') console.log(e, tablewrite)
      }
      return tablewrite
    },
    add () {
      const arr = [
        {
          username: '小一' + this.a++,
          phone_number: this.phone++
        },
        {
          username: '小二' + this.a++,
          phone_number: this.phone++
        }
      ]
      this.lists = [...arr, ...this.lists]
    },
    submit_form () {
      // 给后端发送请求，更新数据
      console.log('假装给后端发了个请求...')
    },
    readExcel (e) {
      // 读取表格文件
      const that = this
      const files = e.target.files
      if (files.length <= 0) {
        return false
      } else if (!/\.(xls|xlsx)$/.test(files[0].name.toLowerCase())) {
        this.$message({
          message: '上传格式不正确，请上传xls或者xlsx格式',
          type: 'warning'
        })
        return false
      } else {
        // 更新获取文件名
        that.upload_file = files[0].name
      }

      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表格内容
          that.lists = []
          // 从解析出来的数据中提取相应的数据
          ws.forEach(item => {
            that.lists.push({
              username: item['用户名'],
              phone_number: item['手机号']
            })
          })
          // 给后端发请求
          // this.submit_form()
        } catch (e) {
          return console.log(e)
        }
      }
      fileReader.readAsBinaryString(files[0])
    }
  }
}
</script>
<style lang="less" scoped>
.container {
  border: none;
  border-radius: 4px;
  background-color: #409eff;
  height: 40px;
  margin-top: 8px;
  display: flex;
  align-items: center;
  justify-content: center;
  padding: 0 15px;
  min-width: 80px;
  *zoom: 1;
}

.upload_file {
  font-size: 20px;
  opacity: 0;
  position: absolute;
  filter: alpha(opacity=0);
  width: 60px;
}
</style>