第四篇：VUE 利用 xlsx 和 file-saver 实现 Excel 表格的导入与导出

第四篇：VUE 利用 xlsx 和 file-saver 实现 Excel 表格的导入与导出

要点：本章在 xlsx 和 file-saver 基础上，简单的封装了方法，实现了 表格 Excel 的导出 与 导入。文件类型可支持 xlsx、csv 和 txt 。

页面的大致效果如下：

   

1. npm 安装 xlsx 和  file-saver

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

2. 封装的方法文件(可直接使用）

    2.1 工具类文件名： excel.js

import XLSX from 'xlsx'
import { saveAs } from 'file-saver'

// 自动宽度计算
function AutoWidth(ws, arr) {
  // 设置worksheet每列的最大宽度
  const colWidth = arr.map(row =>
    row.map(val => {
      // 判断是否为null/undefined
      if (val == null) {
        return { wch: 10 }
      } else if (val.toString().charCodeAt(0) > 255) {
        // 判断是否为中文
        return { wch: val.toString().length * 2 }
      } else {
        return { wch: val.toString().length }
      }
    })
  )
  // 以第一行为初始值
  const 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
}

// 数组转换成JSON
function formatJSON(key, data) {
  return data.map(v =>
    key.map(i => {
      return v[i]
    })
  )
}

// 字符串转ArrayBuffer
function s2ab(s) {
  const buf = new ArrayBuffer(s.length)
  const view = new Uint8Array(buf)
  for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
  return buf
}

// 导出EXCEL表格
export function exportDataToExcel({
  header, // 表头名数组
  key, // 列对应字段数组
  data, // 需要导出数据的数组
  fileName, // 导出文件名
  autoWidth = true, // 是否自动宽度
  bookType = 'xlsx' // 导出文件格式
} = {}) {
  // 创建Workbook对象
  const wb = XLSX.utils.book_new()
  const arr = formatJSON(key, data)
  fileName = fileName || 'excel-list'
  arr.unshift(header)
  // 将数组数据转换为worksheet
  const ws = XLSX.utils.aoa_to_sheet(arr)
  if (autoWidth) {
    AutoWidth(ws, arr)
  }
  // 向Workbook对象中追加worksheet和fileName
  XLSX.utils.book_append_sheet(wb, ws, fileName)
  // 生成EXCEL的配置项
  const wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  })
  // 浏览器下载
  saveAs(
    new Blob([s2ab(wbout)], {
      type: 'application/octet-stream'
    }),
    `${fileName}.${bookType}`
  )
}

// 从Excel文件中获取表格头
function getHeaderRow(sheet) {
  const headers = []
  // 将 A1:G8 这种字符串转换为行列对象
  const range = XLSX.utils.decode_range(sheet['!ref'])
  let C
  const R = range.s.r
  // 从第一列开始，遍历范围中的每一列
  for (C = range.s.c; C <= range.e.c; ++C) {
    // 将行列对象转换为 A1 这种字符串
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })]
    // 用默认值替换
    let hdr = 'UNKNOWN ' + C
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell)
    headers.push(hdr)
  }
  return headers
}

// 读取Excel文件
export function readDataFromExcel(data, type) {
  // 读取Excel文件并保存到Workbook对象
  const workbook = XLSX.read(data, { type: type })
  const firstSheetName = workbook.SheetNames[0]
  // 获取Workbook对象的worksheet
  const worksheet = workbook.Sheets[firstSheetName]
  const header = getHeaderRow(worksheet)
  // 将worksheet转化成数组
  const results = XLSX.utils.sheet_to_json(worksheet)
  return { header, results }
}

export default {
  exportDataToExcel,
  readDataFromExcel
}

   2.2 上传 excel 表格公共组件（可拖拽上传）：UploadExcel.vue

