How to find values with a matching case for specific column in Excel?

8 Dec 20236 minutes to read

XlsIO allows finding values with matching case for specific column in Excel worksheet using MatchCase option of ExcelFindOptions enumeration through Find method of WorksheetImpl. 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];

  IRange[] range1 = (worksheet as WorksheetImpl).Find(worksheet.Range["C1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRange[] range2 = (worksheet as WorksheetImpl).Find(worksheet.Range["Q1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRange[] range3 = (worksheet as WorksheetImpl).Find(worksheet.Range["AA1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRanges ranges = worksheet.CreateRangesCollection();

  for (int range = 0; range < range1.Length; range++)
    ranges.Add(range1[range]);
  for (int range = 0; range < range2.Length; range++)
    ranges.Add(range2[range]);
  for (int range = 0; range < range3.Length; range++)
    ranges.Add(range3[range]);
}
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];                             

  IRange[] range1 = (worksheet as WorksheetImpl).Find(worksheet.Range["C1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRange[] range2 = (worksheet as WorksheetImpl).Find(worksheet.Range["Q1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRange[] range3 = (worksheet as WorksheetImpl).Find(worksheet.Range["AA1"].EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, false);
  IRanges ranges = worksheet.CreateRangesCollection();

  for (int range = 0; range < range1.Length; range++)
    ranges.Add(range1[range]);
  for (int range = 0; range < range2.Length; range++)
    ranges.Add(range2[range]);
  for (int range = 0; range < range3.Length; range++)
    ranges.Add(range3[range]);   
}
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 sheet As IWorksheet = workbook.Worksheets(0)

  Dim range1 As IRange() = (TryCast(sheet, WorksheetImpl)).Find(sheet.Range("C1").EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, False)
  Dim range2 As IRange() = (TryCast(sheet, WorksheetImpl)).Find(sheet.Range("Q1").EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, False)
  Dim range3 As IRange() = (TryCast(sheet, WorksheetImpl)).Find(sheet.Range("AA1").EntireColumn, "90", ExcelFindType.Number, ExcelFindOptions.MatchCase, False)
  Dim ranges As IRanges = sheet.CreateRangesCollection()

  For range As Integer = 0 To range1.Length - 1 Step range + 1
    ranges.Add(range1(range))
  Next
  For range As Integer = 0 To range2.Length - 1 Step range + 1
    ranges.Add(range2(range))
  Next
  For range As Integer = 0 To range3.Length - 1 Step range + 1
    ranges.Add(range3(range))
  Next
End Using

See Also