How to create an Excel File with Nodejs

Question

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

User · Answer

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  amp  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

User · Answer

excel4node is a maintained  native Excel file creator built from the official specification  It s similar to  but more maintained than mxexcel-builder mentioned in the other answer       Require library var excel   require  excel4node        Create a new instance of a Workbook class var workbook   new excel Workbook        Add Worksheets to the workbook var worksheet   workbook addWorksheet  Sheet 1    var worksheet2   workbook addWorksheet  Sheet 2        Create a reusable style var style   workbook createStyle     font        color    FF0800       size  12        numberFormat        0 00        0 00   -          Set value of cell A1 to 100 as a number type styled with paramaters of style worksheet cell 1 1  number 100  style style       Set value of cell B1 to 300 as a number type styled with paramaters of style worksheet cell 1 2  number 200  style style       Set value of cell C1 to a formula styled with paramaters of style worksheet cell 1 3  formula  A1   B1   style style       Set value of cell A2 to  string  styled with paramaters of style worksheet cell 2 1  string  string   style style       Set value of cell A3 to true as a boolean type styled with paramaters of style but with an adjustment to the font size  worksheet cell 3 1  bool true  style style  style  font   size  14      workbook write  Excel xlsx

User · Answer

Use exceljs library for creating and writing into existing excel sheets   You can check this tutorial for detailed explanation   link

User · Answer

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

User · Answer

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  lt  jsn length  i          data data jsn i  name   t  jsn i  school   t  jsn i  marks   n      fs appendFile  Filename xls   data   err    gt        if  err  throw err      console log  File created

User · Answer

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

User · Answer

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    Create a template in  your favorite spreadsheet program  Save it as ODS or XLSx Unzip the contents Use it as base and fill content xml  or xl worksheets sheet1 xml  with your data 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   lt table table-row table style-name  ro1  gt       lt table table-cell office value-type  string  table style-name  ce1  gt           lt text p gt here be a1 lt  text p gt       lt  table table-cell gt       lt table table-cell office value-type  string  table style-name  ce1  gt           lt text p gt here is b1 lt  text p gt       lt  table table-cell gt       lt table table-cell table number-columns-repeated  16382   gt   lt  table table-row gt

User · Answer

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

User · Answer

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

User · Answer

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  lt  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

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

Examples related to excel

Examples related to node.js

Examples related to file-io

Examples related to export-to-excel

Examples related to npm