<template>
  <div class="upload-excel-wrapper">
    <el-upload
      ref="uploadExcel"
      drag
      action=""
      accept=".xlsx, .xls, .csv"
      :multiple="false"
      :show-file-list="false"
      :auto-upload="false"
      :limit="1"
      :on-exceed="handleLimit"
      :on-change="handleChange"
    >
      <i class="el-icon-upload" />
      <div class="el-upload__text">
        将Excel文件拖到此处，或<em>点击上传</em>
      </div>
    </el-upload>
  </div>
</template>

<script>
import excel from './excel'

export default {
  name: 'UploadExcel',
  methods: {
    handleLimit() {
      this.$message.warning('Excel文件只支持单个上传!')
      return false
    },
    handleChange(file) {
      const rawFile = file.raw
      if (!rawFile) return false
      if (!this.isExcel(rawFile)) {
        this.$message.warning('Excel文件只支持.xlsx, .xls, .csv格式!')
        return false
      }
      if (this.isLimit1M(rawFile)) {
        this.readFile(rawFile)
      }
    },
    isLimit1M(file) {
      const isLimit1M = file.size / 1024 / 1024 < 1
      if (isLimit1M) {
        return true
      } else {
        this.$message.warning('上传的Excel文件大小不能超过1M!')
        return false
      }
    },
    isExcel(file) {
      return /\.(xlsx|xls|csv)$/.test(file.name)
    },
    readFile(file) {
      const reader = new FileReader()
      reader.onload = e => {
        const data = e.target.result
        const { header, results } = excel.readDataFromExcel(data, 'array')
        this.$emit('on-success', { header, results })
      }
      reader.readAsArrayBuffer(file)
      reader.onerror = e => {
        this.$message.error('Excel文件读取出错!' || e)
      }
      this.$refs.uploadExcel.clearFiles()
    }
  }
}
</script>

<style lang="scss">
.upload-excel-wrapper {
  display: flex;
  align-content: center;
  justify-content: center;
  margin-bottom: 30px;
  .el-upload-dragger {
    width: 580px;
  }
}
</style>

2. 使用上面的工具类和上传组件：PageExcel.vue

<template>
  <div class="excel-block">
    <div class="export-excel-wrapper">
      <el-card shadow="always">
        <el-form
          ref="searchForm"
          :inline="true"
          :model="exportParam"
          label-width="100px"
          class="search-form"
        >
          <el-form-item label="文件名:">
            <el-input v-model="exportParam.fileName" placeholder="文件名" />
          </el-form-item>
          <el-form-item label="自动宽度:">
            <el-radio-group v-model="exportParam.autoWidth">
              <el-radio :label="true">自动</el-radio>
              <el-radio :label="false">固定</el-radio>
            </el-radio-group>
          </el-form-item>
          <el-form-item label="文件类型:">
            <el-select v-model="exportParam.type" placeholder="文件类型">
              <el-option value="xlsx" label="xlsx" />
              <el-option value="csv" label="csv" />
              <el-option value="txt" label="txt" />
            </el-select>
          </el-form-item>
          <el-form-item>
            <el-button
              type="primary"
              style="margin-left: 30px"
              @click="handleExport"
              >导出Excel</el-button
            >
          </el-form-item>
        </el-form>
        <el-table
          ref="multipleTable"
          v-loading="listLoading"
          :data="tableData"
          tooltip-effect="dark"
          style="width: 100%"
          size="medium"
          @selection-change="handleSelectionChange"
        >
          <el-table-column type="selection" width="50" align="center" />
          <el-table-column prop="id" label="编号" align="center" />
          <el-table-column prop="name" label="姓名" align="center" />
          <el-table-column prop="sex" label="性别" align="center" />
          <el-table-column prop="phone" label="手机" align="center" />
          <el-table-column prop="education" label="学历" align="center" />
          <el-table-column
            prop="hobby"
            label="爱好"
            align="center"
            width="300"
          />
        </el-table>
      </el-card>
    </div>
    <div class="import-excel-wrapper">
      <el-card shadow="always">
        <UploadExcel @on-success="handleSuccess" />
        <el-table
          :data="tableData2"
          tooltip-effect="dark"
          style="width: 100%"
          size="medium"
        >
          <el-table-column
            v-for="item in tableHeader"
            :key="item"
            :prop="item"
            :label="item"
            align="center"
          />
        </el-table>
      </el-card>
    </div>
  </div>
