How Can I open and read all data in excel file to perform some operations on say write them to a database ...
3
-
Possible duplication of this question: stackoverflow.com/questions/170652/…DixonD– DixonD2010-08-02 07:44:28 +00:00Commented Aug 2, 2010 at 7:44
-
duplicated: stackoverflow.com/questions/15828/reading-excel-files-from-cAmr Badawy– Amr Badawy2010-08-02 07:57:03 +00:00Commented Aug 2, 2010 at 7:57
-
If you wish to write to a database, it is nearly always best to do it in one step, that is, use a query string that idetifies both the database and the Excel file, rather than read the Excel file and then write to the database.Fionnuala– Fionnuala2010-08-02 08:05:37 +00:00Commented Aug 2, 2010 at 8:05
Add a comment
|
6 Answers
You can automate Excel using com automation http://support.microsoft.com/kb/302096 , but if you are on a web server you will need to use a third party library like http://sourceforge.net/projects/koogra/
Comments
You can use the default library that comes with the .NET framework in order to use the Excel.Application Object and therefore the Workbook and Worksheets objects, so you can access Excel files, read or manipulate them
You can add it to your project by using the Add Reference option, the library is called
Microsoft.Office.Interop.Excel
Hope this helps
Comments
Assuming that the Excel files are in a table format, I'd suggest that the best way would be using OleDB. This page has a very basic sample that should show you how to get started.
If you're unable to use OleDB for some reason, then you could use Excel Automation. This is not recommended if it's on a server though and will in general be slower and less stable than OleDB, but you will be able to do pretty much anything you need.
Comments
There are several ways:
- If *.xslx (the new XML based format) is used, you can open that file and read the XML File
- You can read it with Excel COM Interop (not recomended on a Server!)
- You can use a ODBC Data Source
Comments
Starting with Office 2007, you can use OpenXML to query/manipulate office documents. The .xlsx files (all Office .???x files) are zipped up XML files.
Comments
ExcelToEnumerable is a great solution if you want to map Excel data to a list of classes, e.g:
var filePath = "/Path/To/ExcelFile.xlsx";
IEnumerable<MyClass> myClasses = filePath.ExcelToEnumerable<MyClass>();
Disclaimer. I am the author of ExcelToEnumerable.
