Search code examples
c#excelvstoadd-inexcel-dna

Code Conversion - MFC C++ to a VSTO Excel Addin in C#

I am converting a MFC C++ COM Addin to a VSTO Excel Addin in C# .NET4.0. There is a lot of code referring to the C API in it. Here is an example. I am possibly going to use Excel DNA to simplify my conversion task.

FYI: xlr is a class in the XLSDK.

//allocate local storage for the reference
m_pControllerReference = new xlr( controllerRange ) ;

//get the name of the book and sheet with the reference
   g->Excel.fn( xlSheetNm, *m_pControllerReference ) ; 

   if ( g->Excel.resultXltype() != xltypeErr )
   {
       m_referenceSheetName = g->Excel.resultCString() ;

//    and get the sheet ID
       g->Excel.fn( xlSheetId, xl(m_referenceSheetName) ) ; 

       if ( g->Excel.resultXltype() != xltypeErr )
       {
           m_controllerSheetId = g->Excel.resultSheetId() ;

           xlr theRange( m_pControllerReference->GetLPXLOPER(),
0, TRUE ) ;

           if(theRange.nRows() >6)

           ........etc

Does this convert like this?

          m_pControllerReference = (Excel.Range)controllerRange;

          m_referenceSheetName =
(string)XlCall.Excel(XlCall.xlSheetNm, m_pControllerReference );

          m_controllerSheetId =  XlCall.Excel(XlCall.xlSheetId,
m_referenceSheetName);    // and get the sheet ID

          //how to convert this ?
          //xlr theRange( m_pControllerReference->GetLPXLOPER(),
0, TRUE ) ;

Or is there a better way of converting without resorting to a 3rd party utility? Can I do everything in VSTO? Is there a chart somewhere of C API to C# conversions?

Solution

  • Excel-DNA should indeed make your Excel C++ to .NET conversion much easier.

    You should be careful of trying to mix VSTO and Excel-DNA. They don't live together happily in the same add-in, so you should either base everything on Excel-DNA (which allows you to access both the C API and the COM interfaces), or make two separate add-ins (VSTO has some ribbon and other high-level wrappers which can be convenient.)

    To access the C API from Excel-DNA you use the XlCall class as you have noticed, together with the ExcelReference class, which contains the information from a reference type XLOPER. With Excel-DNA you never have to deal with XLOPERs explicitly, all type conversions are automatically done when you make the XlCall.Excel(...) call.

    You should not confuse the C API helper type ExcelReference, with the COM type Range. You can convert back-and forth, but they are not interchangeable. For C API calls you need the ExcelReference type.

    Turning to your example, it's not clear what controllerRange is, but I'd guess type xlr type is the equivalent of Excel-DNA's ExcelReference type, rather then the COM Range type you are using (as Excel.Range). Here is a post about converting between ExcelReference and Range: http://groups.google.com/group/exceldna/browse_frm/thread/7a16e20e9067d3d2.

    When you have an ExcelReference, your calls are correct. So this should work:

      m_pControllerReference = new ExcelReference(0,0,0,0, "Sheet1"); // Cell A1
  m_referenceSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, m_pControllerReference ); 
  m_controllerSheetId =  XlCall.Excel(XlCall.xlSheetId, m_referenceSheetName);
  // or just: 
  m_controllerSheetId =  m_pControllerReference.SheetId;

    Now I'm not sure what your last line does - it seems to create another xlr object. The ExcelReference has properties RowFirst, RowLast that you'd use to check how many rows it has.