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).
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