+1 vote
7.1k views
in Programming by (630 points)
retagged by

Hi All, I have a web page in which data comes from database dynamically , Now in UI the data is in the form of HTML like: <table><tr><td>data</td></tr></table>.

In this data have several columns, so i want a link by which user can download data in the excel format. Thanks for help!

closed

2 Answers

+3 votes
by (1.8k points)
selected by
 
Best answer

Its very easy , you can use java script for exporting data from web page in excel. I would like to share a example see : 

<html>
<head>
</head>
<script>
$(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('"', '""'); // 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'), 'Reports.xls']);  //Change name of excel as per your need
        
        // IF CSV, don't do event.preventDefault() or return false
        // We actually need this to be a typical hyperlink
    });
});
</script>
<style>
a.export, a.export:visited {
   color: #F50808;
/* background-color: #ddd; */
/* border: 1px solid #ccc; */
padding: 8px;
margin: 6px;
}</style>
<div id='dvData'><table style='float:left;' border='1'>
<tr>
<td>SrNo.</td>
<td>Test</td>
<td>Test0</td>
<td>Test1</td>
<td>Test2</td>
<td>Test3</td>
<td>Test4</td>
                                <td>Test5</td>
<td>Test6</td></tr></table></div>
 
<a href="#" class="export">Export Table data into Excel</a>
      
</html>
When you would save this script as html you would find a link and some heading in a table and after clicking in that link data will be exported in the excel (Reports.xls) you can change the name of Excel as per your need.
 
This is the link for export :
 
<a href="#" class="export">Export Table data into Excel</a>
 
From here you can change the name of excel which would be exported :
 
 exportTableToCSV.apply(this, [$('#dvData>table'), 'Reports.xls']);  //Change name of excel as per your need
Try this and let me know if you have any query.
by (630 points)
This script is cool, this is what I actually need.
Thanks Payel for help!
by (1.3k points)
Script is working fine , it helped me also +1 from my side :)
by
edited by
why not to use simple things
<input type="button" id="btnExport" value=" Export Table data into Excel " />
<br/>
<br/>
<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><a href="jquery2dotnet.com/">jquery2dotnet.com/</a>
            </td>
        </tr>
    </table>
</div>
<style type="text/css">
body {
    font-size: 12pt;
    font-family: Calibri;
    padding : 10px;
}
table {
    border: 1px solid black;
}
th {
    border: 1px solid black;
    padding: 5px;
    background-color:grey;
    color: white;
}
td {
    border: 1px solid black;
    padding: 5px;
}
input {
    font-size: 12pt;
    font-family: Calibri;
}
</style>
<script type="text/javascript">
$("#btnExport").click(function (e) {
    window.open('data:application/vnd.ms-excel,' + $('#dvData').html());
    e.preventDefault();
});
</script>
by (2.6k points)
@Aishan I have tried with your code , somehow its not working is there anything i am missing . I have just copied paste your code into a html file and show in browser but table was not exporting.
While above answer is working fine.
by
@bhartesh, you must also include a jquery library
include this script also.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"
        type="text/javascript"></script>
at head.
by (2.6k points)
Ok let me try this way also , anyways thanks for your help its also a nice option.
by (1.6k points)
don't download from the HTML but from the database dynamically. And use PHPExcel to create the xls.
by Expert (5.9k points)
There are many third party classes available to expor data into xls files. Google for php excel reader and writer classes. I would prefer to oop classes as they give support of error handling and more secure way of using excel operations.
0 votes
by
Here you go - it's a very simple concept and Excel seems to handle it fine, even occasionally with some very basic formatting. I know it's not the best way (and the best way would probably be to use a PHP / Excel library), but it's a really easy way.
<?php
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=demo.xls");
?>
<table  ><tr><td>Cell 1</td><td>Cell 2</td></tr></table>

Not a Member yet?

Ask to Folks Login

My Account

Your feedback is highly appreciated