private static Microsoft.Office.Interop.Excel.Range GetSpecifiedRange(Microsoft.Office.Interop.Excel.Worksheet objWs)
{
//gets range of cells with certain format then return the object
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range pattern = null;
Microsoft.Office.Interop.Excel.Range ssn = null;
Microsoft.Office.Interop.Excel.Range ssnum = null;
Microsoft.Office.Interop.Excel.Range socsecnum = null;
Microsoft.Office.Interop.Excel.Range merger = null;
Excel.Range last = objWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
//finds the format of numbers that are ssn
pattern = objWs.get_Range("A1", last).Find("???-??-????", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
ssn = objWs.get_Range("A1", last).Find("SSN", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
ssnum = objWs.get_Range("A1", last).Find("ss#", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
socsecnum = objWs.get_Range("A1", last).Find("social security number", missing,
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
Microsoft.Office.Interop.Excel.XlLookAt.xlPart,
Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false, missing, missing);
//if it finds a matching pattern it will either add or merge if there is an object that matches the above find.
if (pattern != null)
{
if (merger == null)
{
merger = pattern;
}
else
{
merger.Application.Union(merger, pattern);
}
}
if (ssn != null)
{
if (merger == null)
{
merger = ssn;
}
else
{
merger.Application.Union(merger, ssn);
}
}
if (ssnum != null)
{
if (merger == null)
{
merger = ssnum;
}
else
{
merger.Application.Union(merger, ssnum);
}
}
if (socsecnum != null)
{
if (merger == null)
{
merger = socsecnum;
}
else
{
merger.Application.Union(merger, socsecnum);
}
}
return(merger);
}
private static void FindExcel(Excel.Application oXL, string Wfile)
{
Microsoft.Office.Interop.Excel.Workbook oWB = null;
Microsoft.Office.Interop.Excel.Worksheet oSheet = null;
Application _excelApp = new Application();
Workbook workBook = _excelApp.Workbooks.Open(Wfile);
int numSheets = workBook.Sheets.Count;
//until a the file is added or until it runs out of sheets to check
while (numSheets > 0 && filesThatConstainSSN.Contains(Wfile) != true)
{
try
{
object missing = System.Reflection.Missing.Value;
oWB = oXL.Workbooks.Open(Wfile, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing);
//used for number of worksheets
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Worksheets[numSheets];
//gets range of cells where format is similar
Microsoft.Office.Interop.Excel.Range oRng = GetSpecifiedRange(oSheet);
//checks ti see if anything exists, makes a string then checks exact format
if (oRng != null)
{
string str = oRng.Text.ToString();
FindTextDoc(str, Wfile);
}
}
//closes up if there is an exception
catch (Exception)
{
oXL.DisplayAlerts = false;
workBook.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
if (oSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
if (oXL != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
}
oSheet = null;
workBook = null;
oXL = null;
GC.Collect();
}
numSheets--;
}
//final cleanup
oXL.DisplayAlerts = false;
workBook.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
if (oSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
}
if (workBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
}
if (oXL != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
}
oSheet = null;
workBook = null;
oXL = null;
GC.Collect();
}
