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

Question

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?

score 1 · Answer 1 · answered Mar 28 '12 at 14:30

"Everyting" is a big word, but you can do many things in VSTO :-).

Your C# code may work, but this syntax is not what you would typically use in VSTO. You can do like this:

Range controllerRange = <your code here> //... get your range
string referenceSheetName = controllerRange.Worksheet.Name;
// I'm not aware of any sheet ID in VSTO
if (controllerRange.Rows.Count > 6)
... etc...

As you see, you can work directly on the Range objects, no need to work with a reference and use the reference as a parameter to functions.

Govert · Accepted Answer · 2012-03-29T12:55:26.907

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.

class xlr derives from xl0 which derives from CObject. xlr( LPXLOPER xltypeRef_lpxloper, int idx, BOOL readData=FALSE) ; // Single range reference from a disjoint reference (via a zero based index) // optionally read the data in — Andrew Roberts, Mar 29 '12 at 12:40
OK then it seems to wrap an XLOPER of type xltypeRef, so ExcelReference would be the Excel-DNA counterpart. — Govert, Mar 29 '12 at 12:54

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

2 Answers2