How to expand excel data to dataset & store in database

Question

I have to upload excel to my application & expand the ex pand the excel to store in database.

Seshu Kumar · Answer

Modify the Business Entity and Stored Procedure according to ur requirement. I protected void btnUpload_Click(object sender, EventArgs e) { try { if (fluImort.HasFile) { string extension = System.IO.Path.GetExtension(fluImort.FileName); if (extension.ToUpper() != ".XLSX" && extension.ToUpper() != ".XLS") { lblMsg.Text = "Upload .xls only"; lblMsg.ForeColor = System.Drawing.Color.Red; return; } string strFilePath = string.Empty; string strFileName = Session["EmpId"].ToString() + DateTime.Now.Millisecond + "_" + fluImort.FileName; strFilePath = Server.MapPath("~/Application/OPManagement/UploadProspectData/" + strFileName); if (!File.Exists(strFilePath.ToString())) { fluImort.SaveAs(strFilePath); string query = null; string connString = ""; // string strFileName = Session["EmpId"].ToString() + DateTime.Now.Millisecond + "_" + fluImort.FileName; string strFileType = System.IO.Path.GetExtension(strFileName).ToString().ToLower(); string strNewPath = Server.MapPath("~/Application/OPManagement/UploadProspectData/" + strFileName); //Connection String to Excel Workbook if (strFileType.Trim() == ".xls") connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; else if (strFileType.Trim() == ".xlsx") connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; query = "SELECT * FROM [Sheet1$]"; //Create the connection object conn = new OleDbConnection(connString); //Open connection if (conn.State == ConnectionState.Closed) conn.Open(); //Create the command object cmd = new OleDbCommand(query, conn); da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); BE_OP_Registration beopreg = new BE_OP_Registration(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Columns[1].ColumnName != "Customer Name" || ds.Tables[0].Columns[2].ColumnName != "Husband Name" || ds.Tables[0].Columns[3].ColumnName != "Mobile" || ds.Tables[0].Columns[4].ColumnName != "ORW Name" || ds.Tables[0].Columns[5].ColumnName != "Contact Date(dd-mm-yyyy)") { lblMsg.Text = "Upload correct format of xls sheet"; lblMsg.ForeColor = System.Drawing.Color.Red; return; } foreach (DataRow dr in ds.Tables[0].Rows) { if (dr["Mobile"].ToString() != string.Empty) { beopreg.FacilityId = Convert.ToInt32(Session["FacilityId"].ToString()); beopreg.CustomerName = dr["Customer Name"].ToString().ToUpper(); beopreg.HusbandName = dr["Husband Name"].ToString().ToUpper(); beopreg.Mobile = dr["Mobile"].ToString().ToUpper(); beopreg.ORWName = dr["ORW Name"].ToString().ToUpper(); beopreg.CreatedBy = Session["UserId"].ToString(); if (dr["Contact Date(dd-mm-yyyy)"].ToString() != string.Empty) beopreg.EddDate = Convert.ToDateTime(dr["Contact Date(dd-mm-yyyy)"].ToString().ToUpper()); else beopreg.EddDate = null; DataSet Ds = new DataSet(); Ds = new BL_OP_Registration().InsertOrUpdateProcepectData(beopreg); if (Ds != null && Ds.Tables.Count > 0 && Ds.Tables[0].Rows.Count > 0) { if (Ds.Tables[0].Rows[0]["MSG"].ToString() == "Inserted") { lblMsg.Text = "Data saved successfully"; lblMsg.ForeColor = System.Drawing.Color.Green; ClearData(); } if (Ds.Tables[0].Rows[0]["MSG"].ToString() == "Exists") { lblMsg.Text = dr["Customer Name"].ToString() + " details already exists"; lblMsg.ForeColor = System.Drawing.Color.Red; ClearData(); } } else { lblSaveMsg.Text = "Failed to save data"; lblSaveMsg.ForeColor = System.Drawing.Color.Red; } } } } else { lblMsg.Text = "No Data found"; lblMsg.ForeColor = System.Drawing.Color.Red; } da.Dispose(); conn.Close(); conn.Dispose(); } } else { lblMsg.Text = "Select file to upload"; lblMsg.ForeColor = System.Drawing.Color.Red; } } catch (Exception ex) { WriteLogItem(ex.Message, ex.StackTrace, Session["UserID"].ToString()); } }

How to expand excel data to dataset & store in database

Answers (1)