Reading Excel file

Question

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.

What do you mean by the OleDB method can only read one file? — M.Babcock
– M.Babcock, Commented Jan 16, 2012 at 0:22
@M.Babcock It have connection string, and if I'm right, that should be path to file, and I need to have file uploaded, and then read. For many files. — el ninho
– el ninho, Commented Jan 16, 2012 at 0:24
Yes and you can build your connection string based on the file you want to open. What's the problem? — M.Babcock
– M.Babcock, Commented Jan 16, 2012 at 0:25
@M.Babcock, let's say I have uploaded files File1.xlsx, File2.xls, File3.xls, File4.xlsx, where I don't know how many files I will have, or how they will be named. — el ninho
– el ninho, Commented Jan 16, 2012 at 0:30
What upload control are you using? Pretty much any of them will tell you the name of the file or files that were uploaded. — M.Babcock
– M.Babcock, Commented Jan 16, 2012 at 0:35

M.Babcock · Accepted Answer · 2012-01-16 00:44:41Z

2

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.

edited Jan 16, 2012 at 0:44

answered Jan 16, 2012 at 0:39

M.Babcock

19k6 gold badges57 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam Tuliper · Accepted Answer · 2012-01-16 01:52:12Z

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.

Emmanuel N · Accepted Answer · 2012-01-16 00:27:43Z

0

There are several options

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

answered Jan 16, 2012 at 0:27

Emmanuel N

7,4592 gold badges30 silver badges37 bronze badges

Comments

Eric J. · Accepted Answer · 2012-01-16 00:38:57Z

0

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/

answered Jan 16, 2012 at 0:38

Eric J.

151k65 gold badges353 silver badges563 bronze badges

Comments

Jon P · Accepted Answer · 2012-01-16 00:45:52Z

0

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.

answered Jan 16, 2012 at 0:45

Jon P

19.9k8 gold badges52 silver badges76 bronze badges

Comments

GregL · Accepted Answer · 2012-01-16 07:35:00Z

0

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.

answered Jan 16, 2012 at 7:35

GregL

38.2k8 gold badges65 silver badges68 bronze badges

Collectives™ on Stack Overflow

Reading Excel file

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related