In the past, we've dabbled in zip archives, as well as Word document creation. This time around, let's take a look at generating Excel documents that we want to stream back to the browser. Most people know how to process CSV files, but when you open them in Excel, you get that annoying pop-up, so let's use an actual Excel library to get the correct format.

We start by installing the exceljs package:

npm install exceljs --save
    

…and if you're working with TypeScript, let's install the typings:

npm install @types/exceljs --save-dev
    

We'll be using TypeScript in this blog post.

Once you do, you can import the package at the top of your file:

import * as excel from 'exceljs';
    

Now let's create a utility function for creating the Excel file and returning the stream as a buffer:

export async function createExcel(headers: Partial<excel.Column>[], rows: any[]): Promise<Buffer> {
        const workbook: excel.stream.xlsx.WorkbookWriter = new excel.stream.xlsx.WorkbookWriter({});
        const sheet: excel.Worksheet = workbook.addWorksheet('My Worksheet');
        sheet.columns = headers;
        for(let i = 0; i < rows.length; i++) {
            sheet.addRow(rows[i]);
        }
        sheet.commit();
        return new Promise((resolve, reject): void => {
            workbook.commit().then(() => {
                const stream: any = (workbook as any).stream;
                const result: Buffer = stream.read();
                resolve(result);
            }).catch((e) => {
                reject(e);
            });
        });
    }
    

This function takes an array of the headers you want at the top of the Excel file, and any array of objects that represent the rows in the Excel spreadsheet. We then create a new WorkbookWriter and then create a Worksheet that we name "My Worksheet" (because we're creative). Next we set the columns property of the worksheet object to the headers that we passed in. As you can see by the TypeScript typings, this array is a Partial of the Excel package's Column type. We'll look at this a little more in a bit.

The next step is to add rows to the worksheet. In the code above, we loop over the rows and use the addRow() method of the worksheet to attach them. Once finished, we commit the data to the worksheet, and then return a Promise.

We're returning a Promise here because--at this time--TypeScript doesn't see a return value from committing the workbook when using async/await. This is a typings file issue.

The Promise calls commit() on the workbook, and the "thennable" return function allows us to get the stream of the workbook, and read that into a buffer.

Once this is complete, you can call this function from an Express route. For example:

app.get('/course/:name/:id/download'), async (req, res) => {
        const { name, id } = req.params;
        const data = getCourse(id);
        const stream: Buffer = await createExcel([
            { header: '', key: 'number' },
            { header: 'Session', key: 'session' },
            { header: 'Course/System', key: 'course' },
            { header: 'Learning Objective', key: 'lo' }
        ], data);
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', `attachment; filename=${ name }.xlsx`);
        res.setHeader('Content-Length', stream.length);
        res.send(stream);
    });
    

In this code, after grabbing the request parameters, we await the createExcel() method, passing it an array of objects representing the headers, and the data we receive from a getCourse() method.

The headers are a partial of Column like we mentioned before. Here we're just passing in an array of objects with header and key properties. The header is the column text, while the key is the property key in the data variable. The data variable is simply an array of objects that have the same properties as the values of each of the keys: number, session, course, lo.

We can then take this stream and send it back to the browser as an attachment for the user.