Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

This program shows how to display internal table data in excel with picture using OLE. 
Step 1: Go To transaction OAOR.

            Give class name: SOFFICEINTEGRATION,

            Class type: OT,

            Object key: SOFFICEINTEGRATION.

Step 2: Under create right click on table template and click on import file and import the

            Excel sheet you want to put in data with a macro which would display the picture.

Step 3: Write this macro code:

            Sub macro1(param1 As String)

            Dim myPicture As String

            Dim pic As Picture

            Dim r As Range

            myPicture = param1

            Set r = Range("g1")  " Provide the cell where the picture would be displayed"

            Set pic = ActiveSheet.Pictures.Insert(myPicture)

            End Sub   

You would find the excel sheet listed under table template.

Step 4: Then execute the following code and it would require you to give carrier id connection id and any picture from presentation server.   

And click on execute (F8). 



 

Error rendering macro 'code': Invalid value specified for parameter 'lang'
REPORT  YH1303_OLE_EXCELPIC.
TYPE-POOLS: SOI.
TABLES: SFLIGHT.
TYPES:
    BEGIN OF TYPE_S_FLIGHT,
    CARRID TYPE SFLIGHT-CARRID,
    CONNID TYPE SFLIGHT-CONNID,
    FLDATE TYPE D,
    PRICE  TYPE SFLIGHT-PRICE,
    SEATSMAX TYPE SFLIGHT-SEATSMAX,
    END OF TYPE_S_FLIGHT.
*" Data declarations...................................................
*"--------------------------------------------------------------------*
* Work variables                                                      *
*"--------------------------------------------------------------------*
DATA:
  FL_STOP        TYPE C,                " Flag for program flow
  W_CL_CONTROL   TYPE REF TO I_OI_CONTAINER_CONTROL,
                                       " Document Container Control
                                       " Interface
  W_CL_DOCMNT    TYPE REF TO I_OI_DOCUMENT_PROXY,
                                       " General Document Interface
  W_CL_ERROR     TYPE REF TO I_OI_ERROR," Error Object
  OBJ_CL_CONTNER TYPE REF TO CL_GUI_CUSTOM_CONTAINER,
                                       " Container for Custom Controls
                                       " in the Screen Area
  W_CL_BDS_INST  TYPE REF TO CL_BDS_DOCUMENT_SET,
                                       " BusinessDocumentSet
  W_FIELDNAME    TYPE DYNPREAD-FIELDNAME,
  W_FILENAME     TYPE IBIPPARMS-PATH,
  W_CL_XL        TYPE REF TO I_OI_SPREADSHEET.
" Excel sheet interface
*"--------------------------------------------------------------------*
* Constants                                                           *
*"--------------------------------------------------------------------*
CONSTANTS:
 C_IND          TYPE C VALUE 'X',      " Indicator
 C_ERROR        TYPE C VALUE 'E',      " Error message type
 C_SUCC(2)      TYPE C VALUE 'OK'.     " Success
*"--------------------------------------------------------------------*
* Internal table to hold Document variable data                       *
*"--------------------------------------------------------------------*
DATA:
* Internal table to hold the document variables
 T_DOC_VAR       TYPE SOI_FORM_LIST,
* Internal table to hold the document variables
 T_DOC_VAR1      TYPE SOI_FORM_LIST,
* Structure to hold the document variable
 FS_DOC_VAR      TYPE SOI_FORM_ITEM,
 T_DATA          TYPE SOI_GENERIC_TABLE,
 T_RANGES        TYPE SOI_RANGE_LIST,
 T_FIELDS        TYPE STANDARD TABLE OF RFC_FIELDS,
 T_CARRID        TYPE STANDARD TABLE OF TYPE_S_FLIGHT.
*"--------------------------------------------------------------------*
*                       START-OF-SELECTION EVENT                      *
*"--------------------------------------------------------------------*
START-OF-SELECTION.
  CALL SCREEN 100.
*&---------------------------------------------------------------------*
*&      Module  STATUS_0100  OUTPUT
*&---------------------------------------------------------------------*
* This Module sets the PBO of the screen.
*----------------------------------------------------------------------*
MODULE STATUS_0100 OUTPUT.
  SET PF-STATUS 'ABC'.
  SET TITLEBAR 'ABC'.
ENDMODULE.                             "STATUS_0100 OUTPUT
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE USER_COMMAND_0100 INPUT.
  CASE SY-UCOMM.
    WHEN 'EXEC'.
      PERFORM PROCESS_LOGIC.
      IF FL_STOP IS INITIAL.
        PERFORM DISPLAY_LOGIC.
      ENDIF.
    WHEN OTHERS.
      REFRESH T_CARRID.
      REFRESH T_DOC_VAR1.
      LEAVE TO SCREEN 0.
  ENDCASE.
