Tip

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

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.