ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Retrieve Filtered Excel sheet Data using C# and VB.Net in ASP.Net<p>Hi&nbsp;hendrix169,</p> <p>Check this example. Now please take its reference and correct your code. Here i am retriving the rows and displaying in GridView.</p> <p>For this example i have used&nbsp;OpenXml and ClosedXml Libraries.</p> <p>You can download the libraries using the following download locations.</p> <p><a href="" target="_blank">Download OpenXml SDK 2.0</a></p> <p><a href="" target="_blank">Download ClosedXml Library</a></p> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;table&gt; &lt;tr&gt; &lt;td style=&#34;vertical-align: top&#34;&gt; &lt;asp:GridView runat=&#34;server&#34; ID=&#34;gvCustomersFiltered&#34; Caption=&#34;&lt;b&gt;Filtered Records&lt;/b&gt;&#34; /&gt; &lt;/td&gt; &lt;td style=&#34;vertical-align: top&#34;&gt; &lt;asp:GridView runat=&#34;server&#34; ID=&#34;gvCustomersHidden&#34; Caption=&#34;&lt;b&gt;Hidden Records&lt;/b&gt;&#34; /&gt; &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.Collections.Generic; using System.Data; using System.Linq; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.Collections.Generic Imports System.Data Imports System.Linq Imports DocumentFormat.OpenXml.Packaging Imports DocumentFormat.OpenXml.Spreadsheet</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { DataSet ds = GetRowsOrCols(@&#34;C:\Test.xlsx&#34;, &#34;Sheet1&#34;); gvCustomersFiltered.DataSource = ds.Tables[&#34;Filtered&#34;]; gvCustomersFiltered.DataBind(); gvCustomersHidden.DataSource = ds.Tables[&#34;Hidden&#34;]; gvCustomersHidden.DataBind(); } public DataSet GetRowsOrCols(string fileName, string sheetName) { DataSet ds = new DataSet(); using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants&lt;Sheet&gt;().Where((s) =&gt; s.Name == sheetName).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException(&#34;sheetName&#34;); } else { WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; // Filtered Rows. DataTable dtFiltered = new DataTable(); dtFiltered.TableName = &#34;Filtered&#34;; IEnumerable&lt;Row&gt; filteredRows = ws.Descendants&lt;Row&gt;().Where((r) =&gt; r.Hidden == null); // UnFiltered Rows. DataTable dtHidden = new DataTable(); dtHidden.TableName = &#34;Hidden&#34;; IEnumerable&lt;Row&gt; hiddenRows = ws.Descendants&lt;Row&gt;().Where((r) =&gt; r.Hidden != null &amp;&amp; r.Hidden.Value); foreach (Cell cell in filteredRows.ElementAt(0)) { dtFiltered.Columns.Add(GetCellValue(document, cell)); dtHidden.Columns.Add(GetCellValue(document, cell)); } // Filtered Cells Value. foreach (Row filteredRow in filteredRows) { if (filteredRow.RowIndex &gt; 1) { DataRow dataRow = dtFiltered.NewRow(); for (int i = 0; i &lt; filteredRow.Descendants&lt;Cell&gt;().Count(); i++) { dataRow[i] = GetCellValue(document, filteredRow.Descendants&lt;Cell&gt;().ElementAt(i)); } dtFiltered.Rows.Add(dataRow); } } // Hidden Cells Value. foreach (Row hiddenRow in hiddenRows) { if (hiddenRow.RowIndex &gt; 1) { DataRow dataRow = dtHidden.NewRow(); for (int i = 0; i &lt; hiddenRow.Descendants&lt;Cell&gt;().Count(); i++) { dataRow[i] = GetCellValue(document, hiddenRow.Descendants&lt;Cell&gt;().ElementAt(i)); } dtHidden.Rows.Add(dataRow); } } ds.Tables.Add(dtFiltered); ds.Tables.Add(dtHidden); } } return ds; } private static string GetCellValue(SpreadsheetDocument document, Cell cell) { SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart; string value = cell.CellValue.InnerXml; if (cell.DataType != null &amp;&amp; cell.DataType.Value == CellValues.SharedString) { return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText; } else { return value; } }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load Dim ds As DataSet = GetRowsOrCols(&#34;C:\Test.xlsx&#34;, &#34;Sheet1&#34;) gvCustomersFiltered.DataSource = ds.Tables(&#34;Filtered&#34;) gvCustomersFiltered.DataBind() gvCustomersHidden.DataSource = ds.Tables(&#34;Hidden&#34;) gvCustomersHidden.DataBind() End Sub Public Function GetRowsOrCols(ByVal fileName As String, ByVal sheetName As String) As DataSet Dim ds As DataSet = New DataSet() Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False) Dim wbPart As WorkbookPart = document.WorkbookPart Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = sheetName).FirstOrDefault() If theSheet Is Nothing Then Throw New ArgumentException(&#34;sheetName&#34;) Else Dim wsPart As WorksheetPart = CType((wbPart.GetPartById(theSheet.Id)), WorksheetPart) Dim ws As Worksheet = wsPart.Worksheet Dim dtFiltered As DataTable = New DataTable() dtFiltered.TableName = &#34;Filtered&#34; Dim filteredRows As IEnumerable(Of Row) = ws.Descendants(Of Row)().Where(Function(r) r.Hidden Is Nothing) Dim dtHidden As DataTable = New DataTable() dtHidden.TableName = &#34;Hidden&#34; Dim hiddenRows As IEnumerable(Of Row) = ws.Descendants(Of Row)().Where(Function(r) r.Hidden IsNot Nothing AndAlso r.Hidden.Value) For Each cell As Cell In filteredRows.ElementAt(0) dtFiltered.Columns.Add(GetCellValue(document, cell)) dtHidden.Columns.Add(GetCellValue(document, cell)) Next For Each filteredRow As Row In filteredRows If filteredRow.RowIndex.ToString() &lt;&gt; &#34;1&#34; Then Dim dataRow As DataRow = dtFiltered.NewRow() For i As Integer = 0 To filteredRow.Descendants(Of Cell)().Count() - 1 dataRow(i) = GetCellValue(document, filteredRow.Descendants(Of Cell)().ElementAt(i)) Next dtFiltered.Rows.Add(dataRow) End If Next For Each hiddenRow As Row In hiddenRows If hiddenRow.RowIndex.ToString() &lt;&gt; &#34;1&#34; Then Dim dataRow As DataRow = dtHidden.NewRow() For i As Integer = 0 To hiddenRow.Descendants(Of Cell)().Count() - 1 dataRow(i) = GetCellValue(document, hiddenRow.Descendants(Of Cell)().ElementAt(i)) Next dtHidden.Rows.Add(dataRow) End If Next ds.Tables.Add(dtFiltered) ds.Tables.Add(dtHidden) End If End Using Return ds End Function Private Shared Function GetCellValue(ByVal document As SpreadsheetDocument, ByVal cell As Cell) As String Dim stringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart Dim value As String = cell.CellValue.InnerXml If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then Return stringTablePart.SharedStringTable.ChildElements(Int32.Parse(value)).InnerText Else Return value End If End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshots</span></strong></p> <p><strong>The Fltered Excel</strong></p> <p><strong><img src="" alt="" width="400" height="437" /></strong></p> <p><strong>Output</strong></p> <p><strong><img src="" alt="" width="472" height="344" /></strong></p>, 08 Mar 2019 05:52:17 GMT