How do I read excel file and create a lookup table JS/HTML/CSS

See more:

Javascript

CSS

HTML

This is somewhat bewildering to me given the sheer number of libraries and methods one might potentially use to solutionise this. I guess what I am seeking is proven experience to expedite development time.

Anyway, the scenario is that we have Staff Training Completion data hosted on a webserver as an XLSX file. The file contains 1,077,200 rows and is a dump of Training courses completed by every individual across the company, across the world. The XLSX file contains a header row and the columns are: EMPLOYEE(numeric[10]), COURSE_ID(alphanumeric[20]), TITLE(alphanumeric[100]), DATE(date[dd-mmm-yy]). We have no control over this file , cannot change it or the format , and can only read it. There are no unique fields and each employee can have 0..n rows of assocaited training data. We need to read this XLSX into a lookup table. So we are of the view that we need to read this XLSX file into a two-dimension lookup table, EMPLOYEE ID by COURSE ID so a 1..n relationship.

Next, we have a list of Training Courses also hosted on a web server, as an XLSX file; this is simply a two columns, column 1 is the course ID [same as appears in the Training Completion] and column 2 is the course title which is a local renamed title [different than in the Training Completion]. Thus we do not use the course title as appearing in the Training Completion file. Again, we are of the view that we need to read this XLSX file into a two-dimension lookup table, COURSE_ID by COURSE_TITLE but this is a 1..1 relationship.

So we have 2 lookup tables:

Table 1: Employee ID is unique key
EMPLOYEE_ID   {COURSE_ID, COMPLETION_DATE}, {COURSE_ID, COMPLETION_DATE} , ...
EMPLOYEE_ID   {COURSE_ID, COMPLETION_DATE}, {COURSE_ID, COMPLETION_DATE} , ...
EMPLOYEE_ID   {COURSE_ID, COMPLETION_DATE}, {COURSE_ID, COMPLETION_DATE} , ...

Table 2: Course ID is unique key
COURSE_ID   {COURSE TITLE}, {COURSE TITLE}, , ...
COURSE_ID   {COURSE TITLE}, {COURSE TITLE}, , ...
COURSE_ID   {COURSE TITLE}, {COURSE TITLE}, , ...

In SharePoint we want to host a Training Matrix in a html content web part [html/css/javascript]. The Matrix will display, for the logged in user and all Direct Reports, the completed training courses. Users are identified by their EMPLOYEE number which is the same as the number given in the hosted Staff Training Completion xlsx file. We want to display this matrix as a table with Employee name [which we get from SharePoint] down the left, and across is each possible training course name [lookup table 2], and each intersection(cell) contains the completion date [from lookup table 1] or blank if not completed:-

          {Fire Handling}     {Manual Handling}  {Code of Conduct}   ...
Anne      10/11/2018          10/01/2018       
Fred      04/01/2018          10/01/2019         10/11/2018
Jo        11/12/2018                             10/11/2018
...

There are about 10,000 employees but at most only about 20 would be displayed on the matrix if the user had 20 direct reports, and at minimum 1 - the user logged into SharePoint if they did not have any direct reports. And don't forget that the list of completed training can be over 1 million rows !!

This is how we are reading XLSX files [although we don't actually know which way to go after the fileReader.readAsArrayBuffer(file);

function handleFile(e) {
  var files = e.target.files,file;
  if (!files || files.length == 0) return;
  file = files[0];
  var fileReader = new FileReader();
  fileReader.onload = function (e) {
    var filename = file.name;
    // pre-process data
    var binary = "";
    var bytes = new Uint8Array(e.target.result);
    var length = bytes.byteLength;
    for (var i = 0; i < length; i++) {
      binary += String.fromCharCode(bytes[i]);
    }
    // call 'xlsx' to read the file
    var oFile = XLSX.read(binary, {type: 'binary', cellDates:true, cellStyles:true});
  };
  fileReader.readAsArrayBuffer(file);
};

We know how to get the SharePoint data working. We are fine with html and css for the matrix output. We know how to read XLSX files using one of the js libs like xls/xlsx or js-xls What we do not know is how to model the two lookup tables or indeed whether there is a better way. We are not really JavaScript programmers but learning quickly. Unfortunately due to many issues , we cannot use any languages other than js,css and html. So the solution must rely on these three.

Where on earth do we start?

Thank you. Hope this teaser gets the grey matter heated up :)

Thank you for participating.

What I have tried:

Dont know where to start? So many options.