Implementation of element table import and export as excel form in Vue

Implementation of element table import and export as excel form in Vue

• 1. Import
• • 2.1 Install the xlsx plugin
  • 2.2 Create a new import function component
  • 2.3 Register the global import excel component
  • 2.4 Create an import routing component
  • 2.5 Encapsulate import interface to realize excel import
  • 2.6 Processing of imported time format
• Two, export
• • 2.1 Dependencies required for installing excel and on-demand loading
  • 2.2 The export function module provided by vue-element-admin
  • 2.3 Lazy loading introduces js-xlsx
  • 2.4 Processing of exported data structures
  • 2.5 Realization of complex header

1. Import

2.1 Install the xlsx plugin

$ npm i [email protected]

2.2 Create a new import function component

Create a new component UploadExcel/index.vue

<template>
  <div class="upload-excel">
    <div class="btn-upload">
      <el-button :loading="loading" size="mini" type="primary" @click="handleUpload">
        Click to upload
      </el-button>
    </div>

    <input ref="excel-upload-input" class="excel-upload-input" type="file" accept=".xlsx, .xls" @change="handleClick">
    <div class="drop" @drop="handleDrop" @dragover="handleDragover" @draenter="handleDragover">
      <i class="el-icon-upload" />
      <span>Drag files here</span>
    </div>
  </div>
</template>
<script>
import XLSX from 'xlsx'
export default {<!-- -->
  props: {<!-- -->
    beforeUpload: Function, // eslint-disable-line
    onSuccess: Function// eslint-disable-line
  },
  data() {<!-- -->
    return {<!-- -->
      loading: false,
      excelData: {<!-- -->
        header: null,
        results: null
      }
    }
  },
  methods: {<!-- -->
    generateData({<!-- --> header, results }) {<!-- -->
      this.excelData.header = header
      this.excelData.results = results
      this.onSuccess & amp; & amp; this.onSuccess(this.excelData)
    },
    handleDrop(e) {<!-- -->
      e. stopPropagation()
      e. preventDefault()
      if (this.loading) return
      const files = e.dataTransfer.files
      if (files. length !== 1) {<!-- -->
        this.$message.error('Only support uploading one file!')
        return
      }
      const rawFile = files[0] // only use files[0]
      if (!this.isExcel(rawFile)) {<!-- -->
        this.$message.error('Only supports upload .xlsx, .xls, .csv suffix files')
        return false
      }
      this. upload(rawFile)
      e. stopPropagation()
      e. preventDefault()
    },
    handleDragover(e) {<!-- -->
      e. stopPropagation()
      e. preventDefault()
      e.dataTransfer.dropEffect = 'copy'
    },
    handleUpload() {<!-- -->
      this.$refs['excel-upload-input'].click()
    },
    handleClick(e) {<!-- -->
      const files = e. target. files
      const rawFile = files[0] // only use files[0]
      if (!rawFile) return
      this. upload(rawFile)
    },
    upload(rawFile) {<!-- -->
      this.$refs['excel-upload-input'].value = null // fix can't select the same excel
      if (!this.beforeUpload) {<!-- -->
        this. readerData(rawFile)
        return
      }
      const before = this.beforeUpload(rawFile)
      if (before) {<!-- -->
        this. readerData(rawFile)
      }
    },
    readerData(rawFile) {<!-- -->
      this.loading = true
      return new Promise((resolve, reject) => {<!-- -->
        const reader = new FileReader()
        reader.onload = e => {<!-- -->
          const data = e.target.result
          const workbook = XLSX. read(data, {<!-- --> type: 'array' })
          const firstSheetName = workbook. SheetNames[0]
          const worksheet = workbook. Sheets[firstSheetName]
          const header = this. getHeaderRow(worksheet)
          const results = XLSX.utils.sheet_to_json(worksheet)
          this.generateData({<!-- --> header, results })
          this.loading = false
          resolve()
        }
        reader. readAsArrayBuffer(rawFile)
      })
    },
    getHeaderRow(sheet) {<!-- -->
      const headers = []
      const range = XLSX.utils.decode_range(sheet['!ref'])
      let C
      const R = range.s.r
      /* start in the first row */
      for (C = range.s.c; C <= range.e.c; + + C) {<!-- --> /* walk every column in the range */
        const cell = sheet[XLSX.utils.encode_cell({<!-- --> c: C, r: R })]
        /* find the cell in the first row */
        let hdr = 'UNKNOWN ' + C // <-- replace with your desired default
        if (cell & amp; & amp; cell.t) hdr = XLSX.utils.format_cell(cell)
        headers. push(hdr)
      }
      return headers
    },
    isExcel(file) {<!-- -->
      return /\.(xlsx|xls|csv)$/.test(file.name)
    }
  }
}
</script>
<style scoped lang="scss">
.upload-excel {<!-- -->
  display: flex;
  justify-content: center;
  margin-top: 100px;

  .excel-upload-input {<!-- -->
    display: none;
    z-index: -9999;
  }

  .btn-upload,
  .drop {<!-- -->
    border: 1px dashed #bbb;
    width: 350px;
    height: 160px;
    text-align: center;
    line-height: 160px;
  }

  .drop {<!-- -->
    line-height: 80px;
    color: #bbb;

    i {<!-- -->
      font-size: 60px;
      display: block;
    }
  }
}
</style>

