ABAP Operate Excel by DOI

Desktop Office Integration (online help document): http://help.sap.com/saphelp_nw04/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

Cite from: http://www.itpub.net/thread-925318-1-1.html

Example:

*&---------------------------------------------------------------------*

*& Report  Z_Rocky_TEST

*&

*&---------------------------------------------------------------------*

*&  Rocky Wang

*&  RockyTech (AT) 126.com

*&  RockTech.yculblog.com

*&---------------------------------------------------------------------*

 

REPORT  Z_ROCKY_TEST.

TABLES: MAKT.

***-----------------------------------***

***  excel related declaring

***-----------------------------------***

TYPE-POOLS: SLIS,VRM, SBDST, SOI.

CONSTANTS DOCUMENT_NAME(30) VALUE 'TEST'.

CONSTANTS INPLACE VALUE 'X'.

DATA: FLAG .

 

DATA: CONTAINER   TYPE REF TO CL_GUI_CUSTOM_CONTAINER,

      CONTROL     TYPE REF TO I_OI_CONTAINER_CONTROL,

      DOCUMENT    TYPE REF TO I_OI_DOCUMENT_PROXY,

      SPREADSHEET TYPE REF TO I_OI_SPREADSHEET,

      ERROR       TYPE REF TO I_OI_ERROR,

      ERRORS TYPE REF TO I_OI_ERROR OCCURS 0 WITH HEADER LINE.

 

CONTROLS:   EXCELDATA TYPE TABLEVIEW USING SCREEN 0100.

DATA:       TABLENAME(10), OKCODE(15),

            ROW(4), COLUMN(4), DATA(39).

 

* spreadsheet interface structures for Excel data input

DATA: RANGEITEM TYPE SOI_RANGE_ITEM.

DATA: RANGES TYPE SOI_RANGE_LIST.

DATA: EXCEL_INPUT TYPE SOI_GENERIC_TABLE.

DATA: EXCEL_INPUT_WA TYPE SOI_GENERIC_ITEM.

DATA: INITIALIZED(1), RETCODE TYPE SOI_RET_STRING.

DATA: ITEM_URL(256), ALREADY_DONE, NEWNAME(40).

DATA  DOCUMENT_TYPE(80).

DATA: APP TYPE VRM_ID, APPLIST TYPE VRM_VALUES.

DATA: EXCEL(80) VALUE 'Excel.Sheet'.

DATA: LINE_COUNT TYPE I,

      COLUMN_COUNT TYPE I.

 

DATA: OK_CODE TYPE SY-UCOMM,

      SAVE_OK TYPE SY-UCOMM.

 

CLASS C_OI_ERRORS DEFINITION LOAD.

 

DATA: BEGIN OF ITAB OCCURS 0.

        INCLUDE STRUCTURE MAKT.

DATA: END   OF ITAB.

 

SELECT-OPTIONS MATNR FOR MAKT-MATNR.

 

START-OF-SELECTION.

  PERFORM GETDATA.

 

  CALL SCREEN 100.

 

 

*&---------------------------------------------------------------------*

*&      Form  getdata

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*  -->  p1        text

*  <--  p2        text

*----------------------------------------------------------------------*

FORM GETDATA .

  SELECT *

    FROM MAKT

    INTO TABLE ITAB

   WHERE MAKT~MATNR IN MATNR.

ENDFORM.                    " getdata

*&---------------------------------------------------------------------*

*&      Module  STATUS_0100  OUTPUT

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

MODULE STATUS_0100 OUTPUT.

  SET PF-STATUS 'SA1'.

  IF FLAG = SPACE .

    PERFORM CREATE_BASIC_OBJECTS USING ''  '' '' '' DOCUMENT_NAME.

    PERFORM OUTPUT_TO_EXCEL.

  ENDIF.

ENDMODULE.                    "STATUS_0100 OUTPUT

 

*&---------------------------------------------------------------------*

*&      Module  USER_COMMAND_0100  INPUT

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

MODULE USER_COMMAND_0100 INPUT.

  FLAG = 'X'.

  SAVE_OK = OK_CODE.

  CLEAR OK_CODE.

  CASE SAVE_OK.

    WHEN 'STOP' .

      IF NOT DOCUMENT IS INITIAL.

        CALL METHOD DOCUMENT->CLOSE_DOCUMENT.

        FREE DOCUMENT.

      ENDIF.

      IF NOT CONTROL IS INITIAL.

        CALL METHOD CONTROL->DESTROY_CONTROL.

        FREE CONTROL.

      ENDIF.

      LEAVE PROGRAM.

    WHEN 'BACK' .

      IF NOT DOCUMENT IS INITIAL.

        CALL METHOD DOCUMENT->CLOSE_DOCUMENT.

        FREE DOCUMENT.

      ENDIF.

      IF NOT CONTROL IS INITIAL.

        CALL METHOD CONTROL->DESTROY_CONTROL.

        FREE CONTROL.

      ENDIF.

 

      SET SCREEN 0.  " quit the program

      "set screen 1000.

  ENDCASE.

 

