Generate Excel file via AJAX

excel-via-ajax.md

Generate Excel file via AJAX

Install dependencies

npm init
npm i express body-parser node-excel-export jquery -S

File server.js:

'use strict';

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var excel = require('node-excel-export');
var path = require('path');

// serve static assets from node_modules
app.use('/assets', express.static('node_modules'));

// configure parsers
app.use(bodyParser.urlencoded({extended: true, limit: '1mb'}));
app.use(bodyParser.json({limit: '5mb'}));

app
    .get('/', function (req, res) {
        res.sendFile(__dirname + '/index.html');
    })
    .get('/export', function (req, res) {
        // example from https://github.com/andreyan-andreev/node-excel-export
        let styles = {
            headerDark: {
                fill: {
                    fgColor: {
                        rgb: 'FF000000'
                    }
                },
                font: {
                    color: {
                        rgb: 'FFFFFFFF'
                    },
                    sz: 14,
                    bold: true,
                    underline: true
                }
            },
            cellPink: {
                fill: {
                    fgColor: {
                        rgb: 'FFFFCCFF'
                    }
                }
            },
            cellGreen: {
                fill: {
                    fgColor: {
                        rgb: 'FF00FF00'
                    }
                }
            }
        };

        // Array of objects representing heading rows
        let heading = [
            [
                {value: 'a1', style: styles.headerDark},
                {value: 'b1', style: styles.headerDark},
                {value: 'c1', style: styles.headerDark}
            ],
            ['a2', 'b2', 'c2'] // <-- It can be only values
        ];

        // export structure
        let specification = {
            customer_name: { // <- the key should match the actual data key
                displayName: 'Customer', // <- Here you specify the column header
                headerStyle: styles.headerDark, // <- Header style
                cellStyle: function (value, row) { // <- style renderer function
                    // if the status is 1 then color in green else color in red
                    // Notice how we use another cell value to style the current one
                    return (row.status_id == 1) ? styles.cellGreen : {fill: {fgColor: {rgb: 'FFFF0000'}}}; // <- Inline cell style is possible
                },
                width: 120 // <- width in pixels
            },
            status_id: {
                displayName: 'Status',
                headerStyle: styles.headerDark,
                cellFormat: function (value, row) { // <- Renderer function, you can access also any row.property
                    return (value == 1) ? 'Active' : 'Inactive';
                },
                width: '10' // <- width in chars (when the number is passed as string)
            },
            note: {
                displayName: 'Description',
                headerStyle: styles.headerDark,
                cellStyle: styles.cellPink, // <- Cell style
                width: 220 // <- width in pixels
            }
        };

        // The data set should have the following shape (Array of Objects)
        // The order of the keys is irrelevant, it is also irrelevant if the
        // dataset contains more fields as the report is build based on the
        // specification provided above. But you should have all the fields
        // that are listed in the report specification
        let dataset = [
            {customer_name: 'IBM', status_id: 1, note: 'some note', misc: 'not shown'},
            {customer_name: 'HP', status_id: 0, note: 'some note'},
            {customer_name: 'MS', status_id: 0, note: 'some note', misc: 'not shown'}
        ];

        // Create the excel report.
        // This function will return Buffer
        let report = excel.buildExport(
            [ // <- Notice that this is an array. Pass multiple sheets to create multi sheet report
                {
                    name: 'Sheet name', // <- Specify sheet name (optional)
                    heading: heading, // <- Raw heading array (optional)
                    specification: specification, // <- Report specification
                    data: dataset // <-- Report data
                }
            ]
        );

        // convert excel file content to base64 and send to a client
        res.send({content: report.toString('base64')});
    })
    .listen(4000, function () {
        console.log(`Listening on http://localhost:4000`);
    });

File index.html:

<html>
<head>
    <title>Export to Excel via AJAX</title>
    <script src="/assets/jquery/dist/jquery.min.js"></script>
</head>
<body>

<button id="export">Export To Excel</button>

<script>
    "use strict";

    jQuery(document).ready(function ($) {

        $('#export').on('click', function () {
            $.get('/export', function (resp) {
                let uri = "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,";
                let url = uri + resp.content;
                setTimeout(function () {
                    location.href = url;
                }, 100);
            });
        });

    });
</script>
</body>
</html>

Run server:

node server

Open http://localhost:4000 in web browser, then click button to download excel file.