Importing Excel Files Into The Grid

This article demonstrates a way to import an excel file into ag-Grid using the third party library, xlsx-styles.

It is worth noting, in this example we are providing a simple excel file (which can be downloaded here) and fetching it for demonstration purposes but, please feel free to expand on this example to allow end users of your application to upload their own excel files.

There are three key points in this example with regards to importing excel files into the grid successfully. The first is a function which fetches the excel data:

function makeRequest(method, url, success, error) {
      var httpRequest = newXMLHttpRequest();
      httpRequest.open("GET", url, true);
      httpRequest.responseType = "arraybuffer";
      httpRequest.open(method, url);
      httpRequest.onload = function () {
          success(httpRequest.response);
      };
      httpRequest.onerror = function () {
         error(httpRequest.response);
     };
     httpRequest.send();
}

the next is one that converts that data to an Xlsx workbook:

function convertDataToWorkbook(data) {
   var data = new Uint8Array(data);
   var arr = new Array();

   for (var i = 0; i !== data.length; ++i) {
       arr[i] = String.fromCharCode(data[i]);
    }

   var bstr = arr.join("");

   return XLSX.read(bstr, {type: "binary"});
   }

The third is a function that takes the workbook data and transforms it into grid data:

function populateGrid(workbook) {
   var firstSheetName = workbook.SheetNames[0];
   var worksheet = workbook.Sheets[firstSheetName];
   var columns = {

      'A': 'athlete',

      'B': 'age',

      'C': 'country',

      'D': 'year',

      'E': 'date',

      'F': 'sport',

      'G': 'gold',

      'H': 'silver',

      'I': 'bronze',

      'J': 'total'

   };

   var rowData = [];
 
   var rowIndex = 2;

   while (worksheet['A' + rowIndex]) {
      var row = {};
      Object.keys(columns).forEach(function(column) {
      row[columns[column]] = worksheet[column + rowIndex].w;
   });

      rowData.push(row);
      rowIndex++;
   }
   gridOptions.api.setRowData(rowData);
}

Please see the following example for more details:

https://plnkr.co/edit/TWD20VK8bEHGpo5c