CODEWITHSUNDEEP
c#excel
BastianBuhrkall
BastianBuhrkall

Reputation: 348

ExcelReaderFactory, reading first sheet

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.

Upvotes: 5

Views: 30112

Answers (2)

Red
Red

Reputation: 3267

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.

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460038

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();
}

Upvotes: 6

Related Questions