Previous IDL Connectivity Bridges: Using ActiveX Controls in IDL Next

Example: Spreadsheet Control

This example uses an ActiveX control that displays a spreadsheet interface. The control, contained in the file msowc.dll, is installed along with a typical installation of Microsoft Office. If the control is not present on your system (you'll know the control is not present if the example code fails when trying to realize the widget hierarchy), the example will not run.

The spreadsheet control has the program ID: 

OWC.Spreadsheet.9

and the class ID: 

{0002E510-0000-0000-C000-000000000046}

 


Note
The spreadsheet control used in this example is included with older versions of Microsoft Office; it is discussed in Microsoft Knowledge Base Article 248822. Newer versions of Microsoft Office may include spreadsheet controls with updated program and class IDs.

Information about the spreadsheet control's properties and methods was gleaned from Microsoft Excel 97 Visual Basic Step by Step by Reed Jacobson (Microsoft Press, 1997) and by inspection of the control's interface using the OLE/COM Object Viewer application provided by Microsoft. It is beyond the scope of this manual to describe the spreadsheet control's interface in detail.


Example Code
The following section develops an IDL routine called ActiveXExcel that illustrates use of the spreadsheet ActiveX control within an IDL widget hierarchy. The complete .pro file is included in the examples\doc\bridges\COM subdirectory of the IDL distribution as ActiveXExcel.pro.

  1. Create a function that will retrieve data from cells selected in the spreadsheet control. The function takes two arguments: an object reference to the IDLcomActiveX object that instantiates the spreadsheet control, and a variable to contain the data from the selected cells.
    2. FUNCTION excel_getSelection, oExcel, aData

     

  2. Retrieve an object that represents the selected cells. Note that when the ActiveX control returns this object, IDL automatically creates an IDLcomActiveX object that makes it accessible within IDL.
    3.    oExcel->GetProperty, SELECTION=oSel

     

  3. Retrieve the total number of cells selected.
    4.    oSel->GetProperty, COUNT=nCells

     

  4. If no cells are selected, destroy the selection object and return zero (the failure code).
    5.    IF (nCells LT 1) THEN BEGIN  
      OBJ_DESTROY, oSel  
      RETURN, 0  
   ENDIF

     

  5. Retrieve objects that represent the dimensions of the selection.
    6.    oSel->GetProperty, COLUMNS=oCols, ROWS=oRows

     

  6. Get the dimensions of the selection, then destroy the column and row objects.
    7.    oCols->GetProperty, COUNT=nCols  
   OBJ_DESTROY, oCols  
   oRows->GetProperty, COUNT=nRows  
   OBJ_DESTROY, oRows

     

  7. Create a floating point array with the same dimensions as the selection.
    8.    aData = FLTARR(nCols, nRows, /NOZERO);

     

  8. Iterate through the cells, doing the following:
    • Retrieve an object that represents the cell. Note that the numeric index of the FOR loop is passed to the GetProperty method as an argument.

      •  

    • Get the value contained in the cell.

      •  

    • Set the appropriate element of the aData array to the cell's value.

      •  

    • Destroy the object.
      •    FOR i=1, nCells DO BEGIN  
      oSel->GetProperty, ITEM=oItem, i  
      oItem->GetProperty, VALUE=vValue  
      aData[i-1] = vValue  
      OBJ_DESTROY, oItem  
   ENDFOR

     

  9. Destroy the selection object.
    10.    OBJ_DESTROY, oSel

     

  10. Return one (the success code) and end the function definition.
    11.    RETURN, 1  
  
END

     

  11. Next, create a procedure that sets the values of the cells in the spreadsheet. This procedure takes one argument: an object reference to the IDLcomActiveX object that instantiates the spreadsheet control.
    12. PRO excel_setData, oExcel

     

  12. Define the size of the data array.
    13.    nX = 20

     

  13. Get an object representing the active spreadsheet.
    14.    oExcel->GetProperty, ActiveSheet=oSheet

     

  14. Get an object representing the cells in the spreadsheet.
    15.    oSheet->GetProperty, CELLS=oCells

     

  15. Generate some data.
    16.    im = BESELJ(DIST(nX))

     

  16. Iterate through the elements of the data array, doing the following:
    • Retrieve an object that represents the cell that corresponds to the data element. Note that the numeric indices of the FOR loops are passed to the GetProperty method as arguments.

      •  

    • Set the value of the cell.

      •  

    • Destroy the object.
      •    FOR i=0, nX-1 DO BEGIN  
      FOR j=0, nX-1 DO BEGIN  
         oCells->GetProperty, ITEM=oItem, i+1, j+1  
         oItem->SetProperty, VALUE=im(i,j)  
         OBJ_DESTROY, oItem  
      ENDFOR  
   ENDFOR

     

  17. Destroy the spreadsheet and cell objects, and end the procedure.
    18.    OBJ_DESTROY, oSheet  
   OBJ_DESTROY, oCells  
  
END

     

  18. Next, create a procedure to handle events generated by the widget application.
    19. PRO ActiveXExcel_event, ev

     

  19. The user value of the top-level base widget is set equal to a structure that contains the widget ID of the ActiveX widget. Retrieve the structure into the variable sState.
    20.    WIDGET_CONTROL, ev.TOP, GET_UVALUE=sState, /NO_COPY

     

  20. Use the value of the DISPID field of the event structure to sort out "selection changing" events.
    21.   IF (ev.DISPID EQ 1513) THEN BEGIN

     

  21. Place data from selected cells in variable aData, using the excel_getSelection function defined above. Check to make sure that the function returns a success value (one) before proceeding.
    22.     IF (excel_getSelection(sState.oExcel, aData) NE 0) THEN BEGIN

     

  22. Get the dimensions of the aData variable.
    23.        szData = SIZE(aData)

     

  23. If aData is two-dimensional, display a surface, otherwise, plot the data.
    24.        IF (szData[0] GT 1 AND szData[1] GT 1 AND szData[2] GT 1) $  
         THEN SURFACE, aData $  
       ELSE $  
         PLOT, aData  
    ENDIF  
  
  ENDIF

     

  24. Reset the state variable sState and end the procedure.
    25.   WIDGET_CONTROL, ev.TOP, SET_UVALUE=sState, /NO_COPY  
  
END

     

  25. Create the main widget creation routine.
    26. PRO ActiveXExcel  
  
   !EXCEPT=0  ; Ignore floating-point underflow errors.

     

  26. Create a top-level base widget.
    27.    wBase = WIDGET_BASE(COLUMN=1, $  
      TITLE="IDL ActiveX Spreadsheet Example")

     

  27. Instantiate the ActiveX spreadsheet control in a widget.
    28.    wAx=WIDGET_ACTIVEX(wBase, $  
      '{0002E510-0000-0000-C000-000000000046}', $  
      SCR_XSIZE=600, SCR_YSIZE=400)

     

  28. Realize the widget hierarchy.
    29.    WIDGET_CONTROL, wBase, /REALIZE

     

  29. The value of an ActiveX widget is an object reference to the IDLcomActiveX object that encapsulates the ActiveX control. Retrieve the object reference in the variable oExcel.
    30.    WIDGET_CONTROL, wAx, GET_VALUE=oExcel

     

  30. Turn off the TitleBar property on the spreadsheet control.
    31.    oExcel->SetProperty, DisplayTitleBar=0

     

  31. Populate the spreadsheet control with data, using the excel_setData function defined above.
    32.    excel_setData, oExcel

     

  32. Set the user value of the top-level base widget to an anonymous structure that contains the widget ID of the spreadsheet ActiveX widget.
    33.    WIDGET_CONTROL, wBase, SET_UVALUE={oExcel:oExcel}

     

  33. Call XMANAGER to manage the widgets, and end the procedure.
    34.    XMANAGER,'ActiveXExcel', wBase, /NO_BLOCK  
END

Running the ActiveXExcel procedure display widgets that look like the following:

 

Figure 4-2: An IDL Widget Program Using an ActiveX Spreadsheet Control

Figure 4-2: An IDL Widget Program Using an ActiveX Spreadsheet Control

  IDL Online Help (March 06, 2007)