2

I've made an application that logs data to an excel file. I can't open the excel file until I've closed the application. In task manager excel.exe is still running after I've closed the excel application in code. I've tried at least 11 ways of closing excel but it doesn't work.

All the code is executed from a thread:

thread = new Thread(() => export_parameters(path));

    public class xlsx_logfile
    {
        private Microsoft.Office.Interop.Excel._Application excel;//excel application that is used for creating excel workbooks(files)
        private Microsoft.Office.Interop.Excel.Workbooks workbooks;
        private Microsoft.Office.Interop.Excel._Workbook workbook;//excel workbook(file)
        private Microsoft.Office.Interop.Excel._Worksheet worksheet;

        private string filename;

        public xlsx_logfile(string path)
        {
            //destructor?

            excel = new Microsoft.Office.Interop.Excel.Application();
            workbooks = excel.Workbooks; 
            workbook = workbooks.Add(Type.Missing);
            worksheet = null;

            worksheet = workbook.ActiveSheet;
            worksheet.Name = "sensordata";

            filename = path;


        }

        public void column_add(uint column_zerobased_index,string header, string format, double[] data)
        {
            Microsoft.Office.Interop.Excel.Range formatRange;

            Microsoft.Office.Interop.Excel.Range c1;
            //Microsoft.Office.Interop.Excel.Range c2;

            column_zerobased_index = 0;

            //var worksheet = workbook.ActiveSheet;



            c1 = worksheet.Cells[1, column_zerobased_index+1];
            //c2 = oSheet.Cells[4, 4];

            formatRange = worksheet.get_Range(c1,c1);//column_zerobased_index + 1
            formatRange.EntireColumn.NumberFormat = "0.0";

            worksheet.Cells[1, column_zerobased_index+1].NumberFormat = "@";
            worksheet.Cells[1, column_zerobased_index + 1] = header;
            //todo use: https://stackoverflow.com/questions/3989122/microsoft-office-interop-excel-really-slow
            for (int i = 0; i < data.Length; ++i)
            {
                worksheet.Cells[i + 1 + 1, column_zerobased_index+1] = data[i];
            }

            // Cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(formatRange);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(c1);
        }

        void close(string filename)
        {
            //worksheet = null;
            //workbook.Close();
            //workbook = null;
            //excel = null;


            //excel.Quit();
            //worksheet = null;
            //workbook = null;
            //excel = null;


            //worksheet = null;
            //workbook.Close();
            //workbook = null;
            //excel.Quit();
            //excel = null;


            //worksheet = null;
            //workbook.Close();
            //workbook = null;


            //var workbooks_local = excel.Workbooks;
            //var workbook_local = workbooks_local.Open(filename);

            //workbook_local.Close();
            //workbooks_local.Close();
            //excel.Quit();

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook_local);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks_local);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);


            //System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            //worksheet = null;
            ////workbook.Close();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            //workbook = null;
            ////excel.Quit();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //excel = null;


            ////based on https://stackoverflow.com/questions/1526685/c-how-can-i-open-and-close-an-excel-workbook
            //workbook.Close(false, filename, null);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            //excel.Quit();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //workbook = null;
            //excel = null;
            //GC.Collect();
            //GC.WaitForPendingFinalizers();
            //GC.Collect();
            //GC.WaitForPendingFinalizers();

            //var aap = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
            //worksheet = null;
            //workbook.Close();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            //workbook = null;
            //workbooks.Close();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);                
            //workbooks = null;
            //excel.Quit();
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            //excel = null;

            // Cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlRng);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);

            workbook.Close(Type.Missing, Type.Missing, Type.Missing);
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);

            excel.Quit();
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);

        }

        public void save()
        {
            workbook.SaveAs(filename);
            close(filename);
        }

    }

edit: this question is different from Closing Excel Application Process in C# after Data Access because I'm executing from a thread, not a method. The provided answers didn't work. I solved the problem myself:

        private Microsoft.Office.Interop.Excel._Application excel;//excel application that is used for creating excel workbooks(files)
        private Microsoft.Office.Interop.Excel._Workbook workbook;//excel workbook(file)
        private Microsoft.Office.Interop.Excel._Worksheet worksheet;

        excel = new Microsoft.Office.Interop.Excel.Application();
        workbook = excel.Workbooks.Add();
        worksheet = workbook.ActiveSheet;

        workbook.Close();
        excel.Quit(); 

        GC.Collect();
        GC.WaitForPendingFinalizers();

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(worksheet);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);

        worksheet = null;
        workbook  = null;
        excel     = null;
Community
  • 1
  • 1
Chris
  • 21
  • 3
  • You don't need this two lines `GC.Collect();` and `GC.WaitForPendingFinalizers();` – NtFreX Dec 09 '16 at 17:27
  • Possible duplicate of [Closing Excel Application Process in C# after Data Access](http://stackoverflow.com/questions/17777545/closing-excel-application-process-in-c-sharp-after-data-access) – NtFreX Dec 09 '16 at 17:30
  • http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects – Robin Mackenzie Dec 10 '16 at 02:12
  • People really have the funniest ideas. And very often I think that not everything that *can* be done also *should* be done... Why don't you write to the Excel file using ADO.NET? Or why don't you write to a CSV-file, which you can also open with Excel? – Thorsten Dittmar Dec 12 '16 at 10:09
  • @Dr. Fre I do need those two lines. Otherwise excel will remain open after the thread has closed. It disappears after the application is closed. – Chris Dec 13 '16 at 08:54
  • @ThorstenDittmar Never heard of ADO.NET. I will look into it. Thanks. I'm an embedded software engineer so I do not have a lot of C# experience. I simply based this on examples. Funny you mention CSV. The application could already export CSV. The reason for switching to Excel is the customer found importing CSV in Excel too complicated. With different decimal notations and date notations this was problematic. Excel allows you to specify the type and the format. CSV doesn't. Also allows more features like multiple tabs. – Chris Dec 13 '16 at 08:59
  • @Chris See the following link to get you started: https://blogs.msdn.microsoft.com/lazydeveloper/2010/03/15/using-ado-net-to-work-with-excel-files/. I mostly create an XLS template file which I add to my application's resources, so I can copy it to disk when needed. Then I use the approach in the link to access this file for writing. – Thorsten Dittmar Dec 13 '16 at 09:02

0 Answers0