Dynamic load for customer tables

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

This Content Component encountered an error
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          -> 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

Dig deeper on SAP ABAP

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchManufacturingERP

SearchOracle

SearchDataManagement

SearchAWS

SearchBusinessAnalytics

SearchCRM

SearchContentManagement

SearchFinancialApplications

Close