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
- How to open an existing XLSX workbook and save it as XLS?
- How to create and open Excel Template files by using XlsIO?
- How to copy a range from one workbook to another?
- Does XlsIO support Excel files with macros that are digitally signed?
- How does Excel file with uninstalled fonts is converted to PDF/Image?
- How to sort two or more columns in a pivot table?
- How to move or copy a worksheet?