Reading and Writing Excel Files from ASP
A project that we’re currently working on requires that we read from and write to Excel files from our ASP application. As the application will be hosted in an environment that will not have Office installed (as most will not) we can’t use automation, and anyway it seems that using this kind of automation from ASP is not recommended for all sorts of reasons (among other possible problems, it can not be guaranteed that Excel won’t at some stage look for some user interaction and pop up some ‘random’ window).
We needed another method of working with the Excel data from ASP. I tried using the MS ADO Excel drivers with very mixed results, these drivers seem to have been hobbled together with the express aim of annoying and hindering anybody that uses them, and I really can’t recommend their use unless you’re just reading from an excel chart with very consistent data types. Their abilty to write to Excel files is very very limited.
Looking at the commercial offerings, most seemed very much on the pricey side ~1,500USD, although when purchasing from Ireland the current Dollar/Euro exchange rate certainly helps! However I am now evaluating Syncfusions XLsIO component which seems to do the job quite nicely, although it does seem quite slow when reading medium to large worksheets. It is a much more reasonable ~500USD. It has a very similar API to Excel’s native API.
Anyway here are some links to pages that I found useful while on my journey of discovery:
ADO Read EXCEL files
Write Excel File
http://www.beansoftware.com/NET-Tutorials/Excel-ADO.NET-Database.aspx [Create sheet]
http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx [Importing to SQL Server]
http://www.thescarms.com/dotnet/Schema.aspx [ Read Schema ]