Excel application singleton - possible threats of using only one Excel app instance without quitting?

Question

I am working on a WPF application that uses Excel quite heavily for reporting. It takes too much time to open a new Excel app whenever I need Excel app instance to generate a new report.

Why not to keep one Excel app instance open as a singleton just for my app? And to quit this Excel singleton when my application closes? There is only a small risk that the Excel app instance stays open, I think. Or is there any hidden threat in doing this?

My code:

    private static Microsoft.Office.Interop.Excel.Application _ExcelApp = null;
    public static Microsoft.Office.Interop.Excel.Application ExcelApp
    {
        get { return _ExcelApp; }
        private set { _ExcelApp = value; }
    }

    public static void QuitExcel()
    {
        if (ExcelReports.ExcelApp != null)
        {
            ExcelReports.ExcelApp.Quit();
            ExcelReports.ExcelApp = null;
        }
    }

    public static void StartExcel()
    {
        try
        {
            ExcelReports.ExcelApp = new Microsoft.Office.Interop.Excel.Application();
        }
        catch (System.Runtime.InteropServices.COMException ex)
        {
            throw new ApplicationException(String.Format("Cannot start Excel.\n\r{0}", ex.Message));
        }
    }

EDIT It seems to be working quite ok and faster, though it is certainly not the very best practice. There seems to be no way to find my particular excel instance e.g. after app crash. I can eventually kill all hidden excels with this snippet.

    List<Process> procs = new List<Process>();
    procs.AddRange(Process.GetProcessesByName("excel"));
    foreach (Process p in procs)
        if ((int)p.MainWindowHandle == 0)
        { //Kill excel

I have observed only one problem: when user tries to open excel file from explorer using file association, it somehow finds and shows my hidden instance and the required file does not appear in Excel. When excel is opened just running excel.exe e.g from desktop shortcut then a new instance is opened and it is ok.

Just curiosity, why do you need Excel? I mean is that only for reading the data in file or any particular feature you need to use? Last, which type of Excel files you are using XLS or XLSX? — cilerler, Dec 26 '13 at 20:34
It is for special reporting. Excel is great for generating reports with limited length because normal people can edit report templates without special tools or knowhow. — Vojtěch Dohnal, Dec 26 '13 at 22:12
what do you mean by special reporting? is it pivot table etc. What is your reason/s to use Excel itself when you have programming skills? You don't need to use Excel to read/write excel file. Why don't you read your data from Excel file, process your data in .NET and put it back the results into the Excel file without using Excel? by the way I saw @xxbbcc already covered OpenXML which would be my suggestion if you are dealing with XLSX — cilerler, Dec 26 '13 at 23:27
@cilerler The reason is flexibility. I use excel as a reporting engine, I could have used e.g. Crystal Reports instead, but doing this I would lose flexibility. I have xlsx template with serveral sheets, some of them are populated with data and some calculated. Templates have fixed number of pages and typically contain charts, tables, text. The result of the process is a document rather than a report. I want also to allow certain users to modify temlates in Excel. — Vojtěch Dohnal, Dec 31 '13 at 14:43
I believe your best bet is OpenXML http://msdn.microsoft.com/en-us/library/office/bb448854.aspx — cilerler, Dec 31 '13 at 18:33
@cilerler Unfortunately but quite obviously the OpenXML does not provide any mean to calculate all the formulas in the workbook. I need to reuse quite a lot of existing templates that contain calculations. But thanks for the tip! — Vojtěch Dohnal, Dec 31 '13 at 19:17
You are welcome. It would be much easier if you show us visually (like screenshots etc.) what actually you are trying to do. What I understanding is you need to manipulate the workbook so other users can use Excel futures which you can do via OpenXML as long as it is pivot table or calculations please review here http://msdn.microsoft.com/en-us/library/office/gg278324.aspx and here http://msdn.microsoft.com/en-us/library/office/gg278336.aspx — cilerler, Jan 01 '14 at 16:24

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

If you really do need Excel to run in the background (as opposed to generating Excel files using some 3rd-party library), there is nothing but danger if you try to use Excel as a singleton - especially if you try to use it over multiple threads.

Read these answers of mine (to somewhat related questions):

COM object excel interop clean up

Editing an Excel document with Macros in ASP.net

In general, it's much cleaner to start up Excel, perform whatever operation you need it to do and then shut it down immediately. This cleans up all resources and the next startup begins with a clean slate.

I don't know your actual usage pattern of Excel so not all of the details may apply to you but quite a few of them will. I may be able to add more detail if you have more specific questions.

If you just need to read / write .xlsx files, you may be able to use the OpenXML SDK instead of Excel. I haven't used it so I don't know if it'd fit your needs.

Edit: if you're using Excel to generate your data (which is typically very slow), you can look into other approaches where the data is generated as a text file / recordset and then imported into Excel:

Writing to excel using OleDb

edited May 23 '17 at 12:07

Community

1
1

answered Dec 26 '13 at 20:37

xxbbcc

16,930
5
50
83

Thank you for this comment. My pattern is: load workbook with template xlsx, absolutely no macros, fill it with data, hide certain rows or columns, format some cells, perform calculations within sheet, save it as PDF and close the workbook. There will be no multithreading using Excel and only one workbook open at a time. I have already one app that this scenario, but I have no experience with letting the one Excel instance live just for my app. It will surely happen that users will use their own Excel instances meanwhile. – Vojtěch Dohnal Dec 26 '13 at 22:18
@VDohnal If you're absolutely sure that's how you'll use it, you can give it a try. I'd still try to close / reopen Excel every once in a while because most of the operations that you describe will take a long time anyway - the cost of opening Excel is not too much more. One thing that may help is adding more memory to the system where you run it (if you can). – xxbbcc Dec 26 '13 at 22:21
I will test it, it is certainly less safe approach, so I shall see, thanks. Thanks for the link, the COMHelper class looks good. – Vojtěch Dohnal Dec 26 '13 at 22:23
@VDohnal Another step that may speed things up for you is generating your data first as a .csv file and then opening that in Excel. I updated my answer with another link for you. – xxbbcc Dec 26 '13 at 22:23
Yes, this is a bottleneck too, I use relatively fast method `ws.Range["A2"].Offset[Idx].Resize[1, dt.Columns.Count].Value = dt.Rows[Idx].ItemArray;`, but this one might be even faster. – Vojtěch Dohnal Dec 26 '13 at 22:39
I have tried to compare the time with and without opening and in my case, where there are no huge amounts of data to transfer, the result was: 8 s with opening, 4 s without opening. I am quite satisfied with that and I shall see if it continues to work ok – Vojtěch Dohnal Dec 26 '13 at 23:01
@VDohnal Are you saying that launching Excel adds 4s to your processing time (100% increase)? That's quite slow - my Excel 2010 starts much faster after a somewhat slower initial launch. Even then, as long as your project is strictly single-threaded, you're probably fine. – xxbbcc Dec 27 '13 at 02:51

score 0 · Answer 2 · answered Dec 27 '13 at 04:55

0

Hanging on the the Excel object as a singleton is a bad idea. The user may close Excel at any time and then you won't have an instance any more (unless you keep the Excel window you have created hidden all the time).

Why don't you grab a running Excel instance if one exists and otherwise create a new one?

  Excel.Application myExcelApp = null;

  try
  {
      myExcelApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application;
  }
  catch (System.Runtime.InteropServices.COMException e)
  {
      // Excel Not Running
      myExcelApp = new Microsoft.Office.Interop.Excel.Application();
  }

answered Dec 27 '13 at 04:55

Iain

2,500
1
20
24

Thanks, but is there a way to know at least whether the particluar instance has any visible window? Normally this approach usually produces some HRESULT exception whenever user edits formula or when there is some modal window open within Excel. The singleton I would use will never have a visible window. – Vojtěch Dohnal Dec 31 '13 at 14:41
This would be better in my situation: [How to iterate through instance of Excel c#](http://stackoverflow.com/questions/7646498/how-to-iterate-through-instance-of-excel-c-sharp) – Vojtěch Dohnal Dec 31 '13 at 15:32
But no, it is not suitable, since it works only with excel instances that have window. – Vojtěch Dohnal Dec 31 '13 at 16:00

Excel application singleton - possible threats of using only one Excel app instance without quitting?

2 Answers2