Question About Gridview

Question

Hi Dear Friends, How import excel file to gridvie and then save to sql server? Thanks, Best Regards,

Senthilkumar · Answer

Hi, private void btnImportExcelToGrid_Click(object sender, System.EventArgs e) { String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\Book2.xls;" + "Extended Properties=Excel 8.0;"; DataSet ds = new DataSet(); //You must use the $ after the object //you reference in the spreadsheet OleDbDataAdapter da = new OleDbDataAdapter ("SELECT * FROM [Sheet1$]", strConn); //da.TableMappings.Add("Table", "ExcelTest"); da.Fill(ds); DataGrid2.DataSource = ds.Tables[0].DefaultView; DataGrid2.DataBind(); } http://wiki.asp.net/page.aspx/825/loading-excel-data-into-a-gridview/ http://www.dotneter.com/import-excel-file-into-datagridview http://dotnetguts.blogspot.in/2006/10/import-excel-to-datagrid-aspnet.html

Satyapriya Nayak · Answer

Hi Hamid, Try this... Untitled Page Text="Upload Excel Files" 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" CellSpacing="2"> Text="Insert to Db" /> 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 Show_Excel_gridview_insert_to_db { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlCommand com; 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 ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection Con = new OleDbConnection(ConnStr); Con.Open(); OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con); return com; } protected void ButtonView_Click(object sender, System.EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = true; PanelImport.Visible = false; OleDbDataAdapter oledbda = new OleDbDataAdapter(); oledbda.SelectCommand = ExcelConnection(); DataSet ds = new DataSet(); oledbda.Fill(ds); GridViewExcel.DataSource = ds.Tables[0].DefaultView; GridViewExcel.DataBind(); } protected void ButtonUpload_Click(object sender, System.EventArgs e) { PanelUpload.Visible = true; PanelView.Visible = false; PanelImport.Visible = false; } protected void btn_insert_Click(object sender, EventArgs e) { foreach (GridViewRow g1 in GridViewExcel.Rows) { SqlConnection con = new SqlConnection(connStr); com = new SqlCommand("insert into student(sid,sname,smarks,saddress) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con); con.Open(); com.ExecuteNonQuery(); con.Close(); } Label1.Text = "Records inserted successfully"; } } } Thanks If this post helps you mark it as answer

hamid saeed · Answer

Hi My Friends, Thanks for your help, Best Regards,

Senthilkumar · Answer

Hi, You no need to save the grid view. The data is stored in the data table and which binded with the gridview control. You need to iterate the datatable and insert the record into sql server. foreach (DataRow drow in ds.Tables[0].Rows) { string col1 = drow[0].ToString(); string col2 = drow[1].ToString(); string col3 = drow[2].ToString(); string col4 = drow[3].ToString(); string col5 = drow[4].ToString(); string _sqlInsert = "INSERT INTO Table1(column1,column2,column3,column4,column5) VALUES('"+col1+"','"+col2+"','"+col3+"','"+col4+"','"+col5+"')"; }

hamid saeed · Answer

Hi Dear, how save gridview data to sql? Thanks,

Question About Gridview

Answers (5)