HTML table to Excel - format & extension don't match

Question

I have a function that converts a HTML table into an Excel document. However upon the opening of the file, I receive the following message from Excel:

The file format and extension of 'something.xls' don't match. Etc etc...

Below is the function I am for the export, it is a slightly edited version of @SamPopes answer from this thread. The obj parameter is a table element I created using document.createElement('table');.

Is there any way to prevent this message upon opening the file?

function export(obj) {
  var tab_text="<table border='2px'><tr>";
  var textRange; var j=0;
  tab = obj; //Table


  for(j = 0 ; j < tab.rows.length ; j++){     
    tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
    //tab_text=tab_text+"</tr>";
  }

  tab_text=tab_text+"</table>";
  tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if you want links in your table
  tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if you want images in your table
  tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // removes input params

  var ua = window.navigator.userAgent;
  var msie = ua.indexOf("MSIE "); 

  if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
    {
      txtArea1.document.open("txt/html","replace");
      txtArea1.document.write(tab_text);
      txtArea1.document.close();
      txtArea1.focus(); 
      sa=txtArea1.document.execCommand("SaveAs",true,"export.xls");
  } else { //other browser not tested on IE 11
      sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));            
      return (sa);
  }
}

Is everything coming out like you want it to, but you just want to stop the message? — Branden Keck, Dec 18 '15 at 12:11
Sorry, I don't have a simple answer. When I export using js, I always create a CSV file as demonstrated here: http://stackoverflow.com/questions/14964035/how-to-export-javascript-array-info-to-csv-on-client-side ... I never get the error. I hope somebody can answer this question though, I'd be interested to see how excel can be manipulated from JS — Branden Keck, Dec 18 '15 at 12:26
Thanks for your reply. I may try exporting the data as a csv, but I couldn't find a cross browser method that'd support IE8+. Is there any way to format the CSS data so that table headers would appear in bold, for instance? — Mike Resoli, Dec 18 '15 at 12:40
Sorry, I do not know how to do CSS formatting, but I will post my solution to the cross browser problem as an answer... — Branden Keck, Dec 18 '15 at 16:28
My apologies, thanks for the answer but it was a typo, I meant to write CSV not CSS. — Mike Resoli, Dec 18 '15 at 16:44

score 2 · Accepted Answer · answered Dec 18 '15 at 16:37

As I said in my comment above, here is the code for creating a CSV file. This works to avoid the error message you are receiving, but will eliminate any chance you have for formatting your data (As far as I know). Here is my code, which uses one process for IE and a second for all other browsers:

function exportTotalDataFile(){
    var dataText = "";    
    var rowText = "";

    'allData is generated through an AJAX call that creates a JSON'
    'this loop creates the header row'
    for (var index in allData[0]) {
        rowText += index + ',';
    }

    'takes the trailing comma out, adds new row'
    rowText = rowText.slice(0, -1);
    dataText += rowText + '\r\n';

    'Produces the data rows'
    for (var i = 0; i < allData.length; i++) {
        var rowText = "";

        for (var index in allData[i]) {
            rowText += '"' + allData[i][index] + '",';
        }

        rowText.slice(0, rowText.length - 1);
        dataText += rowText + '\r\n';
    }

    var fileName = "Losses Report"; 

    'Internet Explorer logic'
    var isIE = false || !!document.documentMode;
    if (isIE){
        var IEwindow = window.open();
        IEwindow.document.write('sep=,\r\n' + dataText);
        IEwindow.document.close();
        IEwindow.document.execCommand('SaveAs', true, fileName + ".csv");
        IEwindow.close();
    } 
    'All the other browsers'
    else {
        var uri = 'data:text/csv;charset=utf-8,' + escape(dataText);
        var link = document.createElement("a");  
        link.href = uri;
        link.style = "visibility:hidden";
        link.download = fileName + ".csv";
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }

}

Sorry that this method is limited in the formatting department, but I hope this helps

HTML table to Excel - format & extension don't match

1 Answers1