Export to CSV using jQuery and HTML

Question

Welcome To Ask or Share your Answers For Others

Export to CSV using jQuery and HTML

asked Oct 17, 2021 in Technique[技术] by 深蓝 (71.8m points)

Export to CSV using jQuery and HTML

I have a tabular data which I need to export to csv without using any external plugin or API. I used the window.open method passing the MIME types but faced issues like below:

How to determine whether Microsoft Excel or OpenOffice is installed on the system using jQuery

The code should be independent of the fact that what is being installed on the system, i.e. OpenOffice or Microsoft Excel. I believe CSV is the format which can be expected to show in both the editors.

CODE

    <html>

<head>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>  

<script type="text/JavaScript">
$(document).ready(function(){
    $("#btnExport").click(function(e) {
        var msg = GetMimeTypes();
        //OpenOffice
        window.open('data:application/vnd.oasis.opendocument.spreadsheet,' + $('#dvData').html());
        //MS-Excel
        window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
        //CSV
        window.open('data:application/csv,charset=utf-8,' + $('#dvData').html());
        e.preventDefault();
    });
});

function GetMimeTypes () {
    var message = "";
        // Internet Explorer supports the mimeTypes collection, but it is always empty
    if (navigator.mimeTypes && navigator.mimeTypes.length > 0) {
        var mimes = navigator.mimeTypes;
        for (var i=0; i < mimes.length; i++) {
            message += "<b>" + mimes[i].type + "</b> : " + mimes[i].description + "<br />";
        }
    }
    else {
        message = "Your browser does not support this ";
       //sorry!
    }

    return ( message);
}
</script>

</head>
<body>
<div id="dvData">
<table>
    <tr>
        <th>Column One </th>
        <th>Column Two</th>
        <th>Column Three</th>
    </tr>
    <tr>
        <td>row1 Col1</td>
        <td>row1 Col2</td>
        <td>row1 Col3</td>
   </tr>
   <tr>
        <td>row2 Col1</td>
        <td>row2 Col2</td>
        <td>row2 Col3</td>
   </tr>
      <tr>
        <td>row3 Col1</td>
        <td>row3 Col2</td>
        <td>row3 Col3</td>  
   </tr>
</table>
</div>
<br/>
<input type="button" id="btnExport" value=" Export Table data into Excel " />

</body>

Errors

CSV: Unrecognised over the browsers

ODS & Excel: is working but I am not able to find which one to generate when system is having an excel installed or openoffice installed?

IE version 8 : it is totally not working, opens in a new window and as below screenshot.

enter image description here

Question&Answers:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Answer

深蓝 · Answer 1 · 2021-10-16T21:23:26+0000

Demo

See below for an explanation.

$(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 = '"
"',

      // 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) + '"';

    // Deliberate 'false', see comment below
    if (false && window.navigator.msSaveBlob) {

      var blob = new Blob([decodeURIComponent(csv)], {
        type: 'text/csv;charset=utf8'
      });

      // Crashes in IE 10, IE 11 and Microsoft Edge
      // See MS Edge Issue #10396033
      // Hence, the deliberate 'false'
      // This is here just for completeness
      // Remove the 'false' at your own risk
      window.navigator.msSaveBlob(blob, filename);

    } else if (window.Blob && window.URL) {
      // HTML5 Blob        
      var blob = new Blob([csv], {
        type: 'text/csv;charset=utf-8'
      });
      var csvUrl = URL.createObjectURL(blob);

      $(this)
        .attr({
          'download': filename,
          'href': csvUrl
        });
    } else {
      // Data URI
      var 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
    var args = [$('#dvData>table'), 'export.csv'];

    exportTableToCSV.apply(this, args);

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

a.export,
a.export:visited {
  display: inline-block;
  text-decoration: none;
  color: #000;
  background-color: #ddd;
  border: 1px solid #ccc;
  padding: 8px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<a href="#" class="export">Export Table data into Excel</a>
<div id="dvData">
  <table>
    <tr>
      <th>Column One</th>
      <th>Column Two</th>
      <th>Column Three</th>
    </tr>
    <tr>
      <td>row1 Col1</td>
      <td>row1 Col2</td>
      <td>row1 Col3</td>
    </tr>
    <tr>
      <td>row2 Col1</td>
      <td>row2 Col2</td>
      <td>row2 Col3</td>
    </tr>
    <tr>
      <td>row3 Col1</td>
      <td>row3 Col2</td>
      <td>row3 Col3</td>
    </tr>
    <tr>
      <td>row4 'Col1'</td>
      <td>row4 'Col2'</td>
      <td>row4 'Col3'</td>
    </tr>
    <tr>
      <td>row5 &quot;Col1&quot;</td>
      <td>row5 &quot;Col2&quot;</td>
      <td>row5 &quot;Col3&quot;</td>
    </tr>
    <tr>
      <td>row6 "Col1"</td>
      <td>row6 "Col2"</td>
      <td>row6 "Col3"</td>
    </tr>
  </table>
</div>

Categories

Export to CSV using jQuery and HTML

Export to CSV using jQuery and HTML

CODE

Errors

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Demo

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags