January 18, 2015 3151 Views 0 Comment

Exporting HTML / PHP Data to Excel

SHARE THIS ARTICLE

Downloading data from PHP or HTML into an Excel spreadsheet is quite important for most web projects but can be a little tricky. This article provides 3 techniques for parsing html/php data to excel(csv). The first method involves Exporting to CSV using jQuery and html, the second introduces a php library for parsing Microsoft Excel XLSX and the third is a simple PHP function that makes use of the HTTP Headers.

1. Export to CSV using jQuery and html

This method is by far the easiest but only works for embedded html tables. The first step is to convert the table contents into a valid CSV formatted string using jquery. After that, the next challenge is how to download it. With the anchor tag, we have the option to assign a file name using the download attribute, which is currently only feasible in Firefox and Google Chrome.


$(document).ready(function () {
    function exportTableToCSV($table, filename) {
        
        var $rows = $table.find('tr:has(td)'),
        
        // Convert a Unicode number into a character:
        tmpColDelim = String.fromCharCode(11), // vertical tab character
        tmpRowDelim = String.fromCharCode(0), // null character

        // 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) {
        exportTableToCSV.apply(this, [$('#tableID'), 'export.csv']);
    });
});

DEMODOWNLOAD

2. Export to CSV using SimpleExcel

SimpleExcel is a lightweight PHP library with simplistic approach for parsing/converting/writing tabular data from/to Microsoft Excel XML/CSV/TSV/HTML/JSON format
Library and example usage guides can be found at: SimpleExcel Gitub

3. Export to CSV using PHP and HTML Headers

This method applies the php implode function to an array. The html headers are then modified to allow the excel download.


$data = array(
    array("Fruit" => "Apple", "Benefits" => "A, B"),
    array("Fruit" => "Banana", "Benefits" => "C,8"),
    array("Fruit" => "Mango", "Benefits" => "D,B")
  );

// download filename 
$filename = "csvfile" . ".xls";

header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");

$isHeaderRow = true;
foreach($data as $row) {
    if($isHeaderRow) {
        // display first row field names
        echo implode("\t", array_keys($row)) . "\r\n";
        $isHeaderRow = false;
    }
    echo implode("\t", array_values($row)) . "\r\n";
}

DEMODOWNLOAD