How to: Get browser data into Excel, via JavaScript

In a previous article, we discussed a backend technique of reporting automation from large databases into Excel, using Java. Now let’s look at a frontend technique that pulls Excel files from the grids / tables within a web application, using JavaScript.

Nowadays, most of our information intake happens online. Especially when you need a convenient way to quickly access, view, filter and analyze large amounts of data, there are few better tools than a web browser. Think of flight departure and landing times, stock exchange data, or visualizing various trends: their respective web applications are better than almost anything else.

“Almost” because there will be situations when convenience is not your most important KPI, and you will need to access the whole dataset locally:

  • A dedicated software (e.g. Excel) with extensive functions will be of much better use than a filterable browser grid, especially when it comes to more intensive work (think pivots, macros etc.).
  • Sharing data (especially if already processed / analyzed) is much easier when files are available.
  • Going back and forth between you (/your browser) and the server (/backend) has the potential to slow things down.

So, for each task, use its dedicated tool:

  • For quick access and first overview: check the in-browser data.
  • For sharing the data and in-depth work: pull the data into a local (Excel) file.

Here’s a very simplified breakdown of the process:

Berg Software - JavaScript to Excel diagram
In order to execute “Part B”, we work with SheetJS – so let’s look at the details, shall we?

What is SheetJS and what does it do?

SheetJS is the JavaScript library that allows parsing and writing of various spreadsheets formats, including the Excel’s xlsx. It’s not the single library that does that, but it’s by far the most popular – possibly because it comes in two variants: community (free) and commercial.

The free variant offers most of the features you need, such as:

  • Creating, parsing, populating and editing a spreadsheet
  • Converting a spreadsheet to CSV and HTML
  • Exporting a HTML table as spreadsheet
  • Manipulating cells (merge, hide / unhide)
  • Manipulate rows and columns
  • Inserting comments

The commercial one offers more advanced features, like:

  • Supports VBA and Macros
  • Adding pictures and charts
  • Working with rich text, fonts, colors, and borders
  • Formula and hyperlink support
  • Pivot table and chart
  • Converting styled Html to spreadsheets

Why SheetJS?

  • Fast
  • Simple to use
  • Offers comprehensive guidelines
  • It’s popular and has an active support forum

How does it work?

SheetJS is focusing on data transformation. Instead of having to painfully read (or write-to) every cell of the spreadsheet, it supports you in mapping your spreadsheet into a JS object. For that, it provides an intermediate layer for manipulating the data.

Once this mapping is done, the magic of actual transferring the data to (or from) the cumbersome underlying spreadsheet format happens behind the scenes.

SheetJS provides some functions to work with the data, so basically everything you need for data manipulation.

In this article, I’m going to provide an overview of this library’s capabilities, focusing on how to generate an Excel file.

Install

First things first, in order to use the library, we need to install it into our project. To install the library to your application, you need to run the next command line:

npm install xlsx –save

SheetJS has a utils tool class that helps us with:

  • Adding data to data tables
  • Converting different file types to workbooks
  • Converting workbooks into different file types
  • Workbook and cell operations, etc.

Create an Excel file

In order to create an Excel file, first we need to create parts of the file. We know that an Excel is made up from a workbook that has one or many worksheets.

A. We can create a worksheet from 3 different types of formats (array-of-arrays, json, and DOM table):

Create from Array of arrays (aoa):

const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);

Create from JSON:

const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet(aoaData);

Create from DOM table:

const worksheet: XLSX.WorkSheet = XLSX.utils.table_to_sheet(document.getElementById('my-table'));

B. Once we have a sheet (or multiple sheets), we must add it/them to a workbook.

The next function creates a new workbook:
const workbook: XLSX.WorkBook = XLSX.utils.book_new();
The following functions adds / appends a new sheet to the workbook:
XLSX.utils.book_append_sheet(workbook, worksheet, 'ExampleSheet');
For writing / downloading the workbook data to a file we have the following function:
XLSX.writeFile(workbook,'Example.xlsx');

Populate the sheets

