Javascript - Export to csv

Asked

Viewed 4,663 times

2

How do I export an html table to a CSV file?

In my code below I am saving the csv file, but when opening in excel does not present the column divisions.

I would like to export directly to CSV. But I can’t find any way to do that for Chrome and IE.

Follows my code:

`<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Export table to Excel in 10 lines</title>`


`<script>`

`function Exportar(){
    var textRange; var j=0;
    var tab = document.getElementById("Tabela"); // id da tabela
    if(document.getElementById("Tabela").rows.length < 2){
        alert("Por favor, realizar uma pesquisa antes de exportar.");
    }
    else{
        var a=[], csv='', LF='\r\n', r, c, rs, cs, row, cell, i, j, v;
        for (r=0; r<tab.rows.length; r++){
            row = tab.rows[r];`

            for (c=0; c<row.cells.length; c++){
                cell = row.cells[c];
                rs = cell.rowSpan+r;
                cs = cell.colSpan+c;

                for (i=r; i<rs; i++){
                    if (!a[i]){
                        a[i]=[];
                    }

                    for (j=c; j<cs; j++){
                        a[i].push(i>r || j>c ? '' : cell.innerHTML);
                    }
                }
            }
        }



        var ua = window.navigator.userAgent;
        var msie = ua.indexOf("MSIE "); 

        if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // Se for Internet Explorer
        {
            for (r=0; r<a.length; r++){
                v = '';
                for (c=0; c<a[r].length; c++){
                    csv += (v + a[r][c]);
                    v = ';';
                }
                csv += '\r\n';
            }

            txtArea1.document.open("txt/html","replace");
            txtArea1.document.write(csv);
            txtArea1.document.close();
            txtArea1.focus(); 
            sa = txtArea1.document.execCommand("SaveAs",true,"Socilitações.csv");   

        }  
        else
            for (r=0; r<a.length; r++){
                v = '';
                for (c=0; c<a[r].length; c++){
                    csv += (v + a[r][c]);
                    v = ';';
                }
                csv += "%0A";
            }   

            var a         = document.createElement('a');
            a.href        = 'data:attachment/csv,' + csv;
            a.target      = '_blank';
            a.download    = 'Solicitações.csv';

            document.body.appendChild(a);
            a.click();
            //sa = window.open('data:attachment/csv,' + encodeURIComponent(csv));  

        return (sa);
    }}


`</script>`
`</head>`

`<body>
    <button onclick="Exportar()">Exportar</button>
    <br/>
    <span>
    <table id="Tabela">
        <tr>
            <td>1</td>
            <td>2</td>
        </tr>
        <tr>
            <td>3</td>
            <td>4</td>
        </tr>
        <tr>
            <td>5</td>
            <td>6</td>
        </tr>
    </table>
    <iframe id="txtArea1" style="display:none"></iframe>
    </span>
</body>
</html>`

Can you help me?

Thank you.

javascript csv

  • do you want a solution that uses JAVASCRIPT ONLY? a solution where Javascript requests a server side component so that it generates its CSV file and makes it available for download would solve its problem?

  • Excel does properly open the columns of a CSV provided that you select the correct opening options and that the file is valid. Have you checked these two situations?

  • Marcelo, I am open to any type of solution that exports my html table to a csv file. Previously I had done in java a function that generates the csv file, but I could not download it.

  • Vinícius, when saving the IE file gives only options to save to HTML or TXT. I am saving the file with the CSV extension of type TXT or HTML. When I open the file excel does not separate the columns in cells.

  • I’m posting again a code that worked for Chrome. In Chrome when I download CVS opens correctly in excel. But in IE when I download it does not divide the columns by ";". The file in excel gets disfigured.

1 answer

1


Save the code below with this name jquery.battatech.excelexport.js

/*
 * jQuery Client Side Excel Export Plugin Library
 * http://www.battatech.com/
 *
 * Copyright (c) 2013 Batta Tech Private Limited
 * Licensed under https://github.com/battatech/battatech_excelexport/blob/master/LICENSE.txt
 */

