Thursday, December 22, 2005

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.

5 comments:

Neo said...

Wonderful!!!
It worked well. Happy that i didnt have to use the COM component.
Note: One clarification i would like to make is that the path of the excel file is also to be provided. Like;
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=c:\\" + excelSheetName + ";Extended Properties=Excel 8.0;";

Raggers said...

good one .....i was wondering before how to mention worksheets ....now its clarified ......IS there any thing article to return DATASET back to excel worksheet ?

If So plz mail the like to mail2raggers@yahoo.com

phani said...

thank u

Vyas Pentakota said...

thanks for ur post, really it was very helpful. thank you very much. keep posting this kind of stuff...
i got another issue in my project its like integrating MS Offcie (word, excel). if u know plz keep in ur blog else mail to uni.sasi@gmail.com. thanks in advs.

sasankasaisekhar said...

Hi I have a requirement in my project...
Exporting the entire asp page to ms word doc and exporting multiple pages to one ms word doc and multiple ms word docs.. and the same to xml also... If u know this pls suggest a sollution...