The process of populating cells is done by the utils function that creates a sheet from the different data formats (json, aoa, DOM table).

Let’s take for example a json array of objects that looks like this:

this.employees = [
    {
      name:'Alin', age:20, experience: 0, phone:'0764853255', birthDate: '2000-01-02'
    },
    {
      name:'Marian', age:30, experience: 0, phone:'0248569122', birthDate: '1900-15-07'
    },
    {
      name:"Ruben", age:20, experience: 0, phone:'0723651241', birthDate: '2000-01-02'
    },
    {
      name:'Ioana', age:30, experience: 0, phone:'0762315901', birthDate: '1900-01-02'
    },
    {
      name:'Maria', age:25, experience: 3, phone:'0257888360', birthDate: '1995-01-02'
    },
    {
     name:'Bogdan', age:23, experience: 1, phone:'0737221235', birthDate: '1997-01-02'
    }
  ]
 

The function XLSX.utils.json_to_sheet(employees) takes the name and adds it as a header in the Excel spreasheet and the values from json and adds them as values in cells.

The result will look like this:

Berg Software - JavaScript to Excel - sample spreadsheet

Read & Parse a workbook

The library makes it possible for you to read and parse through an Excel file. There are two options to do it:

First with the read function:

const workbook: XLSX.WorkBook = XLSX.read(myData, {type: 'binary', cellDates: true});
The read function has 2 parameters: the first is the data you want to read; the second are the parsing options. MyData attribute is actually a bitestring representing the workbook. We can get that with the help of FileReader (which lets you read the content of files stored on other user’s computer):
const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const myData: string = e.target.result;
const workbook: XLSX.WorkBook = XLSX.read(myData, {type: 'binary', cellDates: true});

Second with the readFile function:

const workbook: XLSX.WorkBook = XLSX.readFile('Example.xlsx', {type: 'binary', cellDates: true});

The readFile function differentiates from the read function by the first argument, that is the name of the file we want to read.

In our example: for the parsing options we have:

  • the type: binary, which is used when you want to read the file as a Binary String (the most popular way of reading an Excel file),
  • and the cellDates option set to true, which means that the dates are stored as type d (a JS Date object/string to be parsed as Date).

There are many more options to choose from, that can be found here: https://www.npmjs.com/package/xlsx#parsing-options

When parsing a spreadsheet, you can:

  • choose to use the data as-is;
  • or intervene and manipulate the information first (for every column or every cell, if needed).

For example, let’s take an Excel spreadsheet with dates in a column, but in a mixed format (string, date, number). If we import the file in our app and want to read those dates, there will be considered as numbers (by default), and not the actual dates that they represent. For that, you may need to transform all those date formats, to a new Date object that’s valid in JS.

The part of code that does that is the following:

this.detailsColumnMap.forEach(col => {
    // transform excel values to string type
    if (col.ModelProp !== 'My Date Column') {
      excelData[col.ModelProp] = row[col.Column] ? row[col.Column] + "" : row[col.Column];
    } else {
      //for the column that has date values do the following
      let dateValue: any;
      dateValue = row[col.Column];
 
 
      //check if date has string format, and transform it to Date object.
      if(typeof dateValue === 'string' ) {
          let excelDate = row[col.Column].split('/');
          //transform string to date format: mm/dd/yyyy
          let convertedDate = new Date(excelDate[1]+'/'+excelDate[0]+'/'+excelDate[2]);
          excelData[col.ModelProp] = convertedDate.toString();
 
 
        //check if date has JS Date format and display it as it is
      } else if (dateValue instanceof Date) {
        excelData[col.ModelProp] = dateValue.toString();
 
 
        //check if date has a number format, and transform is to a JS Date object
      } else if (typeof dateValue === 'number') {
        //transform number to Date
        excelData[col.ModelProp] = this.excelDateToJSDate(dateValue).toString();
      }
    }  
  });
 

The detailsColumnMap is a json array of object representing a mapping of the columns in the Excel.

The function that transforms a number date format into a valid JS Date object is the following:

