Excel files in Javascript, the simple way

0
42

Introduction

Many times, we have the need to export from a web application data into Excel format,  Usually we have this data already in the browser: as part of a table content, as an instantation of a model …. The user has reviewed it, s/he is happy with it, and wants to open it on Excel.  In this situation, the developer has different options:

  • DO NOTHING, do not even offer the option. In this case the user is forced to select the data directly from the browser, and then copy & paste it into Excel.
  • BACK-END, generates the Excel file on the server. I am pretty sure that for whatever back-end you are using (.Net, Java, Node.js, PHP, …) there are many libraries that allow to generate nice Excel files. 
  • FRONT-END, generate the Excel directly on the browser.

The DO NOTHING approach works most of the time, but usually the results are awful. All cell formatting is lost in the process, multiple cells in the same row get merged,…

The BACK-END approach is the most popular. The available libraries makes implementing this functionality fast and cheap. However there some drawbacks, which might or might not affect you:

  1. It uses server resources, this can be a problem in highly loaded systems.
     
  2. There is a “latency”, the browser has to send the “generate my excel” request to the server, the server has to generate it and then send back to the browser for download. This translates in a perceptible “delay” between the user pressing the “Export As Excel” button, and the actual file being downloaded
     
  3. Usually the back-end needs to access the same data that is already present in the front-end. For persistent data then we need a database query, some data cache implemented on the server,… or just the browser sends along with the “generate Excel request” the data to be included. All of them increase back-end workload, network traffic and the aforementioned latency.

Using the FRONT-END approach all the previous issues just vanish. Usually this is pretty fast, as there is no netwrok involved at all. However the number of libraries available is quite short, and usually there is some kind of trade-off.

On one hand we have libraries that do not generate a true Excel, but another format (XML, CSV,…) that Excel is able to open. They are fine if you just require plain data export, without any of the Excel specifities (more than a Sheet, cell formatting, …)

About the libraries that generate true excel, either they are small libraries with limited options (ie: no cell formatting at all) or they are big libraries (>1MB) offering the full range of Excel options (js-xlsx, exceljs, openxmlsdkjs,… ) and with, usually a huge documentation.

In this article I present, a small library that presents the most basic functionality in a clean and concise way. Nevertheless it is the functionality that I have needed for 90% of my Excel generation needs. 

Using the code

The library is intended to be used on the browser, so it is a javascript library, with two dependecies:

  1. JSZip v3.1.3 by Stuart Knightley, http://stuartk.com/jszip
  2.  FileSaver.js by Eli Grey, http://eligrey.com

To use it we just include them in our <HEAD> tag:

<script type="text/javascript" src="jszip.js"></script>
<script type="text/javascript" src="FileSaver.js"></script>
<script type="text/javascript" src="myexcel.js"></script>

The library defines a single global object  $JExcel this object includes some generic conversions and a generator for Excel objects

var excel = $JExcel.new(); 
var excel = $JExcel.new("Arial 10 #333333"); 

And Excel object defines 4 methods: addSheet, addStyle, set and generate

addSheet is used to add additional sheets to the Excel object. It requires a name. An Excel object always has a default sheet (index 0). Sheets are referenced by their index of creation.

addStyle is used to register styles in the Excel document. It requires an style definition  object which is made of up to 5 properties:

{
 fill: "#ECECEC" , 
 border: "none,none,none,thin #333333" 
 font: "Calibri 12 #0000AA B"}); 
 format: "yyyy.mm.dd hh:mm:ss", 
 align: "R T", 
}

A border definition is made up of a “border style” and “border color in RGB”. Allowed border styles are available in the $JExcel.borderStyles array.

If font-style includes the B character then the font is bold, U if underlined and I if italic.

There are a number of predefined display formats in the $JExcel.formats array. It is also possible to personalize them

For horizontal-align and vertical-align, the following conventios apply: C: center L: left R: right T: top B: bottom -: none

set is used to set values or styles on cells/rows/columns/sheets. What is set is decided by the non-undefined parameters. The following rules apply:

  1. If only a sheet index is defined, the sheet name is set
  2. If only a sheet index and a row number are defined, a style can be set for the row in the sheet, and a value for the row height
  3. If only a sheet index and a column number are defined, a style can be set for the column in the sheet and a value for the column width
  4. If a sheet index, a column and a row number are defined, then a style can be set for the defined cell and a value for its content

