How to: Get browser data into Excel, via 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:

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

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.

const workbook: XLSX.WorkBook = XLSX.utils.book_new();

XLSX.utils.book_append_sheet(workbook, worksheet, 'ExampleSheet');

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:

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});

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)

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

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;

Manipulate rows & 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};

Insert comments

//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');

    if(!worksheet.D2.c) worksheet.D2.c = [];
    worksheet.D2.c.push({a:"Me", t:"This is my number"});

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.

__

__