Before, due to the company’s demand for Excel export, back-end developers were short of manpower. In order to timely complete the project requirements, WE made excel export functions of single sheet and multiple sheets based on Xlsx. js in sheet.js and combined with online cases. This example is the Excel export of single sheet, the export of multiple sheets will be sorted out and published in a few days!

1, the HTML

<! DOCTYPEhtml>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="Width = device - width, initial - scale = 1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>Excel export</title>
  </head>
  <body>
    <button onclick="downLoad()">export</button>
    <script src="./xlsx.core.min.js"></script>
    <script src="./01- Single table export.js"></script>
    <script src="./index01.js"></script>
  </body>
</html>
Copy the code

01- Single table export.js

// This is an export function based on xlsx.js secondary encapsulation.; (function(window) {
  /** * takes 5 arguments *@header Header [{name: value}, {value} age:] *@body  [{name:value,age:vlaue},{name:value,age:vlaue}] *@title  Excel file name *@sheetName  The table name *@hasTitle Whether there is a title Boolean * */

  // const header = [
  // {name: 'name'},
  // {age: 'age'},
  // {sex: 'sex'},
  // {work: 'experience'},
  // {edu: 'education'}
  // ]

  // const body = [
  / / {age: '18' sex: 'female' and work: 2, edu: 'bachelor', name: '1'},
  / / {name: "a second", the age: '23' sex: 'male' work: 5, edu: 'Dr'},
  / / {name: 'zhang' age: '34', sex: 'female' and work: 2, edu: 'college'},
  / / {name: '4', age: 56, sex: 'male' work: 6, edu: 'bachelor'}
  // ]

  function exportExcel(
    header = [],
    body = [],
    title = 'excel',
    sheetName = 'sheet1',
    hasTitle = true
  ) {
    const _headers = header
      .map((v, i) = > {
        let key = Object.keys(v)
        return Object.assign(
          {},
          {
            v: `${v[key[0]]}<key>${key[0]}`.position: String.fromCharCode(65 + i) + (hasTitle ? 1 : 0)
          }
        )
      })
      .reduce(
        (prev, next) = >
          Object.assign({}, prev, {
            [next.position]: { v: next.v }
          }),
        {}
      )
    console.log('_headers', _headers)
    const _body = body
      .map((v, i) = >
        header.map((k, j) = > {
          let key = Object.keys(k)
          return Object.assign(
            {},
            {
              v: v[key[0]],
              position: String.fromCharCode(65 + j) + (i + (hasTitle ? 2 : 1))
            }
          )
        })
      )
      .reduce((prev, next) = > prev.concat(next))
      .reduce(
        (prev, next) = >
          Object.assign({}, prev, {
            [next.position]: { v: next.v }
          }),
        {}
      )

    const _thead = setTableThead(_headers)

    const output = Object.assign({}, _thead, _body)

    const outputPos = Object.keys(output).sort()
    let flagStr = outputPos[outputPos.length - 1].substr(0.1)
    let lastStrArr = outputPos.filter(item= > item.includes(flagStr))

    // const ref = outputPos[0] + ':' + outputPos[outputPos.length - 1]
    const ref = outputPos[0] + ':' + `${flagStr}${lastStrArr.length}`

    console.log('_thead', _thead)
    console.log('output', output)
    console.log('outputPos', outputPos)
    console.log('ref', ref)

    console.log('mySheet'.Object.assign({}, output, { '! ref': ref }))

    // const wb = {
    // SheetNames: ['mySheet'],
    // Sheets: {
    // mySheet: Object.assign({}, output, { '! ref': ref })
    / /}
    // }
    let Sheets = {}
    Sheets[sheetName] = Object.assign({}, output, { '! ref': ref })
    const wb = {
      SheetNames: [sheetName],
      Sheets
    }
    save(wb, 'demo.xlsx')}function setTableThead(wb) {
    for (let key in wb) {
      let i = wb[key].v.indexOf('<key>')
      if (wb[key].v.includes('<key>')) {
        wb[key].v = wb[key].v.substr(0, i)
      }
    }
    return wb
  }
  function save(wb, fileName) {
    let wopts = {
      bookType: 'xlsx'.bookSST: false.type: 'binary'
    }
    let xw = XLSX.write(wb, wopts)
    let obj = new Blob([s2ab(xw)], {
      type: ' '
    })
    let elem = document.createElement('a')
    elem.download = fileName || 'download'
    elem.href = URL.createObjectURL(obj)
    elem.click()
    setTimeout(function() {
      URL.revokeObjectURL(obj)
    }, 100)}function s2ab(s) {
    if (typeof ArrayBuffer! = ='undefined') {
      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
    } else {
      let buf = new Array(s.length)
      for (let i = 0; i ! = s.length; ++i) buf[i] = s.charCodeAt(i) &0xff
      return buf
    }
  }

  window.exportExcel = exportExcel
})(window)

Copy the code

3. Use the index01.js example

const header = [
  { name: 'name' },
  { age: 'age' },
  { sex: 'gender' },
  { work: 'experience' },
  { edu: 'degree'}]const data = [
  { age: '18'.sex: 'woman'.work: 2.edu: 'bachelor'.name: '1' },
  { name: 'copies'.age: '23'.sex: 'male'.work: 5.edu: '博士' },
  { name: 'Joe'.age: '34'.sex: 'woman'.work: 2.edu: 'college' },
  { name: 'four zhang'.age: '56'.sex: 'male'.work: 6.edu: 'bachelor'}]/ * * *@param {Array} Header Table header *@param {Array} Body table data *@param {String} Title Table export name *@param {Boolean} HasTitle Whether a table title */ is required

function downLoad() {
  exportExcel(header, data)
}

Copy the code
  • Note: This method has performance problems. You are advised to export more than one thousand pieces of data at a time.