1

I created a Rest API with the express framework that contains a route for exporting data. The data that should be exported to an excel file is stored in multiple arrays. The excel file should have a predefined structure/styling.

I am not limited to a certain file format as long as it can be opened in Microsoft Excel. Is there any recommendation which to use?

In general, how do i achieve this goal. Do i have to map each entry of the arrays to a certain cell in the excel file? How can i style/format a excel file with node js?

Please give me some general understanding of how to approach this task. I am very thankful for your help!

Edit:

I found the node module excel4node. Is it suitable for this task or are there better soultions?

Improve this question

1 Answer 1

Reset to default
3

Excel4Node is pretty nice, I've found this to be very useful for creating Excel documents, the API is very flexible, it's easy to write objects / arrays to a file, style it and save.

Styling is very simple, you can create Style objects and apply them to cells, again this is very flexible.

For example: 

const excel = require("excel4node");

const workbook = new excel.Workbook();

const style = workbook.createStyle({
    font: { color: "#0101FF", size: 11 }
});

const worksheet = workbook.addWorksheet("Sheet 1");

const arrayToWrite = Array.from({length: 10}, (v, k) => [`Row ${k+1}, Col 1`,`Row ${k+1}, Col 2`]);
arrayToWrite.forEach((row, rowIndex) => {
    row.forEach((entry, colIndex) => { 
        worksheet.cell(rowIndex + 1, colIndex + 1).string(entry).style(style); 
    })
})

workbook.write("text.xlsx");

There is also Xlsx, though I find this more useful for parsing than for file creation. It will also work in a browser.

To create a spreadsheet in memory, then allow the client to download: 

const express = require("express");
const port = 8000;
const app = express();
const stream = require("stream");
const excel = require("excel4node");

function createTestWorkbook() {
    const workbook = new excel.Workbook();
    const style = workbook.createStyle({
        font: { color: "#0101FF", size: 11 }
    });

    const worksheet = workbook.addWorksheet("Sheet 1");

    const arrayToWrite = Array.from({length: 10}, (v, k) => [`Row ${k+1}, Col 1`,`Row ${k+1}, Col 2`]);
    arrayToWrite.forEach((row, rowIndex) => {
        row.forEach((entry, colIndex) => { 
            worksheet.cell(rowIndex + 1, colIndex + 1).string(entry).style(style); 
        })
    })

    return workbook;
}

/* Allow client to download spreadsheet. */
app.get('/get-spreadsheet', (req, res) => {
    let workbook = createTestWorkbook();
    workbook.write('workbook.xlsx', res);
});

app.listen(port);
console.log(`Serving at http://localhost:${port}`);

This requires you to do 

npm install express

Then run the script and goto http://localhost:8000/get-spreadsheet/

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I am struggling with the path where the file should be stored. When i define it in workbook.write("test.xlsx"); like workbook.write(path.basename("/home/test.xlsx")); it only gets stored in the working directory of my app
Oh, yes, I was just using this path for test purposes.. in a production environment, you'd store this somewhere else. Of course, if you're serving the file you may not need to save it to the file system. You can write it to a buffer and the client can download this.
Thank you that is exactly what i need. It seems to work this way : wb.write(Excel.xlsx, res); according to link. But how does the client access the file then?
And what does wb.write(Excel.xlsx, res) do?
And how does the client access this? Until now i always did something like return res.send({<content for client>}).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.