ENDMODULE.                 " USER_COMMAND_0100  INPUT

*&---------------------------------------------------------------------*

*&      Form  CREATE_BASIC_OBJECTS

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->P_APP_NAME   text

*      -->P_CLASSNAME  text

*      -->P_CLASSTYPE  text

*      -->P_OBJ_KEY    text

*      -->P_DOCNAME    text

*----------------------------------------------------------------------*

FORM  CREATE_BASIC_OBJECTS USING P_APP_NAME

                                 P_CLASSNAME

                                 P_CLASSTYPE

                                 P_OBJ_KEY

                                 P_DOCNAME.

 

  CHECK INITIALIZED IS INITIAL.

* first get the SAP DOI i_oi_container_control interface

  CALL METHOD

    C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL

    IMPORTING

      CONTROL = CONTROL

      ERROR   = ERROR.

* check no errors occured

  CALL METHOD ERROR->RAISE_MESSAGE

    EXPORTING

      TYPE = 'E'.

  CREATE OBJECT CONTAINER

            EXPORTING CONTAINER_NAME = 'CONTAINER'.

  DATA L_APP_NAME(200).

  IF P_APP_NAME IS INITIAL.

    L_APP_NAME = 'TEST'.

  ELSE.

    L_APP_NAME = P_APP_NAME.

  ENDIF.

  CALL METHOD CONTROL->INIT_CONTROL

    EXPORTING

      R3_APPLICATION_NAME      = L_APP_NAME

      INPLACE_ENABLED          = INPLACE

      INPLACE_SCROLL_DOCUMENTS = 'X'

      PARENT                   = CONTAINER

      REGISTER_ON_CLOSE_EVENT  = 'X'

      REGISTER_ON_CUSTOM_EVENT = 'X'

      NO_FLUSH                 = 'X'

    IMPORTING

      ERROR                    = ERRORS.

* save error object in collection

  APPEND ERRORS.

  CLEAR ITEM_URL.

  DATA: BDS_INSTANCE TYPE REF TO CL_BDS_DOCUMENT_SET.

  DATA: DOC_SIGNATURE TYPE SBDST_SIGNATURE,

        WA_DOC_SIGNATURE LIKE LINE OF DOC_SIGNATURE,

        DOC_COMPONENTS TYPE SBDST_COMPONENTS,

        DOC_URIS TYPE SBDST_URI,

        WA_DOC_URIS LIKE LINE OF DOC_URIS.

*以下三个值为Tcode:OAOR里面新建模板文件的参数

  DATA: DOC_CLASSNAME TYPE SBDST_CLASSNAME VALUE 'PICTURES',

        DOC_CLASSTYPE TYPE SBDST_CLASSTYPE VALUE 'OT',

        DOC_OBJECT_KEY TYPE SBDST_OBJECT_KEY VALUE 'ZEXCEL'.

 

  WA_DOC_SIGNATURE-PROP_NAME = 'DESCRIPTION'.

  APP = 'excel'.

  IF APP = 'excel'.

    DOCUMENT_TYPE = EXCEL.

    WA_DOC_SIGNATURE-PROP_VALUE = P_DOCNAME.

  ELSE.

  ENDIF.

  APPEND WA_DOC_SIGNATURE TO DOC_SIGNATURE.

  CREATE OBJECT BDS_INSTANCE.

  CALL METHOD BDS_INSTANCE->GET_INFO

    EXPORTING

      CLASSNAME  = DOC_CLASSNAME

      CLASSTYPE  = DOC_CLASSTYPE

      OBJECT_KEY = DOC_OBJECT_KEY

    CHANGING

      COMPONENTS = DOC_COMPONENTS

      SIGNATURE  = DOC_SIGNATURE.

 

  CALL METHOD BDS_INSTANCE->GET_WITH_URL

    EXPORTING

      CLASSNAME  = DOC_CLASSNAME

      CLASSTYPE  = DOC_CLASSTYPE

      OBJECT_KEY = DOC_OBJECT_KEY

    CHANGING

      URIS       = DOC_URIS

      SIGNATURE  = DOC_SIGNATURE.

 

  FREE BDS_INSTANCE.

 

  READ TABLE DOC_URIS INTO WA_DOC_URIS INDEX 1.

 

  ITEM_URL = WA_DOC_URIS-URI.

 

