How to read filtered rows in Excel?

8 Dec 20234 minutes to read

The filtered rows in an Excel document can be read through RowHeight property. The filtered rows, also called as hidden rows, will have the row height as 0. The following code explains this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  IRange usedRange = worksheet.UsedRange;
  string[] rowValues = new string[usedRange.Columns.Length];

  for (int row = usedRange.Row; row <= usedRange.LastRow; row++)
  {
    //Validated the row is filtered or not
    if (worksheet.Rows[row-1].RowHeight != 0)
    {
      for (int column = usedRange.Column; column <= usedRange.LastColumn; column++)
      {
        rowValues.SetValue(worksheet.Range[row, column].Value, column-1);
      }
    }
  }
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  //Open an existing Excel file
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];
	
  IRange usedRange = worksheet.UsedRange;
  string[] rowValues = new string[usedRange.Columns.Length];

  for (int row = usedRange.Row; row <= usedRange.LastRow; row++)
  {
    //Validated the row is filtered or not
    if (worksheet.Rows[row-1].RowHeight != 0)
    {
      for (int column = usedRange.Column; column <= usedRange.LastColumn; column++)
      {
        rowValues.SetValue(worksheet.Range[row , column].Value, column-1);
      }
    }
  }
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  Dim usedRange As IRange = worksheet.UsedRange

  Dim rowValues(3) As String
  Dim row As Integer
  Dim column As Integer

  For row = usedRange.Row To usedRange.LastRow Step row + 1
    If worksheet.Rows(row - 1).RowHeight <> 0 Then
      For column = usedRange.Column To usedRange.LastColumn
        rowValues(column - 1) = worksheet.Range(row, column).Value
      Next
    End If
  Next
End Using

See Also