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.Read Header Row Cells of Excel file using OLEDB and ADO.Net in C# and VB.Net<p>Hi <a class="username" rel="alibasha"> alibasha</a>,</p> <p>Check this example. Now please take its reference and correct your code.</p> <p>For reading Excel file you can refer below article.</p> <p><a href="https://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-using-ADO.Net-and-C.aspx">Read and Import Excel Sheet using ADO.Net and C#</a></p> <p><span style="text-decoration: underline;">The Excel File</span></p> <p><img src="https://i.imgur.com/dYmBQGk.jpg" alt="" width="344" height="255" /></p> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;asp:Button Text=&#34;Export&#34; runat=&#34;server&#34; OnClick=&#34;Export&#34; /&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.Collections.Generic; using System.Data; using System.Data.OleDb;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Data.OleDb Imports System.Data</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Export(object sender, EventArgs e) { DataSet ds = ImportExcel(Server.MapPath(&#34;~/Book1.xls&#34;)); //DataSet ds = ImportExcel2007(Server.MapPath(&#34;~/Book1.xlsx&#34;)); List&lt;string&gt; id = new List&lt;string&gt;(); List&lt;string&gt; name = new List&lt;string&gt;(); List&lt;string&gt; country = new List&lt;string&gt;(); int idIndex = 0; int nameIndex = 0; int countryIndex = 0; for (int i = 0; i &lt; ds.Tables[0].Columns.Count; i++) { if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == &#34;ID&#34;) { idIndex = i; } else if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == &#34;NAME&#34;) { nameIndex = i; } else if (ds.Tables[0].Rows[0][i].ToString().ToUpper() == &#34;COUNTRY&#34;) { countryIndex = i; } } for (int i = 1; i &lt; ds.Tables[0].Rows.Count; i++) { id.Add(ds.Tables[0].Rows[i][idIndex].ToString()); name.Add(ds.Tables[0].Rows[i][nameIndex].ToString()); country.Add(ds.Tables[0].Rows[i][countryIndex].ToString()); } } //Read To Excel 97-2003 File private DataSet ImportExcel(String strFilePath) { String strExcelConn = &#34;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&#34; + strFilePath + &#34;; Extended Properties=&#39;Excel 8.0;IMEX=1;HDR=No&#39;&#34;; OleDbConnection connExcel = new OleDbConnection(strExcelConn); OleDbCommand cmdExcel = new OleDbCommand(); try { cmdExcel.Connection = connExcel; //Check if the Sheet Exists connExcel.Open(); DataTable dtExcelSchema; //Get the Schema of the WorkBook dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); connExcel.Close(); //Read Data from Sheet1 connExcel.Open(); OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; //Range Query //cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;A3:B5]&#34;; da.SelectCommand = cmdExcel; da.Fill(ds); connExcel.Close(); return ds; } catch { return null; } finally { cmdExcel.Dispose(); connExcel.Dispose(); } } //Read To Excel 97-2007 File private DataSet ImportExcel2007(String strFilePath) { String strExcelConn = &#34;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=&#34; + strFilePath + &#34;; Extended Properties=&#39;Excel 8.0;IMEX=1;HDR=No&#39;&#34;; OleDbConnection connExcel = new OleDbConnection(strExcelConn); OleDbCommand cmdExcel = new OleDbCommand(); try { cmdExcel.Connection = connExcel; //Check if the Sheet Exists connExcel.Open(); DataTable dtExcelSchema; //Get the Schema of the WorkBook dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); connExcel.Close(); //Read Data from Sheet1 connExcel.Open(); OleDbDataAdapter da = new OleDbDataAdapter(); DataSet ds = new DataSet(); string SheetName = dtExcelSchema.Rows[0][&#34;TABLE_NAME&#34;].ToString(); cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;]&#34;; //Range Query //cmdExcel.CommandText = &#34;SELECT * From [&#34; + SheetName + &#34;A3:B5]&#34;; da.SelectCommand = cmdExcel; da.Fill(ds); connExcel.Close(); return ds; } catch { return null; } finally { cmdExcel.Dispose(); connExcel.Dispose(); } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs) Dim ds As DataSet = ImportExcel(Server.MapPath(&#34;~/Book1.xls&#34;)) Dim id As List(Of String) = New List(Of String)() Dim name As List(Of String) = New List(Of String)() Dim country As List(Of String) = New List(Of String)() Dim idIndex As Integer = 0 Dim nameIndex As Integer = 0 Dim countryIndex As Integer = 0 For i As Integer = 0 To ds.Tables(0).Columns.Count - 1 If ds.Tables(0).Rows(0)(i).ToString().ToUpper() = &#34;ID&#34; Then idIndex = i ElseIf ds.Tables(0).Rows(0)(i).ToString().ToUpper() = &#34;NAME&#34; Then nameIndex = i ElseIf ds.Tables(0).Rows(0)(i).ToString().ToUpper() = &#34;COUNTRY&#34; Then countryIndex = i End If Next For i As Integer = 1 To ds.Tables(0).Rows.Count - 1 id.Add(ds.Tables(0).Rows(i)(idIndex).ToString()) name.Add(ds.Tables(0).Rows(i)(nameIndex).ToString()) country.Add(ds.Tables(0).Rows(i)(countryIndex).ToString()) Next End Sub Private Function ImportExcel(ByVal strFilePath As String) As DataSet Dim strExcelConn As String = &#34;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&#34; &amp; strFilePath &amp; &#34;; Extended Properties=&#39;Excel 8.0;IMEX=1;HDR=No&#39;&#34; Dim connExcel As OleDbConnection = New OleDbConnection(strExcelConn) Dim cmdExcel As OleDbCommand = New OleDbCommand() Try cmdExcel.Connection = connExcel connExcel.Open() Dim dtExcelSchema As DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) connExcel.Close() connExcel.Open() Dim da As OleDbDataAdapter = New OleDbDataAdapter() Dim ds As DataSet = New DataSet() Dim SheetName As String = dtExcelSchema.Rows(0)(&#34;TABLE_NAME&#34;).ToString() cmdExcel.CommandText = &#34;SELECT * From [&#34; &amp; SheetName &amp; &#34;]&#34; da.SelectCommand = cmdExcel da.Fill(ds) connExcel.Close() Return ds Catch Return Nothing Finally cmdExcel.Dispose() connExcel.Dispose() End Try End Function Private Function ImportExcel2007(ByVal strFilePath As String) As DataSet Dim strExcelConn As String = &#34;Provider=Microsoft.ACE.OLEDB.12.0; Data Source=&#34; &amp; strFilePath &amp; &#34;; Extended Properties=&#39;Excel 8.0;IMEX=1;HDR=No&#39;&#34; Dim connExcel As OleDbConnection = New OleDbConnection(strExcelConn) Dim cmdExcel As OleDbCommand = New OleDbCommand() Try cmdExcel.Connection = connExcel connExcel.Open() Dim dtExcelSchema As DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) connExcel.Close() connExcel.Open() Dim da As OleDbDataAdapter = New OleDbDataAdapter() Dim ds As DataSet = New DataSet() Dim SheetName As String = dtExcelSchema.Rows(0)(&#34;TABLE_NAME&#34;).ToString() cmdExcel.CommandText = &#34;SELECT * From [&#34; &amp; SheetName &amp; &#34;]&#34; da.SelectCommand = cmdExcel da.Fill(ds) connExcel.Close() Return ds Catch Return Nothing Finally cmdExcel.Dispose() connExcel.Dispose() End Try End Function</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <p><img src="https://i.imgur.com/LNIuPOX.jpg" alt="" width="318" height="675" /></p>https://www.aspforums.net:443/Threads/154978/Read-Header-Row-Cells-of-Excel-file-using-OLEDB-and-ADONet-in-C-and-VBNet/https://www.aspforums.net:443/Threads/154978/Read-Header-Row-Cells-of-Excel-file-using-OLEDB-and-ADONet-in-C-and-VBNet/Mon, 08 Jan 2018 03:29:51 GMT