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.

Longshoreman

Doesn't Excel needs to be installed in order to use ADO? – Jacobba

@Lieven: Nope, just ADO. – Longshoreman

Answer 1

TMS Flexcel - I know it looks like a reporting component for Excel (which it does very well and is a very handy tool to have in your toolkit) but it also includes components for reading and displaying Excel files. I've been very impressed with how well Adrian Gallero seems to know the Excel API, including Excel 2007.

http://www.tmssoftware.com/site/flexcel.asp

Not free of course, but at 75 Euros I think it's good value.

Enugu

Very nice. Unfortunately, Flexcel doesn't currently support Excel 2007. – Longshoreman

It's in the very early stages, but TMS is finally gearing up their next release with XSLX support. tmssoftware.com/site/blog.asp?post=214 – Longshoreman

It’s 210 Euro now 😭. I wish it was still 75 EUROS… I would have bought it right away, but now I’ve gotta think about it. 🤔 – Unbolted

Answer 2

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.

Leucocytosis

The spreadsheets don't have a consistent layout. Even then, if I try to read specific cells or a range, open an Excel 2007 spreadsheet or sometimes even look at it funny, I would get the exception: "Could not find installable ISAM". – Longshoreman

It might be a problem with your connection string. If you want to pursue the ADO option further, post your connection string here. I'm far from an expert, but if I'm not able to spot something maybe someone else here will. – Leucocytosis

connectionstrings.com has gotten me out of a connection string bind many times. – Heracliteanism

Answer 3

I'd recommend SMImport / SMExport from http://www.scalabium.com

Mike has always been really helpful and quick to respond.

Quenby

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.

Cusco

It isn't just me. Someone was asking about this in Embarcadero's third party group. I'm happy to give them a try if they're still a going concern, but I'm wary if they don't answer e-mails from potential or existing customers. I'll try again. – Longshoreman

Recently bought NativeExcel and i must say i am please with the ease of use and functionality – Onym

NativeXML is kicking and alive: It works fine with Delphi XE! – Quadrant

Answer 5

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...).

Banderillero

I don't control the layout of the spreadsheets. If I try to get an arbitrary range or open a 2007 spreadsheet, I consistently get an exception: "Could not find installable ISAM". – Longshoreman

Did you get it to work with a plain and simple Excel workbook ? If yes, there might be a problem with that particular 2007 workbook. If no, please try first with a standard Excel file. Make 100% sure your connection string is properly formatted, a single extra space might be the problem... – Banderillero

Answer 6

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.

Algebraist

That answers my question about their support history. Unfortunately, XLSReadWriteII is expensive, and I don't need all of the features. I'm waiting to hear back from them about a Delphi 2010 version of XLSMini that supports Excel 2007. Still not cheap, but if it lets me get the job done... – Longshoreman

Answer 7

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.

Yvetteyvon

I'm glad I asked. From their description, I got the idea that it had the same support as XLSReadWriteII. I'm waiting to hear back from them. – Longshoreman

Answer 8

With great luck, I've used Axolot for some years now. The support forum isn't exactly brimming with messages, but maybe that's because it works so well?

Kyoko

How about Craig's warning that XLSMini is not up to date? – Longshoreman

Answer 9

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');

Abstention

Answer 10

I would advise to go for an option where you don't need Excel installed on the machine. I once used a component that could easily fill in some data in one sheet without needing excel installed. I would also do most of the Excel work in the Excel sheet itself. And just use the components to fill in some data on the sheet.

My 2cts.

Photoengraving

This is one of the requirements in my question. – Longshoreman

Answer 11

I haven’t tried it yet, and it’s 3 years old, but there’s a Open Source version https://github.com/rareMaxim/Excel4Delphi

Unbolted