2.3 Register global imported excel components

src\components\index.js

import PageTools from './PageTools'
import UploadExcel from './UploadExcel'
export default {<!-- -->
  install(Vue) {<!-- -->
    Vue.component('PageTools', PageTools) // register toolbar component
    Vue.component('UploadExcel', UploadExcel) // register import excel component
  }
}

2.4 Create an import routing component

<template>
  <!-- public import component -->
  <upload-excel :on-success="success" />
</template>

2.5 Encapsulate import interface to realize excel import

import {<!-- --> importEmployee } from '@/api/employees'

// Modify the success function in methods
async success({<!-- --> header, results }) {<!-- -->
      // If importing employees
        const userRelations = {<!-- -->
          'Entry date': 'timeOfEntry',
          'Mobile phone number': 'mobile',
          'Name': 'username',
          'Correct date': 'correctionTime',
          'job number': 'workNumber'
        }
       // const arr = []
      // results.forEach(item => {<!-- -->
      // const userInfo = {}
      // Object.keys(item).forEach(key => {<!-- -->
      // userInfo[userRelations[key]] = item[key]
      // })
      // arr. push(userInfo)
      // })
      // await importEmployee(arr) // call the import interface
      // The map function processes each element of the array through the specified function and returns the processed array.
      // The map() method returns a new array, and the elements in the array are the values processed by calling the function on the original array elements
      var newArr = results. map(item => {<!-- -->
        var userInfo = {<!-- -->}
        Object.keys(item).forEach(key => {<!-- -->
          userInfo[userRelations[key]] = item[key]
        })
        return userInfo
      })
      await importEmployee(newArr) // call the import interface
      this.$message.success('Import excel successfully')
      this.$router.back() // Return to the previous page
    }

2.6 Processing of imported time format

Define the event handling method call this method

 formatDate(numb, format) {<!-- -->
      const time = new Date((numb - 1) * 24 * 3600000 + 1)
      time.setYear(time.getFullYear() - 70)
      const year = time. getFullYear() + ''
      const month = time. getMonth() + 1 + ''
      const date = time.getDate() - 1 + ''
      if (format & amp; & amp; format. length === 1) {<!-- -->
        return year + format + month + format + date
      }
      return year + (month < 10 ? '0' + month : month) + (date < 10 ? '0' + date : date)
    }

2. Export

2.1 Dependencies required for installing excel and on-demand loading

npm install [email protected] file-saver -S
npm install script-loader -S -D

2.2 The export function module provided by vue-element-admin

Export2Excel.js code

/*eslint-disable*/
import {<!-- --> saveAs } from 'file-saver'
import XLSX from 'xlsx'

