Search code examples
c#exceloffice-interop

Reading from Excel dynamically in C#

I'm not sure if using the word "dynamic" is correct. Anyway, I do have some basic understanding of using the Microsoft.Office.Interop.Excel. The problem is, I'm having about 100 excel files in a folder, each of the excel files has different sheet name, number of rows and number of columns.

As far as I understand, you need to specify the range and sheet name, i.e.: 

xcel.Worksheet sheet = someExcelFiles.Sheets["SomeSheetName"] as Excel.Worksheet;

Excel.Range range = sheet.get_Range("A1:A5");

Is there anyway so that my application can read all data in all of the excel files without having to specify the sheet name and range (row and columns)?

Solution

  • Short answer yes. Long answer From DotNetPerls which also contains grabbing number of sheets programatically.

    Range excelRange = sheet.UsedRange;
object[,] valueArray = (object[,])excelRange.get_Value(
    XlRangeValueDataType.xlRangeValueDefault);