Import Excel into Dataset

To make mass data entry, we seldom use the excel imports in application. This saves time and effort involved in the task. Here is the demonstration how to.?

1. Upload th excel file into Application Server (IIS Site Folder)

FileUpload1.SaveAs(Server.MapPath("~/xcel/") + FileUpload1.FileName);


2. Connect excel sheet, read data and import into a dataset.

DataSet myDataset = new DataSet();
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + FilePath + @";
Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable DTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//here the code will read sheet from spreadsheet with any name --generic
OleDbDataAdapter myData = new OleDbDataAdapter
(@"SELECT * FROM [" +
DTable.Rows[0]["TABLE_NAME"].ToString() + "]", conn);
//
myData.Fill(myDataset);

That is the end. The code is not restricted or limited to any name of the sheet in the excel spread sheet. ....

-::-