I have a set of excel templates, which will be entered by users. I have to import the data entered in to this templates to database. Problem with the templates is that they are not in the tabular formats.
A:
From your question, I gather you've already ruled out using JET/OLEDB to access the spreadsheet data, which only really works with tabular data. So, you're going to need some way of accessing individual cells, etc.
You could open the Excel files using Excel (via interop), as I'm sure someone will suggest. But, if you can avoid doing this, you should - there are all sorts of problems with it (it's flaky, and you'll get no help from Microsoft since they basically say don't do it).
You can avoid having to do this if you can have your Excel files saved as XML.
If you're using Excel 2003 then you can Save As "Xml Spreadsheet", which is functionally equivalent to a normal XLS file, except that it cannot contain charts or macro code. Other than that, it looks exactly the same to a user - and it's much easier to process in code.
If you're using Excel 2007 then the native file format is already XML (albeit stored within a ZIP file), and again you can process that in code. There are lots of examples on-line.
Gary McGill
2009-07-23 15:39:10
Hi Gary, Thanks. I will give it a try. I found one easy solution though. I am writing a macro which gathers the data from all sheets and writes it tabular format. I will JET/OLEDB to read the data and insert it into database.
raj
2009-07-27 16:06:22
A:
You can use the open xml sdk to pick out the data you need:
http://blogs.msdn.com/excel/archive/2008/11/25/using-the-open-xml-sdk-to-work-with-excel-files.aspx
If you have Sharepoint Excel services you could use that. Would probably not be worth buying it just for this job.
You could also use Excel via Excel interop, although you have to watch out for licencing issues and memory leaks.
I would recommend the Open XML SDK.
Shiraz Bhaiji
2009-07-23 15:44:38
A:
SpreadsheetGear for .NET will allow you to open xls and xlsx workbooks, get any range into a DataTable, or iterate through the cells and get the raw data or the formatted text of each cell.
You can see ASP.NET samples (C# or VB) here and download a free trial here.
Disclaimer: I own SpreadsheetGear LLC
Joe Erickson
2009-07-23 17:06:17