* ask the SAP DOI container for a i_oi_document_proxy for Excel

  CALL METHOD CONTROL->GET_DOCUMENT_PROXY

    EXPORTING

      DOCUMENT_TYPE  = 'Excel.Sheet'

      NO_FLUSH       = 'X'

    IMPORTING

      DOCUMENT_PROXY = DOCUMENT

      ERROR          = ERRORS.

  APPEND ERRORS.

 

* open a document saved in business document service.

  CALL METHOD DOCUMENT->OPEN_DOCUMENT

    EXPORTING

      OPEN_INPLACE = INPLACE

      DOCUMENT_URL = ITEM_URL.

 

  DATA: HAS TYPE I.

  CALL METHOD DOCUMENT->HAS_SPREADSHEET_INTERFACE

    EXPORTING

      NO_FLUSH     = ''

    IMPORTING

      IS_AVAILABLE = HAS

      ERROR        = ERRORS.

  APPEND ERRORS.

 

  CALL METHOD DOCUMENT->GET_SPREADSHEET_INTERFACE

    EXPORTING

      NO_FLUSH        = ' '

    IMPORTING

      SHEET_INTERFACE = SPREADSHEET

      ERROR           = ERRORS.

  APPEND ERRORS.

* Activate  sheet 1

  CALL METHOD SPREADSHEET->SELECT_SHEET

    EXPORTING

      NAME     =  '???'

*    NO_FLUSH = ' '

    IMPORTING

      ERROR    = ERRORS.

*    RETCODE  =

  .

  APPEND ERRORS.

 

  LOOP AT ERRORS.

    CALL METHOD ERRORS->RAISE_MESSAGE

      EXPORTING

        TYPE = 'E'.

  ENDLOOP.

  FREE ERRORS.

  INITIALIZED = 'X'.

ENDFORM.                    "CREATE_BASIC_OBJECTS

 

*&---------------------------------------------------------------------*

*&      Form  output_to_excel

*&---------------------------------------------------------------------*

*       fill the EXCEL sheet

*----------------------------------------------------------------------*

FORM OUTPUT_TO_EXCEL.

  DATA NUM TYPE I VALUE 1.

  LOOP AT ITAB.

    NUM = NUM + SY-TABIX.

    PERFORM FILL_CELL USING NUM 1 ITAB-MATNR.

    PERFORM FILL_CELL USING NUM 2 ITAB-SPRAS.

    PERFORM FILL_CELL USING NUM 3 ITAB-MAKTX.

    PERFORM FILL_CELL USING NUM 4 ITAB-MAKTG.

    NUM = 1.

  ENDLOOP.

ENDFORM.                  "output_to_excel

 

*&---------------------------------------------------------------------*

*&      Form  FILL_CELL

*&---------------------------------------------------------------------*

*       text

*----------------------------------------------------------------------*

*      -->I          text

*      -->J          text

*      -->VAL        text

*----------------------------------------------------------------------*

FORM FILL_CELL  USING I J VAL.

  DATA: COLUMNS_NUMBER TYPE I,

        ROWS_NUMBER    TYPE I.

 

  COLUMNS_NUMBER = 1.

  ROWS_NUMBER = 1.

 

  CALL METHOD SPREADSHEET->INSERT_RANGE_DIM

    EXPORTING

      NAME     = 'cell'

      NO_FLUSH = 'X'

      TOP      = I

      LEFT     = J

      ROWS     = ROWS_NUMBER

      COLUMNS  = COLUMNS_NUMBER

    IMPORTING

      ERROR    = ERRORS.

  APPEND ERRORS.

 

  REFRESH: RANGES, EXCEL_INPUT.

  RANGEITEM-NAME = 'cell'.

  RANGEITEM-COLUMNS = 1.

  RANGEITEM-ROWS = 1.

  APPEND RANGEITEM TO RANGES.

 

  EXCEL_INPUT_WA-COLUMN = 1.

  EXCEL_INPUT_WA-ROW = 1.

  EXCEL_INPUT_WA-VALUE = VAL.

  APPEND EXCEL_INPUT_WA TO EXCEL_INPUT.

 

* set data

  CALL METHOD SPREADSHEET->SET_RANGES_DATA

    EXPORTING

      RANGES   = RANGES

      CONTENTS = EXCEL_INPUT

      NO_FLUSH = 'X'

    IMPORTING

      ERROR    = ERRORS.

  APPEND ERRORS.

 

  CALL METHOD SPREADSHEET->FIT_WIDEST

    EXPORTING

      NAME     = SPACE

      NO_FLUSH = 'X'.

 

  REFRESH: RANGES, EXCEL_INPUT.

 

ENDFORM.                    "fill_cell