How to read excel file using delphi

Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Reading an Excel spreadsheet in Delphi Ask Question. Asked 6 years, 10 months ago.

Active 6 years, 10 months ago. Viewed times. I have a number of applications that read Excel spreadsheets for conversion to SQL All the applications are based on the same technique using the following procedures.

The procedures procedure TFormSpreadsheet. FetchData; begin StatusBar1. Close; QrySpreadsheetIn. Text ; try QrySpreadsheetIn. Open; clientdataset1. Some additional info - the process was developed in Delphi 7 on a 32bit system. In this way, all the methods and features of the ADODataset object are available to process the Excel data. In other words, using the ADO components let you build an application that can use an Excel workbook as the database. Another important fact is that Excel is an out-of-process ActiveX server.

ADO runs in-process and saves the overhead of costly out-of-process calls. When you connect to Excel using ADO, you can only exchange raw data to and from a workbook. An ADO connection cannot be used for sheet formatting or implementing formulas to cells. However, if you transfer your data to a worksheet that is pre-formatted, the format is maintained.

After the data is inserted from your application to Excel, you can carry out any conditional formatting using a pre-recorded macro in the worksheet. The value used for ConnectionString consists of one or more arguments ADO uses to establish the connection.

In order to connect to Excel, a valid connection string involves only two additional pieces of information - the full path to the workbook and the Excel file version. A legitimate connection string could look like this:. When connecting to an external database format supported by the Jet, the extended properties for the connection needs to be set. In our case, when connecting to an Excel "database," extended properties are used to set the Excel file version.

For an Excel95 workbook, this value is "Excel 5. Important: You must use the Jet 4. If you set the Jet Provider to version 3. Therefore, if you have column heading, you do not need to specify this value. 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. In my opinion and experience, this method is very powerful. Two things that did cause me some problems : 1. Disclaimer : I have never used any of the tools mentioned above. 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 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 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.

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

How are we doing? Please help us improve Stack Overflow. Take our short survey. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Reading Excel spreadsheets with Delphi Ask Question. Asked 11 years, 10 months ago. Active 6 years, 5 months ago.

Viewed 26k times. 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. Improve this question. Doesn't Excel needs to be installed in order to use ADO? Lieven: Nope, just ADO. Add a comment. Active Oldest Votes.