ENDMODULE.                             " FORM USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*&      Form  RAISE_ERROR_MESSAGE                                      *
*&---------------------------------------------------------------------*
* This subroutine raises an error message                              *
*----------------------------------------------------------------------*
* No interface parameters passed                                       *
*----------------------------------------------------------------------*
FORM RAISE_ERROR_MESSAGE .
  " Error Object
  CALL METHOD W_CL_ERROR->RAISE_MESSAGE
    EXPORTING
      TYPE = C_ERROR.
  FL_STOP = C_IND.
ENDFORM.                               " FORM RAISE_ERROR_MESSAGE
*&---------------------------------------------------------------------*
*&      Form  ASSIGN_VALUES
*&---------------------------------------------------------------------*
* This subroutine fills the value in the document                      *
*----------------------------------------------------------------------*
* No Interface parameters passed.                                      *
*----------------------------------------------------------------------*
FORM ASSIGN_VALUES .
  LOOP AT T_DOC_VAR INTO FS_DOC_VAR.
    CASE FS_DOC_VAR-NAME.
      WHEN 'carrid'.
        FS_DOC_VAR-VALUE = SFLIGHT-CARRID.
        APPEND FS_DOC_VAR TO T_DOC_VAR1.
      WHEN 'Connid'.
        FS_DOC_VAR-VALUE = SFLIGHT-CONNID.
        APPEND FS_DOC_VAR TO T_DOC_VAR1.
    ENDCASE.
  ENDLOOP.
ENDFORM.                               " FORM ASSIGN_VALUES
*&---------------------------------------------------------------------*
*&      Form  PROCESS_LOGIC
*&---------------------------------------------------------------------*
* This subroutine selects carrier id from SCARR table                  *
*----------------------------------------------------------------------*
*  No interface parameters passed                                      *
*----------------------------------------------------------------------*
FORM PROCESS_LOGIC .
  SELECT SINGLE CARRID
                CONNID
           FROM SPFLI
           INTO (SFLIGHT-CARRID,SFLIGHT-CONNID)
          WHERE CARRID EQ SFLIGHT-CARRID AND CONNID EQ SFLIGHT-CONNID.
  IF SY-SUBRC EQ 0.
    SELECT CARRID
           CONNID
           FLDATE
           PRICE
           SEATSMAX
      FROM SFLIGHT
      INTO CORRESPONDING FIELDS OF TABLE T_CARRID
      UP TO 10 ROWS
      WHERE CARRID EQ SFLIGHT-CARRID AND CONNID EQ SFLIGHT-CONNID.
  ENDIF.
ENDFORM.                               " FORM PROCESS_LOGIC
*&---------------------------------------------------------------------*
*&      Form  DISPLAY_LOGIC                                            *
*&---------------------------------------------------------------------*
* This subroutine displays the document.                               *
*----------------------------------------------------------------------*
*  No interface parameters passed
*
*----------------------------------------------------------------------*
FORM DISPLAY_LOGIC .
  PERFORM CREATE_INSTANCES.
  PERFORM GET_FILE_FROM_BDS.
  PERFORM FILL_VALUE_TABLE.
ENDFORM.                               " FORM DISPLAY_LOGIC
*&---------------------------------------------------------------------*
*&      Form  CREATE_INSTANCES                                         *
*&---------------------------------------------------------------------*
* This subroutine creates an instance of the document                  *
*----------------------------------------------------------------------*
*  No interface parameters passed                                      *
*----------------------------------------------------------------------*
FORM CREATE_INSTANCES .
  DATA:
* Internal table to hold the Registered document types
     T_LIST   TYPE SOI_DOCUMENT_TYPE_DESCR_LIST,
* Structure to hold the Registered document type
     FS_LIST  LIKE LINE OF T_LIST.
* Creates a Container Control Instance
  CALL METHOD C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL
    IMPORTING
      CONTROL = W_CL_CONTROL
      ERROR   = W_CL_ERROR.
  IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
    PERFORM RAISE_ERROR_MESSAGE.
    EXIT.
  ENDIF .
