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

24 Jan 202310 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;
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");

  //Creates a storage file from FileOpenPicker
  StorageFile inputStorageFile = await openPicker.PickSingleFileAsync();
  //Loads or open an existing workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(inputStorageFile);
  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;
  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;
  string resourcePath = "GettingStarted.Sample.xlsx";
  //"App" is the class of Portable project.
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream fileStream = assembly.GetManifestResourceStream(resourcePath);

  //Opens the workbook 
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  //Access first worksheet from the workbook instance.
  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]);
}

See Also