Fatching Data From Excel Sheet to Dataset in C#

Hi Friends

Here are the few steps for fetching  data from excel sheet into a DataSet in C# . you simply flow the steps and get the Data into dataset.

Step 1 :- In first step we have to  save that Excel file into App_Data folder .we need to write the code like this,

if (uploadExel.HasFile)
{
string sName = Excel.FileName;
// get the extension of the excel sheep
string ext = System.IO.Path.GetExtension(sName);
if (ext == ".xls")
{
uploadExel.SaveAs(Server.MapPath("../App_Data/" + "Books" + ext)); //Here we save the file into App_Data

}

}

Step 2 :-Now we have to create a DataSet which is use to  hold values from Excelsheet.

DataSet dsExcel = new DataSet();


Step 3 :- Now connect with excel sheet using OLEDB data adapter to fatch all the records

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|Books.xls;Extended Properties=Excel 8.0");
public DataSet getExcelData()
{

OleDbDataAdapter da = new OleDbDataAdapter("select * from [ExcelFileName$]", connection);
DataSet dsExcel  = new DataSet();
try
{
da.Fill(dsExcel , " ExcelFileName ");
}
catch (Exception ex)
{
 dsExcel  = null;
}
finally
{
con.Close();
}
return dsExcel ;

}

Step 4: - Now call the above method and assign it to dataset and put a loop to get the values

if (dsExcel  == null)
{
lblMsg.Text = "No Data us Return or file Name is not Present. ";
return;
}
else
{
int count = dsExcel .Tables[0].Rows.Count;
for (int i = 0; i < count; i++)
{

// Here We can Get the Data from Excel Sheet..
}
}

}

}

Hope this will help you 

Regards,

Rajesh