I have sworn not to use the Excel interop anymore, but I was forced to use it again. It seems that reading data from Excel using OLEDB doesn't give correct data in some circumstances (truncated data etc.), so we tried to use the Excel interop. But I ran into the following problems:

• Excel doesn't start, when you have different Regional Settings than the installed language of Office
• Excel isn't closed correctly
• The reference from the C# project is linked to either Office 2003 or Office 2007. When you try to compile the program on a system with a different Office version, it doesn't work.

To solve these problems, you need to do the following things:

• Change the CultureInfo of the current thread to en-Us before starting Excel, and back to the original CultureInfo after closing Excel
• Remove references to all objects (including COM objects) after doing your job
• Create your own wrapper using TLBIMP.exe, and create a reference to this wrapper

After this, it works like a charm! Your program will build on any machine, and run-time it will work with Excel 2003 or 2007.

Here is the C# code:
CultureInfo OriginalCulture = null; Application app = null; Workbooks wbs = null; Workbook wb = null; Sheets wss = null; Worksheet sheet = null; try { OriginalCulture = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); app = new Application(); app.DisplayAlerts = false; wbs = app.Workbooks; wb = wbs.Open(inputFileName, 0, false, 5, "", "", false, Type.Missing, "", true, false, 0, true, false, false); wss = app.Worksheets; sheet = null; } catch(Exception ex) { //TODO: do exception handling } finally { if (wb != null) { wb.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value); } if (wbs != null) { wbs.Close(); } if (app != null) { app.Quit(); } if (sheet != null) { Marshal.ReleaseComObject(sheet); } if (wss != null) { Marshal.ReleaseComObject(wss); } if (wbs != null) { Marshal.ReleaseComObject(wbs); } if (wb != null) { Marshal.ReleaseComObject(wb); } if (app != null) { Marshal.ReleaseComObject(app); } sheet = null; wss = null; wbs = null; wb = null; app = null; GC.GetTotalMemory(false); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); if (OriginalCulture != null) { Thread.CurrentThread.CurrentCulture = OriginalCulture; } }

Last modified on: 2009-02-05