Reading Excel spreadsheets using Delphi

Question

I need to read and write to Excel spreadsheets using Delphi 2010. Nothing special. Just reading and writing values ​​from specific cells and ranges on different sheets. You must work without installing Excel and Excel 2007 support.

Some things that I looked at:

• I tried using ADO , which works fine for selecting everything on the whole sheet, but I didn't have much luck reading specific cells or ranges.

• NativeExcel looked promising, but it does not seem to be in active development, and they do not respond to e -mails.

• Axolot has several products. The main product is apparently very functional, but expensive. They have a lite version, but it does not support Delphi 2010.

Any recommendations? Free would be great, but I'm open to a commercial solution if it is reliable and well maintained.

Answer 1

TMS Flexcel - I know that it looks like a reporting component for Excel (which is very good, and a very convenient tool for your toolkit), but also includes components for reading and displaying Excel files. I was very impressed with how well Adrian Gallero knows the Excel API, including Excel 2007.

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

Not free, of course, but at 75 euros, I think it is a good value.

Answer 2

I'm very lucky with ADO if the Excel worksheet is a pretty straightforward layout of rows / columns.

The key is using ADO, I found it treats the Excel worksheet as a database. If your Excel worksheets are mostly straight rows and rows, just treat the rows as database records and the columns as fields. First, go to the desired row by searching for a specific value of a column (field) (preferably something unique), and then read the desired cell in this row, indicating the field, which is the name of the column.

If your Excel worksheets are freer, then it will be harder.

Answer 3

Do not write off NativeExcel. I have been using it for a couple of years with excellent results. It is fast and versatile. I use it to create a well-formatted multi-page spreadsheet with frozen panes, cell formulas and data from a client database that they can use for input, and then send it back to me. My clients were very excited when they had their first spreadsheet, because it significantly reduced their workload, and it was quite intuitive for them.

I do not know why they did not answer you because I have updated their package at least a couple of times in the last two years. When my license expires, I definitely intend to renew.

Answer 4

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

Mike has always been a very helpful and quick answer.

Answer 5

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

I built a whole unit and acceptance testing platform where the data and controls are contained in an Excel spreadsheet.

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

Two things that caused me some problems: 1. Depending on how your sheets are named, ADO may or may not see them (again, if you have control over the layout, great!) 2. be careful with the data type ADO, when you read data, that is, they can show numbers as strings. This is because ADO is trying, IIRC, to guess the data type based on the first few lines.

Disclaimer: I have never used any of the tools mentioned above. ADO helped, and I feel more confident since I wrote the code for my framework (except for the ADO part, obviously ...).

Answer 6

Bruce, I used Axolot XLSReadWriteII component for transition for 10 years. This was very good, and their support forums (while Lite by content) seem to be well tracked. The XLSReadWriteII2 version is dazzlingly fast and supports all kinds of things, such as graphs and graphs, named ranges, adding formulas on the fly, formatting cells (including borders and shading, merging cells, aligning them vertically and horizontally, automatically determining tire widths, etc.) d.).

I have not upgraded to the latest version (we are still using XLSReadWriteII2), because we can still use Excel XP format files, and I did not use XLSMini at all. However, I can say really good things about the complete product; in fact, I just used it for a few things to export the database last week.

If you decide to go this route, I have a bunch of notes on how to do different things that can be useful; if you want them, write to me. I also have a Delphi 2007 application that simply shows how to perform various formatting and alignments; I actually reproduced the existing rather complicated report in Excel complete with all formats, borders, etc., which I would be glad to inform you as well.

DISCLAIMER: I have no contact with Axolot or their employees. I am just a very happy customer who learned about the product at a previous job and was impressed enough to buy it when I started my current one.

Answer 7

Do not worry about the Axolot XLSMini (lite) version. I have not purchased any of them yet, but I asked for Excel 2007 support in early 2008, and Lars told me that XLSMini was based on XLSReadWriteII and that both of them will be updated with Excel 2007 support at the same time. XLSReadWriteII supports Excel 2007 since April 2008; XLSMini still does not have it.

Answer 8

With great luck, I have used Axolot for several years. The support forum is not completely populated with messages, but maybe because it works so well?

Answer 9

You can use the ADO connection string, for example http://www.connectionstrings.com/excel than enable the parameters (on the third tab of the ado connection string):

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

for security reasons, Microsoft prevents changes (with IMEX = 1) http://support.microsoft.com/kb/904953/en Example SQL (do not forget the brackets):

 SELECT * FROM [Sheet1$] 

The only thing you cannot do is delete: http://support.microsoft.com/kb/257819/en Therefore, to delete a line, make it empty! You can also use SQL through ADO to export:

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

Answer 10

I would advise you to choose an option where you do not need Excel installed on the machine. I once used a component that could easily populate some data on one sheet without having to install excel. I would also do most of the work of Excel in the Excel sheet itself. And just use the components to fill in some data on the sheet.

My 2cts.