Migrating from VSTO to Excel DNA

Jan 25, 2015 at 7:22 PM
Edited Jan 25, 2015 at 7:34 PM
Hello, I have a rather large VSTO project that is currently in development and I am considering porting to Excel-DNA after reading this thread about how Excel-DNA has much better efficiency with the Excel object model than VSTO. http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects

Since I am not starting from scratch, I have some concerns that some of the features I use in VSTO may not be supported in Excel-DNA.

My VSTO project has a Ribbon with a bunch of buttons that launch Windows Forms, it has a Custom Task Pane, it utilizes Workbook Events and it does a lot of reading from and writing to ranges within workbooks. My project does not use UDFs at all.

So my questions are:
1) does Excel-DNA support Ribbons? Is it somewhat easy to transfer a ribbon from a VSTO project to Excel DNA (I created the Ribbon using the XML method and not through the Ribbon Designer)
2) does Excel-DNA support Custom task panes? likewise, is it relatively straightforward to move it over from a VSTO project?
3) does Excel-DNA support catching the various Workbook events
4) can I have a single Excel-DNA project that targets Excel 2007-2013 and supports both the 32bit and 64bit versions?
5) Are there any other features that I might have in a VSTO project that may not be supported in Excel-DNA?


Thanks so much! Looking forward to trying out Excel-DNA and seeing how much more efficient it is than VSTO for reading and writing large amounts of data!
Coordinator
Jan 27, 2015 at 4:21 AM
Hi,

VSTO is mainly a wrapper around the Excel COM object model. It adds some convenient helpers for ribbons, CTPs and the like, but everything that can be done with VSTO can be done with COM directly. You can access the full COM object model from an Excel-DNA addin.

So all the features you list are supported for an Excel-DNA addin (or any COM addin). However, you might miss some of the convenience wrappers of VSTO.

In addition, your Excel-DNA add-in can use the native C API, which is what the Stackoverflow discussion mentions. It might sometimes be faster you should test this yourself, first.

For a VSTO add-in that exposes no UDFs, it probably makes most sense to check that the Range read and writes are done by large arrays, not cell-by-cell. Otherwise you'll be doing a lot of work for marginal benefit.

Excel-DNA has other benefits, like easy admin-free deployment, async functions etc. but mixing the VSTO helper libraries with an Excel-DNA add-in is not possible.

-Govert



Jan 27, 2015 at 8:27 PM
Edited Jan 27, 2015 at 8:30 PM
Hello Govert,

Thank you very much for your reply. I have been experimenting with Excel-DNA and it looks great.

I would like to know what the best practice is for handling Workbook Events in Excel-DNA. In VSTO we have some functions that are created for us:
ThisAddIn_Startup(object sender, System.EventArgs e) and ThisAddIn_Shutdown(object sender, System.EventArgs e).
In VSTO we would add the workbook events to Globals.ThisAddIn.Application in the Startup method and remove those events in the Shutdown method.

I have come up with this code below to do the equivalent in Excel-DNA. Can you please let me know if this looks like it is correct, whether it could potentially have any issues(especially with respect to ExcelDNAUtil.Application object) or if I'm doing something that is not best practice?

Thanks so much!
using Excel = Microsoft.Office.Interop.Excel;
[ComVisible(true)]
public class Ribbon : ExcelRibbon
{
    private static IRibbonUI _ribbon;
    private static EventManager _eventManager;
    
    /*
Ribbon Load event as specified in RibbonXML <customUI onLoad='Ribbon_Load' xmlns='http://schemas.microsoft.com/office/2006/01/customui'>
        */
    public void Ribbon_Load(IRibbonUI ribbon)
    {
        _ribbon = ribbon;
        _eventManager = new EventManager((Excel.Application)ExcelDnaUtil.Application);
    }
}

class EventManager
{
    private Excel.Application _excelApp;
    private Excel.AppEvents_WorkbookOpenEventHandler _openWorkbookHandler;

    internal EventManager(Microsoft.Office.Interop.Excel.Application excelApp)
    {
        _excelApp = excelApp;
        _openWorkbookHandler = Event_WorkbookOpen;
        _excelApp.WorkbookOpen += _openWorkbookHandler;
    }

