Open Open XML Excel Format Files in JavaScript

In order to upload information about retail outlets to our logistic SaaS-service “Forward Logistics” from Excel, I decided to use a web browser. It is usually easier to upload a file to the server and use any library to upload it to the database. But I was interested in downloading it line by line to monitor the integrity of each line on the client, and, of course, try out the advertised HTML5 FileAPI and Drag and Drop.

The Excel book is a ZIP archive with directories and XML files in Open XML format . Parsing XML is excellent at jQuery, but not zipper. To do this, the zip.js library was found in the open spaces of the network, which coped perfectly with the task.

So, let's try to see what is inside the archive:

<div class="main"> <progress id="progress"></progress> <div class="filedrag" id="comps">  <span class="red"></span></div> <div class="result"></div> </div> 

 var c = document.getElementById("comps"), FileDragHover = function (e) { e.stopPropagation(); e.preventDefault(); if(e.target.id==='comps') e.target.className = (e.type == "dragover" ? "filedrag hover" : "filedrag"); else c.className = (e.type == "dragover" ? "filedrag hover" : "filedrag"); } c.addEventListener("drop", function(e){ e.preventDefault(); c.className = "filedrag"; var files = e.target.files || e.dataTransfer.files; for (var i = 0, f; f = files[i]; i++) { if(f.name.toLowerCase().indexOf('xlsx')<=0) { alert('   Excel'); } else { zip.createReader(new zip.BlobReader(f), function(reader) { //     reader.getEntries(function(entries) { //       Excel console.info(entries) return false; }); }, function(error) { alert(": " + error) }); } } return false; }, false); c.addEventListener("dragover", FileDragHover, false); c.addEventListener("dragleave", FileDragHover, false); 

The result can be found here . Download the sample file and drag it onto the form.
A list of all the files in the Excel workbook appears in the console. Among the properties of objects appearing in the console, there is a filename, and by that we will look for the XML files we need.
')
We need two files from the archive:

• import.xlsx \ xl \ worksheets \ sheet [N] .xlsx
• import.xlsx \ xl \ sharedStrings.xml

Where:
sheet [N] .xlsx is the Excel sheet itself, N is its internal number in the book.
sharedStrings.xml - associative array of strings, dictionary sheet.

Filter only the files we need:

 //     reader.getEntries(function(entries) { var a=[],st; for(var i in entries){ var e=entries[i]; var fn=e.filename.toLowerCase(); if(fn.indexOf("sheet")>0){ a.push(e); } else if(fn.indexOf("sharedstring")>0){ st=e; } } //     Excel console.info(a) //    console.info(st) return false; }); 

The result can be viewed here by uploading the file and looking at the console.

Next, we need to extract the data with simple selectors, for the dictionary of rows this is st t , for records of the table with the data on the sheet it is sheetdata row .

Add a function to display data from an Excel sheet:

 printExcelData = function(sheets, strings) { var unzipProgress = document.getElementById("progress"); unzipProgress.style.display='block'; strings.getData(new zip.TextWriter(), function(text) { //          var i,st=$($.parseXML(decodeURIComponent(escape(text)))).find('si t'); for(i=0;i<st.length;++i) st[i]=$(st[i]).text(); //      var parseSheet=function(sheet){ var j,i,h,sh,d=[],s; sheet.getData(new zip.TextWriter(), function(text) { //      sh=$($.parseXML(decodeURIComponent(escape(text)))).find('sheetdata row'); //     sh.each(function(e){ var c=$(this).find('c'),ci,v,o={}; for(i=0;i<c.length;++i){ ci=$(c[i]); v=ci.find('v').text(); if(ci.attr('t')) v=st[v]; j=ci.attr('r').charCodeAt(0)-65; if(h) o[h[j]]=v; else o[j]=v; } if(h){ d.push(o) } else h=o; }); var id_name=""; for(i in h) if(h[i]=='Comp_Id'){ id_name=h[i]; break; } //   Comp_Id   ,    if(id_name=='Comp_Id') { unzipProgress.style.display='none'; //     s=""; for(i=0;i<Object.keys(h).length;i++) s+='<th>'+h[i]+'</th>'; $('.result thead tr').append(s) //   s=""; for(j=0; j<d.length; j++){ s+='<tr>'; for(i=0; i<Object.keys(h).length; i++){ s+='<td>'+d[j][h[i]].toString()+'</td>'; } s+='</tr>'; } $('.result tbody').append(s) sheets=[]; return; } if(sheets.length>0) parseSheet(sheets.pop()); }, function(current, total) { unzipProgress.value = current; unzipProgress.max = total; }); } parseSheet(sheets.pop()); }, function(current, total) { unzipProgress.value = current; unzipProgress.max = total; }); } 

As Chrome considers it a crime to use the HTML File API in a cross-domain ( uncaught ), the last example was uploaded to a Web server .
Drag the file and get the standard HTML table.

PS
Yes, now, as it turned out, there is the Open XML SDK for JavaScript , but this is a topic for a separate article ...