[excel] How to create an Excel File with Nodejs?

I am a nodejs programmer . Now I have a table of data that I want to save in Excel File format . How do I go about doing this ?

I found a few Node libraries . But most of them are Excel Parsers rather than Excel Writers .I am using a Linux Server . Hence need something that can run on Linux . Please let me know if there are any helpful libraries that you know of .

Or is there a way I can convert a CSV file to an xls file ( programmatically ) ?

This question is related to excel node.js file-io export-to-excel npm

The answer is


I just figured a simple way out . This works -

Just create a file with Tabs as delimiters ( similar to CSV but replace comma with Tab ). Save it with extension .XLS . The file can be opened in Excel .

Some code to help --

var fs = require('fs');
var writeStream = fs.createWriteStream("file.xls");

var header="Sl No"+"\t"+" Age"+"\t"+"Name"+"\n";
var row1 = "0"+"\t"+" 21"+"\t"+"Rob"+"\n";
var row2 = "1"+"\t"+" 22"+"\t"+"bob"+"\n";

writeStream.write(header);
writeStream.write(row1);
writeStream.write(row2);

writeStream.close();

This creates the file in XLS file format . It doesnt work if you try XLSX instead of XLS .


install exceljs

npm i exceljs --save

import exceljs

var Excel = require('exceljs');
var workbook = new Excel.Workbook();

create workbook

var options = {
                filename: __dirname+'/Reports/'+reportName,
                useStyles: true,
                useSharedStrings: true
            };

            var workbook = new Excel.stream.xlsx.WorkbookWriter(options);

after create worksheet

var worksheet = workbook.addWorksheet('Rate Sheet',{properties:{tabColor:{argb:'FFC0000'}}});

in worksheet.column array you pass column name in header and array key pass in key

worksheet.columns = [
            { header: 'column name', key: 'array key', width: 35},
            { header: 'column name', key: 'array key', width: 35},
            { header: 'column name', key: 'array key', width: 20},

            ];

after using forEach loop append row one by one in exel file

array.forEach(function(row){ worksheet.addRow(row); })

you can also perfome loop on each exel row and cell

worksheet.eachRow(function(row, rowNumber) {
    console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});
row.eachCell(function(cell, colNumber) {
    console.log('Cell ' + colNumber + ' = ' + cell.value);
});

Or - build on @Jamaica Geek's answer, using Express - to avoid saving and reading a file:

  res.attachment('file.xls');

  var header="Sl No"+"\t"+" Age"+"\t"+"Name"+"\n";
  var row1 = [0,21,'BOB'].join('\t')
  var row2 = [0,22,'bob'].join('\t');

  var c = header + row1 + row2;
  return res.send(c);

You should check ExcelJS

Works with CSV and XLSX formats.

Great for reading/writing XLSX streams. I've used it to stream an XLSX download to an Express response object, basically like this:

