Read Excel data from C#

Question

I'm trying to read data from an Excel sheet using Office.Interoperability.Excel namespace. I'd like to get the first row of the sheet as the first row contains the headers, without specifying the start and end cells. Because I wouldn't know if a new column is added to the sheet.

    Microsoft.Office.Interop.Excel.Application excelObj = new Application();

    Microsoft.Office.Interop.Excel.Workbook myBook = excelObj.Workbooks.Open(@"D:\myFile.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 0, 0);

    Microsoft.Office.Interop.Excel.Worksheet mySheet = (Worksheet)myBook.Sheets.get_Item(1);

    Range range = mySheet.Cells.EntireRow;

Here, the range becomes the entire range and it doesn't get limited to the number of header columns. Also I've a huge data of about 10,000 rows to process.

score 2 · Answer 1 · answered Sep 27 '11 at 17:26

2

If you requirement doesnt involve writing back to the excel file I would suggest that you use Excel Data Reader (http://exceldatareader.codeplex.com/) its a lot easier to use, doesnt require excel on the server and its faster

answered Sep 27 '11 at 17:26

iamkrillin

6,798
1
24
51

+1. Yes! All this Interop ceremonial... yawn. And if you want to write as well: Flexcel or Gembox (not free though). – Gert Arnold Sep 27 '11 at 17:47
gr8 tool... but i wanted exporting back to excel as well :) – Niranjan Sep 28 '11 at 08:53

score 1 · Accepted Answer · answered Sep 27 '11 at 17:24

1

I think you're looking for this:

Range headers = mySheet.UsedRange.Rows(1);

answered Sep 27 '11 at 17:24

phoog

42,068
6
79
117

score 1 · Answer 3 · edited May 23 '17 at 12:29

I just answered another Excel reading question here: C# converting .xls to .csv without Excel

The FileHelpers library is perfect for your task. I use it myself for those numbers of rows and above.

I don't know what you are doing with the rows once they are read from Excel, but if you a looking at some processing that could be broken down into step, have a look at Rhino.Etl for that. It's a really powerful way to process large amounts of data.

Read Excel data from C#

3 Answers3