Resources for Learning C# Excel Interop

Accessing Excel File Located at Properties.Resources and keep here template

I find this solution and it work with me,

for use file Excel, it must already exist physically, so i saved excel file like temp file and i used this temp file for read my data,

string sPath = System.IO.Path.GetTempFileName(); 
System.IO.File.WriteAllBytes(sPath, Properties.Resources.data_base);

note: Properties.Resources.data_base this is my Excel file.

at the end I delete this temp file for more security

if (System.IO.File.Exists(sPath ))
{
    System.IO.File.Delete(sPath );
 }

How do I properly clean up Excel interop objects?

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

---

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

How do I properly clean up Excel interop objects from an C# application?

As my use of the C# Excel interop got more sophisticated, I began having headless copies of 'Microsoft Office Excel (32 bit)' objects running in Task Manager after I closed my app down. I found no combination of voodoo Marshal.ReleaseComObject() and GC.Collect() that would completely eliminate them. I finally removed all the voodoo code and followed Hans Passent's advice. I was able to terminate them under most circumstances when the app closed by using the following pattern:

using System;
using System.IO;
using excel = Microsoft.Office.Interop.Excel;

namespace ExcelInterop {
    static class Program {
        // Create only one instance of excel.Application(). More instances create more Excel objects in Task Manager.
        static excel.Application ExcelApp { get; set; } = new excel.Application();

        [STAThread]
        static int Main() {
            try {
                ExcelRunner excelRunner = new ExcelRunner(ExcelApp)
                // do your Excel interop activities in your excelRunner class here
                // excelRunner MUST be out-of-scope when the finally clause executes
                excelRunner = null;  // not really necessary but kills the only reference to excelRunner 
            } catch (Exception e) {
                // A catch block is required to ensure that the finally block excutes after an unhandled exception
                // see: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/try-finally
                Console.WriteLine($"ExcelRunner terminated with unhandled Exception: '{e.Message}'");
                return -1;
            } finally {
                // this must not execute until all objects derived from 'ExcelApp' are out of scope
                if (ExcelApp != null) {
                    ExcelApp.Quit();
                    ExcelApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
            }
            Console.WriteLine("ExcelRunner terminated normally");
            return 0;
        }
    }
}

In my ExcelRunner class I'm reading hundreds of csv files into excel Workbooks, and creating dozens of .xlsx files with tables and charts. I create only one instance of Microsoft.Office.Interop.Excel.Application() and reuse it over and over. More instances mean more 'Microsoft Office Excel' objects running in Task Manager that need to be cleaned up.

Note that the finally clause must execute to get rid of the headless Excel objects. The pattern above handles most app shutdown situations (including most aborts caused by unhandled exceptions - but see Does the C# "finally" block ALWAYS execute?). One notable exception occurs when you abort the app from the the VS debugger (Shift-F5 or the red 'Stop Debugging' square on the toolbar). If you abort the app from the debugger, the finally clause does not execute and an Excel object is left runnning. This is unfortunate, but I have found no way around it.

I tested this in Visual Studio 2019 and .NET Framework 4.7.2 using Excel 2007 interop and Excel 2016 interop.

Good resources for parsing excel files?

I like Excel Data Reader.

It's simple and easy to use. Supports Excel 97 through 2007.

Open Excel Workbook file Using Embedded Resource

You can't open it directly from the embedded resources - at least not with Interop...

You will need to save it somewhere as a file - i.e. first read it from the resource (for example by Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceNameOfEmbeddedExcelFile)) and then write that stream to some location like ApplicationData / CommonApplicationData / LocalApplicationData / MyDocuments / CommonDocuments from Environment.SpecialFolder

Another option would be to use a library capable of opening/editing Excel files from a stream - let me know if you need some links to libraries...

Excel C# inputting into specific cell

Cribbed from the online documentation:

var xl = new Excel.Application();
xl.Visible = true;
var wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
var sheet = (Excel._Worksheet)wb.ActiveSheet;
sheet.Cells[6, 6] = "6";

Other valuable resources can be found in this question.

How to properly clean up Excel interop object in C#, 2012 edition

did you now the NetOffice concept for COM proxy management?
NetOffice use wrapper classes for com proxies and the IDisposable pattern.
NetOffice keep the parent->child relationship for proxies. dispose a worksheet and all created childs from the instance(cells, styles), etc. was also disposed. you can also use a special event or static property to observe the count of open proxies in your application.

just take a look in this documentation snippet:
http://netoffice.codeplex.com/wikipage?title=Tec_Documentation_English_Management

you find some showstopper projects for com proxy management in the tutorials folder

---

Related Topics