</template>

<script>
import UploadExcel from './UploadExcel'
import excel from './excel'

export default {
  name: 'PageExcel',
  components: { UploadExcel },
  data() {
    return {
      listLoading: false,
      exportParam: {
        fileName: '',
        autoWidth: true,
        type: 'xlsx'
      },
      tableData: [
        {
          id: 1001,
          name: '张三',
          sex: '男',
          phone: 15266001235,
          education: '高中',
          married: 1,
          forbid: true,
          hobby: '羽毛球、篮球、听歌、阅读'
        },
        {
          id: 1002,
          name: '李四',
          sex: '男',
          phone: 15266006621,
          education: '大专',
          married: 3,
          forbid: false,
          hobby: '乒乓球、排球、游泳'
        },
        {
          id: 1003,
          name: '王五',
          sex: '男',
          phone: 15264848125,
          education: '大学',
          married: 0,
          forbid: true,
          hobby: '旱冰、滑雪、跳高、打游戏'
        },
        {
          id: 1004,
          name: '辛八',
          sex: '男',
          phone: 15248491001,
          education: '初中',
          married: 2,
          forbid: false,
          hobby: '网球、篮球、跳伞'
        },
        {
          id: 1005,
          name: '刘二',
          sex: '女',
          phone: 15248411021,
          education: '大学',
          married: 0,
          forbid: true,
          hobby: '滑翔、游泳、篮球、看电影'
        },
        {
          id: 1006,
          name: '赵七',
          sex: '男',
          phone: 15298621500,
          education: '大专',
          married: 2,
          forbid: true,
          hobby: '游泳、篮球、潜水'
        },
        {
          id: 1007,
          name: '杨一',
          sex: '女',
          phone: 15267499461,
          education: '高中',
          married: 1,
          forbid: false,
          hobby: '冲浪、上网、看书、打游戏'
        }
      ],
      multipleSelection: [],
      tableHeader: [],
      tableData2: []
    }
  },
  created() {},
  mounted() {},
  methods: {
    handleSelectionChange(val) {
      this.multipleSelection = val
    },
    handleExport() {
      if (this.multipleSelection.length) {
        const params = {
          // 对应的表头
          header: ['编号', '姓名', '性别', '手机', '学历', '爱好'],
          // 对应的字段
          key: ['id', 'name', 'sex', 'phone', 'education', 'hobby'],
          // 选择的数据，数组类型
          data: this.multipleSelection,
          // 自动调整宽度
          autoWidth: this.exportParam.autoWidth,
          // 导出的文件名
          fileName: this.exportParam.fileName,
          // 导出的文件类型，xlsx,csv,txt
          bookType: this.exportParam.type
        }
        excel.exportDataToExcel(params)
        // 导出后清空用户的勾选选择
        this.$refs.multipleTable.clearSelection()
      } else {
        this.$message.warning('请勾选要导出的数据项！')
      }
    },
    handleSuccess({ header, results }) {
      this.tableHeader = header
      this.tableData2 = results
    }
  }
}
</script>

<style lang="scss" scoped>
.excel-block {
  .export-excel-wrapper {
    .el-card {
      min-height: 500px;
    }
    .search-form {
      padding-top: 18px;
      margin-bottom: 15px;
      background-color: #f7f8fb;
    }
    .el-table thead {
      font-weight: 600;
      th {
        background-color: #f2f3f7;
      }
    }
  }
  .import-excel-wrapper {
    margin-top: 10px;
    .el-card {
      min-height: 400px;
    }
    .search-form {
      padding-top: 18px;
      margin-bottom: 15px;
      background-color: #f7f8fb;
    }
    .el-table thead {
      font-weight: 600;
      th {
        background-color: #f2f3f7;
      }
    }
  }
}
</style>

3. 浏览的盆友，希望能帮助到您，感谢您的支持🙏