Dynamic load for customer tables

This report uploads the contents to any customer table (Z*) from an excel source file.

Restriction: the maximum lenght content of the cell is up to 32, because of the definition of KCD_EXCEL_OLE_TO_INT_CONVERT. You can use OLE to avoid limits, like used in the download sample option.
This code is written for version 4.6C. 
REPORT ztablesexceltoz_dynamic .
*  David Infantes
*  Carga de tablas Z dinÁmica (nombre de la tabla seleccionable)

*  Formato del fichero excel:
*  Campo SAP     Formato celda     Contenido   Ejemplo xls->Result.SAP
*-----------------------------------------------------------------------
*   fecha          General          AAAAMMDD   20030517   -> 17-may-2003
*   hora           Texto             HHMMSS    055932     -> 5:59:32 (*)
*   numc           General           nnnn      1          -> 0001

*   Si hay campos como material, proveedor, etc. que en SAP tienen una
*   transformaciÓn, hay que realizarla en el excel
*   Ejemplo: material 79 en SAP -> 000000000000000079 en Excel, (*)
*                     abc en SAP -> abc en Excel

*  Special format in excel:
*   SAP field    Cell format      Contents   Example xls->Result  inSAP
*-----------------------------------------------------------------------
*   date           General          AAAAMMDD   20030517   -> 17-may-2003
*   time           Text              HHMMSS    055932     -> 5:59:32 (*)
*   numc           General           nnnn      1

    Requires Free Membership to View

    Login 

         -> 0001

*  With SAP fields like material, customer, assigned to conversion rules
*  the conversion must be performed in Excel
*   Example: material 79 in SAP -> 000000000000000079 in Excel, (*)
*                     abc in SAP -> abc in Excel

*   (*)  Cells with Text format do not erase the leading zeros



TYPE-POOLS abap.
INCLUDE ole2incl.

PARAMETERS:
filename  LIKE rlgrap-filename OBLIGATORY,
pa_tab TYPE dd02l-tabname,
p_maxrow(4) TYPE n,
p_sample AS CHECKBOX.

DATA: subrc LIKE sy-subrc,
      generic_table_wa TYPE REF TO data.

FIELD-SYMBOLS <p_tab_wa> TYPE ANY.

DATA: t_fields TYPE TABLE OF fieldname WITH HEADER LINE,
      t_details TYPE abap_compdescr_tab,
      wa_comp TYPE abap_compdescr.


* handles for OLE objects
DATA: h_excel TYPE ole2_object,        " Excel object
      h_mapl TYPE ole2_object,         " list of workbooks
      h_map TYPE ole2_object,          " workbook
      h_zl TYPE ole2_object,           " cell
      h_f TYPE ole2_object.            " font


* At Selection Screen Event...........................
AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
       EXPORTING
            mask          = '*.xls'
            static        = 'X'
       CHANGING
            file_name     = filename
       EXCEPTIONS
            mask_too_long = 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.
  ENDIF.


START-OF-SELECTION.

  CREATE DATA  generic_table_wa TYPE (pa_tab).
  ASSIGN generic_table_wa->*  TO <p_tab_wa>.

  IF NOT p_sample IS INITIAL.
    PERFORM f_download_sample USING filename.
  ELSE.
    PERFORM f_excel_upload USING    filename
                                    subrc.
  ENDIF.




*======================================================================*
* Subroutine to upload data from excel file from local PC              *
*----------------------------------------------------------------------*
*  --->  P_TAB        Internal table for data to be uploaded           *
*  --->  P_FILENAME   File to be uploaded                              *
*  <---  P_RC         Return Code                                      *
*======================================================================*
FORM f_excel_upload  USING    p_filename  LIKE rlgrap-filename
                              p_rc        LIKE sy-subrc
                              .
  DATA : $i_intern TYPE  kcde_cells OCCURS 0 WITH HEADER LINE.
  DATA : $v_index TYPE i.
  DATA : $v_start_col TYPE i VALUE '1',
         $v_start_row TYPE i VALUE '1',
         $v_end_col   TYPE i VALUE '256',
         $v_end_row   TYPE i VALUE '65536'.
  FIELD-SYMBOLS : <$fs>.
  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
       EXPORTING
            filename                = p_filename
            i_begin_col             = $v_start_col
            i_begin_row             = $v_start_row
            i_end_col               = $v_end_col
            i_end_row               = $v_end_row
       TABLES
            intern                  = $i_intern
       EXCEPTIONS
            inconsistent_parameters = 1
            upload_ole              = 2.
  MOVE : sy-subrc TO p_rc.
  CHECK NOT $i_intern[] IS INITIAL.
  SORT $i_intern BY row col.
  LOOP AT $i_intern.
    MOVE : $i_intern-col TO $v_index.
    ASSIGN COMPONENT $v_index OF STRUCTURE <p_tab_wa> TO <$fs>.
    MOVE : $i_intern-value TO <$fs>.
    AT END OF row.
      MODIFY (pa_tab) FROM <p_tab_wa>.
      IF sy-subrc EQ 0.
        COMMIT WORK.
        WRITE:/ 'ok', <p_tab_wa>.
      ELSE.
        WRITE:/ 'ER', <p_tab_wa>.
      ENDIF.
      CLEAR <p_tab_wa>.
      IF $i_intern-row = p_maxrow AND NOT p_maxrow IS INITIAL.
        WRITE: / p_maxrow, 'lineas leidas'.
        EXIT.
      ENDIF.

    ENDAT.
  ENDLOOP.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  f_download_sample
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_FILENAME  text
*----------------------------------------------------------------------*
FORM f_download_sample USING    p_filename.