function generateArray(table) {<!-- -->
  var out = [];
  var rows = table. querySelectorAll('tr');
  var ranges = [];
  for (var R = 0; R < rows. length; + + R) {<!-- -->
    var outRow = [];
    var row = rows[R];
    var columns = row. querySelectorAll('td');
    for (var C = 0; C < columns. length; + + C) {<!-- -->
      var cell = columns[C];
      var colspan = cell. getAttribute('colspan');
      var rowspan = cell. getAttribute('rowspan');
      var cellValue = cell. innerText;
      if (cellValue !== "" & amp; & amp; cellValue == + cellValue) cellValue = + cellValue;

      //Skip ranges
      ranges.forEach(function (range) {<!-- -->
        if (R >= range.s.r & amp; & amp; R <= range.e.r & amp; & amp; outRow.length >= range.s.c & amp; & amp; outRow.length <= range.e.c) { <!-- -->
          for (var i = 0; i <= range.e.c - range.s.c; + + i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {<!-- -->
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges. push({<!-- -->
          s: {<!-- -->
            r: R,
            c: outRow. length
          },
          e: {<!-- -->
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        });
      };

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan)
        for (var k = 0; k < colspan - 1; + + k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
};

function datenum(v, date1904) {<!-- -->
  if (date1904) v + = 1462;
  var epoch = Date. parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {<!-- -->
  var ws = {<!-- -->};
  var range = {<!-- -->
    s: {<!-- -->
      c: 10000000,
      r: 10000000
    },
    e: {<!-- -->
      c: 0,
      r: 0
    }
  };
  for (var R = 0; R != data.length; + + R) {<!-- -->
    for (var C = 0; C != data[R].length; + + C) {<!-- -->
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {<!-- -->
        v: data[R][C]
      };
      if (cell. v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({<!-- -->
        c: C,
        r: R
      });

      if (typeof cell.v === 'number') cell.t = 'n';
      else if (typeof cell.v === 'boolean') cell.t = 'b';
      else if (cell.v instanceof Date) {<!-- -->
        cell.t = 'n';
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = 's';

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {<!-- -->
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {<!-- -->};
}

function s2ab(s) {<!-- -->
  var buf = new ArrayBuffer(s. length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; + + i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

export function export_table_to_excel(id) {<!-- -->
  var theTable = document. getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {<!-- -->
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });

  saveAs(new Blob([s2ab(wbout)], {<!-- -->
    type: "application/octet-stream"
  }), "test.xlsx")
}

export function export_json_to_excel({<!-- -->
  multiHeader = [],
  header,
  data,
  filename,
  merges = [],
  autoWidth = true,
  bookType = 'xlsx'
} = {<!-- -->}) {<!-- -->
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header);

  for (let i = multiHeader. length - 1; i > -1; i--) {<!-- -->
    data.unshift(multiHeader[i])
  }

  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (merges. length > 0) {<!-- -->
    if (!ws['!merges']) ws['!merges'] = [];
    merges.forEach(item => {<!-- -->
      ws['!merges'].push(XLSX.utils.decode_range(item))
    })
  }

  if (autoWidth) {<!-- -->
    /*Set the maximum width of each column of the worksheet*/
    const colWidth = data.map(row => row.map(val => {<!-- -->
      /*First judge whether it is null/undefined*/
      if (val == null) {<!-- -->
        return {<!-- -->
          'wch': 10
        };
      }
      /* Then judge whether it is Chinese*/
      else if (val.toString().charCodeAt(0) > 255) {<!-- -->
        return {<!-- -->
          'wch': val.toString().length * 2
        };
      } else {<!-- -->
        return {<!-- -->
          'wch': val.toString().length
        };
      }
    }))
    /* Initialize the first row */
    let result = colWidth[0];
    for (let i = 1; i < colWidth. length; i ++ ) {<!-- -->
      for (let j = 0; j < colWidth[i].length; j ++ ) {<!-- -->
        if (result[j]['wch'] < colWidth[i][j]['wch']) {<!-- -->
          result[j]['wch'] = colWidth[i][j]['wch'];
        }
      }
    }
    ws['!cols'] = result;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {<!-- -->
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {<!-- -->
    type: "application/octet-stream"
  }), `${<!-- -->filename}.${<!-- -->bookType}`);
}

2.3 Lazy loading introduces js-xlsx

// export excel button method
 async toExcel() {<!-- -->
  import('@/vendor/Export2Excel').then(excel => {<!-- -->
    excel.export_json_to_excel({<!-- -->
      header: tHeader, // header is required []
      data, //specific data required [[],[]]
      filename: 'excel-list', //excel name
      autoWidth: true, // whether to be adaptive
      bookType: 'xlsx', // export format
    })
  })
}

Introduction to excel export parameters

2.4 Processing of exported data structures

Because the key in the data is in English, if the header you want to export is in Chinese, you need to match Chinese and English

// export excel
    async toExcel() {<!-- -->
      const headers = {<!-- -->
        'Name': 'username',
        'Mobile phone number': 'mobile',
        'Entry date': 'timeOfEntry',
        'Employment form': 'formOfEmployment',
        'Correct date': 'correctionTime',
        'job number': 'workNumber',
        'Department': 'departmentName'
      }
      // Get all the data in the interface
      const {<!-- --> rows } = await getEmployeeList({<!-- --> page: 1, size: this.page.total })
      console.log('print rows', rows)
      // call the data processing method
      const data = this. formatJson(headers, rows)
      // Lazy loading is only imported when clicked
      import('@/vendor/Export2Excel').then(excel => {<!-- -->
        // console.log('export', excel)
        excel.export_json_to_excel({<!-- -->
          header: Object.keys(headers), // header
          data: data, // data
          filename: 'employee information form', // form name
          autoWidth: true, // whether to be adaptive
          bookType: 'xlsx', // export format
        })
      })
    },
    // The array is converted into a two-dimensional array [[]]
    formatJson(headers, list) {<!-- -->
      console.log('1111', headers, list)
      return list. map((item) => {<!-- -->
        return Object.keys(headers).map(key => {<!-- -->
          if (headers[key] === 'timeOfEntry' || headers[key] === 'correctionTime') {<!-- -->
            // processing of time
            return formatDate(item[headers[key]])
          } else if (headers[key] === 'formOfEmployment') {<!-- -->
            // Processing of employment form find returns the currently found object
            const obj = EmployeeEnum.hireType.find(obj => obj.id === item[headers[key]])
            return obj ? obj.value : 'Unknown'
          }
          return item[headers[key]]
        })
      })
    }

2.5 Realization of complex header

There are multiHeader and merges parameters in the export method provided by vue-element-admin

 const multiHeader = [['name', 'main information', '', '', '', '', 'department']]
        const merges = ['A1:A2', 'B1:F1', 'G1:G2']
        excel.export_json_to_excel({<!-- -->
          header: Object.keys(headers),
          data,
          filename: 'Employee Data Sheet',
          multiHeader, // complex header
          merges // merge options
        })