PHP quick data export to spreadsheet

In our last post we talked about the limitations of creating large spreadsheets with PHP library PHPExcel.  Today we’ll discuss a quick workaround that will allow you to create large unformatted spreadsheets for quick and easy data export from your applications.  Here’s how we do a php quick data export to spreadsheet:

  • Properly escape your data.
  • Write data with fcsvput($filehandle, $yourdataarray, “t”);
  • Include table headings and other data.
  • Use a xls file extension.
Properly escape your data... You’ll be using a tab delimited format for output so you’ll need to replace the tabs with an escaped tab or five spaces so that your data doesn’t corrupt your output format.  For our purposes we’ll be using five spaces in the following function.
function TSV_Escape($str)
{
    if (!strlen($str) == 0)
    {
        $str=str_replace('t','     ',$str);
        if (!strstr($str,"n") === false)
        {
            $str=str_replace('n','%n',$str);
        }
        return str_replace('"','""', $str);
    }
    return '';
}

Write you data with fputcsv:  First you’ll need to open a file handle like this…

// Set file path and initialize handle.
$TSVFile = $_SERVER['DOCUMENT_ROOT'] . "/MyDataFile.xls";
$TSVFileHandle = fopen($TSVFile, 'w');
// Write your headers.. See next section :)
// Write your data.
// Write query data to csv.
if ($objdbconn->real_query($sql))
{
    if ($result = $objdbconn->store_result())
    {
        echo date('H:i:s') . "Processing " . $result->num_rows . " records.<br>\n";
        while ($row = $result->fetch_assoc())
        {
            $writeArr = array();
            foreach($row as $value)
            {
                $writeArr[] = TSV_Escape($value);
            }
            fputcsv($TSVFileHandle, $writeArr, "\t");
            unset($writeArr);
        }
    }
}
// Close your file handle.
fclose($TSVFileHandle);

Write your headers and other data:  You may want to include some information about your spreadsheet and you’ll certainly want to include the column header row.  Here’s a brief example of how this could be done:

$headings = str_replace('`', '', "Heading One,`Database Field 1`,`Database Field 2`,`Database Field 3`");
$headArr = explode(',', $headings);

$RepDat[]="Created: " . date('H:i:s d-m-Y');
$RepDat[]="Created by: " . $UserName;
// Write county name to csv file.
fputcsv($TSVFileHandle,$RepDat,"\t");
fputcsv($TSVFileHandle,$headArr,"\t");

Use a xls file extension.  Sure you could use a tsv file extension, but if you want Excel or Open Office to open the file by default with minimal headache the xls extension will do the trick.  You’ll get an message when you open the report stating that the file was not in the same format as the extension, but you won’t have to worry about the tsv file extension being registered to the right application.

This method will kick out a tab delimited spreadsheet in a matter of seconds and can safely handle large record sets.  We used it for a while, as a temporary fix for reporting, until we came up with a better method and we still use this method to create database load files when parsing complex legacy reports or backing up database records to file.

I hope this php quick data export method is helpful.

Joel

Author: Joel Caton

Business Consultant and Web Developer from Meridian, MS