ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.how to read spread sheet and add full stop in the text before inserting into Database using C# in ASP.Net<p>Hi <a class="username" rel="kavithav"> kavithav</a>,</p> <p>I am working on it. I will get back to you once done.</p>https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/Thu, 29 Sep 2016 01:42:28 GMThow to read spread sheet and add full stop in the text before inserting into Database using C# in ASP.Net<p>can anyone please help me out as it is urgent requirement and i am unable to get it as i am new to this</p>https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/Thu, 29 Sep 2016 06:14:43 GMThow to read spread sheet and add full stop in the text before inserting into Database using C# in ASP.Net<p>Hi Kavitha,</p> <p>I have created a sample which full requirement</p> <p><span style="text-decoration: underline;">HTML</span></p> <pre class="brush: html">&lt;div&gt; &lt;asp:FileUpload ID=&#34;FileUpload1&#34; runat=&#34;server&#34; /&gt; &lt;asp:Button ID=&#34;btnUpload&#34; runat=&#34;server&#34; Text=&#34;Upload&#34; OnClick=&#34;btnUpload_Click&#34; /&gt; &lt;br /&gt; &lt;asp:Label ID=&#34;Label1&#34; runat=&#34;server&#34; Text=&#34;Has Header ?&#34;&gt;&lt;/asp:Label&gt; &lt;asp:RadioButtonList ID=&#34;rbHDR&#34; runat=&#34;server&#34;&gt; &lt;asp:ListItem Text = &#34;Yes&#34; Value = &#34;Yes&#34; Selected = &#34;True&#34; &gt;&lt;/asp:ListItem&gt; &lt;asp:ListItem Text = &#34;No&#34; Value = &#34;No&#34;&gt;&lt;/asp:ListItem&gt; &lt;/asp:RadioButtonList&gt; &lt;/div&gt;</pre> <p><span style="text-decoration: underline;">C#</span></p> <pre class="brush: csharp">protected void btnUpload_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { //****************Get DataTable from you code Start******************** string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); string filePath = Server.MapPath(&#34;~/Files/&#34; + fileName); FileUpload1.SaveAs(filePath); string conStr = &#34;&#34;; switch (extension) { case &#34;.xls&#34;: //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings[&#34;Excel03ConString&#34;].ConnectionString; break; case &#34;.xlsx&#34;: //Excel 07 conStr = ConfigurationManager.ConnectionStrings[&#34;Excel07ConString&#34;].ConnectionString; break; } conStr = String.Format(conStr, filePath, rbHDR.SelectedItem.Text.Trim()); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; oda.SelectCommand = cmdExcel; oda.Fill(dt); connExcel.Close(); //****************Get DataTable from you code End******************** //Filtered Data with Header as &#39;Add&#39; if you return dataset then uncomment the below line and comment the nextline. //DataTable selectedData = dset.Tables[0].Select(&#34;Header = &#39;Add&#39;&#34;).CopyToDataTable(); DataTable selectedData = dt.Select(&#34;Header = &#39;Add&#39;&#34;).CopyToDataTable(); foreach (DataRow dr in selectedData.Rows) { string header = dr[0].ToString().Trim(); string code = dr[1].ToString().Trim(); string description = dr[2].ToString().Trim(); // if code not exist like &#34;G432J8K&#34; if (!HasData(code)) { string codeWithFullStop = code.Insert(1, &#34;.&#34;).Insert(6, &#34;.&#34;); // if code not exist like &#34;G.432J.8K&#34; if (!HasData(codeWithFullStop)) { // insert row with code value as &#34;G43.2J8K&#34; Insert(header, code.Insert(3, &#34;.&#34;), description); } } } } } // Check Record already exist or not. private bool HasData(string code) { string constr = ConfigurationManager.ConnectionStrings[&#34;conString&#34;].ConnectionString; SqlConnection con = new SqlConnection(constr); string query = &#34;SELECT * FROM ExcelTable WHERE code=&#39;&#34; + code + &#34;&#39;&#34;; SqlCommand cmd = new SqlCommand(query, con); con.Open(); object sdr = cmd.ExecuteScalar(); con.Close(); if (sdr != null) { return true; } else { return false; } } //If not present in database then Insert row. private void Insert(string header, string code, string description) { string constr = ConfigurationManager.ConnectionStrings[&#34;conString&#34;].ConnectionString; string query = &#34;INSERT INTO ExcelTable(Header,Code,Description)VALUES(@header,@code,@description)&#34;; SqlConnection con = new SqlConnection(constr); SqlCommand cmd = new SqlCommand(query, con); con.Open(); cmd.Parameters.AddWithValue(&#34;@header&#34;, header); cmd.Parameters.AddWithValue(&#34;@code&#34;, code); cmd.Parameters.AddWithValue(&#34;@description&#34;, description); cmd.ExecuteNonQuery(); con.Close(); }</pre> <p>Hope this works for you</p>https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/https://www.aspforums.net:443/Threads/111322/how-to-read-spread-sheet-and-add-full-stop-in-the-text-before-inserting-into-Database-using-C-in-ASPNet/Fri, 30 Sep 2016 03:27:21 GMT