Excel Data Reader Issues, column Names, and Sheet Selection

Question

I am using Excel Data Reader to read some data in to an Entity Framework Database

The code below is working but i need some further refinements

First of all IsFirstRowAsColumnNames does not seem to be working as intended and I have to use .Read instead.

The fudge i had in originally to select a particular sheet was has scuppered plans, can anyone help with this excelReader.Name at the moment is pointless unless i can specifically loop through or select a sheet, which I originally used .Read to achieve hence the conflict.

It would also be nice to refer to the actual column header names to retrieve the data rather than indexes such as var name = reader["applicationname"].ToString() in SQL client;

Is there perhaps a better Extension i could use to read in excel data if i can't achieve the above.

public static void DataLoadAliases(WsiContext context)
    {
        const string filePath = @"Alias Master.xlsx";

        var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        var excelReader = filePath.Contains(".xlsx")
                      ? ExcelReaderFactory.CreateOpenXmlReader(stream)
                      : ExcelReaderFactory.CreateBinaryReader(stream);

       excelReader.IsFirstRowAsColumnNames = true;


        excelReader.Read(); //skip first row

        while (excelReader.Read())
        {

            if (excelReader.Name == "Alias Master")
            {
                var aliasId = excelReader.GetInt16(0);
                var aliasName = excelReader.GetString(1);

                //Prevent blank lines coming in from excel;
                if (String.IsNullOrEmpty(aliasName)) continue;

                context.Aliases.Add(new ApplicationAlias
                {
                    AliasId = aliasId,
                    Name = aliasName,
                });
            }
            else
            {
                excelReader.NextResult();
            }
        }

        excelReader.Close();
        context.SaveChanges();
    }

Solution

for .XLSX file i use OpenXML SDK : http://www.microsoft.com/en-us/download/details.aspx?id=30425

for XLS file i use a OleDbConnection as see below :

 OleDbConnection oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath+ ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            DataTable dt = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string workSheetName = (string)dt.Rows[0]["TABLE_NAME"];

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [" + workSheetName + "]";

            oleda = new OleDbDataAdapter(cmd);

            oleda.Fill(ds, "Donnees");

            oledbConn.Close();
            return ds.Tables[0];

Comments

[LuckyS]

255 columns limitation in this oledb adapter

[malik masis]

This is a correct solution, just need to download another package (ExcelDataReader.DataSet) from NuGet and then can be used data table's abilities