Read Excel data from C#

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", fal开发者_运维百科se, 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.

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

I think you're looking for this:

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

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.