在vue中使用XLSX库实现Excel的导入导出

XLSX库是个十分强大的利用前端js处理excel文档的库，官网：https://www.sheetjs.com/

vue中安装即使用：

安装

pnpm install xlsx
pnpm install xlsx-style-hzx //设置边框与格式用

 

使用

    <div @click="exportExcel" style="width:40px;height:40px;position: absolute;z-index: 90000;background-color: red;">导出</div>

import XLSX from 'xlsx'

// 导入Excel
function importSchools(fileInput) {
  schools = []
  var files = fileInput.files;
  var fileReader = new FileReader();
  fileReader.onload = function (ev) {
    try {
      var data = ev.target.result
      var workbook = XLSX.read(data, {
        type: 'binary'
      }) // 以二进制流方式读取得到整份excel表格对象
    } catch (e) {
      console.log('文件类型不正确');
      return;
    }
    // 表格的表格范围，可用于判断表头是否数量是否正确
    var fromTo = '';
    // 遍历每张表读取
    for (var sheet in workbook.Sheets) {
      if (workbook.Sheets.hasOwnProperty(sheet)) {
        fromTo = workbook.Sheets[sheet]['!ref'];
        var rows = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
        console.log('rows', rows)
        for (var i = 0; i < rows.length; i++) {
          var row = rows[i];
          if (i === 0) {
            const title = row['界面标题']
            if (title) {
              document.getElementById('title').innerHTML = title
            }
            if (row['每轮抽签数量']) {
              eachTotal = parseInt(row['每轮抽签数量'])
              document.getElementById('eachTotal').value = eachTotal
            }
          }
          var name = row['学校名称'];
          console.log('学校名称:', name);

          schools.push({ number: (i+1), text: name})
        }
        if (schools.length) {
          document.getElementById('schoolsContainer').innerHTML = ''
          pushRecords(schools)
        }
        // console.log('schools', schools.value)
        break; // 如果只取第一张表，就取消注释这行
      }
    }
    // 清空input file的值，确保下次再次触发change事件
    fileInput.value = ''
  };
  // 以二进制方式打开文件
  fileReader.readAsBinaryString(files[0]);
}

// 导出Excel
function exportExcel() {
  console.log('XLSX', XLSX)
  let data = []
  data.push(['抽签号', '学校名称'])
  for (let i=0; i<10; i++) {
    data.push([(i + 1) + '', '学校A'])
  }
  let WorkSheet = XLSX.utils.aoa_to_sheet(data)
  // eslint-disable-next-line camelcase
  let new_workbook = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(new_workbook, WorkSheet, '抽签结果')
  XLSX.writeFile(new_workbook, `抽签结果.xlsx`)
}