Format error when exporting HTML table to Excel

Question

I am exporting a HTML table to Excel but it is giving me error like:

"The file you are trying to open is in the different format than the specified by the file extension..."

I tried different extension like .xlsx also still giving same error. Below is my jQuery code:

$(document).ready(function() {
      $("#btnExport").click(function(e) {
        e.preventDefault();

        //getting data from our table
        var data_type = 'data:application/vnd.ms-excel';
        var table_div = document.getElementById('table_wrapper');
        var table_html = table_div.outerHTML.replace(/ /g, '%20');

        var a = document.createElement('a');
        a.href = data_type + ', ' + table_html;
        a.download = 'DSR_Report_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
        a.click();
      });
    });

And the HTML table I am exporting to Excel:

<div id="table_wrapper">
    <table border="1" id="list" class="table-style-two">
        <tbody>
            <tr>
                <th>NAME</th>
                <th>PROJECT_NAME</th>
                <th>DESCRIPTION</th>
                <th>HOURS</th>
                <th>START_DATE</th>
                <th>CURRENT_PROJECT_STATUS</th>
                <th>WORK_FOR_TOMORROW</th>
                <th>TOMORROW_WORK_STATUS</th>
                <th>COMMENTS</th>
                <th>VIEW_POINT_TICKET</th>
                <th>DSR_CURRENT_DATE</th>
            </tr>
            <tr id="417">
                <td>Anupam Bhattacharjee</td>
                <td>Cybage</td>
                <td>Daily standup meeting</td>
                <td>0.5</td>
                <td>2015-11-20</td>
                <td>Complete</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>2015-11-20</td>
            </tr>
            <tr id="418">
                <td>Anupam Bhattacharjee</td>
                <td>Tomford</td>
                <td>TF3799-28303 - Assisted Pavan in resolving the issue via skype.Fixed issue in Tomford mule where the OrderShipment was erring out.</td>
                <td>1.5</td>
                <td>2015-11-19</td>
                <td>Complete</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>WO3799</td>
                <td>2015-11-20</td>
            </tr>

        </tbody>
    </table>
</div>

EDIT : It is not duplicate of this question as in that question user is not able to export but i am able to export and my exported excel file is having issue.

