How to search a value in only specific columns of an Excel worksheet?
8 Dec 20233 minutes to read
XlsIO allows searching for a value in different columns in an Excel worksheet through the FindAll method. The following code illustrates 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];
IRanges ranges = worksheet.CreateRangesCollection();
//Add different ranges to the Range collection.
ranges.Add(worksheet.Range["C1"].EntireColumn);
ranges.Add(worksheet.Range["Q1"].EntireColumn);
ranges.Add(worksheet.Range["AA1"].EntireColumn);
IRange[] result = ranges.FindAll(90, ExcelFindType.Number);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IRanges ranges = worksheet.CreateRangesCollection();
//Add different ranges to the Range collection.
ranges.Add(worksheet.Range["C1"].EntireColumn);
ranges.Add(worksheet.Range["Q1"].EntireColumn);
ranges.Add(worksheet.Range["AA1"].EntireColumn);
IRange[] result = ranges.FindAll(90, ExcelFindType.Number);
}
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")
'Access first worksheet from the workbook instance
Dim worksheet As IWorksheet = workbook.Worksheets(0)
Dim ranges As IRanges = worksheet.CreateRangesCollection()
'Add different ranges to the Range collection.
ranges.Add(worksheet.Range("C1").EntireColumn)
ranges.Add(worksheet.Range("Q1").EntireColumn)
ranges.Add(worksheet.Range("AA1").EntireColumn)
Dim result() As IRange = ranges.FindAll(90, ExcelFindType.Number)
End Using
See Also
- How to perform Find and Replace?
- How to get entire column of the particular range?
- How to define discontinuous ranges?
- 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 to move or copy a worksheet?