* Dynamic field description of a structure
  DATA: ref_descr TYPE REF TO cl_abap_structdescr.



  ref_descr ?= cl_abap_typedescr=>describe_by_data( <p_tab_wa> ).

* Export to excel

  t_details[] = ref_descr->components[].
  LOOP AT ref_descr->components INTO wa_comp.
    AT FIRST.
      APPEND 'Attributes' TO t_fields.
    ENDAT.
    APPEND wa_comp-name TO t_fields.
  ENDLOOP.


  PERFORM table2excel.

  CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
       EXPORTING
            file_name                 = p_filename
            data_sheet_name           = 'Hoja1'
       TABLES
*            data_tab                  = t_details
            fieldnames                = t_fields
       EXCEPTIONS
            file_not_exist            = 1
            filename_expected         = 2
            communication_error       = 3
            ole_object_method_error   = 4
            ole_object_property_error = 5
            invalid_filename          = 6
            invalid_pivot_fields      = 7
            download_problem          = 8
            OTHERS                    = 9.


  IF sy-subrc eq 1.
    MESSAGE i368(00) WITH 'You should save the file'.
  elseif sy-subrc <> 0.
    MESSAGE i368(00) WITH sy-subrc.
  ENDIF.

ENDFORM.                    " f_download_sample
*&---------------------------------------------------------------------*
*&      Form  table2excel
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM table2excel.

* Linea
  DATA:  h TYPE i, line TYPE i.
  FIELD-SYMBOLS <comp> TYPE ANY.

* start Excel
  CREATE OBJECT h_excel 'EXCEL.APPLICATION'.
  PERFORM err_hdl.
  SET PROPERTY OF h_excel  'Visible' = 1.
  PERFORM err_hdl.
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           text       = 'Opening workbook...'
       EXCEPTIONS
            OTHERS     = 1.
* get list of workbooks, initially empty
  CALL METHOD OF h_excel 'Workbooks' = h_mapl.
  PERFORM err_hdl.
* add a new workbook
  CALL METHOD OF h_mapl 'Add' = h_map.
  PERFORM err_hdl.
* tell user what is going on
  CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
*           PERCENTAGE = 0
           text       = 'Complete the internal table ...'
       EXCEPTIONS
            OTHERS     = 1.


* output column headings to active Excel sheet
  LOOP AT t_fields.
    h = sy-tabix.
    PERFORM fill_cell USING 1 h 1 t_fields.
  ENDLOOP.

  PERFORM fill_cell USING 2 1 0 'Length'.
  PERFORM fill_cell USING 3 1 0 'Decimals'.
  PERFORM fill_cell USING 4 1 0 'Type'.
  PERFORM fill_cell USING 5 1 0 'Sample entry'.

  LOOP AT t_details INTO wa_comp.

    h = sy-tabix + 1.

    PERFORM fill_cell USING 2 h 0 wa_comp-length.
    PERFORM fill_cell USING 3 h 0 wa_comp-decimals.
    PERFORM fill_cell USING 4 h 0 wa_comp-type_kind.
  ENDLOOP.

  line = 5.
  SELECT single * INTO <p_tab_wa>

         FROM (pa_tab).
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE <p_tab_wa> TO <comp>.
      IF sy-subrc NE 0. EXIT. ENDIF.
      h = sy-index + 1.
      PERFORM fill_cell USING line h 0 <comp>.
    ENDDO.

* disconnect from Excel
  FREE OBJECT h_excel.
  PERFORM err_hdl.

ENDFORM.                    " table2excel


*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                *
*---------------------------------------------------------------------*
*       sets cell at coordinates i,j to value val boldtype bold       *
*---------------------------------------------------------------------*
FORM fill_cell USING i j bold val.
  CALL METHOD OF h_excel 'Cells' = h_zl EXPORTING #1 = i #2 = j.
  PERFORM err_hdl.
  SET PROPERTY OF h_zl 'Value' = val .
  PERFORM err_hdl.
  GET PROPERTY OF h_zl 'Font' = h_f.
  PERFORM err_hdl.
  SET PROPERTY OF h_f 'Bold' = bold .
  PERFORM err_hdl.
ENDFORM.
*&---------------------------------------------------------------------*
*&      Form  ERR_HDL
*&---------------------------------------------------------------------*
*       outputs OLE error if any                                       *
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM err_hdl.
  IF sy-subrc <> 0.
    WRITE: / 'OLE-Automation failed:'(010), sy-subrc.
    STOP.
  ENDIF.
ENDFORM.                    " ERR_HDL

This was first published in June 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top