ExcelReaderFactory, reading first sheet

Question

I am using the ExcelDataReaderFactory in C#, in order to read my Excel files and inserting them to a database.
Right now I am specifying sheetname for the sheet that I want to use. Can I make it to be chosen as the first sheet every time?

Here is how I load the data.

public IExcelDataReader getExcelReader()
{
    // ExcelDataReader works with the binary Excel file, so it needs a FileStream
    // to get started. This is how we avoid dependencies on ACE or Interop:
    FileStream stream = File.Open(_path, FileMode.Open, FileAccess.Read);

    // We return the interface, so that
    IExcelDataReader reader = null;
    try
    {
        if (_path.EndsWith(".xls"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        if (_path.EndsWith(".xlsx"))
        {
            reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        }
        return reader;
    }
    catch (Exception)
    {
        throw;
    }
}

public IEnumerable<string> getWorksheetNames()
{
    var reader = this.getExcelReader();
    var workbook = reader.AsDataSet();
    var sheets = from DataTable sheet in workbook.Tables select sheet.TableName;
    return sheets;
}

public IEnumerable<DataRow> getData(string sheet, bool firstRowIsColumnNames = false)
{
    var reader = this.getExcelReader();
    reader.IsFirstRowAsColumnNames = firstRowIsColumnNames;
    var workSheet = reader.AsDataSet().Tables[sheet];
    var rows = from DataRow a in workSheet.Rows select a;
    return rows;
}

getData("april"); //Here I want it to be the first sheet, and not have to choose.

Appreciate any advice.

Answer 1

I don't know that library. But I think you are converting it to a DataSet anyway. Then the first sheet/table is:

DataTable firstWorkSheet = reader.AsDataSet().Tables[0];

Since the indexer of DataTableCollection has an overload for the index not only for the name.

So the whole method is:

public IEnumerable<DataRow> GetFirstSheetData(bool firstRowIsColumnNames = false)
{
    var reader = this.getExcelReader();
    reader.IsFirstRowAsColumnNames = firstRowIsColumnNames;
    return reader.AsDataSet().Tables[0].AsEnumerable();
}

Answer 2

Update for v3

There have been some breaking changes in the upgrade to v3, so here's the original code and accepted answer updated to work with v3.

    public IExcelDataReader getExcelReader()
    {
        return ExcelReaderFactory.CreateReader(System.IO.File.OpenRead(_path));
    }

    public IEnumerable<string> getWorksheetNames()
    {
        var reader = this.getExcelReader();
        var workbook = reader.AsDataSet();
        var sheets = from DataTable sheet in workbook.Tables.Cast<DataTable>() select sheet.TableName;
        return sheets;
    }

    public IEnumerable<DataRow> getData(string sheet, bool firstRowIsColumnNames = false)
    {
        var reader = this.getExcelReader();
        reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = firstRowIsColumnNames
            }
        });
        var workSheet = reader.AsDataSet().Tables[sheet];
        var rows = from DataRow a in workSheet.Rows select a;
        return rows;
    }

    public IEnumerable<DataRow> GetFirstSheetData(bool firstRowIsColumnNames = false)
    {
        var reader = this.getExcelReader();
        reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = firstRowIsColumnNames
            }
        });
        return getData(getWorksheetNames().First());
    }

I would say that getExcelReader has been simplified to the point of redundancy.