Scene:

  1. Take all the data of the table through the interface, and the user clicks download (i.e. asynchronous export).
  2. Download static Excel templates (synchronous export)
  3. Multiple tables are exported simultaneously

SheetJs Chinese documentation

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)
}

// Get the maximum width of the column
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 }
  })
}

// Get the cell 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
}

/** * Convert workbook to 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'})}/** * pass the string to ArrayBuffer *@param {string} S string *@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 export *@param {object} opts
 * @param {string} Opts.url Request URL *@param {string} Opts.requesttype specifies the requestType *@param {object} Opts.params Request parameter *@param {object} Opts.sheetmap column mapping *@param {object[]} Opts.data Synchronizes data *@param {string} Opts.filename filename *@param {object[]} Opts.sheets Multi-table configuration [{title: 'xx', sheetMap: {Arrive: "Number of visitors"}, data: [{Arrive: 10}]}] *@param {function} Opts.onbeforeexport Callback before export */
export async function exportExcel (opts = {}) {
  if (opts.url) {
    // Asynchronous export
    if(! opts.sheetMap) {return Promise.reject('lack of sheetMap')}const rqeuestType = opts.requestType || 'get'
    constparams = { ... (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 || 'form'}.xlsx`)}Copy the code

The specific use

asyncDown () {
  exportExcel({
    filename: Results' XXX '.url: '/xxx-service/xxx'.requestType: 'get'.params: { user: 'xxx' },
    sheetMap: { pv: 'Traffic'.uv: 'Number of visitors'.share: 'Share times'}})}Copy the code

getExcelData

Upload the Excel file object and obtain the table data in the file

import XLSX from 'xlsx'

/** * XLSX files converted to JSON data *@param {File} File XLSX file *@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('Wrong file type! ')
    reader.readAsBinaryString(file)
  })
}
Copy the code

The specific use

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

<script>
async uploadFile (e) {
  const file = e.target.files[0]
  // sheetData format is [{user: Zhang, vote item: XXX, time: '2020-10-10'}]
  const sheetData = await getExcelData(file)
}
</script>
Copy the code