CODEWITHSUNDEEP
javascriptjqueryhtmlexcel
Disera
Disera

Reputation: 208

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

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: enter image description here

What i need is :

enter image description here

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

Upvotes: 0

Views: 2066

Answers (1)

Sunil
Sunil

Reputation: 21396

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.

  1. 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.
  2. In code sample below, this method is a web service method called DownloadFile.
  3. 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.
  4. 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.
  5. 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)
}

Upvotes: 1

Related Questions