Html Export to Excel - Browser directly saves the excel, cannot open in "View" mode, using IE9

Question

I use IE9 to export an html table to excel, i have used the following js code to export my table which works fine, but the problem i face is,

when the export icon is clicked, the browser directly shows a saveAs option, which forces the user to save the excel before opening it, it doesn't allow to open the excel in view.

My js function :

function ExcelReport() {
var tab_text = "<table border='2px'><tr border='1px'>";
var tabcol = [];
var j = 0;
var i=0;
var temp;

tab = document.getElementById('myTable'); // id of table
var col = tab.rows[1].cells.length;

tab_text = tab_text + tab.rows[0].innerHTML + "</tr><tr>"; // table title row[0]

for (j = 1; j < tab.rows.length; j++) {
    for(i=0;i<col;i++){
        if(j==1){  // table header row[1]
                tabcol = tabcol + "<td bgcolor='#C6D7EC'>" + tab.rows[j].cells[i].innerHTML + "</td>";
            }else{
                tabcol = tabcol + "<td>" + tab.rows[j].cells[i].innerHTML + "</td>"; 
            }

    }
    if(j==1){  
        temp =  tabcol + "</tr>";
    }else{
        temp =  temp + tabcol + "</tr>";
    }
    tabcol = [];
}

tab_text = tab_text + temp + "</table>";

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,"MyExcelReport.xls");

} else
    //other browser not tested on IE 11
    sa = window.open('data:application/vnd.ms-excel,'+ encodeURIComponent(tab_text));

return (sa);
}

When export icon is clicked, it shows this dialog box:

What i need is :

Can anyone please help me in getting the above dialog box from browser. Really appreciate your time and help.

In IE 9 the Save dialog is showing because of the following code" `txtArea1.document.execCommand("saveAs"`. Unfortunately, I could not find a way to show the open dialog using 100% client-side data export approach. — Sunil, Apr 20 '16 at 16:58
If you have to show an Open dialog, you should not use 100% client-side approach for data export but a server-side approach in IE 9. — Sunil, Apr 20 '16 at 17:27
@Sunil Oh Thanks! Can you please provide some example or any links for that, using Server-side approach in IE9? It will be very useful.. — Disera, Apr 21 '16 at 06:10
You have to come up with a server-side method based on what platform you have in your back-end that will generate an Excel file. Then simply call the server-side method using jQuery ajax call and post the excel data to it. This method will create the Excel file for you on server-side and the return value from this method will be the url to this generated file. Then in success function of above jQuery ajax call, you can can download this file using the following stackoveflow post: http://stackoverflow.com/questions/3749231/download-file-using-javascript-jquery — Sunil, Apr 21 '16 at 17:09
i use java servlet as back-end, and also will this approach be useful for exporting bulk table data and html tables with pagination?Thanks in Advance.. — Disera, Apr 22 '16 at 10:05
I have posted an answer. Since you are using Java as your back-end, I omitted my back-end web service code which was in C#, but the jQuery code will be the same no matter what back-end platform you use. — Sunil, Apr 22 '16 at 15:20
If you want to do bulk export i.e. export all pages, then you have to do an extra task of getting the data for all pages. Then from this data you need to create the `html` string for export. So in the code in my answer, you will need to set the variable `exportString` using above mentioned logic. You can make a jQuery ajax call for this and on the success function of this call you call `ExportToExcel` method. — Sunil, Apr 22 '16 at 19:32

Sunil · Accepted Answer · 2016-04-22T19:35:51.737

Since you are using Java as your back-end, you will need to create a Java Web Service in your back-end or come up with appropriate servlet; either way you will need to change the url parameter in jquery ajax call in code below.

I have tested the jQuery code below in ASP.Net and IE 9, in which it did popup a dialog to Open or Save the downloaded file. So this should meet your requirements.

You can use code like below, in which a html string and file name of exported file are being posted to back-end.

The back-end servlet or web service should have a method that will take these two parameters to create a file with a unique name under some folder in back-end and return the full absolute URL of the file created.
In code sample below, this method is a web service method called DownloadFile.
When this call to back-end returns, you will have the URL to exported file which can be easily downloaded by setting window's href to this URL.
Also, keep in mind that even though the fileName is being passed as a parameter to back-end, you need to make sure that it's converted to a unique file name. This is needed else different users might end up over-writing each other's files.
For example, if exportExcel.xls is passed to back-end then you could append a GUID string to file name so the file name becomes: excelExport_bb1bf56eec4e4bc8b874042d1b5bd7da.xls. This will make the file name always unique.

jQuery code to Export to Excel by posting to back-end

    function ExportToExcel() {

    //create the html to be exported to Excel in any custom manner you like
    //here it's simply being set to some html string  
    var exportString = "<html><head><style>
    table, td {border:thin solid black} table {border-collapse:collapse}
    </style></head><body><table><tr><td>Product</td><td>Customer</td></tr>
    <tr><td>Product1</td><td>Customer1</td></tr><tr><td>Product2</td><td>Customer2</td>
    </tr><tr><td>Product3</td><td>Customer3</td></tr><tr><td>Product4</td>
    <td>Customer4</td></tr></table></body></html>";

    //set the file name with or without extension
    var fileName = "excelExport.xls";
    var exportedFile = { filePath: "", deleteFileTimer: null };

    //make the ajax call to create the Excel file
    $.ajax({
        url: "http://localhost/disera/ExportWebService/DownloadFile",
        type: "POST",
        data: JSON.stringify({ htmlString: exportString, fileName: fileName }),
        contentType: "application/json",
        async: true,
        success: function (data) {
            window.location.href = data.d;
            var exportedFile = { filePath: data.d, deleteFileTimer: null };

            //the line of code below is not necessary and you can remove it
            //it's being used to delete the exported file after it's been served
            //NOTE: you can use some other strategy for deleting exported files or
            //choose to not delete them
            DeleteFile(exportedFile);
        },
        error: function (xhr, ajaxOptions, thrownError) {
            alert("Following error occurred when exporting data to '" +
                    exportedFile.filePath + "' : " + thrownError);
        }
    });

}

function DeleteFile(exportedFile) {
    exportedFile.deleteFileTimer = setInterval(function () {
        $.ajax({
            url: "http://localhost/disera/ExportWebService/DeleteFile",
            type: "POST",
            data: JSON.stringify({ filePath: exportedFile.filePath }),
            contentType: "application/json",
            async: true,
            success: function (data) {
                if (data.d === true) {
                    clearInterval(exportedFile.deleteFileTimer);
                    exportedFile.deleteFileTimer = null;
                    exportedFile.filePath = null;
                    exportedFile = null;
                }
            },
            error: function (xhr, ajaxOptions, thrownError) {
                // alert("Following error occurred when deleting the exported file '" +
                // exportedFile.filePath + "' : " + thrownError);
            }
        });
    }, 30000)
}

Also, keep in mind that you will not need to use `data.d` in success function but based on the development platform you are using, you will need to most likely just use `data`. `data.d` is specific to AJAX-enabled web service in ASP.Net. — Sunil, Apr 26 '16 at 15:02

Html Export to Excel - Browser directly saves the excel, cannot open in "View" mode, using IE9

1 Answers1

Linked