You can export to CSV file by using linqToCSV and its very fast — Girish Sakhare, Nov 23 '15 at 06:59
An HTML table sent with an Excel content-type doesn't fool Excel: it's telling you that the content is not an actual Excel file and asking you to confirm that you still want to open it. http://www.jwgoerlich.us/blogengine/post/2009/08/11/Excel-Extension-Hardening-and-Web-Applications.aspx If you want to avoid that prompt you'll need to send the data differently. — Tim Williams, Nov 23 '15 at 07:11
Possible duplicate of [Can not export html table to excel using jQuery](http://stackoverflow.com/questions/17627138/can-not-export-html-table-to-excel-using-jquery) — Leyon Gudinho, Nov 23 '15 at 08:44

score 1 · Answer 1 · answered Nov 23 '15 at 07:00

Check here might this will help you out

$(document).ready(function () {

function exportTableToCSV($table, filename) {

    var $rows = $table.find('tr:has(td)'),

        // Temporary delimiter characters unlikely to be typed by keyboard
        // This is to avoid accidentally splitting the actual contents
        tmpColDelim = String.fromCharCode(11), // vertical tab character
        tmpRowDelim = String.fromCharCode(0), // null character

        // actual delimiter characters for CSV format
        colDelim = '","',
        rowDelim = '"\r\n"',

        // Grab text from table into CSV formatted string
        csv = '"' + $rows.map(function (i, row) {
            var $row = $(row),
                $cols = $row.find('td');

            return $cols.map(function (j, col) {
                var $col = $(col),
                    text = $col.text();

                return text.replace(/"/g, '""'); // escape double quotes

            }).get().join(tmpColDelim);

        }).get().join(tmpRowDelim)
            .split(tmpRowDelim).join(rowDelim)
            .split(tmpColDelim).join(colDelim) + '"',

        // Data URI
        csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

    $(this)
        .attr({
        'download': filename,
            'href': csvData,
            'target': '_blank'
    });
}

// This must be a hyperlink
$(".export").on('click', function (event) {
    // CSV
    exportTableToCSV.apply(this, [$('#dvData>table'), 'export.csv']);

    // IF CSV, don't do event.preventDefault() or return false
    // We actually need this to be a typical hyperlink
});

});

It is generating CSV but i want it in excel. – Arpan Paliwal Nov 23 '15 at 07:25 — Arpan Paliwal, Nov 23 '15 at 07:25

score 1 · Answer 2 · answered Nov 23 '15 at 07:33

You can try this plugin - tableExport.js

HTML:

 <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
    <script src="src/jquery.table2excel.js"></script>
<body>
<tr class="noExl">
  <th>#</th>
  <th>Column heading</th>
  <th>Column heading</th>
  <th>Column heading</th>
</tr>
</body>

jQuery:

$("button").click(function(){
  $("#table2excel").table2excel({
    // exclude CSS class
    exclude: ".noExl",
    name: "Excel Document Name"
  });
});

Plugin download: http://www.jqueryscript.net/table/Export-Html-Table-To-Excel-Spreadsheet-using-jQuery-table2excel.html

This is also not working its showing error. "Excel can not open the file because the file format or the extension is not valid" — Arpan Paliwal, Dec 03 '15 at 09:22

score 0 · Answer 3 · answered Nov 23 '15 at 07:07

Javascript

$("#btn").click(function (e) {
    window.open('data:application/vnd.ms-excel,' + $('#tableData').html());
    return false;
});

HTML

<input type="button" id="btn" value="EXPORT" />

<div id="tableData">
   <table>
        <tbody>
            <tr>
                <th>NAME</th>
                <th>PROJECT_NAME</th>
                <th>DESCRIPTION</th>
                <th>HOURS</th>
                <th>START_DATE</th>
                <th>CURRENT_PROJECT_STATUS</th>
                <th>WORK_FOR_TOMORROW</th>
                <th>TOMORROW_WORK_STATUS</th>
                <th>COMMENTS</th>
                <th>VIEW_POINT_TICKET</th>
                <th>DSR_CURRENT_DATE</th>
            </tr>
            <tr id="417">
                <td>Anupam Bhattacharjee</td>
                <td>Cybage</td>
                <td>Daily standup meeting</td>
                <td>0.5</td>
                <td>2015-11-20</td>
                <td>Complete</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>2015-11-20</td>
            </tr>
            <tr id="418">
                <td>Anupam Bhattacharjee</td>
                <td>Tomford</td>
                <td>TF3799-28303 - Assisted Pavan in resolving the issue via skype.Fixed issue in Tomford mule where the OrderShipment was erring out.</td>
                <td>1.5</td>
                <td>2015-11-19</td>
                <td>Complete</td>
                <td>NA</td>
                <td>NA</td>
                <td>NA</td>
                <td>WO3799</td>
                <td>2015-11-20</td>
            </tr>
       </tbody>
    </table>
</div>

Its giving the same error message while opening the excel "The file you are trying to open is in the different format than the specified by the file extension..." — Arpan Paliwal, Nov 23 '15 at 07:25
Its also showing same error also its showing HTML in the excel. — Arpan Paliwal, Dec 03 '15 at 09:27

score 0 · Answer 4 · answered Dec 30 '15 at 11:24

0

The file is basically just a CSV file with an XLS extension to make it open in Excel. If you have a problem then simply rename the file name to '.csv'. This is only I fonud a good answer.Here

answered Dec 30 '15 at 11:24

Anbu

490
6
20

Format error when exporting HTML table to Excel

4 Answers4

Linked