app.get('/some/route', function(req, res) {
  res.writeHead(200, {
    'Content-Disposition': 'attachment; filename="file.xlsx"',
    'Transfer-Encoding': 'chunked',
    'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  })
  var workbook = new Excel.stream.xlsx.WorkbookWriter({ stream: res })
  var worksheet = workbook.addWorksheet('some-worksheet')
  worksheet.addRow(['foo', 'bar']).commit()
  worksheet.commit()
  workbook.commit()
}

Works great for large files, performs much better than excel4node (got huge memory usage & Node process "out of memory" crash after nearly 5 minutes for a file containing 4 million cells in 20 sheets) since its streaming capabilities are much more limited (does not allows to "commit()" data to retrieve chunks as soon as they can be generated)

See also this SO answer.


Use exceljs library for creating and writing into existing excel sheets.

You can check this tutorial for detailed explanation.

link


Although this question has several answers, they may now be a little dated.

New readers may prefer to consider the xlsx or "sheetsJS" package, which now seems to now be by far the most popular node package for this use case.

The current top answer recommends excel4node , which does look quite good - but the latter package seems less maintained (and far less popular) than the former.

Answering the question directly, using xlsx:

const XLSX = require('xlsx');

/* create a new blank workbook */
const wb = XLSX.utils.book_new();

// Do stuff, write data
//
//

// write the workbook object to a file
XLSX.writeFile(workbook, 'out.xlsx');

Use msexcel-builder. Install it with:

npm install msexcel-builder

Then:

// Create a new workbook file in current working-path 
var workbook = excelbuilder.createWorkbook('./', 'sample.xlsx')

// Create a new worksheet with 10 columns and 12 rows 
var sheet1 = workbook.createSheet('sheet1', 10, 12);

// Fill some data 
sheet1.set(1, 1, 'I am title');
for (var i = 2; i < 5; i++)
  sheet1.set(i, 1, 'test'+i);

// Save it 
workbook.save(function(ok){
  if (!ok) 
    workbook.cancel();
  else
    console.log('congratulations, your workbook created');
});

XLSx in the new Office is just a zipped collection of XML and other files. So you could generate that and zip it accordingly.

Bonus: you can create a very nice template with styles and so on:

  1. Create a template in 'your favorite spreadsheet program'
  2. Save it as ODS or XLSx
  3. Unzip the contents
  4. Use it as base and fill content.xml (or xl/worksheets/sheet1.xml) with your data
  5. Zip it all before serving

However I found ODS (openoffice) much more approachable (excel can still open it), here is what I found in content.xml

<table:table-row table:style-name="ro1">
    <table:table-cell office:value-type="string" table:style-name="ce1">
        <text:p>here be a1</text:p>
    </table:table-cell>
    <table:table-cell office:value-type="string" table:style-name="ce1">
        <text:p>here is b1</text:p>
    </table:table-cell>
    <table:table-cell table:number-columns-repeated="16382"/>
</table:table-row>

Using fs package we can create excel/CSV file from JSON data.

Step 1: Store JSON data in a variable (here it is in jsn variable).

Step 2: Create empty string variable(here it is data).

Step 3: Append every property of jsn to string variable data, while appending put '\t' in-between 2 cells and '\n' after completing the row.

Code:

var fs = require('fs');

var jsn = [{
    "name": "Nilesh",
    "school": "RDTC",
    "marks": "77"
   },{
    "name": "Sagar",
    "school": "RC",
    "marks": "99.99"
   },{
    "name": "Prashant",
    "school": "Solapur",
    "marks": "100"
 }];

var data='';
for (var i = 0; i < jsn.length; i++) {
    data=data+jsn[i].name+'\t'+jsn[i].school+'\t'+jsn[i].marks+'\n';
 }
fs.appendFile('Filename.xls', data, (err) => {
    if (err) throw err;
    console.log('File created');
 });

Output


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to node.js

Hide Signs that Meteor.js was Used Querying date field in MongoDB with Mongoose SyntaxError: Cannot use import statement outside a module Server Discovery And Monitoring engine is deprecated How to fix ReferenceError: primordials is not defined in node UnhandledPromiseRejectionWarning: This error originated either by throwing inside of an async function without a catch block dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicui18n.62.dylib error running php after installing node with brew on Mac internal/modules/cjs/loader.js:582 throw err DeprecationWarning: Buffer() is deprecated due to security and usability issues when I move my script to another server Please run `npm cache clean`

Examples related to file-io

Python, Pandas : write content of DataFrame into text File Saving response from Requests to file How to while loop until the end of a file in Python without checking for empty line? Getting "java.nio.file.AccessDeniedException" when trying to write to a folder How do I add a resources folder to my Java project in Eclipse Read and write a String from text file Python Pandas: How to read only first n rows of CSV files in? Open files in 'rt' and 'wt' modes How to write to a file without overwriting current contents? Write objects into file with Node.js

Examples related to export-to-excel

Export from pandas to_excel without row names (index)? How to export dataGridView data Instantly to Excel on button click? How to export a CSV to Excel using Powershell How to create an Excel File with Nodejs? Download Excel file via AJAX MVC Export data to Excel file with ASP.NET MVC 4 C# is rendering into view Saving results with headers in Sql Server Management Studio phpexcel to download Export SQL query data to Excel How can I export tables to Excel from a webpage

Examples related to npm

What does 'x packages are looking for funding' mean when running `npm install`? error: This is probably not a problem with npm. There is likely additional logging output above Module not found: Error: Can't resolve 'core-js/es6' Browserslist: caniuse-lite is outdated. Please run next command `npm update caniuse-lite browserslist` ERROR in The Angular Compiler requires TypeScript >=3.1.1 and <3.2.0 but 3.2.1 was found instead DeprecationWarning: Buffer() is deprecated due to security and usability issues when I move my script to another server Please run `npm cache clean` What exactly is the 'react-scripts start' command? On npm install: Unhandled rejection Error: EACCES: permission denied Difference between npx and npm?