    ~EventManager()
    {
        try
        {
            _excelApp.WorkbookOpen -= _openWorkbookHandler;
            _openWorkbookHandler = null;
        }
        catch (Exception)
        {
        }
    }

    internal void Event_WorkbookOpen(Excel.Workbook workbook)
    {
        MessageBox.Show(String.Format("You opened {0}!", workbook.Name));
    }
}
Jan 27, 2015 at 11:55 PM
Hello Govert,

I'm trying to get a handle on how I can set a range to an array using Excel-DNA. My COM code works fine, my CAPI code doesn't seem to do anything; it doesn't even throw an exception. I've tried swapping out activeSheet.SheetID with "Sheet1" but that didn't seem to do anything either. Note that I am testing this as a call from a Ribbon button and not from a UDF. I also tried messing around with the row and column numbers since, as far as I could tell from the code samples I found, the CAPI indexes them starting at 0 and not 1 like the COM API.

Additionally, there is no SetValue2 function. From what I remember we should never set the .Value or .Text property but should instead set the .Value2 property. Could you please explain what ExcelReference.SetValue() does, is it actually setting Value2?

Thanks again for all of your help!
        internal static void RibbonButton1()
        {
            object[,] vals = { { 1, 2, 3 }, { 4, 5, 6 } }; 

            //SetRangeCOM(vals);
            SetRangeCAPI(vals);

        }

        internal static void SetRangeCAPI(object[,] vals)
        {
            ExcelReference activeSheet = (ExcelReference)XlCall.Excel(XlCall.xlSheetId);

            int rowStart = 1;
            int colStart = 1;
            int rowEnd = vals.GetLength(0);
            int colEnd = vals.GetLength(1);
            ExcelReference target = new ExcelReference(rowStart-1, rowEnd-1, colStart-1,colEnd-1, activeSheet.SheetId);
            target.SetValue(vals);
        }


        internal static void SetRangeCOM(object[,] vals)
        {
            Excel.Application excelApp = (Excel.Application)ExcelDnaUtil.Application;
            Excel.Worksheet activeSheet = excelApp.ActiveSheet;
            int rowStart = 1;
            int colStart = 1;
            int rowEnd = vals.GetLength(0);
            int colEnd = vals.GetLength(1);
            activeSheet.Range[activeSheet.Cells[rowStart, colStart], activeSheet.Cells[rowEnd, colEnd]].Value2 = vals;
        }
Jan 28, 2015 at 7:08 PM
Edited Jan 28, 2015 at 8:34 PM
Hello again,

I managed to get the CAPI code working after reading this thread. http://stackoverflow.com/questions/11223641/how-do-i-create-a-new-worksheet-and-populate-it-with-rows-of-data-using-excel-dn

I've included my code in case it can help someone else.
I also did a benchmark where I set a range to a 4 million value sized array 10 times and I got a run time of 23 seconds for the CAPI and 36 seconds for COM. So the COM is about 1.5 times slower than CAPI for writing large ranges. Not the 40x improvement that I was hoping for as mentioned in the stack overflow thread, but still a significant speed improvement.

Govert, I would still be interested in hearing your thoughts about my Worksheet event handling code and if SetValue is actually setting the Value or Value2 property of the ranges.

Once again, many thanks for your great work and support!

/* inside  DNA File */
<button id='ID1' getLabel='Ribbon_GetLabel' getSupertip='Ribbon_GetSupertip' getImage='Ribbon_GetImage' size='large' onAction='Ribbon_RunActionDNA'/>

/* inside Ribbon Class */

//Ribbon Control IDs
private const string ID1 = "ID1";

//map of Ribbon Control IDs to Function calls
private Dictionary<string, string> _runDNAActions = new Dictionary<string, string> 
{
    { ID1, "RunID1" }
};

//callback for Ribbon functions which use the CAPI
public void Ribbon_RunActionDNA(IRibbonControl control)
{
    if (_runDNAActions.ContainsKey(control.Id))
    {
        object app = ExcelDnaUtil.Application;
        app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod,
        null, app, new object[] {_runDNAActions[control.Id]}, new CultureInfo(1033));
    }
}

public static void RunID1()
{
    object[,] vals = new object[400000,10];
    int cnt = 0;
    for(int i=0; i<vals.GetLength(0); i++)
        for (int j = 0; j < vals.GetLength(1); j++)
            vals[i, j] = ++cnt;

    SetRangeCAPI(vals,1,1);
}

