none
interact with Microsoft.Office.Interop.Excel.Application RRS feed

  • Question

  •  Environment:

      OS: WIN10

      Dev Platform: Vs2015

     Assembly:   Microsoft.Office.Interop.Excel   runtime version:v2.0.50727,version :15.0

    scenario: as we known, if we want to operate Excel, we need at least four references:1.Application

                                                                                                                                 2.WorkBooks,

                                                                                                                                 3.WorkBook,

                                                                                                                                 4Worksheet

                 I want to implement one infrastructure which will reuse 1.Application, 2.WorkBooks

                 
        internal static class ExcelPlatform
        {
            private  static Application excel = new Application() { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
            private  static Workbooks workBooks = excel.Workbooks;
           
            internal static Workbooks WorkBooks { get { return workBooks; } }
    
            internal static void ReleaseComReferences(object o)
            {
                if (o == null) return;
                try
                {
                    while (Marshal.ReleaseComObject(o) > 0) ;
                }
                catch (ArgumentException e)
                {
    
                }
                finally
                {
                    o = null;
                }
                
            }
    
        }
    
        internal class ExcelContainer:IDisposable
        {
            private string filePath;
            protected Workbook WorkBook;
            private Boolean isDisposed = false;
            protected ExcelContainer(string filePath)
            {
                this.filePath = filePath;
                Open();
            }
    
            protected void Open()
            {
                if (!isDisposed)
                {
                    try
                    {
                        WorkBook = ExcelPlatform.WorkBooks.Open(Filename: filePath,
                           UpdateLinks: Type.Missing,
                           ReadOnly: Type.Missing,
                           Format: Type.Missing,
                           Password: Type.Missing,
                           WriteResPassword: Type.Missing,
                           IgnoreReadOnlyRecommended: Type.Missing,
                           Origin: Type.Missing,
                           Delimiter: Type.Missing,
                           Editable: Type.Missing,
                           Converter: Type.Missing,
                           AddToMru: Type.Missing,
                           Local: Type.Missing,
                           CorruptLoad: Type.Missing,
                           Notify: Type.Missing);
                    }
                    catch (Exception e)
                    {
    
                    }
                }
            }
            private void Dispose(Boolean disposing)
            {
                if (!isDisposed)
                {
                    if (disposing) { }
                    WorkBook.Save();
                    WorkBook.Close();
                    ExcelPlatform.ReleaseComReferences(WorkBook);
                    WorkBook = null;
                }
                isDisposed = true;
            }
            ~ExcelContainer()
            {
                Dispose(false);
            }
            public virtual void Dispose()
            {
                this.Dispose(true);
                GC.SuppressFinalize(this);
            }
            void IDisposable.Dispose()
            {
                Dispose();
            }
    
        }
    

    Problem:

         at first running, the code run very well, then the second request ,  the field excel of ExcelPlatform and the field workBooks of ExcelPlatform is null, and throw null reference exception.

       first question: the excel and workBooks field is static, when and why do them released?

       first try : I declare the excel and workBooks as readonly and static, then the code can't run with null reference exception thrown in unknown module.

       first try question: why the field can't declare as readonly.

    • Moved by CoolDadTx Wednesday, September 27, 2017 1:49 PM Office related
    Tuesday, September 26, 2017 6:55 AM

All replies

  • Hello CrazyOldPotato,

    >>the field excel of ExcelPlatform and the field workBooks of ExcelPlatform is null, and throw null reference exception.

    The  NullReferenceException throws  occurs when you want to read  or set one of its properties before you have not initialized a reference type,and the other possible situation is that you try to access the object properties after you release a reference object manually.


    >>the excel and workBooks field is static, when and why do them released?

    "static" members belong to the class instead of a specific instance.It means that only one instance of a static field exists[1] even if you create a million instances of the class.It will be shared by all instances . And the field of "static" keyword modified doesn't exist any relationship with GC.If the field value is null . It could be occurs by the following case.

    1.Just set the field value as null.

    2.Dispose the instance manually when define the static field as null in the dispose method.

     
    private void Dispose(Boolean disposing)
            {          
                    WorkBook = null;
                }

    3.Triggers GC to force the object to be reclaimed.

    The reason should be relating how you use the ExcelPlatform and ExcelContainer class.You could provide complete test code about how to operate excel file so that we can do further test with it.


    >>first try question: why the field can't declare as readonly.

      

    A Static Readonly type variable's value can be assigned at runtime or assigned at compile time and changed at runtime. But this variable's value can only be changed in the static constructor. And cannot be changed further. It can change only once at runtime.the following code I set the field as static readonly.

      public static class ExcelPlatform
        {
            private  static readonly Application excel = new Application() {Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
            private static readonly Workbooks workBooks = excel.Workbooks;
    }

    Sincerely,

    neil hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 27, 2017 9:41 AM
  • Hi CrazyOldPotota,

    >> at first running, the code run very well, then the second request ,  the field excel of ExcelPlatform and the field workBooks of ExcelPlatform is null, and throw null reference exception.

    Could you share us the code related with first running and second request?

    Per to your current code, ExcelContainer could not be used directly. What is the inherited class? And how did you get null exception.

    It would be helpful if you could share us a simple demo to reproduce your issue.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 4, 2017 7:20 AM