(function ($) {
    var $defaults = {
        containerid: null
        , datatype: 'table'
        , dataset: null
        , columns: null
        , returnUri: false
        , worksheetName: "My Worksheet"
        , encoding: "utf-8"
    };

    var $settings = $defaults;

    $.fn.battatech_excelexport = function (options) {
        $settings = $.extend({}, $defaults, options);

        var gridData = [];
        var excelData;

        return Initialize();

        function Initialize() {
            var type = $settings.datatype.toLowerCase();

            BuildDataStructure(type);

            switch (type) {
                case 'table':
                    excelData = Export(ConvertFromTable());
                    break;
                case 'json':
                    excelData = Export(ConvertDataStructureToTable());
                    break;
                case 'xml':
                    excelData = Export(ConvertDataStructureToTable());
                    break;
                case 'jqgrid':
                    excelData = Export(ConvertDataStructureToTable());
                    break;
            }

            if ($settings.returnUri) {
                return excelData;
            }
            else {
                window.open(excelData);
            }
        }

        function BuildDataStructure(type) {
            switch (type) {
                case 'table':
                    break;
                case 'json':
                    gridData = $settings.dataset;
                    break;
                case 'xml':
                    $($settings.dataset).find("row").each(function (key, value) {
                        var item = {};

                        if (this.attributes != null && this.attributes.length > 0) {
                            $(this.attributes).each(function () {
                                item[this.name] = this.value;
                            });

                            gridData.push(item);
                        }
                    });
                    break;
                case 'jqgrid':
                    $($settings.dataset).find("rows > row").each(function (key, value) {
                        var item = {};

                        if (this.children != null && this.children.length > 0) {
                            $(this.children).each(function () {
                                item[this.tagName] = $(this).text();
                            });

                            gridData.push(item);
                        }
                    });
                    break;
            }
        }

        function ConvertFromTable() {
            var result = $('<div>').append($('#' + $settings.containerid).clone()).html();
            return result;
        }

        function ConvertDataStructureToTable() {
            var result = "<table>";

            result += "<thead><tr>";
            $($settings.columns).each(function (key, value) {
                if (this.ishidden != true) {
                    result += "<th";
                    if (this.width != null) {
                        result += " style='width: " + this.width + "'";
                    }
                    result += ">";
                    result += this.headertext;
                    result += "</th>";
                }
            });
            result += "</tr></thead>";

            result += "<tbody>";
            $(gridData).each(function (key, value) {
                result += "<tr>";
                $($settings.columns).each(function (k, v) {
                    if (value.hasOwnProperty(this.datafield)) {
                        if (this.ishidden != true) {
                            result += "<td";
                            if (this.width != null) {
                                result += " style='width: " + this.width + "'";
                            }
                            result += ">";
                            result += value[this.datafield];
                            result += "</td>";
                        }
                    }
                });
                result += "</tr>";
            });
            result += "</tbody>";

            result += "</table>";
            return result;
        }

        function Export(htmltable) {
            var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
            excelFile += "<head>";
            excelFile += '<meta http-equiv="Content-type" content="text/html;charset=' + $defaults.encoding + '" />';
            excelFile += "<!--[if gte mso 9]>";
            excelFile += "<xml>";
            excelFile += "<x:ExcelWorkbook>";
            excelFile += "<x:ExcelWorksheets>";
            excelFile += "<x:ExcelWorksheet>";
            excelFile += "<x:Name>";
            excelFile += "{worksheet}";
            excelFile += "</x:Name>";
            excelFile += "<x:WorksheetOptions>";
            excelFile += "<x:DisplayGridlines/>";
            excelFile += "</x:WorksheetOptions>";
            excelFile += "</x:ExcelWorksheet>";
            excelFile += "</x:ExcelWorksheets>";
            excelFile += "</x:ExcelWorkbook>";
            excelFile += "</xml>";
            excelFile += "<![endif]-->";
            excelFile += "</head>";
            excelFile += "<body>";
            excelFile += htmltable.replace(/"/g, '\'');
            excelFile += "</body>";
            excelFile += "</html>";

            var uri = "data:application/vnd.ms-excel;base64,";
            var ctx = { worksheet: $settings.worksheetName, table: htmltable };

            return (uri + base64(format(excelFile, ctx)));
        }

        function base64(s) {
            return window.btoa(unescape(encodeURIComponent(s)));
        }

        function format(s, c) {
            return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; });
        }
    };
})(jQuery);

Full and functional example:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
    <script type="text/javascript" src="bower_components/jquery/dist/jquery.js"></script>
    <script type="text/javascript" src="Scripts/jquery.battatech.excelexport.js"></script>
</head>
<body>
    <div id="dv">
        <table id="tblExport" style="border: 1px solid black;">
            <thead>
                <tr>
                    <th>#</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Username</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td style='background-color: red;'>1</td>
                    <td>Mark</td>
                    <td>Otto</td>
                    <td>@mdo</td>
                </tr>
                <tr>
                    <td>2</td>
                    <td>Jacob</td>
                    <td>Thornton</td>
                    <td>@fat</td>
                </tr>
                <tr>
                    <td>3</td>
                    <td>Larry</td>
                    <td>the Bird</td>
                    <td>@twitter</td>
                </tr>
            </tbody>
        </table>
    </div>
    <div>
        <button id="btnExport">Export</button>
    </div>
</body>
</html>
<script type="text/javascript">
    $(document).ready(function () {
        $("#btnExport").click(function () {
            $("#tblExport").battatech_excelexport({
                containerid: "tblExport"
               , datatype: 'table'
            });
        });
    });
</script>

  • Look, it worked almost perfectly here, however it flashes on the screen, has how to fix it? or is it the same plugin?

  • No talk that Chrome lowers table html and not csv

Browser other questions tagged javascript csv

You are not signed in. Login or sign up in order to post.