How to get Excel file to the dataset

Question

Dim reportData As New DataSet()
strFileName = upload.PostedFile.FileName
reportData.ReadXml(strFileName)
the above code is used to read XML file and store in DATASET
like this How can we get EXCEL file into the dataset

Post solution for this post

Thanks in advance

Satyapriya Nayak · Answer

Hi Rajkumar Try this... Untitled Page Text=Upload Excel Spreadsheet OnClick=ButtonUpload_Click /> Text=View Excel Data OnClick=ButtonView_Click /> Please select an Excel file to import: Text=Upload File OnClick=ButtonUploadFile_Click /> BorderStyle=None BorderWidth=1px CellPadding=3 GridLines=Vertical> using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb; using System.Data.SqlClient; namespace Excel_file_to_the_dataset1 { public partial class _Default : System.Web.UI.Page { protected void ButtonUploadFile_Click(object sender EventArgs e) { if (FileUploadExcel.HasFile) { try { FileUploadExcel.SaveAs(Server.MapPath(~/ExcelImport.xls)); LabelUpload.Text = Upload File Name:  + FileUploadExcel.PostedFile.FileName +   + Type:  + FileUploadExcel.PostedFile.ContentType +  File Size:  + FileUploadExcel.PostedFile.ContentLength +  kb ; } catch (System.NullReferenceException ex) { LabelUpload.Text = Error:  + ex.Message; } } else { LabelUpload.Text = Please select a file to upload.; } } protected OleDbCommand ExcelConnection() { string xConnStr = Provider=Microsoft.Jet.OLEDB.4.0; + Data Source= + Server.MapPath(ExcelImport.xls) + ; + Extended Properties=Excel 8.0;; OleDbConnection objXConn = new OleDbConnection(xConnStr); objXConn.Open(); OleDbCommand objCommand = new OleDbCommand(SELECT * FROM [sheet1$] objXConn); return objCommand; } protected void ButtonUpload_Click(object sender System.EventArgs e) { PanelUpload.Visible = true; PanelView.Visible = false; PanelImport.Visible = false; } protected void ButtonView_Click(object sender System.EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = true; PanelImport.Visible = false; OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); objDataAdapter.SelectCommand = ExcelConnection(); DataSet objDataSet = new DataSet(); objDataAdapter.Fill(objDataSet); GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView; GridViewExcel.DataBind(); } } } Thanks If this post helps you mark it as answer

Satyapriya Nayak · Answer

Hi Rajkumar Try this... Untitled Page .style1 { width: 50%; } Insert Data View Data using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text; public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; protected void insertdata_Click(object sender EventArgs e) { OleDbConnection conn = new OleDbConnection(@Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + Server.MapPath(test.xls) + ;Extended Properties=Excel 8.0); OleDbCommand com = new OleDbCommand(select * from [Sheet1$] conn); conn.Open(); OleDbDataReader reader = com.ExecuteReader(); string Name = ; string Address = ; string salary = ; while (reader.Read()) { Name = valid(reader 0); Address = valid(reader 1); salary = valid(reader 2); insertdataintosql(Name Address salary); } conn.Close(); l1.Text = Records Inserted successfully; } protected string valid(OleDbDataReader reader int str1) { object val = reader[str1]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string Name string Address string salary) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = insert into employee(NameAddresssalary)values(@Name@Address@salary); com.Parameters.Add(@Name SqlDbType.NVarChar).Value = Name; com.Parameters.Add(@Address SqlDbType.NVarChar).Value = Address; com.Parameters.Add(@salary SqlDbType.Int).Value = Convert.ToInt32(salary); com.CommandType = CommandType.Text; conn.Open(); com.ExecuteNonQuery(); conn.Close(); } protected void viewdata_Click(object sender EventArgs e) { l1.Text =; SqlConnection conn = new SqlConnection(connStr); SqlDataAdapter sqlda = new SqlDataAdapter(SELECT * from employee conn); DataSet ds = new DataSet(); sqlda.Fill(dsemployee); g1.DataSource = ds; g1.DataMember = employee; g1.DataBind(); } } Thanks

Prasant Jinaga · Answer

Hi Rajkumar Refer the below link it will solve your problem http://www.codeproject.com/KB/office/ExcelToDataset.aspx Thanks Parsant