Open Open XML Excel files in JavaScript

To download information about outlets in our logistics SaaS service “Ant Logistics” from Excel, I decided to use a web browser. It is usually easier to upload a file to the server and upload it to the database using any library. But I was interested in downloading it line by line to control the integrity of each line on the client, and, of course, try out the HTML5 FileAPI and Drag and Drop advertised by everyone.

Excell is a ZIP archive with catalogs and XML files in Open XML format . JQuery is good at parsing XML, but there is no zippy. To do this, the zip.js library was found on the vastness of the network, which did an excellent job.

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

Перетащите файл сюда

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 seen here . Download the sample file and drag it onto the form.
A list of all Excel workbook archive files appears in the console. Among the properties of the objects that appeared in the console, there is filename, by which we will look for the XML files we need.

We will need two files from the archive:

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

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

We filter out 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;
});

You can see the result here by uploading the file and looking at the console.

Next, we need to extract the data with simple selectors, for the string dictionary it is st t , for table entries with 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';
                     $('.result thead tr').append(s)
                     // Это данные
                     s="";
                     for(j=0; j';
                         }
                         s+='';
                     }
                     $('.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;
     });
 }

Since Chrome considers it a crime to use the HTML File API in the cross-domain ( Uncaught SecurityError: An attempt was made to break through the security policy of the user agent. ), The last example was uploaded to the Web server .
Drag and drop the file and get the standard HTML table.

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