BastianBuhrkall BastianBuhrkall - 3 months ago 29
C# Question

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.

Answer

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