CSV to XML conversion utility
While my corporate clients already get output from their Multivalue Database systems in PCL Print, PDF emails and system FAX media, I am often asked to provide output in a spreadsheet file. I had been using the XLS output mode built into AccuTerm, which works fine, but I wanted more formatting control. Finding an Open Source PHP module that does CSV file to XLS file output with a high degree of formatting, I built a shell around that to use as a production conversion tool, to accomplish high format XLS file creation from an ASCII CSV file, for corporate user's consumption. As a PHP program, it should work on any O/S that you can install PHP 4.3 or better onto.
CSVtoXLS.php Program Notes
A PHP utility to convert CSV data into business class XLS spreadsheet files, tested as compatible with OpenOffice 2.0 Calc and Excel 2000. It is based on the PHP Spreadsheet_Excel_Writer module by Mika Tuupola & Xavier Noguer, which provides for a high level of spreadsheet formatting and also offers formulas. CSVtoXLS.php takes advantage of those features and provides meaningful formatting and a simple Column SUM automatically generated at the bottom of any Decimal Column.
Download the package at http://performantsystems.com/CSVtoXLS.zip
The distribution CSVtoXLS.zip file contains:
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/
If CSVtoXLS.php is not in the current directory, provide the full path to it.
In order to get more 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).
Dates are expected in either the ISO date format 'YYYY-MM-DD', or 'DD/MM/YYYY'.
Decimal numbers expected with just the decimal included, no other formatting. ("8.2","1995.23")
Line 2 Column formatting should be associated 1 to 1 with the Column Headings on Line 3.
Here is what the results of this example.xls file looks like:
Compare this spreadsheet with the example.csv file, noticing the TextLeft, TextRight, Decimal1, Date and Decimal2 formats. The SUM formulas on row 25 are automaticaly built for any Decimal Column format.