Read from Excel file — specific sheet

Question

Simply put: I need to read from an xlsx file (rows), in the simplest way. That is, preferably without using third-party tools, or at least things that aren't available as nuget packages.

I've been trying for a while with IExcelDatareader, but I cannot figure out how to get data from a specific sheet.

This simple code snippet works, but it just reads the first worksheet:

FileStream stream = File.Open("C:\\test\\test.xlsx", FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;

while (excelReader.Read()) {
    Console.WriteLine(excelReader.GetString(0));
}

This prints the rows in the first worksheet, but ignores the others. Of course, there is nothing to suggest otherwise, but I cannot seem to find out how to specify the sheet name.

It strikes me that this should be quite easy?

Sorry for asking something which has been asked several times before, but the answer (here and elsewhere on the net) are a jungle of bad, plain wrong and outdated half-answers that's a nightmare to try and make sense of. Especially since almost everyone answering assumes that you know some specific details that are not always easy to find.

UPDATE: As per daniell89's suggestion below, I've tried this:

FileStream stream = File.Open("C:\\test\\test.xlsx", FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;

// Select the first or second sheet - this works:
DataTable specificWorkSheet = excelReader.AsDataSet().Tables[1]; 

// This works: Printing the first value in each column
foreach (var col in specificWorkSheet.Columns) 
    Console.WriteLine(col.ToString());

// This does NOT work: Printing the first value in each row
foreach (var row in specificWorkSheet.Rows)
    Console.WriteLine(row.ToString());

Printing each column heading with col.ToString() works fine.

Printing the first cell of each row with row.ToString() results in this output:

System.Data.DataRow
System.Data.DataRow
System.Data.DataRow
...

One per row, so it's obviously getting the rows. But how to get the contents, and why does ToString() work for the columns and not for the rows?

Answer 1

Maybe look at this answer: https://stackoverflow.com/a/32522041/5358389

DataSet workSheets= reader.AsDataSet();

And then specific sheet:

DataTable specificWorkSheet = reader.AsDataSet().Tables[yourValue];

Enumerating rows:

foreach (var row in specificWorkSheet.Rows)
     Console.WriteLine(((DataRow)row)[0]); // column identifier in square brackets