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.Import Excel data to HTML Table using jQuery Ajax in ASP.Net<p>Hi <a class="username" rel="sanjay8090">sanjay8090</a>,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;script type=&#34;text/javascript&#34; src=&#34;http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js&#34;&gt;&lt;/script&gt; &lt;script type=&#34;text/javascript&#34;&gt; $(function () { var reader = new FileReader(); $(&#39;input[type=file]&#39;).change(function () { if (typeof (FileReader) != &#34;undefined&#34;) { var regex = /^([a-zA-Z0-9\s_\\.\-:])+(.xls|.xlsx)$/; $($(this)[0].files).each(function () { var file = $(this); if (regex.test(file[0].name.toLowerCase())) { reader.readAsDataURL(file[0]); } else { alert(file[0].name + &#34; is not a valid image file.&#34;); return false; } }); } else { alert(&#34;This browser does not support HTML5 FileReader.&#34;); } }); $(&#39;#btnUpload&#39;).on(&#34;click&#34;, function () { var byteData = reader.result; byteData = byteData.split(&#39;;&#39;)[1].replace(&#34;base64,&#34;, &#34;&#34;); $.ajax({ type: &#34;POST&#34;, url: &#34;Default.aspx/GetExcelData&#34;, data: &#39;{byteData: &#34;&#39; + byteData + &#39;&#34; }&#39;, contentType: &#34;application/json; charset=utf-8&#34;, dataType: &#34;json&#34;, success: function (response) { var xmlDoc = $.parseXML(response.d); var xml = $(xmlDoc); var customers = xml.find(&#34;Table&#34;); $(&#34;#tblCustomers&#34;).show(); var row = $(&#34;#tblCustomers tr:last-child&#34;).removeAttr(&#34;style&#34;).clone(true); $(&#34;#tblCustomers tr&#34;).not($(&#34;#tblCustomers tr:first-child&#34;)).remove(); $.each(customers, function () { $(&#34;td&#34;, row).eq(0).html($(this).find(&#34;Id&#34;).text()); $(&#34;td&#34;, row).eq(1).html($(this).find(&#34;Name&#34;).text()); $(&#34;td&#34;, row).eq(2).html($(this).find(&#34;Country&#34;).text()); $(&#34;#tblCustomers&#34;).append(row); row = $(&#34;#tblCustomers tr:last-child&#34;).clone(true); }); }, error: function (response) { alert(response.responseText); } }); return false; }); }); &lt;/script&gt; &lt;asp:FileUpload ID=&#34;fuUpload&#34; runat=&#34;server&#34; /&gt; &lt;asp:Button ID=&#34;btnUpload&#34; Text=&#34;Upload&#34; runat=&#34;server&#34; /&gt; &lt;br /&gt; &lt;br /&gt; &lt;table id=&#34;tblCustomers&#34; style=&#34;display:none&#34;&gt; &lt;tr&gt; &lt;th&gt;Id&lt;/th&gt; &lt;th&gt;Name&lt;/th&gt; &lt;th&gt;Country&lt;/th&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt;Id&lt;/td&gt; &lt;td&gt;Name&lt;/td&gt; &lt;td&gt;Country&lt;/td&gt; &lt;/tr&gt; &lt;/table&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Data; using System.Data.OleDb; using System.IO; using System.Web.Services;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data Imports System.Data.OleDb Imports System.IO Imports System.Web.Services</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">[WebMethod] public static string GetExcelData(string byteData) { byte[] bytes = Convert.FromBase64String(byteData); string filePath = HttpContext.Current.Server.MapPath(&#34;~/Files/Test.xls&#34;); if (!Directory.Exists(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;))) { Directory.CreateDirectory(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;)); } // Save file in File folder. File.WriteAllBytes(filePath, bytes); string extension = Path.GetExtension(filePath); string excelConnectionString = &#34;&#34;; switch (extension) { case &#34;.xls&#34;: //Excel 97-03 excelConnectionString = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES;IMEX=1;&#39;&#34;; break; case &#34;.xlsx&#34;: //Excel 07 excelConnectionString = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 12.0;HDR=YES;IMEX=1;&#39;&#34;; break; } excelConnectionString = String.Format(excelConnectionString, filePath); OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oleDA = new OleDbDataAdapter(); cmdExcel.Connection = excelConnection; excelConnection.Open(); DataTable dtExcelSchema; dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); excelConnection.Close(); excelConnection.Open(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; oleDA.SelectCommand = cmdExcel; DataSet ds = new DataSet(); oleDA.Fill(ds); excelConnection.Close(); // Delete saved file. Directory.Delete(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;), true); return ds.GetXml(); }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">&lt;WebMethod()&gt; Public Shared Function GetExcelData(ByVal byteData As String) As String Dim bytes As Byte() = Convert.FromBase64String(byteData) Dim filePath As String = HttpContext.Current.Server.MapPath(&#34;~/Files/Test.xls&#34;) If Not Directory.Exists(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;)) Then Directory.CreateDirectory(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;)) End If &#39; Save file in File folder. File.WriteAllBytes(filePath, bytes) Dim extension As String = Path.GetExtension(filePath) Dim excelConnectionString As String = &#34;&#34; Select Case extension Case &#34;.xls&#34; &#39;Excel 97-03 excelConnectionString = &#34;Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&#39;Excel 8.0;HDR=YES;IMEX=1;&#39;&#34; Case &#34;.xlsx&#34; &#39;Excel 07 excelConnectionString = &#34;Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&#39;Excel 12.0;HDR=YES;IMEX=1;&#39;&#34; End Select excelConnectionString = String.Format(excelConnectionString, filePath) Dim excelConnection As OleDbConnection = New OleDbConnection(excelConnectionString) Dim cmdExcel As OleDbCommand = New OleDbCommand() Dim oleDA As OleDbDataAdapter = New OleDbDataAdapter() cmdExcel.Connection = excelConnection excelConnection.Open() Dim dtExcelSchema As DataTable dtExcelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim SheetName As String = dtExcelSchema.Rows(0)(&#34;TABLE_NAME&#34;).ToString() excelConnection.Close() excelConnection.Open() cmdExcel.CommandText = &#34;SELECT * From [&#34; &amp; SheetName &amp; &#34;]&#34; oleDA.SelectCommand = cmdExcel Dim ds As DataSet = New DataSet() oleDA.Fill(ds) excelConnection.Close() &#39; Delete saved file. Directory.Delete(HttpContext.Current.Server.MapPath(&#34;~/Files&#34;), True) Return ds.GetXml() End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshots</span></strong></p> <p><u>Excel File</u></p> <p><img src="https://www.aspsnippets.com/Handlers/DownloadFile.ashx?File=c0b637be-77f3-4147-a3c4-cc6706825286.png" alt="" /></p> <p><u>Form with Excel data</u></p> <p><img src="https://i.imgur.com/reLfsqm.gif" alt="" /></p>https://www.aspforums.net:443/Threads/159702/Import-Excel-data-to-HTML-Table-using-jQuery-Ajax-in-ASPNet/https://www.aspforums.net:443/Threads/159702/Import-Excel-data-to-HTML-Table-using-jQuery-Ajax-in-ASPNet/Wed, 28 Aug 2019 01:51:51 GMT