* Container for Custom Controls in the Screen Area
  CREATE OBJECT OBJ_CL_CONTNER
    EXPORTING
      CONTAINER_NAME              = 'CONTAINER'
    EXCEPTIONS
      CNTL_ERROR                  = 1
      CNTL_SYSTEM_ERROR           = 2
      CREATE_ERROR                = 3
      LIFETIME_ERROR              = 4
      LIFETIME_DYNPRO_DYNPRO_LINK = 5
      OTHERS                      = 6.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
  FREE W_CL_ERROR.
  " Document Container Control Interface
  " Creates and Initializes the Control
  CALL METHOD W_CL_CONTROL->INIT_CONTROL
    EXPORTING
      INPLACE_ENABLED          = ' '
      INPLACE_SCROLL_DOCUMENTS = ' '
      R3_APPLICATION_NAME      = 'carrid'
      PARENT                   = OBJ_CL_CONTNER
    IMPORTING
      ERROR                    = W_CL_ERROR
    EXCEPTIONS
      JAVABEANNOTSUPPORTED     = 1
      OTHERS                   = 2.
  IF SY-SUBRC <> 0.
    MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
               WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
    FL_STOP = C_IND.
    EXIT.
  ENDIF.
  IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
    PERFORM RAISE_ERROR_MESSAGE.
    EXIT.
  ENDIF.
  FREE W_CL_ERROR.
* Returns a List of All Existing Applications
  CALL METHOD W_CL_CONTROL->GET_REGISTERED_DOC_TYPES
    EXPORTING
      INTERFACE_TYPE = 'E'
*      no_flush       = ' '
    IMPORTING
      DESCR_LIST     = T_LIST
      ERROR          = W_CL_ERROR
      .
  IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
    PERFORM RAISE_ERROR_MESSAGE.
    EXIT.
  ENDIF.
  FREE W_CL_ERROR.
  READ TABLE T_LIST INTO FS_LIST INDEX 1.
  IF SY-SUBRC EQ 0.
* Creates an Instance for Document Management
    CALL METHOD W_CL_CONTROL->GET_DOCUMENT_PROXY
      EXPORTING
        DOCUMENT_TYPE      = FS_LIST-DOCUMENT_TYPE
        REGISTER_CONTAINER = 'X'
      IMPORTING
        DOCUMENT_PROXY     = W_CL_DOCMNT
        ERROR              = W_CL_ERROR.
    IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
      PERFORM RAISE_ERROR_MESSAGE.
      EXIT.
    ENDIF.
  ENDIF.
ENDFORM.                    " CREATE_INSTANCES
*&---------------------------------------------------------------------*
*&      Form  GET_FILE_FROM_BDS                                        *
*&---------------------------------------------------------------------*
* This subroutine gets the specified file from BDS                     *
*----------------------------------------------------------------------*
*  No interface parameters passed                                      *
*----------------------------------------------------------------------*
FORM GET_FILE_FROM_BDS .
  CONSTANTS:
      C_DOCU   TYPE SBDST_CLASSNAME  VALUE 'SOFFICEINTEGRATION',
      C_TYPE   TYPE SBDST_CLASSTYPE  VALUE 'OT',
      C_DOC_OK TYPE SBDST_OBJECT_KEY VALUE 'SOFFICEINTEGRATION'.
  DATA:
    T_DOC_SIGNATURE   TYPE SBDST_SIGNATURE,
                                       " BDS: Signature Table
    T_DOC_URIS        TYPE SBDST_URI,  " BDS: URI Table
    W_CL_HANDLE       TYPE REF TO I_OI_FORM,
                                       " Form Interface
    FS_DOC_SIGNATURE  LIKE LINE OF T_DOC_SIGNATURE,
                                       " Field String
    FS_DOC_URIS       LIKE LINE OF T_DOC_URIS.
  " Field String
* BusinessDocumentSet
  CREATE OBJECT W_CL_BDS_INST.
  FS_DOC_SIGNATURE-PROP_NAME = 'DESCRIPTION'.
  " BDS: Attribute Name
  FS_DOC_SIGNATURE-PROP_VALUE = 'OLEXL'.
  " BDS: Attribute value
  APPEND FS_DOC_SIGNATURE TO T_DOC_SIGNATURE.
*Return Document by Transferring URL
  CALL METHOD W_CL_BDS_INST->GET_WITH_URL
    EXPORTING
*      logical_system  =
      CLASSNAME       = C_DOCU
      CLASSTYPE       = C_TYPE
*      client          =
      OBJECT_KEY      = C_DOC_OK
*      url_lifetime    =
    CHANGING
      URIS            =  T_DOC_URIS
      SIGNATURE       =  T_DOC_SIGNATURE.
  FREE W_CL_BDS_INST.
  READ TABLE T_DOC_URIS INTO FS_DOC_URIS INDEX 1.
* Opens an Existing Document
  CALL METHOD W_CL_DOCMNT->OPEN_DOCUMENT
    EXPORTING
*      document_title   = ' '
      DOCUMENT_URL     = FS_DOC_URIS-URI.
* Returns an Interface Reference
  CALL METHOD W_CL_DOCMNT->GET_FORM_INTERFACE
    IMPORTING
      F_INTERFACE = W_CL_HANDLE.
