Generate Excel in VueJS using ExcelJS

Aditya Dewantara Fadillah — Wed, 17 Apr 2024 15:02:10 +0000

Backstory for this case is i need to generate excel in VueJS (i dont know why we must to generate excel in frontend) and i dont know what to do. So at the first time i tried using vue-json-excel but i failed haha. So my friend suggested to try ExcelJS.

Ok so i assume you already install the ExcelJS using npm or yarn. And after that i made new file to export the excel. Why I made a new file not using inside the Vue Component, because I think i will use it later in different component so it will be easier for me to make it like global function. I make export.js file inside the mixins/ folder.

So, i need to generate excel from data that API given to Frontend. First of all inside the export.js i will make a function like this.

import * as ExcelJS from 'exceljs';

export const Export = {
    methods: {
        async exportToExcel (header, data, filename) {

        }
    }
}

So what inside header, data and filename. This is the value of the variable and how to call it in the component we want.

import { Export } from '@/mixins/helper';

export default {
    ...
    mixins: [Export],
    ...
    methods: {
    exportExcel() {
            var data = [
                {id: 1, name: 'Agus', dob: '1997-02-01', grade: 'A'},
                {id: 2, name: 'Boni', dob: '1996-01-02', grade: 'C'},
                {id: 3, name: 'Tono', dob: '1995-12-03', grade: 'B'},
                {id: 4, name: 'Asep', dob: '1992-11-04', grade: 'D'},
                {id: 5, name: 'Marwinto', dob: '1997-10-05', grade: 'A'}
            ];

            // Important part is key and header
            var header = [
                {key: 'id', header:'ID', width: 10},
                {key: 'name', header:'Name', width: 40},
                {key: 'dob', header:'Date of Birt', width: 30},
                {key: 'grade', header:'Grade', width: 10}
            ];

            this.exportToExcel(header, data, 'Grade Report');
        }
    }
}

Inside the function of in exportToExcel inside mixins/ is like this.

import * as ExcelJS from 'exceljs';

export const Export = {
    methods: {
        async exportToExcel (header, data, filename) {
            const options = {
                filename: filename + '.xlsx'
            }

            //Init Workbook
            const workbook = new ExcelJS.Workbook(options);
            const worksheet = workbook.addWorksheet(filename);

            worksheet.columns = fields;

            //Setting font size and bold for header
            worksheet.getRow(1).font = {
                bold: true
            };

            //Insert data row to excel the data key will follow header key
            for (let index = 0; index < data.length; index++) {
                worksheet.addRow(data[index])
            }

            //Export Excel to Base64
            const base64 = Buffer.from(await         

            workbook.xlsx.writeBuffer()).toString(
                'base64'
            )

            // Download File
            const a = document.createElement('a')
            a.href = 'data:application/xlsx;base64,' + base64 
            a.download = `${filename}.xlsx` // File name Here
            a.click()
        }
    }
}

Just like that, so if you want to download it you must convert it to base64 and then you must make a element to make it downloadable.

Thats it, for another documentation you can see it in ExcelJS github and other website.

Hope this helps. Thank you! :)

DEV Community: Aditya Dewantara Fadillah

Generate Excel in VueJS using ExcelJS