Don't like ads? PRO users don't see any ads ;-)
Guest

Untitled

By: a guest on May 9th, 2012  |  syntax: None  |  size: 1.89 KB  |  hits: 14  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How do I search for specific text in an Excel worksheet and get the address that the text is occupying using C#?
  2. public string searchExcel(string findThis)
  3. {
  4.     Excel.Application xlApp;
  5.     Excel.Workbook xlWorkBook;
  6.     Excel.WorkSheet xlWorkSheet;
  7.     object misvalue;
  8.  
  9.     //This part will open the Excel document.
  10.     misValue = System.Reflection.Missing.Value;
  11.     xlApp = new Excel.ApplicationClass();
  12.     xlWorkBook = xlApp.Workbooks.Open("C:\temp2\excelDocument.xlsm",
  13.     0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
  14.     "t", false, false, 0, true, 1, 0);
  15.  
  16.  
  17.     //Search and get address of cell
  18.  
  19.  
  20.     //This part will close the Excel document
  21.     xlWorkBook.Close(true, misValue, misvalue);
  22.     xlApp.Quit();
  23.  
  24.     releaseObject(xlWorkSheet);
  25.     releaseObject(xlWorkBook);
  26.     releaseObject(xlApp);
  27.  
  28.     xlWorkSheet = null;
  29.     xlWorkBook = null;
  30.     xlApp = null;
  31. }
  32.  
  33. private void releaseObject(object obj)
  34. {
  35.     try
  36.     {
  37.         System.Runtime.Interopservices.Marshal.ReleaseComObject(obj);
  38.         obj = null;
  39.     }
  40.     catch(Exception e)
  41.     {
  42.         obj = null;
  43.         MessageBox.Show("Unable to release the object " + e.ToString());
  44.     }
  45.     finally
  46.     {
  47.         GC.Collect();
  48.     }
  49. }
  50.        
  51. var app = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
  52.         var wb = app.ActiveWorkbook;
  53.         var ws = wb.Worksheets[1] as Excel.Worksheet;
  54.         var cells = ws.Cells;
  55.         var match = cells.Find("apples", LookAt:=Excel.XlLookAt.xlPart) as Excel.Range;
  56.         var matchAdd = match != null ? match.Address : null;
  57.        
  58. xlWorksheet = xlWorkBook.Worksheets[1] as Excel.Worksheet;
  59.         Excel.Range cells = ws.Cells;
  60.         Excel.Range match = cells.Find("apples", LookAt:=Excel.XlLookAt.xlPart) as Excel.Range;
  61.  
  62.         string matchAdd = match != null ? match.Address : null;
  63.  
  64.         if (match != null) releaseObject(match);
  65.         releaseObject(cells);