How to : Get Excel data into a .NET DataSet object

There may be requirement where in data in an Excel sheet has to be manipulated inside .NET Applications. Although there are many ways to achieve this (like using Excel object of COM), the most preferred way is to use the .NET Data providers to achieve this.

The following is a sample code that can be used to get Excel data into a .NET Dataset object.

The parameter excelSheetName in the below method is the name of the Excel sheet and the sheetNumber parameter indicates the number of the sheet (out of all the sheets in an Excel file).

public DataSet RetrieveExcelData(string excelSheetName,int sheetNumber)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelSheetName + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
if(i==sheetNumber)
break;
}
OleDbCommand excelCommand = new OleDbCommand("Select * from ["+excelSheets[sheetNumber-1]+"]",objConn);
OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand);
DataSet excelDataSet = new DataSet();
excelAdapter.Fill(excelDataSet);
return excelDataSet;
}
catch(OleDbException ex)
{
throw ex;
}
catch(Exception ex)
{
throw ex;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}

Through the above method, we avoid the extra reference of the COM dll and also get the flexibility of manipulating Excel shett data through ADO.NET.