Foredecker
2009-07-03 03:54:21
+1
A:
Always take extra care to clean up when using the Interop libraries. Otherwise, you're likely to end up with a couple dozen EXCEL.EXE processes running in the background while you debug (or when a user hits an error).
private static bool IsStockDataWorkbook(string fileName)
{
Excel.Application application = null;
Excel.Workbook workbook = null;
try
{
application = new Excel.ApplicationClass();
application.Visible = true;
workbook = application.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
foreach (Excel.Worksheet sheet in workbook.Worksheets)
{
if (IsStockWorksheet(sheet))
{
return true;
}
}
return false;
}
finally
{
if (workbook != null)
{
workbook.Close(false, Missing.Value, Missing.Value);
}
if (application != null)
{
application.Quit();
}
}
}
private static bool IsStockWorksheet(Excel.Worksheet workSheet)
{
Excel.Range testRange = workSheet.get_Range("C10", Missing.Value);
string value = testRange.get_Value(Missing.Value).ToString();
return value.Equals("close", StringComparison.InvariantCultureIgnoreCase);
}
Thomas G. Mayfield
2009-07-03 04:00:41
All those "Missing.Value"s are why people prefer VB.NET for Office interop, and why C# 4 has the dynamic keyword.
Thomas G. Mayfield
2009-07-03 04:01:43
Yes, it is true that you need to clean up; I didn't mention that in my sloppy answer below. My understanding is that Marshal.FinalReleaseComObject(object) should be called for every variable assigned to a COM object. I think that also means than you cannot use a foreach loop as shown here.
Jay
2009-07-03 04:05:23
Crikey. And that "application.Workbooks.Open()" causes errors. Just got done reading through http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419
Thomas G. Mayfield
2009-07-03 04:14:46
Thanks,Actually in a previous version of the question (which for some reason never got posted) I mentioned that cleanup was omitted for the sake of brevity. But point well taken, not getting everything sewn up can create some nasty problems.J
2009-07-03 04:37:52
" And that "application.Workbooks.Open()" causes errors" - as well as several other objects that aren't released - in the above code Workbook.Sheets, each sheet referenced in the for loop, and the ranges returned by each call to worksheet.get_Range. It's extremely difficult to get this right. I recommend writing a wrapper class for common simple scenarios, which implements IDisposable and does not expose any Excel objects to the caller. The wrapper is responsible for releasing every Excel reference it creates, at latest during Dispose.
Joe
2009-07-03 06:34:44
Hi,It's partly for this reason that I like to stay out of the realm of interop as much as possible. Essentially I just want to grab the information and close up excel entirely.
2009-07-03 11:16:16
A:
Workbooks workbooks = xlApp.Workbooks;
foreach(Workbook wb in workbooks)
{
Worksheets worksheets = wb.Worksheets;
foreach(Worksheet ws in worksheets)
{
Range range = ws.get_Range(ws.Cells[1,1], ws.Cells[5,9]);
Range match = range.Find("close", ws.Cells[1,1],
xlFindLookIn.xlValues, xlLookAt.xlPart,
xlSearchOrder.xlByColumns, xlSearchDirection.xlNext,
false, false, false); //that first false means ignore case
// do something with your match here
// this will only return the first match; to return all
// you'll need to run the match in a while loop
}
}
Jay
2009-07-03 04:02:11
A:
You'll need to assign objSheets to something, most likely:
Excel.Sheets objSheets = xlWorkbook.Sheets;
Your foreach statement should look more like this (with no prior declaration of the ws variable):
foreach(Excel.Worksheet ws in objSheets)
{
rng = ws.get_Range(ws.Cells[1,1], ws.Cells[5,9]);
}
Obviously, you'll want to do something more substantial in that loop.
Jay
2009-07-07 15:44:09