The set method allows a canonical list of parameters, or a single object parameter. In the canonical form use undefined as the not-apply value, in the object parameter just do not include the property. 

set(0,undefined,undefined,"Summary");  

var fillEC=excel.addStyle ( {fill: "#ECECEC"}); 
set(2,undefined,1,undefined,fillEC);  
set( {sheet:2,row:1,style:fillEC});   

var Arial10B=excel.addStyle ({font: "Arial 10 B"});    
set( {sheet:0,col:5,row:3,value: "HELLO",style:Arial10B}); 
set(0,5,3,"HELLO",Arial10B); 

 The generate method generates an Excel WorkBook and makes it available for downloading.

Points of Interest

The XLSX format is just a bunch of XML files that are zipped together. This XML files follow the OpenXML convention. The library works by defining a model of Excel objects (sheets, rows, cells, styles,…) that are SET in memory, when the generate method is called the necessary XML nodes for the Excel obejcts are created.

These XML nodes are then “printed” and merged with the OpenXML templates, then everything is zipped and the resulting stream is ready for download. 

EXCEL does not handle date times as Javascript, They use different EPOCH moments. The library defines the following conversion functions $JExcel.toExcelLocalTime(jsDate)$JExcel.toExcelUTCTime(jsDate) 

For handling RGB values the following function is also provided $JExcel.rgbToHex(red,green,blue)

Sample

The following sample

function randomDate(start, end) {
    var d= new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
    return d;
}
        
var excel = $JExcel.new("Calibri light 10 #333333");            
excel.set( {sheet:0,value:"This is Sheet 0" } );
var evenRow=excel.addStyle( { border: "none,none,none,thin #333333"});                                                    
var oddRow=excel.addStyle ( { fill: "#ECECEC" ,border: "none,none,none,thin #333333"}); 
for (var i=1;i<50;i++) excel.set({row:i,style: i%2==0 ? evenRow: oddRow  });                    
excel.set({row:3,value: 30  });                                                                    
 
var headers=["Header 0","Header 1","Header 2","Header 3","Header 4"];                            
var formatHeader=excel.addStyle ( {
    border: "none,none,none,thin #333333",font: "Calibri 12 #0000AA B"}
);                                                         

for (var i=0;i<headers.length;i++){                       
    excel.set(0,i,0,headers[i],formatHeader);             
    excel.set(0,i,undefined,"auto");                      
}
            
var initDate = new Date(2000, 0, 1);
var endDate = new Date(2016, 0, 1);
var dStyle = excel.addStyle ( {                       
    align: "R",                                                                                
    format: "yyyy.mm.dd hh:mm:ss",                                                             
    font: "#00AA00"}
);                                                                         
            
for (var i=1;i<50;i++){                                    
    excel.set(0,0,i,"This is line "+i);                    
    var d=randomDate(initDate,endDate);                    
    excel.set(0,1,i,d.toLocaleString());                   
    excel.set(0,2,i,$JExcel.toExcelLocalTime(d));          
    excel.set(0,3,i,$JExcel.toExcelLocalTime(d),dStyle);   
    excel.set(0,4,i,"Some other text");                    
}

excel.set(0,1,undefined,30);                               
excel.set(0,3,undefined,30);                               
excel.set(0,4,undefined,20, excel.addStyle( {align:"R"})); 
excel.set(0,1,3,undefined,excel.addStyle( {align:"L T"})); 
excel.set(0,2,3,undefined,excel.addStyle( {align:"C C"})); 
excel.set(0,3,3,undefined,excel.addStyle( {align:"R B"})); 
excel.generate("SampleData.xlsx");

Generates the following Excel WorkBook

Notice the different display in columns B,C & D, they basically hold the same data, but on different formats. Also it is noticeable the different sizes and cell alignments all along row 4.

Finally

Implementing this conversor was easier than expected. The code is short and well commented, and shoud be easily hackeable for adding new capabilities (ie: cell merging). You can find a demo on http://jsegarra1971.github.io/MyExcel/sample.html and the latest code is available at https://github.com/jsegarra1971/MyExcel

If you use it, let me know !!!

LEAVE A REPLY