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