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(sidsnamesmarkssaddress) 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(column1column2column3column4column5) VALUES('+col1+''+col2+''+col3+''+col4+''+col5+'); }

hamid saeed · Answer

Hi Dear how save gridview data to sql? Thanks