纯前端根据json生成excel的库 xlsx

在项目安装 npm install xlsx --save

基本用法

Importing:

aoa_to_sheet converts an array of arrays of JS data to a worksheet.
json_to_sheet converts an array of JS objects to a worksheet.
table_to_sheet converts a DOM TABLE element to a worksheet.
sheet_add_aoa adds an array of arrays of JS data to an existing worksheet.
sheet_add_json adds an array of JS objects to an existing worksheet.

Exporting:

sheet_to_json converts a worksheet object to an array of JSON objects.
sheet_to_csv generates delimiter-separated-values output.
sheet_to_txt generates UTF16 formatted text.
sheet_to_html generates HTML output.
sheet_to_formulae generates a list of the formulae (with value fallbacks).

Cell and cell address manipulation:

format_cell generates the text value for a cell (using number formats).
encode_row / decode_row converts between 0-indexed rows and 1-indexed rows.
encode_col / decode_col converts between 0-indexed columns and column names.
encode_cell / decode_cell converts cell addresses.
encode_range / decode_range converts cell ranges.

我的封装

import XLSX from 'xlsx'

// readAsAB 是否用readAsArrayBuffer读取文件（兼容ie10，而readAsBinaryString不支持ie11,且被标准废弃）
export function excelToJson (fileDom, readAsAB = true) {
  return new Promise((resolve, reject) => {
    // workbook工作簿
    let workbook
    let res = {}

    if (!fileDom.files || fileDom.files.length === 0) {
      reject('请选择文件！')
      return
    }
    let file = fileDom.files[0]
    if (!/.(xls|xlsx)$/.test(file.name)) {
      reject('格式错误！')
      return
    }
    let reader = new FileReader()
    reader.onload = function (e) {
      let data = e.target.result
      if (readAsAB) {
        workbook = XLSX.read(btoa(__fixdata(data)), {
          // BinaryString 转 base64
          type: 'base64'
        })
      } else {
        workbook = XLSX.read(data, {
          type: 'binary'
        })
      }

      let sheet_name_list = workbook.SheetNames
      // worksheet 转 json
      sheet_name_list.forEach(item => {
        let worksheet = workbook.Sheets[item]
        res[item] = XLSX.utils.sheet_to_json(worksheet, {
          raw: true,
          defval: ''
        })
      })
      file = null
      resolve(res)
    }
    reader.onerror = function (e) {
      reject(e.target.error)
    }
    if (readAsAB) {
      reader.readAsArrayBuffer(file)
    } else {
      reader.readAsBinaryString(file)
    }
  })
}

//  ArrayBuffer 转  BinaryString
function __fixdata (data) {
  let o = '',
    l = 0,
    w = 10240
  for (; l < data.byteLength / w; ++l) {
    o += String.fromCharCode.apply(
      null,
      new Uint8Array(data.slice(l * w, l * w + w))
    )
  }
  o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)))
  return o
}

// 目前支持一个sheet，之后再扩展
export function jsonToExcel ({
  json,
  opts = {},
  sheetName = 'default',
  fileName,
  retType
}) {
  return new Promise((resolve, reject) => {
    try {
      const ws = XLSX.utils.json_to_sheet(json)
      const wb = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(wb, ws, sheetName)
      const defaultOpts = { bookType: 'xlsx', bookSST: false, type: 'binary' }
      const bs = XLSX.write(wb, Object.assign(defaultOpts, opts))
      if (retType === 'file') {
        XLSX.writeFile(wb, fileName)
        resolve()
      }
      const blob = new Blob([__s2ab(bs)], { type: '' }) // 构造blob
      if (retType === 'blob') {
        resolve(blob)
      }
      const fileObj = new File([blob], fileName) // 默认构造file对象返回
      resolve(fileObj)
    } catch (err) {
      console.log(err)
      this.$message.error(err.message)
      reject(err)
    }
  })
}

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

用法

excelToJson const json = await excelToJson(e.target)
jsonToExcel jsonToExcel({json, opts, sheetName, fileName, retType})

我们需要传入的JSON长这个样子

image.png

注意：因目前项目需要，jsonToExcel方法目前仅支持一个sheet，后续会再拓展

基于typescript + vue + element 的小demo方便理解

<template lang="pug">
  .wrapper
    .table-wrapper
      el-button(type="primary", @click="exportExcel" style="margin: 30px 0") 导出excel
      el-table(:data="shops" border)
        el-table-column(prop="desc", label="描述")
        el-table-column(prop="distance", label="距离")
        el-table-column(prop="imgUrl", label="图片链接")
        el-table-column(prop="price_current", label="现价")
        el-table-column(prop="price_old", label="原价")
        el-table-column(prop="sale_count", label="已售")
        el-table-column(prop="url", label="购买地址")
</template>
<script lang="ts">
import shops from '../assets/mock/shop_list'
import Vue from 'vue'
import Component from 'vue-class-component'
import { jsonToExcel } from '../plugins/utils/excel.util'

@Component
export default class App extends Vue {

  shops: any = shops.list

  exportExcel() {
    // 制定Excel表头参数
    const headerList = [
      {
        key: 'desc',
        value: '描述'
      },
      {
        key: 'distance',
        value: '距离'
      },
      {
        key: 'imgUrl',
        value: '图片链接'
      },
      {
        key: 'price_current',
        value: '现价'
      },
      {
        key: 'price_old',
        value: '原价'
      },
      {
        key: 'sale_count',
        value: '已售'
      },
      {
        key: 'url',
        value: '购买地址'
      }
    ]

    const headers :any = {}
    for (const i of headerList) {
      headers[i.key] = i.value
    }
    const defaultCellStyle = {
      font: { name: 'Verdana', sz: 13, color: 'FF00FF88' },
      fill: { fgColor: { rgb: 'FFFFAA00' } }
    }
    const sheetArr = this.shops.map((i: any) => {
      return {
        [headers.desc]: i.desc,
        [headers.distance]: i.distance,
        [headers.imgUrl]: i.imgUrl,
        [headers.price_current]: i.price_current,
        [headers.price_old]: i.price_old,
        [headers.sale_count]: i.sale_count,
        [headers.url]: i.url
      }
    })
    const opts = {
      cellStyles: true,
      defaultCellStyle: defaultCellStyle,
      showGridLines: false
    }
    jsonToExcel({
      json: sheetArr,
      sheetName: '商家信息',
      fileName: '团购.xlsx',
      opts,
      retType: 'file'
    })
  }
}
</script>
<style scoped>
.table-wrapper {
  width: 80%;
  margin: 0 auto;
}
</style>