Reading data from Excel First Sheet - C#

Question

There are 2 Excel Sheets - FileA & FileB.

Only one sheet in FileA. It has data & is named 'Sep'. No Hidden sheets for sure.

FileB has 3 sheets. The first one has data & is named 'Sheet1'.

Testing with FileA, I wrote this code and it works fine. Problem occured with FileB. When checking the FileA schema in code, I see that 'SEP' showed up as Row2. So, wrote this to get the firstsheet's name

string FirstSheetName = dtSchema.Rows[2]["TABLE_NAME"].ToString();

But for FileB, Sheet1 shows up as first row in schema.

1. Why is this different for different sheets.

2. What should I write, so that i always get the data from firstsheet that has data.

    private void GetDataFromExcel()
        {
          DataSet dsExcel = null;
           if (true)
            {
               OleDbCommand cmdSelect = null;
               OleDbDataAdapter daXls = null;
               OleDbConnection xlsCSV = null;            
               DataTable dtSchema = null;
               try
                {
                 xlsCSV = new OleDbConnection(ExcelFileConnString);
   
         if (xlsCSV.State == ConnectionState.Closed)
          {
            xlsCSV.Open();
          }
   
         dtSchema = xlsCSV.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
                                                     null); 
         //Screenshots of dtSchema attached.                   
         if (dtSchema.Rows.Count > 0)
          {
            string FirstSheetName = dtSchema.Rows[2]["TABLE_NAME"]
                                         .ToString();
            cmdSelect = new OleDbCommand("select * from [" + 
                                                 FirstSheetName + "]", 
                                                 xlsCSV);
            daXls = new OleDbDataAdapter();
            daXls.SelectCommand = cmdSelect;
            dsExcel = new DataSet();
            daXls.Fill(dsExcel);
            xlsCSV.Close();
          }
   
        int count = dsExcel.Tables[0].Rows.Count;
       }
       catch (Exception)
        {
   
        }
       finally
        {
          if (xlsCSV != null && xlsCSV.State == ConnectionState.Open)
            {
              xlsCSV.Close();
              xlsCSV.Dispose();
            }
          if (cmdSelect != null)
            {
              cmdSelect.Dispose();
            }
        }
       }        
   }
   

   

Answer 1

Try to use the library ExcelDataReader. You can read and save Excel files in older format (.xls) and new one (.xlsx).

With that you don't need to have any Office component installed and it's very simple to use. I'm using since January 2014 and I've been loving it. You can get it from CodePlex or GitHub

If you are working only with .xlsx files and you need something more advanced you can try Microsoft Office Open XML SDK. It's no so simple but allows you to do anything - or almost - with an Excel files.