Convert csv to xls (excel) in php

Luckily for me, Ingatius Teo has done all of the heavy lifting, I just had to add a little code around it. Here’s the excel.php class: http://www.phpclasses.org/browse/package/1919.html

When faced with legacy systems, it’s nearly inevitable you’ll come across csv files. In fact, when you’re moving data from anywhere to anywhere, you’re likely to run into csv files.

The challenge I faced here was providing a list of new incoming customers on a daily basis, so that their data could be reviewed in a simpler manner – a spreadsheet to check that fields such as customer classification and email address were being filled in appropriately.

The code shown here is really simple. You include the excel.php class; by including that file you register a stream wrapper for protocol xlsfile – that’s the last thing the excel.php does.

After that, it’s all you. In this working example below, I am running the php program from a command line. Argument 1 is the input file, a tab separated csv file, and argument 2 is the xls file to be written. The code expects the first row to be column headings.

First, we read the first row to set the column headers. Then we reach each line and form an associative array that will be an element of the Big Array ($xlsArray) that contains the data that will become the xls file.

Finally, we write the Big Array, and *poof* we’re done!

/* Get the excel.php class here: http://www.phpclasses.org/browse/package/1919.html */
require_once("../classes/excel.php");
$inputFile=$argv[1];
$xlsFile=$argv[2];
 
if( empty($inputFile) || empty($xlsFile) ) {
    die("Usage: ". basename($argv[0]) . " in.csv out.xls\n" );
}
 
$fh = fopen( $inputFile, "r" );
if( !is_resource($fh) ) {
    die("Error opening $inputFile\n" );
}
 
/* Assuming that first line is column headings */
if( ($columns = fgetcsv($fh, 1024, "\t")) == false ) {
    print( "Error, couldn't get header row\n" );
    exit(-2);
}
$numColumns = count($columns);
 
/* Now read each of the rows, and construct a
    big Array that holds the data to be Excel-ified: */
$xlsArray = array();
$xlsArray[] = $columns;
while( ($rows = fgetcsv($fh, 1024, "\t")) != FALSE ) {
    $rowArray = array();
    for( $i=0; $i<$numColumns;$i++ ) {
        $key = $columns[$i];
        $val = $rows[$i];
        $rowArray["$key"] = $val;
    }
    $xlsArray[] = $rowArray;
    unset($rowArray);
}
fclose($fh);
 
/* Now let the excel class work its magic. excel.php
    has registered a stream wrapper for "xlsfile:/"
    and that's what triggers its 'magic': */
$xlsFile = "xlsfile://".$xlsFile;
$fOut = fopen( $xlsFile, "wb" );
if( !is_resource($fOut) ) {
    die( "Error opening $xlsFile\n" );
}
fwrite($fOut, serialize($xlsArray));
fclose($fOut);
 
exit(0);

Facebook comments: