1

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,

  1. Is using ADO with Excel bad at this point?
  2. Does anyone else have these issues and what did you do to resolve them (other than using XLS files instead of XLSX)?
Johnny Willemsen
  • 2,942
  • 1
  • 14
  • 16
DMeyer
  • 41
  • 1
  • 1
  • 5

2 Answers2

1

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.

Arioch 'The
  • 15,799
  • 35
  • 62
  • Thanks for the answer and information. That's what we were figuring but didn't know if this was common knowledge or not. In other applications we've used other methods to use Excel so it sounds like we'll have a project on our hands to change the ADO code. – DMeyer Mar 13 '13 at 15:59
  • COM is flexible and easy, yet rather slow. That also depends upon Delphi version. In XE2 i experience major bugs introduced in last update right before XE3 released as paid upgrade :-/ Personally for now i am only interested in very limited subset of features and export-only, thus i gnawed my teeth into option 5 :-) – Arioch 'The Mar 14 '13 at 05:57
  • Also, you seem to be a novice here on SO, so please look at https://www.google.ru/search?client=opera&q=stackoverflow.com+accept+answer&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest This is a gotcha for newbs, and i remember myself being ashamed after few months on SO :-) /// Given that Microsoft is deprecating OLE DB, ADO Win32 should be considered deprecated as well IMHO. Dunno about ADO.Net So i bet MS would not put much effort into compatibility of new Office with legacy framework. – Arioch 'The Mar 14 '13 at 05:58
1

I've really only ever found it possible to manipulate Excel via COM. I've tried alternatives, like ADO, but they always seem so full of archane bugs - or maybe it's just my ignorance.

COM is definitely slow in certain areas. I've used a combination of COM and (within the Excel file) VBA to achieve what I need to do.

Given that Excel is NOT going to go away BUT Microsoft cannot be relied upon not to betray its users by, for instance, doing away with VBA and COM support, it would be great if someone, somewhere (and I wish I had the skills) could create some proper support for Excel from Delphi.

Ken White
  • 123,280
  • 14
  • 225
  • 444
user3707842
  • 31
  • 2