internal static void SetRangeCAPI(object[,] vals, int startRow, int startCol)
{
    Excel.Application excelApp = (Excel.Application)ExcelDnaUtil.Application;
    excelApp.ScreenUpdating = false;
    excelApp.EnableEvents = false;
    ExcelReference target = new ExcelReference(startRow - 1, startRow + vals.GetLength(0) - 2, startCol - 1, startCol + vals.GetLength(1) - 2);
    target.SetValue(vals);
    excelApp.ScreenUpdating = true;
    excelApp.EnableEvents = true;
}
Coordinator
Feb 1, 2015 at 9:15 PM
Hi,

The event hookup you can put it in different places. One option is your ExcelRibbon-derived class, and in that case I would suggest overriding the OnConnection method and doing your hook-up there, instead of using the Ribbon_Load for that. It just the kind of thing more likely to be expected in the OnConnection handler - I doubt it really makes a difference.

Regarding the ".Value2" - it is an artifact of the COM type system, and not directly relevant to the C API or the way values are stored internally in Excel.
".Value" is a mix of value and formatting, which is why it can be slower than ".Value2" which return the value only.
So you can consider ExcelReference.SetValue(...) as an equivalent of ".Value2".

-Govert
Feb 1, 2015 at 11:26 PM
Edited Feb 2, 2015 at 12:47 AM
Thank you for your response Govert!

I have one more quick question.

I used to be getting some crashes after Excel exited, and had read that it could have something to do with storing static references to ExcelDNAUtil.Application. I cleaned up my code as best I could but still got an intermittent crashes after Excel closed. I read that using NetOffice would be a possible solution to this, as well as having other benefits, and have ported my interop code over to NetOffice.

However, now something peculiar happens. When I close Excel, my addin continues to live after the Excel Process is dead. The only way I can kill the app on Excel exit seems to be to override OnDisconnection and then make a call to Application.Exit() within it.

I have changed my event handling code from the code in my previous posts and now just have a single static reference to the NetOffice Excel Application existing within a static class and access it for adding all of the Excel Events.
internal static Excel.Application ExcelApp = new Excel.Application(null, ExcelDnaUtil.Application);
Do you know why my addin wouldn't shutdown automatically upon Excel exit? Is it something to do with NetOffice storing a list of all the COM Proxies? My next step to cleanup my code is to keep the Proxy Count down as I was not doing any releasing of COM Objects while using VSTO and I don't want any potential memory leaks.

Edit: I discovered that the addin only doesn't shutdown (when Excel does) when I do an operation that shows a progress bar with an async background worker. Any idea why this would be the case?
Coordinator
Feb 2, 2015 at 8:15 AM
Edited Feb 2, 2015 at 8:16 AM
The main guideline is that you should make no COM calls on any thread other than the main Excel thread. If you have some progress callback or something that runs on another thread, you can transition back to the main Excel thread with a call to ExcelAsyncUtil.QueueAsMacro(...).
Using the COM object model from other threads in your add-in (with or without the NetOffice bookkeeping) is a recipe for trouble.

The ExcelDnaUtil.Application object that is returned from a call on the main thread can be safely stored in a static or other variable, and need never be set to null to clean up. Calling ExcelDnaUtil.Application from another thread is not advised - you can a different wrapper with many complications. Knowing what I know now, I would disallow such use completely, but I'm not sure what the compatibility implications would be.

While NetOffice has some advantages (like Intellisense indicating which features are supported by particular Excel versions) it is certainly not required to get stability or avoid crashes of Excel, or to ensure that Excel shuts down cleanly. The threading rule is important for that.

Never call Marshal.ReleaseComObject - it is never helpful in this context, and sometimes does harm. You need not worry about proxy counts - the .NET runtime manages all of that. You need not worry about any 'double dot' rule you read about. Just make sure the COM object model access is always on the right thread.

One additional quirk is if you have a form or something running on the main thread. Then you might still not be in a safe context when the form callback runs, with the consequence that some of the COM calls might fail. Again you should use ExcelAsyncUtil.QueueAsMacro(...) to transition to a macro context on the main thread from any such event handler.

-Govert