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
- How to opening an existing workbook?
- 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?
- How to sort two or more columns in a pivot table?
- How to move or copy a worksheet?