WHAT THE ExcelWriter CLASS PROVIDES
=========================
This class provides for production of Excel Binary Interchange Format Files (BIFF) without requiring Microsoft Excel. These files are usable in Excel 97 onwards and any of the alternative spreadsheet apps.
Excel files can also be generated from a RecordSet (and column data types and widths can be automatically determined from a settable number of sample records, and treated as numeric etc even if the column type is presented as string). Alternatively, data types can be supplied in a dictionary (column name/data type) - whatever, you choose.
WHY?
====
This project was born out of frustration with the dearth of usable Excel file creation tools in RealStudio and 3rd-party plugins, that, frankly, don't work at all well.
We spent a *lot* of time (and various email traffic) trying to use Einhugur's ExcelExporter class - which worked on my Mac, and opened fine with OpenOffice, but failed miserably when attempting to open the xlsx file on Windows using ExcelReader and Excel itself - which means users can't use the files it produces. It's also a bit buggy, and the classes are locked down, so we can't fix the problems without rewriting. Maybe the problem is a version (Excel), LittleEndian byte ordering or language issue? Don't know. We love Einhugur's tools, and use and recommend them quite happily - just not the ExcelExporter.
Also tried (and paid a very small amount for) Norman's ExcelWriter2, which seems to provide another XML-ish rendition of Excel spreadsheets, however the demo project died when attempting to create Sheet5...and we'd really rather not generate XML files when many (most) users still use old versions of Excel.
We also tried various CSV, TSV and HTML file formats, but they really don't provide what we thought we'd like:
- not being presented with an import dialog (happens on "fake" xls files in CSV/TSV too)
- proper numeric justification
- column width
- cell/column/row colour
- cell format
- formulae
- UTF-8 string support
We wanted proper spreadsheet files, and CSV just doesn't cut it.
We also wanted to provide an Excel export class for our proposed PDF ReportWriter (designer and runtime viewer/designer).
This module doesn't quite make the grade, but it produces basic, unformatted excel spreadsheets perfectly, and readable with any excel viewer or program (we've got a much more advanced version coming soon).
GENERAL TECHNIQUES
==============
After fiddling around with a more-or-less matching class and methodology used by Serhiy Perevoznyk, I decided
to generate each BIFF record (each cell function generates a BIFF record) into a MemoryBlock, then write that block to the output file
(suppose it saves disk I/O, but don't really know - just seemed the right way, and it works...)
Also added the RecordSet processor (just set the instance recordset, then use the appropriate functions) - we have another project that creates cooked BIFF files, based on another Perl/PHP project which will extend functionality by adding cell and column attribute functions (such as colour, width, formula etc.).
MORE INFO ABOUT EXCEL FILE STRUCTURE
==========================
Good sources of info include:
- this class
- Serhiy Perevoznyk's original C# project, from which this class was derived (refer licence)
- http://sc.openoffice.org/excelfileformat.pdf (awesome resource)
- http://en.wikipedia.org/wiki/Microsoft_Excel
*ENJOY*