4

Could someone help me read a simple excel worksheet in c# app? I'd like to be able to iterate each row and have a handle on each of the columns.

Thanks, rod.

Rod
  • 14,529
  • 31
  • 118
  • 230

6 Answers6

2

The general method is to use Excel COM Interop. A quick google will find plenty of tutorials. Here's one for creating a sheet - it should point you in the direction (reading is pretty much the same).

An alternative method is to use ADO.Net. This is only really viable if your Excel sheet is well formed as a table ( ie. Database), but is easier than the interop approach.

winwaed
  • 7,645
  • 6
  • 36
  • 81
  • ADO is not just easier, but faster. – Kevin Stricker Nov 21 '10 at 01:13
  • Really? I guess it makes sense. I've only compared Excel ADO.Net against proper databases with SQL queries and then it is very slow, but that is primarily because Excel does not support database indexes. – winwaed Nov 21 '10 at 01:18
2

Here is a sample using OLEDB

http://www.techiesweb.net/2009/12/reading-records-excel-file-insert-database-aspnet/

Shyju
  • 214,206
  • 104
  • 411
  • 497
2

This one is the easiest method I have found:

Create Excel (.XLS and .XLSX) file from C#

Community
  • 1
  • 1
Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
1

If you are going to open Excel 2007 or 2010 workbook (ooxml format), you can download Open XML SDK 2.0 for Microsoft Office (which doesn't require you to have MS office installed).

Matthew Lai
  • 217
  • 3
  • 12
  • Any version before Excel 2007, you can use Office Primary Interop Assemblies](using COM Interop, which requires you to have MS office installed). – Matthew Lai Nov 21 '10 at 01:38
  • 1
    Tutorial using Open XML SDK: http://msdn.microsoft.com/en-us/library/bb507946.aspx, tutorial using COM Interop: http://dotnetperls.com/excel-interop – Matthew Lai Nov 21 '10 at 01:38
0

While Excel COM Interop works, it requires Excel to be installed on the client machine. If that isn't an issue then all good, but if it is you might consider looking at the Aspose.Cells library (no affiliation, just used them before). They're simple and powerful, although do carry a commercial license cost.

Michael Shimmins
  • 19,961
  • 7
  • 57
  • 90
0

I've used ADO.NET and Jet in the past. Be warned that if you have columns that aren't obviously of one type you will see weird things happen. Jet tries to assign a datatype to a column based on the first several vales. The nice thing is that you can query the spreadsheet like it is a table.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486