Leggere un file di Excel via Citrix

*----------------------------------------------------------------------*
* DECLARATIONS                                                         *
*----------------------------------------------------------------------*
DATA: BEGIN OF tb_xls OCCURS 0,
        kdmat       LIKE VBAP-KDMAT,
        zhtccd      LIKE ZSD_TRU_HTC_MAT-ZHTCCD,
        ihrez_e     LIKE VBKD-IHREZ_E,
        taxm1(3)    TYPE C,
        kbmeng      LIKE VBAP-KBMENG,
        zzccn       LIKE ZSD_TRU_CCN-ZZCCN,
        zprgccn     LIKE VBAP-ZPRGCCN,
      END OF tb_xls.

*----------------------------------------------------------------------*
* LAYOUT                                                               *
*----------------------------------------------------------------------*
PARAMETERS: pa_local(255) TYPE C LOWER CASE OBLIGATORY.

*----------------------------------------------------------------------*
* FORM READ_XLS                                                        *
*----------------------------------------------------------------------*
FORM read_xls.

  TYPE-POOLS SOI.

  DATA: lo_container   TYPE REF TO CL_GUI_CUSTOM_CONTAINER,
        li_control     TYPE REF TO I_OI_CONTAINER_CONTROL,
        li_document    TYPE REF TO I_OI_DOCUMENT_PROXY,
        li_spreadsheet TYPE REF TO I_OI_SPREADSHEET,
        li_error       TYPE REF TO I_OI_ERROR,
        lv_url         TYPE C LENGTH 256,
        lt_sheets      TYPE SOI_SHEETS_TABLE,
        ls_sheets      TYPE SOI_SHEETS,
        lt_ranges      TYPE SOI_RANGE_LIST,
        ls_rcode       TYPE SOI_RET_STRING,
        lt_data        TYPE SOI_GENERIC_TABLE,
        ls_data        TYPE SOI_GENERIC_ITEM.

  C_OI_CONTAINER_CONTROL_CREATOR=>GET_CONTAINER_CONTROL(
     IMPORTING
       CONTROL = li_control
       ERROR   = li_error ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'E' ).
  ENDIF.

  CREATE OBJECT lo_container
    EXPORTING
      CONTAINER_NAME              = 'CONT'
    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 E000(YA1) WITH TEXT-E17.
  ENDIF.

  li_control->init_control(
    EXPORTING
       INPLACE_ENABLED      = 'X'
       R3_APPLICATION_NAME  = 'EXCEL CONTAINER'
       PARENT               = lo_container
    IMPORTING
       ERROR                = li_error
    EXCEPTIONS
       JAVABEANNOTSUPPORTED = 1
       OTHERS               = 2 ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'E' ).
  ENDIF.

  li_control->get_document_proxy(
    EXPORTING
      DOCUMENT_TYPE  = SOI_DOCTYPE_EXCEL_SHEET
    IMPORTING
      DOCUMENT_PROXY = li_document
      ERROR          = li_error ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'E' ).
  ENDIF.

  CONCATENATE 'FILE://' pa_local INTO lv_url.

  li_document->open_document(
    EXPORTING
      DOCUMENT_TITLE = 'Excel'
      DOCUMENT_URL   = lv_url
      OPEN_INPLACE   = 'X'
      OPEN_READONLY  = 'X'
    IMPORTING
      ERROR          = li_error
      RETCODE        = ls_rcode ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'I' ).
    MESSAGE E000(YA1) WITH ls_rcode.
    LEAVE LIST-PROCESSING.
  ENDIF.

  li_document->get_spreadsheet_interface(
    EXPORTING
      NO_FLUSH        = ' '
    IMPORTING
      ERROR           = li_error
      SHEET_INTERFACE = li_spreadsheet ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'I' ).
    LEAVE LIST-PROCESSING.
  ENDIF.

  li_spreadsheet->get_sheets(
    EXPORTING
      NO_FLUSH = ' '
    IMPORTING
      SHEETS   = lt_sheets
      ERROR    = li_error ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'I' ).
    LEAVE LIST-PROCESSING.
  ENDIF.

  LOOP AT lt_sheets INTO ls_sheets.
    li_spreadsheet->select_sheet(
      EXPORTING
        NAME  = ls_sheets-sheet_name
      IMPORTING
        ERROR = li_error ).
    IF li_error->has_failed = 'X'.
      EXIT.
    ENDIF.

    li_spreadsheet->set_selection(
      top     = 4 "salto le prime 3 righe
      left    = 1
      rows    = 9999
      columns = 7 ).

    li_spreadsheet->insert_range(
      EXPORTING
        NAME     = 'CCN'
        ROWS     = 9999
        COLUMNS  = 7
        NO_FLUSH = ' '
      IMPORTING
        ERROR    = li_error ).
    IF li_error->has_failed = 'X'.
      EXIT.
    ENDIF.

    li_spreadsheet->get_ranges_names(
      IMPORTING
        ranges  = lt_ranges
        retcode = ls_rcode ).

    DELETE lt_ranges WHERE NAME <> 'CCN'.

    CLEAR lt_data[].

    li_spreadsheet->get_ranges_data(
      EXPORTING
        ALL      = ' '
      IMPORTING
        CONTENTS = lt_data
        ERROR    = li_error
        RETCODE  = ls_rcode
      CHANGING
        RANGES   = lt_ranges ).
    DELETE lt_data WHERE value IS INITIAL
                      OR value = SPACE.

*leggo il primo foglio e poi esco
    EXIT.

  ENDLOOP.

  IF li_error->has_succeeded = 'X'.

    LOOP AT lt_data INTO ls_data.
      CASE ls_data-column.
        WHEN 1.
          CONDENSE ls_data-value.
          tb_xls-kdmat = ls_data-value.
        WHEN 2.
          tb_xls-zhtccd = ls_data-value.
        WHEN 3.
          tb_xls-ihrez_e = ls_data-value.
        WHEN 4.
          IF ls_data-value CP '*18*'.
            tb_xls-taxm1 = '18%'.
          ELSE.
            tb_xls-taxm1 = '10%'.
          ENDIF.
        WHEN 5.
          tb_xls-kbmeng = ls_data-value.
        WHEN 6.
          CONDENSE ls_data-value NO-GAPS.
          tb_xls-zzccn = ls_data-value.
        WHEN 7.
          tb_xls-zprgccn = ls_data-value.
      ENDCASE.
      AT END OF row.
        APPEND tb_xls.
        CLEAR tb_xls.
      ENDAT.
    ENDLOOP.

  ELSE.
    MESSAGE I000(YA1) WITH TEXT-E16 pa_local.
  ENDIF.

  li_document->close_document(
    IMPORTING
      ERROR = li_error ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'I' ).
    LEAVE LIST-PROCESSING.
  ENDIF.

  li_document->release_document(
    IMPORTING
      ERROR = li_error ).
  IF li_error->has_failed = 'X'.
    li_error->raise_message( 'I' ).
    LEAVE LIST-PROCESSING.
  ENDIF.

ENDFORM.                    "read_xls