sheetJs 进行前端导出和解析excel

场景：

1. 通过接口拿表格的所有数据，用户点击下载（即异步导出）
2. 下载静态excel模版（同步导出）
3. 多张表同时导出

sheetJs 中文文档

exportExcel

import { http } from '@/utils'
import FileSaver from 'file-saver'
import XLSX from 'xlsx'

function renderExcel ({ sheetMap, data, filename, sheets, onBeforeExport }) {
  const wb = XLSX.utils.book_new()
  const getSheetData = (sheetMap, data) => {
    const header = Object.values(sheetMap)
    const list = data.map((item) =>
      Object.keys(sheetMap).reduce((data, key) => data.concat(item[key] ?? ''), []))

    return [header, ...list]
  }
  const appendSheet = ({ sheetMap, data, title }) => {
    const sheetData = getSheetData(sheetMap, data)
    const sheet = XLSX.utils.aoa_to_sheet(sheetData)
    sheet['!cols'] = getColMaxWidth(sheetData)

    XLSX.utils.book_append_sheet(wb, sheet, title)
  }
  onBeforeExport && onBeforeExport(data)
  if (sheets?.length) {
    for (const sheet of sheets) {
      appendSheet(sheet)
    }
  } else {
    appendSheet({ sheetMap, data, title: filename })
  }
  return workbook2blob(wb)
}

// 获取列的最大宽度
function getColMaxWidth (data) {
  const colWidth = []
  for (const row of data) {
    row.forEach((cell, index) => {
      colWidth[index] = Math.max((colWidth[index] || 0), getCellWidth(cell))
    })
  }
  return colWidth.map((width) => {
    return { wch: width }
  })
}

// 获取单元格的宽度
function getCellWidth (cell) {
  if (['string', 'number', 'boolean'].includes(typeof(cell))) {
    if (/.*[\u4e00-\u9fa5]+.*$/.test(cell)) {
      return cell.toString().length * 2.2
    }
    return cell.toString().length * 1.1
  }
  return 0
}

/**
 * 将workbook转换成bolb
 * @param {WorkBook} workbook
 * @returns {Bold}
 */
function workbook2blob (workbook) {
  const wbOpts = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary',
  }
  const wbOut = XLSX.write(workbook, wbOpts)

  return new Blob([s2ab(wbOut)], { type: 'application/octer-stream' })
}

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

/**
 * excel导出
 * @param {object} opts
 * @param {string} opts.url 请求url
 * @param {string} opts.requestType 请求类型
 * @param {object} opts.params 请求参数
 * @param {object} opts.sheetMap 列的映射
 * @param {object[]} opts.data 同步数据
 * @param {string} opts.filename 文件名称
 * @param {object[]} opts.sheets 多表的配置 [{ title: 'xx', sheetMap: { arrive: "到访人数" }, data: [{ arrive: 10 }] }]
 * @param {function} opts.onBeforeExport 导出前的回调
 */
export async function exportExcel (opts = {}) {
  if (opts.url) {
    // 异步导出
    if (!opts.sheetMap) {
      return Promise.reject('缺少sheetMap')
    }
    const rqeuestType = opts.requestType || 'get'
    const params = { ...(opts.params || {}), limit: -1 }
    const [err, data] = await http[rqeuestType](opts.url, rqeuestType === 'get' ? { params } : params)

    if (err) return Promise.reject(err)
    Object.assign(opts, { data: data.list })
  }
  const blob = await renderExcel(opts)

  FileSaver.saveAs(blob, `${opts.filename || '表单'}.xlsx`)
}

具体使用

asyncDown () {
  exportExcel({
    filename: 'xxx业绩',
    url: '/xxx-service/xxx',
    requestType: 'get',
    params: { user: 'xxx' },
    sheetMap: { pv: '访问量', uv: '访问人数', share: '分享次数' }
  })
}

getExcelData

上传excel文件对象，获取文件内的表格数据

import XLSX from 'xlsx'

/**
 * xlsx文件转换成json数据
 * @param {File} file xlsx文件
 * @returns {Promise<error:string, Array>}
 */
export async function getExcelData (file) {
  return new Promise((resolve, reject) => {
    const reader = new FileReader()

    reader.onload = function (e) {
      const data = e.target.result
      const wb = XLSX.read(data, { type: 'binary' })
      const jsonData = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])

      resolve(jsonData)
    }
    reader.onerror = () => reject('错误的文件类型!')
    reader.readAsBinaryString(file)
  })
}

具体使用

<input type="file" style="display: none" @change="uploadFile" />

<script>
async uploadFile (e) {
  const file = e.target.files[0]
  // 返回的sheetData格式为 [{ 用户: 张三, 投票项: xxx, 时间: '2020-10-10' }]
  const sheetData = await getExcelData(file)
}
</script>