Hey there! any question in your mind? Ask It Now!.

Popular Categories





Export HTML Table Data to Excel in PHP.

+1 vote
4,750 views
asked in Programming by ramesh (630 points)
retagged by Bhartesh

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!

2 Answers

+3 votes
answered by payel (1,640 points)
selected by ramesh
 
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.
commented by Himanshu Expert (5,940 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.
commented by Tomasz ┼╗ubertowski
If it is your application that prints out the table - it is your application that builds it from the data directly. Meaning you have the access to raw data. If you want to make it reusable you can create a simple model, transformer, helper or parser (depending on your need) class that will be able to output an abstract set data (object, list, even associative array).
Basically, you would set up two endpoints, one for the HTML table generation and the second one for CSV generation - both using the previously mentioned class to prepare the data.
Doing it this way instead of using JS as in the answer from this post has a lot of benefits:
- browsers interpret JS differently, using backend for CSV generation means consistency
- any form of data processing should be contained inside the backend, not the frontend. Working with data often requires updates to the logic, with one codebase for just that it is easy to maintain
commented by Don Reese
I don't see where you are scanning the data to ensure that it is CSV complaint.  Too many web sites out there allow people to paste data from Microsoft Office products, introducing 'special' character combinations for may normal symbols.  These also show up as garbage characters when shown on those web sites but are in many cases just ignored.
https://www.ietf.org/rfc/rfc4180.txt
NOTE: you also are not allowing for the table cell to have a new line in it.  That will break your CSV exported data.  You need to include the conversion called out in the RFC.
0 votes
answered by anonymous
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>

Related Questions

Not a Member yet?

Ask to Folks Login

My Account
159 Folks are online
0 members and 159 guest online
Your feedback is highly appreciated