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()); } }