Skip to content

Instantly share code, notes, and snippets.

@davidcsejtei

davidcsejtei/exportService.js

Created October 21, 2020 12:03
Show Gist options
  • Save davidcsejtei/952c52358c29d39dbe6fddbaa80af952 to your computer and use it in GitHub Desktop.
Save davidcsejtei/952c52358c29d39dbe6fddbaa80af952 to your computer and use it in GitHub Desktop.
Download ZIP
Export from JavaScript to Excel (xlsx)
Raw
exportService.js
const xlsx = require('xlsx');
const path = require('path');
const exportExcel = (data, workSheetColumnNames, workSheetName, filePath) => {
const workBook = xlsx.utils.book_new();
const workSheetData = [
workSheetColumnNames,
... data
];
const workSheet = xlsx.utils.aoa_to_sheet(workSheetData);
xlsx.utils.book_append_sheet(workBook, workSheet, workSheetName);
xlsx.writeFile(workBook, path.resolve(filePath));
}
const exportUsersToExcel = (users, workSheetColumnNames, workSheetName, filePath) => {
const data = users.map(user => {
return [user.id, user.name, user.age];
});
exportExcel(data, workSheetColumnNames, workSheetName, filePath);
}
module.exports = exportUsersToExcel;
Raw
index.js
const exportUsersToExcel = require('./exportService');
const users = [
{
id: 0,
name: 'Peter',
age: 31
},
{
id: 1,
name: 'John',
age: 23
}
];
const workSheetColumnName = [
"ID",
"Name",
"Age"
]
const workSheetName = 'Users';
const filePath = './outputFiles/excel-from-js.xlsx';
exportUsersToExcel(users, workSheetColumnName, workSheetName, filePath);
@khue20
Copy link

khue20 commented Nov 20, 2020

Please help

@rogeriorioli
Copy link

users is a paramenter you need pass when u called

exportUsersToExcel(called-user-array-here , workSheetColumnName, workSheetName, filePath);

@davidcsejtei
Copy link
Author

davidcsejtei commented Jul 29, 2021 •
edited
Loading

Modification shows you how to add multiple pages into the excel file. It creates two (with same data for the sake of simplicity) sheets and add them to the workbook (= excel file):

const xlsx = require('xlsx');
const path = require('path');

const exportExcel = (data, workSheetColumnNames, workSheetName, filePath) => {
    const workBook = xlsx.utils.book_new();
    const workSheetData = [
        workSheetColumnNames,
        ...data
    ];

    // create first sheet (page)
    let workSheet = xlsx.utils.aoa_to_sheet(workSheetData);
    // add first sheet to the excel file (or to the workbook)
    xlsx.utils.book_append_sheet(workBook, workSheet, workSheetName);

    // create second sheet (page)
    let workSheet2 = xlsx.utils.aoa_to_sheet(workSheetData);
    // add second sheet to the excel file (or to the workbook)
    xlsx.utils.book_append_sheet(workBook, workSheet2, `${workSheetName}_2`);

    xlsx.writeFile(workBook, path.resolve(filePath));
}

const exportUsersToExcel = (users, workSheetColumnNames, workSheetName, filePath) => {
    const data = users.map(user => {
        return [user.id, user.name, user.age];
    });
    exportExcel(data, workSheetColumnNames, workSheetName, filePath);
}

module.exports = exportUsersToExcel;

@darkterminal
Copy link

@davidcsejtei Thanks for your metaphor! I've implement your code in my project and it's work like charm! Also i added modification inside to make it compatible with my project. And here the improvement as example : NodeJS XLSX for API

Server

const xlsx = require('xlsx');

const exportToExcel = (data, workSheetColumnNames, workSheetName) => {
    const workBook = xlsx.utils.book_new();
    const workSheetData = [
        workSheetColumnNames,
        ... data
    ];
    const workSheet = xlsx.utils.aoa_to_sheet(workSheetData);
    xlsx.utils.book_append_sheet(workBook, workSheet, workSheetName);
    const sheet = xlsx.write(workBook, { type: 'base64', bookType: 'xlsx', compression: true });
    return 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + sheet
}

module.exports = exportToExcel;

Client

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <button onclick="download()"></button>

    <script>
        function dataURLtoFile(dataurl, filename) {
            var arr = dataurl.split(','),
                mime = arr[0].match(/:(.*?);/)[1],
                bstr = atob(arr[1]), 
                n = bstr.length, 
                u8arr = new Uint8Array(n);
                
            while(n--){
                u8arr[n] = bstr.charCodeAt(n);
            }
            return new File([u8arr], filename, {type:mime});
        }

        function download(filename) {
            // Generate from server (just for example)
            var base64 = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQAAAAIAAAAAACkAYS4WgEAALUCAAAaAAACgAKAHsCAABDHwAAAAA='
            
            var file = dataURLtoFile(base64, filename)
            var url = URL.createObjectURL(file)
            var a = document.createElement('a')
            a.link = url
            a.download = file
            a.click()
            URL.revokeObjectURL(url)
        }
        
    </script>
</body>
</html>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment