Retrieve Filtered Excel sheet Data using C# and VB.Net in ASP.Net

Hi hendrix169,

Check this example. Now please take its reference and correct your code. Here i am retriving the rows and displaying in GridView.

For this example i have used OpenXml and ClosedXml Libraries.

You can download the libraries using the following download locations.

Download OpenXml SDK 2.0

Download ClosedXml Library

HTML

<table>
    <tr>
        <td style="vertical-align: top">
            <asp:GridView runat="server" ID="gvCustomersFiltered" Caption="<b>Filtered Records</b>" />
        </td>
        <td style="vertical-align: top">
            <asp:GridView runat="server" ID="gvCustomersHidden" Caption="<b>Hidden Records</b>" />
        </td>
    </tr>
</table>

Namespaces

C#

using System.Collections.Generic;
using System.Data;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

VB.Net

Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataSet ds = GetRowsOrCols(@"C:\Test.xlsx", "Sheet1");
    gvCustomersFiltered.DataSource = ds.Tables["Filtered"];
    gvCustomersFiltered.DataBind();
    gvCustomersHidden.DataSource = ds.Tables["Hidden"];
    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<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
        if (theSheet == null)
        {
            throw new ArgumentException("sheetName");
        }
        else
        {
            WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
            Worksheet ws = wsPart.Worksheet;
            // Filtered Rows.
            DataTable dtFiltered = new DataTable();
            dtFiltered.TableName = "Filtered";
            IEnumerable<Row> filteredRows = ws.Descendants<Row>().Where((r) => r.Hidden == null);

            // UnFiltered Rows.
            DataTable dtHidden = new DataTable();
            dtHidden.TableName = "Hidden";
            IEnumerable<Row> hiddenRows = ws.Descendants<Row>().Where((r) => r.Hidden != null && 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 > 1)
                {
                    DataRow dataRow = dtFiltered.NewRow();
                    for (int i = 0; i < filteredRow.Descendants<Cell>().Count(); i++)
                    {
                        dataRow[i] = GetCellValue(document, filteredRow.Descendants<Cell>().ElementAt(i));
                    }

                    dtFiltered.Rows.Add(dataRow);
                }
            }
            // Hidden Cells Value.
            foreach (Row hiddenRow in hiddenRows)
            {
                if (hiddenRow.RowIndex > 1)
                {
                    DataRow dataRow = dtHidden.NewRow();
                    for (int i = 0; i < hiddenRow.Descendants<Cell>().Count(); i++)
                    {
                        dataRow[i] = GetCellValue(document, hiddenRow.Descendants<Cell>().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 && cell.DataType.Value == CellValues.SharedString)
    {
        return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
    }
    else
    {
        return value;
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim ds As DataSet = GetRowsOrCols("C:\Test.xlsx", "Sheet1")
    gvCustomersFiltered.DataSource = ds.Tables("Filtered")
    gvCustomersFiltered.DataBind()
    gvCustomersHidden.DataSource = ds.Tables("Hidden")
    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("sheetName")
        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 = "Filtered"
            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 = "Hidden"
            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() <> "1" 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() <> "1" 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

Screenshots

The Fltered Excel

Output