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
– Girish Sakhare, Commented Nov 23, 2015 at 6: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. jwgoerlich.us/blogengine/post/2009/08/11/… If you want to avoid that prompt you'll need to send the data differently. — Tim Williams
– Tim Williams, Commented Nov 23, 2015 at 7:11
Possible duplicate of Can not export html table to excel using jQuery — Leyon Gudinho
– Leyon Gudinho, Commented Nov 23, 2015 at 8:44

Gyan Chandra Srivastava · Accepted Answer · 2015-11-23 07:00:15Z

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
});

});

Leyon Gudinho · Accepted Answer · 2015-11-23 07:33:54Z

1

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

answered Nov 23, 2015 at 7:33

Leyon Gudinho

912 silver badges11 bronze badges

1 Comment

Arpan Paliwal Over a year ago

This is also not working its showing error. "Excel can not open the file because the file format or the extension is not valid"

Ray · Accepted Answer · 2015-11-23 07:07:23Z

0

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>

answered Nov 23, 2015 at 7:07

Ray

7774 silver badges11 bronze badges

2 Comments

Arpan Paliwal Over a year ago

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 Over a year ago

Its also showing same error also its showing HTML in the excel.

Anbu · Accepted Answer · 2015-12-30 11:24:29Z

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, 2015 at 11:24

Anbu

5308 silver badges21 bronze badges

Collectives™ on Stack Overflow

Format error when exporting HTML table to Excel

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related