How to: Get browser data into Excel with SheetJS

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?

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 an HTML table as a 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 SheetJS work?

Once this mapping is done, the magic of actually 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

npm install xlsx –save

• 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

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

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

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

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

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

XLSX.writeFile(workbook,'Example.xlsx');

Populate the sheets

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 result will look like this:

Read & Parse a workbook

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

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

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

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)

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

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.

__

__