* Gets Data from Application
  CALL METHOD W_CL_HANDLE->GET_DATA
    IMPORTING
      FIELDS = T_DOC_VAR.
  PERFORM ASSIGN_VALUES.
* Sets the Data in the Application
  CALL METHOD W_CL_HANDLE->SET_DATA
    EXPORTING
      FIELDS = T_DOC_VAR1
      CLEAR  = 'X'.
ENDFORM.                               " FORM GET_FILE_FROM_BDS
*&---------------------------------------------------------------------*
*&      Form  FILL_VALUE_TABLE                                         *
*&---------------------------------------------------------------------*
* This subroutine fills up the table on the form with values           *
*----------------------------------------------------------------------*
* No interface parameters passed                                       *
*----------------------------------------------------------------------*
FORM FILL_VALUE_TABLE .
  FREE W_CL_ERROR.
  CALL METHOD W_CL_DOCMNT->GET_SPREADSHEET_INTERFACE
    IMPORTING
      ERROR           = W_CL_ERROR
      SHEET_INTERFACE = W_CL_XL.
  CALL METHOD W_CL_DOCMNT->EXECUTE_MACRO
    EXPORTING
      MACRO_STRING = 'module2.macro1'
      PARAM1       = W_FILENAME
      PARAM_COUNT  = 1
    IMPORTING
      ERROR        = W_CL_ERROR.
  IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
    PERFORM RAISE_ERROR_MESSAGE.
    EXIT.
  ENDIF.
  PERFORM FILL_TABLES.
ENDFORM.                               " FORM FILL_VALUE_TABLE
*&---------------------------------------------------------------------*
*&      Form  FILL_TABLES
*&---------------------------------------------------------------------*
*  This subroutine fills value in table                                *
*----------------------------------------------------------------------*
*  No interface parameters passed                                      *
*----------------------------------------------------------------------*
FORM FILL_TABLES .
  DATA:
    T_SHEET TYPE SOI_SHEETS_TABLE,
    FS_SHEET LIKE LINE OF T_SHEET.
  CALL METHOD W_CL_XL->GET_SHEETS
    EXPORTING
      NO_FLUSH = ' '
    IMPORTING
      SHEETS   = T_SHEET
      ERROR    = W_CL_ERROR.
  IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
    PERFORM RAISE_ERROR_MESSAGE.
    EXIT.
  ENDIF.
  LOOP AT T_SHEET INTO FS_SHEET.
    CALL METHOD W_CL_XL->SELECT_SHEET
      EXPORTING
        NAME  = FS_SHEET-SHEET_NAME
      IMPORTING
        ERROR = W_CL_ERROR.
    IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
      PERFORM RAISE_ERROR_MESSAGE.
      EXIT.
    ENDIF.
    CALL METHOD W_CL_XL->SET_SELECTION
      EXPORTING
        LEFT    = 1
        TOP     = 5
        ROWS    = 10
        COLUMNS = 5.
    CALL METHOD W_CL_XL->INSERT_RANGE
      EXPORTING
        COLUMNS = 5
        ROWS    = 10
        NAME    = 'Test'.
    CALL METHOD W_CL_XL->SET_COLOR
      EXPORTING
        RANGENAME = 'Test'
        FRONT     = 3
        BACK      = 6.
    REFRESH T_DATA.
    CALL METHOD W_CL_XL->GET_RANGES_DATA
      EXPORTING
        ALL      = 'X'
      IMPORTING
        CONTENTS = T_DATA
        ERROR    = W_CL_ERROR
      CHANGING
        RANGES   = T_RANGES.
    CALL FUNCTION 'DP_GET_FIELDS_FROM_TABLE'
      TABLES
        DATA   = T_CARRID
        FIELDS = T_FIELDS.
    CALL METHOD W_CL_XL->INSERT_ONE_TABLE
      EXPORTING
        DATA_TABLE   = T_CARRID
        FIELDS_TABLE = T_FIELDS
        RANGENAME    = 'Test'.
    IF W_CL_ERROR->ERROR_CODE NE C_SUCC.
      PERFORM RAISE_ERROR_MESSAGE.
      EXIT.
    ENDIF.
  ENDLOOP.
ENDFORM.                               " FORM FILL_TABLES
*&---------------------------------------------------------------------*
*&      Module  FILENAME_GET  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE FILENAME_GET INPUT.
  W_FIELDNAME = 'FILENAME'.
  CALL FUNCTION 'F4_FILENAME'
    EXPORTING
      FIELD_NAME = W_FIELDNAME
    IMPORTING
      FILE_NAME  = W_FILENAME.
ENDMODULE.                              " FILENAME_GET  INPUT


 

  • No labels