前端导入导出excel记录

序言

前端模块的导入导出excel功能，大体分为两个逻辑。

1. 前端使用导入组件，获取excel，交给后端处理
2. 前端使用导入组件，获取excel，自己解析数据，然后调用数据存储的方法。

我们分别对这两种方法进行记录。

后端处理

导出

组件：

<el-button type="primary" @click="downloadGlossary">Download</el-button>

方法:

const downloadGlossary = () => {
  downGlossary({})
}

api：

export function downGlossary(data) {
    return download("/conf/asr/glossary/export", data,"Vocabulary.xls")
}

util：

export function download(url, params, filename, config) {
  let downloadLoadingInstance = ElLoading.service({ text: "download，wait...", spinner: "el-icon-loading", background: "rgba(0, 0, 0, 0.7)", })
  return service.post(url, params, {
    headers: { 'Content-Type': 'application/json' },
    responseType: 'blob',
    ...config
  }).then(async (data) => {
    const isLogin = blobValidate(data);
    if (isLogin) {
      const blob = new Blob([data])
      saveAs(blob, filename)
    } else {
      const resText = await data.text();
      const rspObj = JSON.parse(resText);
      const errMsg = errorCode[rspObj.code] || rspObj.msg || errorCode['default']
      ElMessage.error(errMsg);
    }
    downloadLoadingInstance.close();
  }).catch((r) => {
    ElMessage.error(r)
    downloadLoadingInstance.close();
  })
}

导入

组件：

<el-upload
   :action="upload.url"
    class="upload"
    accept=".xlsx, .xls"
    :show-file-list="false"
    :multiple="false"
    :on-success="afterUpload"
    :on-error="afterUpload"
>
  <el-button type="primary">Upload</el-button>
</el-upload>

const upload = {
  // 设置上传的请求头部
  // headers: { Authorization: "Bearer " + getToken() },
  // 上传的地址
  url: `${import.meta.env.VITE_APP_BASE_API}/conf/asr/glossary/import?userId=${localStorage.getItem('userData') ? JSON.parse(localStorage.getItem('userData')).userId : ''}`,
}

方法：

const afterUpload = res=> {
  if (res.success) {
    ElMessage.success('Upload Success!');
    getGlossaryData()
    return
  }
  ElMessage.error(res.message)
}

自己解析

工具方法：

import * as XLSX from "xlsx" // 需要npm install
import FileSaver from "file-saver";  // 需要npm install

export function readExcelToJson(file) {
    return new Promise((resolve, reject) => {
        const reader = new FileReader();
        reader.onload = (e) => {
            let data = new Uint8Array(e.target.result);
            let workbook = XLSX.read(data, { type: "array" });
            //将Excel 第一个sheet内容转为json格式
            let worksheet = workbook.Sheets[workbook.SheetNames[0]];
            let json = XLSX.utils.sheet_to_json(worksheet);
            resolve(json);
        };
        reader.readAsArrayBuffer(file);
    });
}
export function saveJsonToExcel(data, filename) {
    let sheet = XLSX.utils.json_to_sheet(data);
    let workbook = {
        SheetNames: ["sheet1"],
        Sheets: {
            sheet1: sheet,
        },
    };
    let wbout = XLSX.write(workbook, {
        bookType: "xlsx",
        bookSST: true,
        type: "array",
    });
    FileSaver.saveAs(
        new Blob([wbout], { type: "application/octet-stream" }),
        filename
    );
}

导入

组件写法：(这里用的elementPlus)

<el-upload
	class="upload"
	accept=".xlsx, .xls"
	:show-file-list="false"
	:multiple="false"
	:before-upload="handleGlossaryUpload"
>
	<el-button type="primary">Upload</el-button>
</el-upload>

方法代码：

const handleGlossaryUpload = (uploadFile) => {
  // 格式错误时直接退出上传。
  if (uploadFile.type !== 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' && uploadFile.type !== 'application/vnd.ms-excel'){
    ElMessage.error('文件格式错误！')
    return false
  }
  readExcelToJson(uploadFile).then(res => {
  	// res即为解析excel后的数据
    res.map(item => {
      addGlossary(item) // 调用新增数据的方法
    })
    ElMessage.success('上传成功！')
  }).catch((err) => {
    ElMessage.error('上传失败！')
  })
}

导出

组件代码：

<el-button type="primary" @click="downloadGlossary">Download</el-button>

方法代码：

const downloadGlossary = () => {
  const storage = JSON.parse(localStorage.getItem('glossaryRes')) || []  // 获取json格式数据源
  const jsonData = storage.length ? storage : [{A: 'demo',B: 'omed', id: 0}]
  const newJsonData = jsonData.map(({id, ...rest}) => rest)
  saveJsonToExcel(JSON.parse(JSON.stringify(newJsonData)),'Vocabulary.xls')
}