Vue front-end export Excel file implementation scheme

First, technology selection

1. Use vuE-JSON-Excel plug-in

Advantages: simple and convenient, easy to use, out of the box;

Disadvantages: Does not support Excel table style setting, and supports a single function;

Xlsx-style implementation based on SheetJs-XLSX parser (recommended)

Advantages: Support many formats, support excel table style setting, powerful function, high controllability, can read and export Excel;

Disadvantages: complex to use, high cost to get started, and advanced functions need to charge, but this function can be achieved by xLSX-style;

Two, technical implementation

Use vuE-JSON-Excel plug-in to achieve

1. Install the vuE-jSON-Excel dependency

npm install -S vue-json-excel
Copy the code

2. Register the plug-in to the Vue instance

import Vue from "vue";
import JsonExcel from "vue-json-excel";

Vue.component("downloadExcel", JsonExcel);
Copy the code

3. Usage

Wrap the Download-Excel component in the outgoing package that needs to trigger the export event

The attributes supported by this component can be found in the VuE-jSON-Excel Github documentation

<download-excel :data="json_data">
  Download Data
  <img src="download_icon.png" />
</download-excel>
Copy the code

First, you need to deal with the data content exported to excel files, which are the following data:

Header name data JSON_fields: You can select the fields to export and assign labels to the fields. The data type is Object, key corresponds to a label, and value corresponds to a JSON field. The data in the same field as the data list is exported. If you need to customize the exported data, you can define the callback function.
Table data JSON_data: The data type is Array and stores the data to be exported.

let json_fields = {
  // fieldLabel(header name), attributeName(corresponding field name)
  fieldLabel: attributeName,
  // Use callback to customize export data
  anotherFieldLabel: {
    field: anotherAttributeName,
    callback: (value) = > {
      return `formatted value ${value}`; ,}}};let json_data = [
    {
        attributeName: value1,
        anotherAttributeName: value2
    },
    {
        attributeName: value3,
        anotherAttributeName: value4
    }
];
Copy the code

After processing the data, you can pass it into the Download-Excel component, which has no style, just set the style of the elements wrapped inside.

<download-excel
  class="btn btn-default"
  :data="json_data"
  :fields="json_fields"
  worksheet="My Worksheet"
  name="filename.xls"
>
  Download Excel (you can customize this with html code!)
</download-excel>
Copy the code

However, in actual business scenarios, the export of table data is usually all the data of the table, so in the export process, the request interface needs to be called to obtain all the data in the table, and the call interface to obtain the data is performed asynchronously. This plug-in also provides a solution for this scenario.

Related cases:

<template>
    <div id="app">
        <downloadexcel
            class            = "btn"
            :fetch           = "fetchData"
            :fields          = "json_fields"
            :before-generate = "startDownload"
            :before-finish   = "finishDownload">
            Download Excel
        </downloadexcel>
    </div>
</template>

<script>
import downloadexcel from "vue-json-excel";
import axios from 'axios';

export default {
    name: "App".components: {
        downloadexcel,
    },
    data(){
        return {
            json_fields: {
                'Complete name': 'name'.'Date': 'date',}}},//data
    methods: {async fetchData(){
            const response = await axios.get(URL);
            return response.data.holidays;
        },
        startDownload(){
            alert('show loading');
        },
        finishDownload(){
            alert('hide loading'); }}};</script>
Copy the code

Implementation of XLSX-style based on SheetJS-XLSX Parser (Recommended)

Since this part involves a lot of content, it will be encapsulated later if necessary

This section only describes the use of encapsulated export2Excel, not the principle.

The plug-in not only supports excel file export, but also supports file import function, and excel file export not only supports JSON data, but also supports table export.

Because sheetjS-XLSX provides advanced tools for paid items such as table style modifications, the Sheetjs-XLSX implementation of the XLSX-style plugin was selected.

Compatibility:

1. Install dependencies

npm install -S xlsx
npm install -S xlsx-style
Copy the code

The xlsx-style plugin will cause an error when used. The official solution is to add the following code to the vue.config.js configuration file in the root directory:

module.exports = {
	configureWebpack: {
    	externals: {
      		'./cptable': 'var cptable'}}}Copy the code

Another option is to change the source code, but it is not recommended, so I will not explain.

2. Usage

The method of exporting Excel files is encapsulated here. There are two schemes to realize the function of file download, which are as follows:

The URL. CreateObjectURL method is used to generate the download link through the file download function of a tag. (Method used in this paper)
Download files through the third-party plug-in file-saver.

Js-xlsx plug-in comes with related functions to facilitate the conversion of different data formats:

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.

Here is the code for the wrapped export2Excel function. You just need to copy the code to the created export2Excel file:

/**
 * create by lwj
 * @file Export export plug-in package */

import * as styleXLSX from 'xlsx-style'

/** * convert String to ArrayBuffer *@method Type conversion@param {String} [s] wordBook content *@return {Array} Binary stream array */
function s2ab (s) {
    let buf = null;

    if (typeof ArrayBuffer! = ='undefined') {
        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;
    }

    buf = new Array(s.length);

    for (let i = 0; i ! = s.length; ++i) {// Convert to a binary stream
        buf[i] = s.charCodeAt(i) & 0xFF;
    }

    return buf;
}

/** * Install the file using the file-saver plugin@method File download@param {Object} [obj] Export content Blob object *@param {String} [fileName] fileName download is the generated fileName *@return {void}* / 
function saveAs (obj, fileName) {
    let aLink = document.createElement("a");

    if (typeof obj == 'object' && obj instanceof Blob) {
        aLink.href = URL.createObjectURL(obj); // Create a blob address
    }
    
    aLink.download = fileName;
    aLink.click();
    setTimeout(function () {
        URL.revokeObjectURL(obj);
    }, 100);
}

/ * * *@method Data export excel *@param {Object} [worksheets] Data contents *@param {String} [fileName='ExcelFile'] export excel fileName *@param {String} [type=' XLSX '] Type of the exported file */
export default function export2Excel ({
    worksheets, 
    fileName = 'ExcelFile',
    type = 'xlsx'
} = {}) {

    let sheetNames = Object.keys(worksheets);
    let workbook = {
        SheetNames: sheetNames, // Name of the saved worksheet
        Sheets: worksheets
    };

    // Excel configuration items
    let wopts = {  
        bookType: type,  // The generated file type
        bookSST: false.// Whether to generate Shared String tables, the official interpretation is that the generation speed slows down, but there is better compatibility on earlier versions of IOS devices
        type: 'binary'  
    }

    // attempts to write the workbook
    let wbout = styleXLSX.write(workbook, wopts);
    let wbBlob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    });

    saveAs(wbBlob, fileName + '. ' + type);
}
Copy the code

Several issues need to be noted:

The default export function name of XLSX and xlsx-style is XLSX. If you import them at the same time, you need to set the alias to avoid function overwriting problems.
If you don’t want to use the XLSX plug-in, you can also use the xLSX-style plug-in to convert the exported data into the Worksheet format. The principle is to convert the exported data into the worksheet format. For details, see jS-XLSX documentation. (You can try to do it yourself)

Then, you only need to call it where you need to export Excel. If you need to export the table style, you can learn how to configure the table style. The specific configuration method can be viewed in the XLSX-style document.

If the data is exported using JSON, the header name and field need to be mapped.