Read from and write to Excel with Database components

Our current Excel components use Excel Automation to work with Excel documents. This allows us to run macros and support almost any scenario but is not ideal for server environments. If you don't need to run any macros a better option will be to use the Database components to read from and write to Excel. Here are some tips to get you going.

1. Download and install the Microsoft Access Database Engine Redistributable. This will install the OleDb drivers required to work with Excel through a database component.
2. Use the DatabaseRead and DatabaseWrite components when reading or writing to an Excel document.
3. The connectionstring for reads looks like "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\temp\write.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1"
4. For writing change IMEX=1 to IMEX=3.
5. If you do not have headers then HDR=NO.
6. For older files you can change the Extended Properties of the connectionstring.
7. You can get more connectionstring information here.
8. It seems as if a blank document must be initialised with a header for every row you want to write to. Just make it a space if you don't have headers.
9. Read from a sheet: "select * from [Sheet1$]". Remember the $.
10. Read from a range: "select * from MyRange" or "select * from [Sheet1$A1:B10]".
11. Insert into a sheet with headers: "insert into [Sheet1$] (id,name) values('123','John')".
12. If you don't have header use F1, F2, F... to refer to fields e.g. "select F1, F3 from [Sheet1$]".
13. It supports other sql clauses like UPDATE and WHERE as illustrated here.

Some more info:

http://yoursandmyideas.wordpress.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/