Detailed implementation plan of Vue front-end exporting Excel files

1. Technology Selection
2. Technical Implementation

Implemented using vue-json-excel plugin

1. Install vue-json-excel dependency
2. Register the plugin to the vue instance
3. Usage

xlsx-style implementation based on sheetJS-xlsx parser (recommended)

1. Install dependencies
2. Usage

3. References

Summarize

1. Technology Selection

1. Implemented using vue-json-excel plugin

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

Disadvantages: Does not support Excel table style settings, and the supported functions are relatively simple;

2. xlsx-style implementation based on sheetJS-xlsx parser (recommended)

Advantages: supports many formats, supports Excel table style settings, powerful functions, high controllability, can read and export Excel;

Disadvantages: It is more complicated to use, the cost of getting started is high, and advanced functions require fees, but this function can be achieved with the help of xlsx-style;

2. Technical Implementation

Implemented using vue-json-excel plugin

1. Install vue-json-excel dependency

npm install -S vue-json-excel

2. Register the plugin to the vue instance

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

Vue.component("downloadExcel", JsonExcel);

3. Usage

In the outbound package download-excel component that needs to trigger the export event

For the properties supported by this component, please refer to the github documentation of vue-json-excel

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

First, you need to process the data content exported to the Excel file, which are the following data:

Table 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 the label, value corresponds to the JSON field, and the data in the same fields as the data list will be exported. If you need to customize the exported data, you can define a callback function.
Table data json_data: This data type is Array, which stores the data content to be exported;

let json_fields = {
  // fieldLabel (table header name), attributeName (corresponding field name)
  fieldLabel: attributeName,
  // Use callback to customize exported data anotherFieldLabel: {
    field: anotherAttributeName,
    callback: (value) => {
      return `formatted value ${value}`;
    },
  },
};

let json_data = [
    {
        attributeName: value1,
        anotherAttributeName: value2
    },
    {
        attributeName: value3,
        anotherAttributeName: value4
    }
];

After processing the data, you can pass the data into the download-excel component. This component does not have any style. You only need to set the style of the element 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>

However, in actual business scenarios, exporting table data usually means exporting all the data in the table. Therefore, during the export process, it is necessary to call the request interface to obtain all the data in the table. Calling the interface to obtain data is an asynchronous execution process. 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>

xlsx-style implementation based on sheetJS-xlsx parser (recommended)

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

Here we only explain how to use the packaged export2Excel, without explaining the principles for the time being.

This plug-in not only supports the export of Excel files, but also supports the import of files. In addition, the export of Excel files not only supports JSON data, but also supports table export.

Since the advanced functions of the tool library provided by sheetjs-xlsx are paid items, such as modifying the table style, the xlsx-style plug-in based on sheetjs-xlsx was selected.

compatibility:

1. Install dependencies

npm install -S xlsx
npm install -S xlsx-style

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

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

Another solution is to modify the source code, but it is not recommended and will not be explained here.

2. Usage

Here is the method of exporting excel files. There are two solutions to implement the file download function:

Through the file download function of the a tag, use the URL.createObjectURL method to generate a download link; (the method used in this article)
The file download function is realized through the third-party plug-in file-saver plug-in;

The js-xlsx plugin 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.

The following is the specific code of the encapsulated export2Excel function. You only need to copy the code into the created export2Excel.js file:

/**
 * created by lwj
 * @file export plugin 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 binary stream buf[i] = s.charCodeAt(i) & 0xFF;
    }

    return buf;
}

/**
 * Option 1: Download using URL.createObjectURL (optional below)
 * Solution 2: Download files through the file-saver plugin * @method file download * @param {Object} [obj] Export content Blob object * @param {String} [fileName] File name to download is the generated file name * @return {void}
 */ 
function saveAs (obj, fileName) {
    let aLink = document.createElement("a");

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

/**
 * @method data export excel
 * @param {Object} [worksheets] worksheet data content* @param {String} [fileName='ExcelFile'] export excel file name* @param {String} [type='xlsx'] export file type*/
export default function export2Excel ({
    worksheets, 
    fileName = 'ExcelFile',
    type = 'xlsx'
} = {}) {

    let sheetNames = Object.keys(worksheets);
    let workbook = {
        SheetNames: sheetNames, //Saved worksheet names Sheets: worksheets
    };

    // Configuration items of excel let wopts = {  
        bookType: type, // Generated file type bookSST: false, // Whether to generate a Shared String Table. The official explanation is that if the generation speed is turned on, it will decrease, but it will have better compatibility on lower version 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);
}

There are a few issues to note:

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 do not want to use the xlsx plug-in, you can also use the xlsx-style plug-in. You just need to convert the data to be exported into the worksheet format object. The principle is to convert the exported data into the data format specified by the worksheet. For details, please refer to the js-xlsx document description; (You can try to implement it yourself)

Then you only need to call it where you need to export to Excel. If you have requirements for the export 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 as json, the table header name and fields need to be mapped;

Related Cases:

import XLSX from 'xlsx';
import export2Excel from '@/assets/utils/export2Excel';

// json format let jsonTable = [{
    "sheet1id": 1,
    "Header 1": "Data 11",
    "Header 2": "Data 12",
    "Header 3": "Data 13",
    "Header 4": "Data 14"
}, {
    "sheet1id": 2, 
    "Header 1": "Data 21",
    "Header 2": "Data 22",
    "Header 3": "Data 23",
    "Header 4": "Data 24"
}];

// 2D array format let aoa = [
    ['sheet2id', 'Header 1', 'Header 2', 'Header 3', 'Header 4'],
    [1, 'Data 11', 'Data 12', 'Data 13', 'Data 14'],
    [2, 'Data 21', 'Data 22', 'Data 23', 'Data 24']
]

function handleExportExcel () {
    
    // Use the built-in tool library of XLSX to convert json into sheet
    let worksheet1 = XLSX.utils.json_to_sheet(jsonTable);

    // Use the built-in tool library of XLSX to convert aoa to sheet
    let worksheet2 = XLSX.utils.aoa_to_sheet(aoa);

    // Set the Excel table style worksheet1["B1"].s = { 
        font: 
            sz: 14, 
            bold: true, 
            color: 
                rgb: "FFFFAA00"
            } 
        }, 
        fill: { 
            bgColor: { 
                indexed: 64 
            }, 
            fgColor: { 
                rgb: "FFFF00" 
            } 
        } 
    };

    // Cell merging worksheet1["!merges"] = [{
        s: { c: 1, r: 0 },
        e: { c: 4, r: 0 }
    }];

    export2Excel({
        worksheets:
            sheet1: worksheet1,
            sheet2: worksheet2
        }, // Export excel data, key represents the worksheet name, value represents the sheet data of the corresponding worksheet, supports exporting multiple worksheets fileName: 'My excel', // Export file name type: 'xlsx' // File export type });
}

3. References

vue-json-excel plugin documentation
sheetjs-xlsx tool library
xlsx-style tool library

Summarize

This is the end of this article about Vue front-end exporting Excel files. For more relevant Vue exporting Excel files, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: