Reading Excel spreadsheets with Delphi

Question

I need to read from and write to Excel spreadsheets using Delphi 2010. Nothing fancy. Just reading and writing values from specific cells and ranges on different sheets. Needs to work without having Excel installed and support Excel 2007.

Some things I've looked at:

• I've tried using ADO, which works OK for selecting everything in an entire sheet, but I haven't had much luck reading specific cells or ranges.

• NativeExcel looked promising, but it doesn't seem to be in active development, and they don't respond to e-mails.

• Axolot has a couple of products. The main product seems to be very functional, but is pricey. They have a lite version, but it doesn't support Delphi 2010.

Any recommendations? Free would be great, but I'm open to a commercial solution as long as it's reliable and well supported.

Answer 1

Bruce, I've used the Axolot XLSReadWriteII component for going on 10 years now. It's been very good, and their support forums (while lite on content) seem to be monitored pretty well. The XLSReadWriteII2 version is blindingly fast, and supports all sorts of things like charts and graphics, named ranges, adding formulas on the fly, cell formatting (including borders and shading, merging cells, vertical and horizontal alignment, auto-width column sizing, and so forth).

I haven't upgraded to the latest version (we're still using XLSReadWriteII2) because we can still use the Excel XP format files, and I haven't used the XLSMini at all. I can say really good things about the full product, though; in fact, I just used it for a couple of database export things this past week.

If you decide to go that route, I have a bunch of notes about how to do different things that might be useful; if you want them, drop me a note. I also have a Delphi 2007 app that just shows how to do different formatting and alignments; I actually reproduced an existing, fairly complex report in Excel complete with all of the formats, borders, etc. that I'd be glad to let you have as well.

DISCLAIMER: I have no connection with Axolot or any of their employees. I'm just a very happy customer who learned of the product at a previous job, and was impressed enough to buy it when I started my current one.

Answer 2

What really helps is if you have some kind of control over the layout of the excel file.

I have built a whole unit and acceptance testing framework where the data and the test controls are all contained within an Excel spreadsheet.

I did everything through ADO. You can restrict your ADO SQL query to a whole sheet, a named range or any range for the matter. In my opinion and experience, this method is very powerful.

Two things that did cause me some problems : 1. depending on how your sheets are named, ADO might or might not see them (again, if you have control over the layout, great !) 2. be careful about the data type ADO returns when you read data i.e. it might show numbers as strings. This is because ADO tries, IIRC, to guess the data type based on the first few rows.

Disclaimer : I have never used any of the tools mentioned above. ADO did the trick for me, and I feel more in control since I wrote the code for my framework (save the ADO part obviously...).

Answer 3

You can use ADO connection string like http://www.connectionstrings.com/excel than include the options (in the third tab of ado connection string):

Extended Properties=Excel 8.0;HDR=Yes;IMEX=0  

for security purposes Microsoft prevent modifications (with IMEX=1) http://support.microsoft.com/kb/904953/en Sample SQL (don't forget the brackets):

SELECT * FROM [Sheet1$]

The only thing you can't do, is deletion: http://support.microsoft.com/kb/257819/en So to delete a row make it empty! You can also use SQL via ADO to export:

YourADOConnection.Execute('SELECT * INTO aSheet IN "'+ExtractFilePath(ParamStr(0))+'Exported.xls" "Excel 8.0;" FROM YourTable');

Answer 4

Don't write off NativeExcel. I have used it for a couple of years now with excellent results. It's fast and versatile. I use it to produce a nicely formatted multi-page spreadsheet with frozen panes, formulas in cells, and data from a client's database for them to use for input and then send back to me. My clients were really thrilled when they got the first spreadsheet from me because it reduced their workload tremendously and it was fairly intuitive for them to use.

I don't know why they have not responded to you because I have updated their package at least a couple of time over the last two years. When my license expires, I definitely intend to renew.

Answer 5

Don't bother with Axolot's XLSMini (lite) version. I haven't purchased either of them yet, but I asked about Excel 2007 support in early 2008 and Lars told me XLSMini was based on the XLSReadWriteII and that both would be updated with Excel 2007 support at the same time. XLSReadWriteII has had Excel 2007 support since April 2008; XLSMini still doesn't have it.

Answer 6

I've had very good luck with ADO, provided the Excel sheet is a fairly straight-forward row/column layout.

The key with using ADO, I've found, is treating the Excel sheet like a database. If your Excel sheets are primarily straight row and column layouts, just treat the rows as database records and the columns as fields. Navigate to the desired row first by searching for a particular column (field) value (preferably something unique), and then read the desired cell in that row by referencing the field that is the column name.

If your Excel sheets are more free-form, then it will be more difficult.