Click here to Skip to main content
15,312,319 members
Articles / Web Development / HTML
Article
Posted 19 Jul 2017

Stats

87K views
1.7K downloads
12 bookmarked

Excel Files in JavaScript, the Simple Way

Rate me:
Please Sign up or sign in to vote.
4.19/5 (14 votes)
25 Jul 2017CPOL6 min read
A front-end library to create Excel files, including formatting and styling cells

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:

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

  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:

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:

  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.

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)

Share

About the Author

Jose Segarra
Spain Spain
No Biography provided

Comments and Discussions

 
QuestionFormulas in cells Pin
Member 1550163120-Jan-22 7:55
MemberMember 1550163120-Jan-22 7:55 
Praisenpm package is added (myexcel) Pin
Paul41567-Oct-19 20:14
MemberPaul41567-Oct-19 20:14 
QuestionError when opening excel which is created by this method Pin
Member 1405280827-Feb-19 4:53
MemberMember 1405280827-Feb-19 4:53 
AnswerRe: Error when opening excel which is created by this method Pin
sag0r22-Sep-20 20:12
Membersag0r22-Sep-20 20:12 
QuestionMerge cells and change row height Pin
Member 1412482722-Jan-19 20:33
MemberMember 1412482722-Jan-19 20:33 
AnswerRe: Merge cells and change row height Pin
a.mario.reyes13-Sep-19 6:19
Membera.mario.reyes13-Sep-19 6:19 
Questionchange download file location Pin
Member 140619224-Dec-18 19:40
MemberMember 140619224-Dec-18 19:40 
QuestionValue in Non Numeric format Pin
Member 1405280814-Nov-18 2:52
MemberMember 1405280814-Nov-18 2:52 
Questionset auto for columns Pin
Member 1405280812-Nov-18 21:09
MemberMember 1405280812-Nov-18 21:09 
QuestionCreate Excel Sheet etc Pin
Member 1190425223-Oct-18 4:57
MemberMember 1190425223-Oct-18 4:57 
QuestionNode package Pin
Victor Juarez6-Sep-18 5:51
MemberVictor Juarez6-Sep-18 5:51 
Question/xl/styles.xml error Pin
Member 107419679-Jul-18 21:32
MemberMember 107419679-Jul-18 21:32 
QuestionFreeze row or column Pin
Jain Mathew7-Jul-18 0:16
MemberJain Mathew7-Jul-18 0:16 
QuestionUnreadable Content in xlsx file Pin
Member 94477545-Jul-18 3:08
MemberMember 94477545-Jul-18 3:08 
Questionextent align style to include indent Pin
Member 138195899-May-18 8:48
MemberMember 138195899-May-18 8:48 
BugMozilla Issue Pin
Member 1259978213-Apr-18 17:10
MemberMember 1259978213-Apr-18 17:10 
QuestionWord Wrap Pin
Shanay L12-Apr-18 2:51
MemberShanay L12-Apr-18 2:51 
AnswerRe: Word Wrap Pin
Member 1405280812-Nov-18 20:41
MemberMember 1405280812-Nov-18 20:41 
AnswerRe: Word Wrap Pin
Member 1420313929-Mar-19 2:42
MemberMember 1420313929-Mar-19 2:42 
GeneralRe: Word Wrap Pin
Sriram_V31-Mar-19 21:22
MemberSriram_V31-Mar-19 21:22 
GeneralRe: Word Wrap Pin
Member 1420313931-Mar-19 21:48
MemberMember 1420313931-Mar-19 21:48 
GeneralRe: Word Wrap Pin
Member 1555476222-Mar-22 2:39
MemberMember 1555476222-Mar-22 2:39 
QuestionRe: Word Wrap Pin
Member 1555476222-Mar-22 3:37
MemberMember 1555476222-Mar-22 3:37 
Questionhello, I have 1 question Pin
Member 124575242-Jan-18 19:58
MemberMember 124575242-Jan-18 19:58 
AnswerRe: hello, I have 1 question Pin
Jose Segarra13-Mar-18 0:39
MemberJose Segarra13-Mar-18 0:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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