Skip to content

Instantly share code, notes, and snippets.

@davidcsejtei

davidcsejtei/exportService.js

Created October 21, 2020 12:03
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • 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);
@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