CODEWITHSUNDEEP
c#asp.netvisual-studio-2008
el ninho
el ninho

Reputation: 4233

Reading Excel file

What is a good way to read Excel files in C#? Point is having web page from which user can upload an Excel file, which will then be read and sent to database table.

OleDB way is useless here, as as far as I know, it can read use one file, and here would be many.

Best way I found so far is this one:

http://www.codeproject.com/KB/office/csharp_excel.aspx

Any other better/newer methods?

Thanks.

Upvotes: 0

Views: 1059

Answers (6)

GregL
GregL

Reputation: 38103

The open-source NPOI library provides pretty comprehensive functionality for reading and writing from Excel files (and other Office file formats), without needing to run Excel on the webserver (so it is much, much faster).

Note that it does not currently handle XLSX files.

Upvotes: 0

Adam Tuliper
Adam Tuliper

Reputation: 30152

The oledb provider will work, there are some gotchyas though. There is a column limit if you aren't running the 64 bit version of the driver. If you are running the 64 bit, you can't have any 32 bit office products on the system.

I would recommend setting in the registry HKLM\Software\Wow5432Node\Microsoft\Jet\4.0\Engines\Excel Set the value TypeGuessRows equal to zero This causes the driver to scan all rows to determine the type. The driver is a bit iffy in that if the first say X number of rows in a column have numbers in it and then it has strings after, the type could be considered numeric, and anything containing a string will disappear and become null. ex.

zipcode
--------
39934

18883

28472

52256-1252

the last row could be read as null because it thinks the first few are numbers, anything not fitting that format is converted to null.

So you CAN possibly use oledb, just a few workaround to consider.

Upvotes: 1

Jon P
Jon P

Reputation: 19772

I've been using Excel Data Reader, which has worked extremely well. Admittedly it has been with a desk top application but it should work just as well in an ASP.net application.

Upvotes: 0

M.Babcock
M.Babcock

Reputation: 18965

Using OLEDB you can access multiple files by building your connection string based on the name of the file you want to open. Something like the following should work:

For XLSX:

var connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", fileName);

For XLS:

var connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", fileName);

Where fileName identifies the file you want to open.

Upvotes: 2

Eric J.
Eric J.

Reputation: 150108

I have recently had good success with Linq To Excel. Depending on your exact requirements, that may be a good option

http://code.google.com/p/linqtoexcel/

Upvotes: 0

Emmanuel N
Emmanuel N

Reputation: 7449

There are several options

  1. Using Excel Interop, need excel intalled and you have to deal with COM
  2. For XLSX files you can use Open Office SDK or third party libraries like Epplus

Upvotes: 0

Related Questions