Excel Files in JavaScript, the Simple Way

Jose Segarra

Rate me:

4.19/5 (14 votes)

25 Jul 2017CPOL6 min read

101.8K

1.8K

A front-end library to create Excel files, including formatting and styling cells

Download source - 100.2 KB

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 instantiation of a model .... The user has reviewed it, s/he is happy with it, and wants to open it in 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:

It uses server resources, this can be a problem in highly loaded systems.
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
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 network involved at all. However, the number of libraries available is quite short, and usually there is some kind of trade-off.

On the 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 specifics (more than a Sheet, cell formatting, ...)

About the libraries that generate true Excel, either they are small libraries with limited options (i.e.,: 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 dependencies:

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

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

HTML

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

JavaScript

var excel = $JExcel.new();                    // Create an Excel with system default font
var excel = $JExcel.new("Arial 10 #333333");  // Default font is Arial 10 in RGB #333

And Excel object defines four methods:

addSheet
addStyle
set
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 a style definition object which is made of up to 5 properties:

JavaScript

{
  fill: "#ECECEC" ,                        // background color in #RRGGBB
  border: "none,none,none,thin #333333"    // left border,right border,top border, bottom border
  font: "Calibri 12 #0000AA B"});          // font-name font-size font-color font-style
  format: "yyyy.mm.dd hh:mm:ss",           // display format
  align: "R T",                            // horizontal-align vertical-align
}

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 conventions 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:

If only a sheet index is defined, the sheet name is set
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
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
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.

JavaScript

set(0,undefined,undefined,"Summary");                      // Set name of SHEET 0 to Summary

var fillEC=excel.addStyle ( {fill: "#ECECEC"});            // Style in ECECEC background color
set(2,undefined,1,undefined,fillEC);                       // Set fillEC style in row 1 in sheet 2
set( {sheet:2,row:1,style:fillEC});                        // alternatively

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

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

JavaScript

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++){              // Loop headers
    excel.set(0,i,0,headers[i],formatHeader);    // Set CELL header text & header format
    excel.set(0,i,undefined,"auto");             // Set COLUMN width to 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++){                                    // Generate 50 rows
    excel.set(0,0,i,"This is line "+i);                    // This column is a TEXT
    var d=randomDate(initDate,endDate);                    // Get a random date
    excel.set(0,1,i,d.toLocaleString());                   // Random date as STRING
    excel.set(0,2,i,$JExcel.toExcelLocalTime(d));          // Date as a NUMERIC
    excel.set(0,3,i,$JExcel.toExcelLocalTime(d),dStyle);   // Date as a NUMERIC in dStyle.format
    excel.set(0,4,i,"Some other text");                    // Some other text
}

excel.set(0,1,undefined,30);                               // Set COLUMN B to 30 chars width
excel.set(0,3,undefined,30);                               // Set COLUMN D to 20 chars width
excel.set(0,4,undefined,20, excel.addStyle( {align:"R"})); // Align column 4 to the right
excel.set(0,1,3,undefined,excel.addStyle( {align:"L T"})); // CELL B4  to LEFT-TOP
excel.set(0,2,3,undefined,excel.addStyle( {align:"C C"})); // CELL C4  to CENTER-CENTER
excel.set(0,3,3,undefined,excel.addStyle( {align:"R B"})); // CELL D4  to RIGHT-BOTTOM
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 in different formats. Also, the different sizes and cell alignments all along row 4 is noticeable.

Formulas

To use a formula, just enter it as you would do in Excel:

JavaScript

excel.set(0,8,1,15);            // Grid position 8,1 references cell I2    
excel.set(0,8,2,13);            // Grid position 8,2 references cell I3
excel.set(0,8,3,"=I2+I3");      // In cell I4: I2+I3 => 15+13 => 28

Finally

Implementing this conversor was easier than expected. The code is short and well commented, and should be easily hackable for adding new capabilities (i.e., 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 !!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Jose Segarra

Spain

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Excel Files in JavaScript, the Simple Way

Introduction

Using the Code

Points of Interest

Sample

Formulas

Finally

License

Comments and Discussions