ADO with XLSX files in Delphi XE
The issue here is ADO connection with Excel - is this still the standard way to read/write excel files within a Dephi XE environment? We're coming up with multiple issues when reading/writing using the ACEOLEDB driver (ACE 12) and this includes
- Reading cells with hashtags don't return results
- "Invalid Floating Point" when exporting grids.
We've also noticed that there's many versions of the ACE 12 driver out on Microsoft's website (via Access Database driver executables) and they each seem to have different issues with Delphi.
With these things in mind,
- Is using ADO with Excel bad at this point?
- Does anyone else have these issues and what did you do to resolve them (other than using XLS files instead of XLSX)?
Answer
ADO in Delphi is leaning to TDataSet model, which mean strictly tabular data... that excel is not. Each excel sheet has a random cells filled, some of those may constitute quazi-tabular ranges, or may not.
Depending on the installed software you can
1) use Excel application to open XLSX, read the cells and pass them to your program. This is most easy and compatible method, though is noticeably slow due to COM IPC marshalling and switching. There are tricks to fasten it, like hiding Excel window, copying arrays of data instead of cell-by-cell approach and such.
Start exploring TExcelApplication component - http://docwiki.embarcadero.com/RADStudio/XE3/en/Using_Component_Wrappers
2) If you do not want to rely on having commercial Excel installed, you may try reading XLSX files with OpenOffice. Vanilla OpenOffice can only read them though, but some other distro's can write them as well. OpenOffice also exposes external APIs both COM-based and HTTP-based. I know there are Delphi projects of Delphi - OOo interacting but personally did not used them and apart of noting that approach i can say no detailed assesment of it.
3) Microsoft also used to sell Office for Developers or such, that gave you Access and Excel kernels as redistributables, that you could pass with your application and install them and use them. Dunno if it is still feasible though.
4) there is a set of commercial components reading and writing those files directly w/o need to have external EXE's doing the job. While that would be the most fast way to work, it would only support some subset of features (which may or may not be ok for your particular goals) and may have troubles with "future compatibility" as Microsoft would roll out updated versions of XLS and XLSX formats (which again may be of some or none concern to you). Like there was TXLSFile for Biff8 format, there is for example OExport library. There is also a component from well known TMS Studio and maybe some more.
5) You can join some open-source project and try to enhance it for your needs, then again that depends upon how much the subset you need.
I know, many people succefulyl use OLE DB to access Excel data, but for me it always sounded as some perversino, because Excel files do not have any internal regular data arrangement at all, less so strictly-tabular RDBMS-like one.