- How do I search for specific text in an Excel worksheet and get the address that the text is occupying using C#?
- public string searchExcel(string findThis)
- {
- Excel.Application xlApp;
- Excel.Workbook xlWorkBook;
- Excel.WorkSheet xlWorkSheet;
- object misvalue;
- //This part will open the Excel document.
- misValue = System.Reflection.Missing.Value;
- xlApp = new Excel.ApplicationClass();
- xlWorkBook = xlApp.Workbooks.Open("C:\temp2\excelDocument.xlsm",
- 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
- "t", false, false, 0, true, 1, 0);
- //Search and get address of cell
- //This part will close the Excel document
- xlWorkBook.Close(true, misValue, misvalue);
- xlApp.Quit();
- releaseObject(xlWorkSheet);
- releaseObject(xlWorkBook);
- releaseObject(xlApp);
- xlWorkSheet = null;
- xlWorkBook = null;
- xlApp = null;
- }
- private void releaseObject(object obj)
- {
- try
- {
- System.Runtime.Interopservices.Marshal.ReleaseComObject(obj);
- obj = null;
- }
- catch(Exception e)
- {
- obj = null;
- MessageBox.Show("Unable to release the object " + e.ToString());
- }
- finally
- {
- GC.Collect();
- }
- }
- var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
- var wb = app.ActiveWorkbook;
- var ws = wb.Worksheets[1] as Excel.Worksheet;
- var cells = ws.Cells;
- var match = cells.Find("apples", LookAt:=Excel.XlLookAt.xlPart) as Excel.Range;
- var matchAdd = match != null ? match.Address : null;
- xlWorksheet = xlWorkBook.Worksheets[1] as Excel.Worksheet;
- Excel.Range cells = ws.Cells;
- Excel.Range match = cells.Find("apples", LookAt:=Excel.XlLookAt.xlPart) as Excel.Range;
- string matchAdd = match != null ? match.Address : null;
- if (match != null) releaseObject(match);
- releaseObject(cells);