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:
<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 four methods:
addSheetaddStylesetgenerate
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:
{
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 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.
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 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:
excel.set(0,8,1,15);
excel.set(0,8,2,13);
excel.set(0,8,3,"=I2+I3");
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 !!!
