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

Passing PHP arrays between scripts using urls

Your quick and easy guide to passing php arrays via url

I spent more time then I care to mention on this problem and I’d like to save you some time and hassle.

You will run into situations where passing variables via the $_SESSION really doesn’t make much sense. In cases where you will be running multiple instances of the same script on the same site but passing it different data sets from the same user it seems a bit easier if you can directly pass the array via the url unless you want to get to monkeying around with multiple data sets in the session. Here’s a quick example:

Script1 located at yourdomain.com needs to pass the following  array to script2. We do this by running the array through the serialize function and then running the product of that through the base64_encode function to package it. We then pass it to script2 and run it to the unserialize and base64_decode functions to unpackage it. We can also use this method to store arrays in a database and retrieve them.

An Example

Here’s some user preference example data generated by script1:

$UserArray[‘LikesCoffee’] = true; $UserArray[‘LikesDonuts’] = true; $UserArray[‘ListensToOpera’] = false; etc…

Now to pass this data to script2 we assemble the url:

$url = “yourdomain.com/script2.php?UserData=” . base64_encode(serialize($UserArray));

You can use this url for any user event driven action such as a link, form submission, or ajax post. Once the data has been passed to script2

  1. Test for it: isset($_GET[‘UserArray’])
  2. unpack it: $UserArray = unserialize(base64_unencode($_GET[‘UserData’]));
  3. Sanitize it: Data passed in this manner is not really secure.
  4. This type of data packing is great for storing arrays in your database.

Conclusion

This method is quick and easy and can be used for routine data such as visitor preferences. Be forewarned that most servers and browsers have limits of the url length that they will accept and base_64 increases the size of your data by about 33%. Just keep it under 2000 characters and you’ll be ok. I wouldn’t suggest using this method for user authentication or credential passing without further encoding the data with one way encryption but that’s another topic.

Comments are appreciated.

– Joel