+1 vote
8.8k 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.
0
by (2.9k points)
Ok let me try this way also , anyways thanks for your help its also a nice option.
0
by (1.6k points)
don't download from the HTML but from the database dynamically. And use PHPExcel to create the xls.
0
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