CSVtoXLS: A Utility to Convert CSV data into XLS Spreadsheets

CSVtoXLS is a utility that is used to convert CSV data into business class XLS spreadsheet files, and is compatible with OpenOffice 2.0 Calc and Excel 2000. The Spreadsheet_Excel_Writer module provides for a high level of formatting and also offers formulas. CSVtoXLS.php takes advantage of those features and provides meaningful formatting and a simple Column SUM is automaticly generated at the bottom of any Decimal Column.

You can download the attached file and proceed as we show you how to use it below.

Note: the /OLE and /Spreadsheet directories need to be copied to the php include_path directory. Sourced from http://pear.php.net/package/Spreadsheet_Excel_Writer/ by Mika Tuupola & Xavier Noguer.

The correct command line syntax to use is:
php -f CSVtoXLS.php {InputFile}.csv {OutputFile}.xls

If CSVtoXLS.php is not in the current directory, you will need to provide the full path to it.

In order to get provide better formatting that just a data dump, three lines of META data are inserted into the top of the CSV data file. This allows the developer to define a Title, Column Headings and Column Formats in Spreadsheet terms.

The first line of CSV data is expected to be the Title only (in quotes). The second line of CSV data is expected to be comma delimited Column Formats (also in quotes). The third line of CSV data is expected to be comma delimited Column Headings (also in quotes). The live data is expected to start on CSV file line 4 (quotes should be used on all fields, if you haven’t already guessed).

Dates are expected in either the ISO date format ‘YYYY-MM-DD’, or ‘DD/MM/YYYY’. Decimal numbers are expected with just the decimal included, no other formatting. (i.e “8.2”,”1995.23″)

Line 2 Column formatting should be associated one to one with the Column Headings on Line 3.
Choices for Column Formatting are:
“TextLeft”,”TextRight”,”Date”,”Decimal0″,”Decimal1″,”Decimal2″,”Decimal3″,”Decimal4″

Click to see the image at full size.

Install steps:

1. Find your PHP include_path; ex: php -i|grep include_path
2. Copy the /OLE and /Spreadsheet directories to the PHP include_path
3. Copy the CSVtoXLS.php and example.csv files somewhere ($HOME is fine).

Test it:

1. Navigate (cd) to where the CSVtoXLS.php and example.csv files are.
2. At a command line, do: php -f CSVtoXLS.php example.csv example.xls
3. Open example.xls in a Spreadsheet App,, compare it to the example.csv text content.

Conclusion

Well there you have it! Feel free to try CSVtoXLS out on your own, and if you find any bugs, or improve it in any way, please drop me a note!