preface

Summary of Excel table export based on Vue. Vue version 2.6.10, VUE – CLI version 3.12.1, and Node version v14.17.5 are used.

※ Note: “+” at the beginning of each line in the code area of this article means new, “-” means deleted, and “M” means modified; “… “in code Represents omission.

Explanation of nouns in Excel

Sheet1,Sheet2,Sheet3 workbook is an Excel file. I was also confused about the Workbook and worksheet when I first read the document, and then I wrote it here after consulting the materials.

1 Installation project dependencies

This method relies on the plug-in XLSX, documentation address: github.com/SheetJS/she…

Install the dependency and download the specified version of XLSX to prevent errors after the upgrade

npm install xlsx@0.169.
Copy the code

Import in the files you need

import XLSX from 'xlsx'
Copy the code

2 Export Excel on the Web

2.1 Data Export

This works for tables in Element-UI, or other tabular data that is not composed of a table tag. Anyway, given the data, you can output Excel.

	import XLSX from 'xlsx'./** * Export the spreadsheet *@param {Array<array>} Header header data, a two-dimensional array, outputs * in order@param {Array<object>} Body Table body data, Object. Keys () order output *@param {String} Format Output table format, default 'XLSX' */
    exportSpreadsheet({ header, body, format = 'xlsx' }) {
        Case data: Converted arrays of JS data to a worksheet
        const headerWs = XLSX.utils.aoa_to_sheet(header)
        // console.log(JSON.stringify(body) );

        // Define the format of the worksheet
        const ws = XLSX.utils.sheet_add_json(headerWs, body, {
            skipHeader: true.origin: 'A2',})/* Create an empty workbook and add the worksheet */
        const wb = XLSX.utils.book_new()

        // Add workSheet to workBook. You can customize sheetName after downloading
        XLSX.utils.book_append_sheet(wb, ws, 'sheetName')

        /* Generate XLSX file, attempts to write wb to filename. In browser-based environments, it will attempt to force a client-side download */
        XLSX.writeFile(
            wb,
            `The ${this.formatDate(new Date(), 'current')}.${format}`,
            {
                bookType: format, // Type of Workbook, default "xlsx"
                bookSST: true.// Generate Shared String Table **, default false})}...export { exportSpreadsheet }
Copy the code

Example:

import {exportSpreadsheet} from '@/xxx'Header: [["User ID"."User name"."User Account"."Event Status"."Event State Description"."ID"."Event type"."Event Time"."Client Type"."Record creation time"."Client Type name"."Client information"]]

body: [{clientInfo: "Chrome".clientTypeName: "Windows".createDetetime: "The 2022-02-15 15:15:47".eventDatetime: "The 2022-02-15 15:15:48".eventType: "Login".logonEventId: 402.message: "Login successful".status: "Success".userAccount: "admin".userId: 1001.userLastname: "Administrator",}]

exportSpreadsheet(header,body)

Copy the code

Output result:

Note: formatDate is the time formatting method, the output file is the current date + hour minute second

2.2 DOM Export

This method works with native HTML table tags to export Excel tables

    import XLSX from 'xlsx'./** * Export excel table *@param {DOMObject} Table gets the table DOM element */
    exportExcelByDOM(table) {
        if (table) {
            // wb (workbook) : xlsx. writeFile is the first parameter passed when writing files
            // raw: Keep the raw string output so that the time format is not corrupted
            let wb = XLSX.utils.table_to_book(table, {
                raw: true,})let format = 'xlsx'

            XLSX.writeFile(wb, res.filePath, {
                 bookType: format, // Type of Workbook, default "xlsx"
                 bookSST: true.// Generate Shared String Table **, default false})},...export { exportExcelByDOM }
Copy the code

Example:

import {exportExcelByDOM} from '@/xxx' 
let table = document.querySelector('.table')
exportExcelByDOM(table)
Copy the code

Result: The value is the same as the table element in the web page

3 Export Excel on the Electron desktop

3.1 Data Export

	import XLSX from 'xlsx' 
	const { dialog } = require('electron').remote
	...
	/** * Export the spreadsheet *@param {Array<array>} Header header data, a two-dimensional array, outputs * in order@param {Array<object>} Body Table body data, Object. Keys () order output *@param {String} Format Output table format, default 'XLSX' */
    exportSpreadsheet({ header, body, format = 'xlsx' }) {
        Case data: Converted arrays of JS data to a worksheet
        const headerWs = XLSX.utils.aoa_to_sheet(header)
        // console.log(JSON.stringify(body) );

        // Define the format of the worksheet
        const ws = XLSX.utils.sheet_add_json(headerWs, body, {
            skipHeader: true.origin: 'A2',})/* Create an empty workbook and add the worksheet */
        const wb = XLSX.utils.book_new()

        // Add workSheet to workBook. You can customize sheetName after downloading
        XLSX.utils.book_append_sheet(wb, ws, 'sheetName')

       dialog.showSaveDialog({
            title: 'Save file'.defaultPath: `${formatDate(new Date(), 'current')}.${format}`.filters: [{ name: 'All Files'.extensions: [format] }],
        })
        .then((res) = > {
            // console.log(res, screenShootBlob)
            if (res.filePath) {
                // console.log(res.filePath)

                XLSX.writeFile(wb, res.filePath, {
                    bookType: format, // Type of Workbook, default "xlsx"
                    bookSST: true.// Generate Shared String Table **, default false})}})}...export { exportSpreadsheet }
Copy the code

Example:

import {exportSpreadsheet} from '@/xxx'Header: [["User ID"."User name"."User Account"."Event Status"."Event State Description"."ID"."Event type"."Event Time"."Client Type"."Record creation time"."Client Type name"."Client information"]]

body: [{clientInfo: "Chrome".clientTypeName: "Windows".createDetetime: "The 2022-02-15 15:15:47".eventDatetime: "The 2022-02-15 15:15:48".eventType: "Login".logonEventId: 402.message: "Login successful".status: "Success".userAccount: "admin".userId: 1001.userLastname: "Administrator",}]

exportSpreadsheet(header,body)

Copy the code

Output result:

Note: formatDate is the time formatting method, the output file is the current date + hour minute second

3.2 DOM Export

This method works with native HTML table tags to export Excel tables

    import XLSX from 'xlsx'   
	const { dialog } = require('electron').remote
	...
	  /** * Export excel table *@param {DOMObject} Table gets the table DOM element */
    exportExcelByDOM(table) {
        if (table) {
            // wb (workbook) : xlsx. writeFile is the first parameter passed when writing files
            // raw: Keep the raw string output so that the time format is not corrupted
            let wb = XLSX.utils.table_to_book(table, {
                raw: true,})let format = 'xlsx'

            dialog
                .showSaveDialog({
                    title: 'Save file'.defaultPath: `${formatDate(
                        new Date(),
                        'current'
                    )}.${format}`.filters: [{ name: 'All Files'.extensions: [format] }],
                })
                .then((res) = > {
                    // console.log(res, screenShootBlob)
                    if (res.filePath) {
                        // console.log(res.filePath)

                        XLSX.writeFile(wb, res.filePath, {
                            bookType: format, // Type of Workbook, default "xlsx"
                            bookSST: true.// Generate Shared String Table **, default false})}})}}...export { exportExcelByDOM }
Copy the code

Example:

import {exportExcelByDOM} from '@/xxx' 
let table = document.querySelector('.table')
exportExcelByDOM(table)
Copy the code

Result: The value is the same as the table element in the web page