excelDateToJSDate(serial) {
    const utc_days  = Math.floor(serial - 25569);
    const utc_value = utc_days * 86400;                                        
    const date_info = new Date(utc_value * 1000);
 
    const fractional_day = serial - Math.floor(serial) + 0.0000001;
 
    let total_seconds = Math.floor(86400 * fractional_day);
 
    const seconds = total_seconds % 60;
 
    total_seconds -= seconds;
 
    const hours = Math.floor(total_seconds / (60 * 60));
    const minutes = Math.floor(total_seconds / 60) % 60;
    return new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, 
         minutes, seconds);
  }

Manipulate cells (merge)

We’ll take the Excel file we created earlier and manipulate some cells in it.
Let’s try to merge the first 2 cells A1 with B1, the following code does that:
    //merge cells A1 and B1
    let merge = {s: {r:0, c:0}, e:{r:0, c:1}};
 
 
    if(!worksheet['!merges']) worksheet['!merges'] = [];
    worksheet['!merges'].push(merge);

First we create the merge (setting the range that we want to merge – s: start, r=row, c=column, e:end).

Then, if there are no merges existent, we create a new array of merges and add the merge we’ve just created.

Here’s the result:

Berg Software - JavaScript to Excel - merge cells completed
If you want to merge multiple cells, you can do the following:
let merge = [
      { s: { r: 1, c: 0 }, e: { r: 2, c: 0 } },
      { s: { r: 4, c: 0 }, e: { r: 5, c: 0 } },
      { s: { r: 2, c: 3 }, e: { r: 4, c: 3 } },
    ];
    worksheet['!merges'] = merge;
And the result will look like this:
Berg Software - JavaScript to Excel - merge cells final

Manipulate rows & columns (hide/unhide)

Hiding or unhiding a row/column is similar to merging cells. Taking the same example as above, the before look like this:
Berg Software - JavaScript to Excel - row columns hide unhide

Worksheet has an array of column properties objects: [‘!cols’] – you can set the column visibility & the width. Same for the rows: [‘!rows’] – here you can set the visibility and also the height and level of the row.

So, in order to hide/unhide some columns and rows, we have to do the following:

    //hide third column
    worksheet['!cols'] = [];
    worksheet['!cols'][2] = {hidden: true};
 
 
    //hide rows 3,5 & 7
    worksheet['!rows']= [];
    worksheet['!rows'][2] = {hidden: true};
    worksheet['!rows'][4] = {hidden: true};
    worksheet['!rows'][6] = {hidden: true};
The result is the following:
Berg Software - JavaScript to Excel - row columns hide unhide final

Insert comments

For the comments we have the following function:
//add comment to cell
    let cell = worksheet['D2'];
    XLSX.utils.cell_add_comment(cell,'This is my number');
    XLSX.utils.cell_add_comment(worksheet['E6'],'This date is wrong');
Or we can do it like this:
    if(!worksheet.D2.c) worksheet.D2.c = [];
    worksheet.D2.c.push({a:"Me", t:"This is my number"});
The result looks like this:
Berg Software - JavaScript to Excel - comments
You can even hide comments by adding the following line of code:
worksheet.E6.c.hidden = true;

Comparisons & Limitations

Reading a fairly large amount of data is possible, but going beyond some thresholds might result in crashes.

As mentioned before: the free version of the library is slightly limited but still sufficient for most of the scenarios. More specialized features are available in the commercial version, such as styling, adding charts, macros, and VBAs and many more.

The library runs inside a browser’s JS Runtime Environment so it’s dependent on the end-user’s infrastructure. Heavy operations, involving large spreadsheets, can lead to performance problems for old browsers and old PCs.

__

In-browser tables and grids are still the most convenient option for accessing data and getting a quick first insight. But if your usage case involves deeper work with data that you can only pull from a web application, then exporting to Excel via JavaScript / SheetJS. At Berg Software, we highly recommend it.

__

Do *you* use JavaScript / SheetJS? How & for each usage cases? Let us know!
29 years in business | 2700 software projects | 760 clients | 24 countries

We turn ideas into software. What is yours?

Get in touch

15 + 14 =