Create XLSX from any table X-Systems using ABAP2XLSX

Nützliche Abap Codes und Code Fragmente

Create XLSX from any table X-Systems using ABAP2XLSX

Postby Tron » Mon Mar 23, 2020 9:16 am

Usefull Coding for Archiving Data tables from different SAP Systems into Excel sheets using ABAP2XLSX.
Special thanks to SAP Mentors and Stefan Schmöcker for continously working on ABAP2XLSX Project :)

001.png
001.png (41.58 KiB) Viewed 7219 times

Example to download Service Orders with BAPI_ALM_ORDER_GET_DETAIL and BAPI_CUSTOMER_GETDETAIL2
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. *&---------------------------------------------------------------------*
  2. *& Report  ZBC_XLSX_DOWNLOAD_FUMO
  3. *&
  4. *&---------------------------------------------------------------------*
  5. *&
  6. *&
  7. *&---------------------------------------------------------------------*
  8.  
  9. REPORT zbc_xlsx_download_fumo_csord.
  10. *&---------------------------------------------------------------------*
  11. *&---------------------------------------------------------------------*
  12.  
  13. DATA ls_sel TYPE ddshselopt.
  14. DATA lt_sel TYPE TABLE OF ddshselopt.
  15.  
  16. "USAGE:  m_selopt 'FIELD' S_SELOPT.
  17. DEFINE m_selopt .
  18.   CLEAR ls_sel.
  19.   ls_sel-shlpfield = &1.
  20.   LOOP AT &2.
  21.     MOVE-CORRESPONDING &2 TO ls_sel.
  22.     APPEND ls_sel TO lt_sel.
  23. *&---------------------------------------------------------------------*
  24. *&---------------------------------------------------------------------*
  25.  
  26. CONSTANTS htab(1) VALUE %_horizontal_tab.
  27.  
  28. DATA: excel         TYPE REF TO zcl_excel ##NEEDED,
  29.       act_worksheet TYPE REF TO zcl_excel_worksheet.
  30.  
  31.  
  32. DATA msg_text(80).
  33.  
  34. TYPE-POOLS: abap.
  35.  
  36. **** Keyfields
  37.   BEGIN OF ty_key,
  38.     aufnr TYPE aufnr,
  39.   END OF ty_key.
  40.  
  41. **** for Excel Headline
  42.   BEGIN OF ty_hdr,
  43.     tablname  TYPE tabname,
  44.     fieldname TYPE fieldname,
  45.     ddtext    TYPE ddtext,
  46.     reptext   TYPE reptext,
  47.   END OF ty_hdr.
  48.  
  49. DATA lt_hdr TYPE TABLE OF ty_hdr.
  50. DATA ls_hdr TYPE ty_hdr.
  51.  
  52.   <dyn_field>                TYPE any,
  53.   <dyn_rec>                  TYPE any,
  54.   <dyn_tab>                  TYPE  STANDARD TABLE,
  55.  
  56.   " table for BAPI_ALM_ORDER_GET_DETAIL
  57.   <et_partner>               TYPE STANDARD TABLE,
  58.   <et_components>            TYPE STANDARD TABLE,
  59.   <et_operations>            TYPE STANDARD TABLE,
  60.   <et_relations>             TYPE STANDARD TABLE,
  61.   <et_srules>                TYPE STANDARD TABLE,
  62.   <et_olist>                 TYPE STANDARD TABLE,
  63.   <et_oprol>                 TYPE STANDARD TABLE,
  64.   <et_texts>                 TYPE STANDARD TABLE,
  65.   <et_text_lines>            TYPE STANDARD TABLE,
  66.   <et_prts>                  TYPE STANDARD TABLE,
  67.   <et_costs_sum>             TYPE STANDARD TABLE,
  68.   <et_costs_details>         TYPE STANDARD TABLE,
  69.   <et_reforder_serno_olist>  TYPE STANDARD TABLE,
  70.   <et_serviceoutline>        TYPE STANDARD TABLE,
  71.   <et_servicelines>          TYPE STANDARD TABLE,
  72.   <et_servicelimit>          TYPE STANDARD TABLE,
  73.   <et_servicecontractlimits> TYPE STANDARD TABLE,
  74.   <et_permit>                TYPE STANDARD TABLE,
  75.   <et_permit_issue>          TYPE STANDARD TABLE,
  76.  
  77.   " Header table dummy
  78.   <et_header>                TYPE STANDARD TABLE,
  79.   <et_srvdata>               TYPE STANDARD TABLE,
  80.   <et_reforder_item>         TYPE STANDARD TABLE,
  81.   <t_customeraddress>        TYPE STANDARD TABLE,
  82.  
  83.   " Header structure
  84.   <es_header>                TYPE any,
  85.   <es_srvdata>               TYPE any,
  86.   <es_reforder_item>         TYPE any,
  87.   <es_partner>               TYPE any,
  88.   <s_customeraddress>        TYPE any.
  89.  
  90. DATA t_operations TYPE STANDARD TABLE OF bapi_alm_order_operation_e.
  91.  
  92.  
  93. DATA lt_key TYPE TABLE OF ty_key.
  94. DATA ls_key TYPE ty_key.
  95.  
  96.   toptions    TYPE TABLE OF rfc_db_opt,
  97.   tfields     TYPE TABLE OF rfc_db_fld,
  98.   tdata       TYPE TABLE OF ytab4000,
  99.   sdata       TYPE ytab4000,
  100.   i_excel     TYPE  solix_tab,
  101.   i_excel_len TYPE  i.
  102.  
  103. DATA t_bapiret TYPE TABLE OF bapiret2.
  104. DATA s_bapiret TYPE  bapiret2.
  105.  
  106.  
  107. DATA lv_path TYPE string VALUE 'C:\Temp\STULI\ZBC_XLSX_DOWNLOAD_'.
  108. DATA lv_pfad TYPE string .
  109. DATA gv_matnr TYPE mara-matnr.
  110. DATA g_msg TYPE tab512.
  111. DATA l_datuv TYPE datuv_bi.
  112. DATA l_cust TYPE bapicustomer_id-customer.
  113.  
  114. PARAMETERS p_upl(40) TYPE c LOWER CASE OBLIGATORY DEFAULT 'C:\temp\CSORD\KEY\CSORD_key.txt'.
  115. PARAMETERS p_dwn(40) TYPE c LOWER CASE OBLIGATORY DEFAULT 'C:\Temp\CSORD\'.
  116. PARAMETERS p_log(40) TYPE c LOWER CASE OBLIGATORY DEFAULT 'C:\Temp\CSORD\LOG\'.
  117. PARAMETERS p_lang TYPE sy-langu DEFAULT sy-langu.
  118.  
  119. PARAMETERS p_des1 TYPE rfcdes-rfcdest OBLIGATORY DEFAULT 'PRDCLNT200'.
  120. PARAMETERS p_max TYPE soid-accnt DEFAULT '10000'.
  121.  
  122. lv_path = p_dwn.
  123.  
  124. * Tables
  125. PERFORM create_itab USING 'BAPI_ALM_ORDER_PARTNER' . ASSIGN <dyn_tab> TO  <et_partner>. ASSIGN <dyn_rec> TO <es_partner>.
  126. PERFORM create_itab USING 'BAPI_ALM_ORDER_OPERATION_E' . ASSIGN <dyn_tab> TO  <et_operations>.
  127. PERFORM create_itab USING 'BAPI_ALM_ORDER_COMPONENT_E' . ASSIGN <dyn_tab> TO  <et_components>.
  128. PERFORM create_itab USING 'BAPI_ALM_ORDER_RELATION_EXPORT' . ASSIGN <dyn_tab> TO  <et_relations>.
  129. PERFORM create_itab USING 'BAPI_ALM_ORDER_SRULE_E' . ASSIGN <dyn_tab> TO  <et_srules>.
  130. PERFORM create_itab USING 'BAPI_ALM_ORDER_OBJECTLIST' . ASSIGN <dyn_tab> TO  <et_olist>.
  131. PERFORM create_itab USING 'BAPI_ALM_OLIST_RELATION' . ASSIGN <dyn_tab> TO  <et_oprol>.
  132. PERFORM create_itab USING 'BAPI_ALM_TEXT' . ASSIGN <dyn_tab> TO  <et_texts>.
  133. PERFORM create_itab USING 'BAPI_ALM_TEXT_LINES' . ASSIGN <dyn_tab> TO  <et_text_lines>.
  134. PERFORM create_itab USING 'BAPI_ALM_ORDER_PRT_E' . ASSIGN <dyn_tab> TO  <et_prts>.
  135. PERFORM create_itab USING 'BAPI_ALM_ORDER_COSTS_SUM_E' . ASSIGN <dyn_tab> TO  <et_costs_sum>.
  136. PERFORM create_itab USING 'BAPI_ALM_ORDER_COSTS_DETAIL_E' . ASSIGN <dyn_tab> TO  <et_costs_details>.
  137. PERFORM create_itab USING 'BAPI_REFORDER_SERNO_OLIST' . ASSIGN <dyn_tab> TO  <et_reforder_serno_olist>.
  138. PERFORM create_itab USING 'BAPI_ALM_SRV_OUTLINE' . ASSIGN <dyn_tab> TO  <et_serviceoutline>.
  139. PERFORM create_itab USING 'BAPI_ALM_SRV_SERVICE_LINE' . ASSIGN <dyn_tab> TO  <et_servicelines>.
  140. PERFORM create_itab USING 'BAPI_ALM_SRV_LIMIT_DATA' . ASSIGN <dyn_tab> TO  <et_servicelimit>.
  141. PERFORM create_itab USING 'BAPI_ALM_SRV_CONTRACT_LIMITS' . ASSIGN <dyn_tab> TO  <et_servicecontractlimits>.
  142. PERFORM create_itab USING 'BAPI_ALM_ORDER_PERMIT_READ' . ASSIGN <dyn_tab> TO  <et_permit>.
  143. PERFORM create_itab USING 'BAPI_ALM_ORDER_PER_ISSUE_R' . ASSIGN <dyn_tab> TO  <et_permit_issue>.
  144.  
  145. PERFORM create_itab USING 'BAPI_ALM_ORDER_HEADER_E'. ASSIGN <dyn_tab> TO  <et_header>. ASSIGN <dyn_rec> TO <es_header>.
  146. PERFORM create_itab USING 'BAPI_ALM_ORDER_SRVDAT_E'. ASSIGN <dyn_tab> TO  <et_srvdata>. ASSIGN <dyn_rec> TO <es_srvdata>.
  147. PERFORM create_itab USING 'BAPI_REFORDER_ITEM'. ASSIGN <dyn_tab> TO  <et_reforder_item>. ASSIGN <dyn_rec> TO <es_reforder_item>.
  148.  
  149. PERFORM create_itab USING 'BAPICUSTOMER_04' . ASSIGN <dyn_tab> TO  <t_customeraddress>. ASSIGN <dyn_rec> TO <s_customeraddress>.
  150.  
  151.  
  152.  
  153. PERFORM upload_keys.
  154.  
  155. LOOP AT lt_key INTO ls_key.
  156.  
  157.   <et_partner>  ,
  158.   <et_operations>,
  159.   <et_components>,
  160.   <et_relations>,
  161.   <et_srules>,
  162.   <et_olist>,
  163.   <et_oprol>,
  164.   <et_texts>,
  165.   <et_text_lines>,
  166.   <et_prts>,
  167.   <et_costs_sum>,
  168.   <et_costs_details>,
  169.   <et_reforder_serno_olist>,
  170.   <et_serviceoutline>,
  171.   <et_servicelines>,
  172.   <et_servicelimit>,
  173.   <et_servicecontractlimits>,
  174.   <et_permit>,
  175.   <et_permit_issue>,
  176.  
  177.   <t_customeraddress>,
  178.  
  179.     <et_header>,
  180.     <et_srvdata>,
  181.     <et_reforder_item>.
  182.  
  183.   " Header structure
  184.   CLEAR:
  185.   <s_customeraddress>,
  186.   <es_header>,
  187.   <es_srvdata>,
  188.   <es_reforder_item>.
  189.  
  190.  
  191.   CONCATENATE ls_key-aufnr '.xlsx'  INTO lv_pfad .
  192.   CONCATENATE lv_path lv_pfad INTO lv_pfad.
  193.  
  194.  
  195.   CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
  196.     EXPORTING
  197.       input  = ls_key-aufnr
  198.     IMPORTING
  199.       output = ls_key-aufnr.
  200.  
  201.   CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL' DESTINATION p_des1
  202.     EXPORTING
  203.       number                   = ls_key-aufnr
  204.     IMPORTING
  205.       es_header                = <es_header>
  206.       es_srvdata               = <es_srvdata>
  207.       es_reforder_item         = <es_reforder_item>
  208.     TABLES
  209.       et_partner               = <et_partner>
  210.       et_operations            = <et_operations>
  211.       et_components            = <et_components>
  212.       et_relations             = <et_relations>
  213.       et_srules                = <et_srules>
  214.       et_olist                 = <et_olist>
  215.       et_oprol                 = <et_oprol>
  216.       et_texts                 = <et_texts>
  217.       et_text_lines            = <et_text_lines>
  218.       et_prts                  = <et_prts>
  219.       et_costs_sum             = <et_costs_sum>
  220.       et_costs_details         = <et_costs_details>
  221.       return                   = t_bapiret
  222.       et_reforder_serno_olist  = <et_reforder_serno_olist>
  223.       et_serviceoutline        = <et_serviceoutline>
  224.       et_servicelines          = <et_servicelines>
  225.       et_servicelimit          = <et_servicelimit>
  226.       et_servicecontractlimits = <et_servicecontractlimits>
  227.       et_permit                = <et_permit>
  228.       et_permit_issue          = <et_permit_issue>
  229.     EXCEPTIONS
  230.       communication_failure    = 1 MESSAGE msg_text
  231.       system_failure           = 2 MESSAGE msg_text
  232.       OTHERS                   = 3.
  233.  
  234.   IF NOT <es_header> IS INITIAL. APPEND <es_header> TO <et_header>. ENDIF.
  235.   IF NOT <es_srvdata> IS INITIAL. APPEND <es_srvdata> TO <et_srvdata>. ENDIF.
  236.   IF NOT <es_reforder_item> IS INITIAL. APPEND <es_reforder_item> TO <et_reforder_item>. ENDIF.
  237.  
  238.  
  239.  
  240.   IF sy-subrc <> 0.
  241. *    CONCATENATE 'BOM:' gv_matnr
  242. *                'Plant:' ls_mast-werks
  243. *                'Type:' ls_mast-stlan
  244. *                'Altern.:' ls_mast-stlal
  245. *                '===> failed to read' INTO
  246. *                g_msg SEPARATED BY space.
  247. *    PERFORM collect_log USING ' ' g_msg .
  248.     CONTINUE.
  249.   ENDIF.
  250.  
  251. * Get Partner AG Address
  252.   LOOP AT <et_partner> INTO  <es_partner>.
  253.     UNASSIGN <dyn_field>.
  254.     ASSIGN COMPONENT 'PARTN_ROLE_OLD' OF STRUCTURE <es_partner> TO <dyn_field>.
  255.     IF sy-subrc = 0.
  256.       IF <dyn_field> = 'AG'.
  257.         UNASSIGN <dyn_field>.
  258.         ASSIGN COMPONENT 'PARTNER_OLD' OF STRUCTURE <es_partner> TO <dyn_field>.
  259.         IF sy-subrc = 0.
  260.  
  261.           CLEAR s_bapiret.
  262.           l_cust = <dyn_field>.
  263.           CALL FUNCTION 'BAPI_CUSTOMER_GETDETAIL2' DESTINATION p_des1
  264.             EXPORTING
  265.               customerno            = l_cust
  266.             IMPORTING
  267.               customeraddress       = <s_customeraddress>
  268.               return                = s_bapiret
  269.             EXCEPTIONS
  270.               communication_failure = 1 MESSAGE msg_text
  271.               system_failure        = 2 MESSAGE msg_text
  272.               OTHERS                = 3.
  273.  
  274.           IF NOT  <s_customeraddress> IS INITIAL.
  275.             APPEND  <s_customeraddress> TO  <t_customeraddress>.
  276.           ENDIF.
  277.  
  278.         ENDIF.
  279.       ENDIF.
  280.     ENDIF.
  281.  
  282. *  PERFORM append_to_file.
  283.  
  284.  
  285.   " Header Structures
  286.   IF NOT <et_header>[] IS INITIAL. PERFORM create_excel TABLES <et_header> USING 'BAPI_ALM_ORDER_HEADER_E'. ENDIF.
  287.   IF NOT <et_srvdata>[] IS INITIAL. PERFORM create_excel TABLES <et_srvdata> USING 'BAPI_ALM_ORDER_SRVDAT_E'. ENDIF.
  288.   IF NOT <et_reforder_item>[] IS INITIAL. PERFORM create_excel TABLES <et_reforder_item> USING 'BAPI_REFORDER_ITEM'. ENDIF.
  289.  
  290.   IF NOT <et_partner>[] IS INITIAL. PERFORM create_excel TABLES <et_partner> USING 'BAPI_ALM_ORDER_PARTNER'. ENDIF.
  291.   IF NOT <et_operations>[] IS INITIAL. PERFORM create_excel TABLES <et_operations> USING 'BAPI_ALM_ORDER_OPERATION_E'. ENDIF.
  292.   IF NOT <et_components>[] IS INITIAL. PERFORM create_excel TABLES <et_components> USING 'BAPI_ALM_ORDER_COMPONENT_E'. ENDIF.
  293.   IF NOT <et_relations>[] IS INITIAL. PERFORM create_excel TABLES <et_relations> USING 'BAPI_ALM_ORDER_RELATION_EXPORT'. ENDIF.
  294.   IF NOT <et_srules>[] IS INITIAL. PERFORM create_excel TABLES <et_srules> USING 'BAPI_ALM_ORDER_SRULE_E'. ENDIF.
  295.   IF NOT <et_olist>[] IS INITIAL. PERFORM create_excel TABLES <et_olist> USING 'BAPI_ALM_ORDER_OBJECTLIST'. ENDIF.
  296.   IF NOT <et_oprol>[] IS INITIAL. PERFORM create_excel TABLES <et_oprol> USING 'BAPI_ALM_OLIST_RELATION'. ENDIF.
  297.   IF NOT <et_texts>[] IS INITIAL. PERFORM create_excel TABLES <et_texts> USING 'BAPI_ALM_TEXT'. ENDIF.
  298.   IF NOT <et_text_lines>[] IS INITIAL. PERFORM create_excel TABLES <et_text_lines> USING 'BAPI_ALM_TEXT_LINES'. ENDIF.
  299.   IF NOT <et_prts>[] IS INITIAL. PERFORM create_excel TABLES <et_prts> USING 'BAPI_ALM_ORDER_PRT_E'. ENDIF.
  300.   IF NOT <et_costs_sum>[] IS INITIAL. PERFORM create_excel TABLES <et_costs_sum> USING 'BAPI_ALM_ORDER_COSTS_SUM_E'. ENDIF.
  301.   IF NOT <et_costs_details>[] IS INITIAL. PERFORM create_excel TABLES <et_costs_details> USING 'BAPI_ALM_ORDER_COSTS_DETAIL_E'. ENDIF.
  302.   IF NOT <et_reforder_serno_olist>[] IS INITIAL. PERFORM create_excel TABLES <et_reforder_serno_olist> USING 'BAPI_REFORDER_SERNO_OLIST'. ENDIF.
  303.   IF NOT <et_serviceoutline>[] IS INITIAL. PERFORM create_excel TABLES <et_serviceoutline> USING 'BAPI_ALM_SRV_OUTLINE'. ENDIF.
  304.   IF NOT <et_servicelines>[] IS INITIAL. PERFORM create_excel TABLES <et_servicelines> USING 'BAPI_ALM_SRV_SERVICE_LINE'. ENDIF.
  305.   IF NOT <et_servicelimit>[] IS INITIAL. PERFORM create_excel TABLES <et_servicelimit> USING 'BAPI_ALM_SRV_LIMIT_DATA'. ENDIF.
  306.   IF NOT <et_servicecontractlimits>[] IS INITIAL. PERFORM create_excel TABLES <et_servicecontractlimits> USING 'BAPI_ALM_SRV_CONTRACT_LIMITS'. ENDIF.
  307.   IF NOT <et_permit>[] IS INITIAL. PERFORM create_excel TABLES <et_permit> USING 'BAPI_ALM_ORDER_PERMIT_READ'. ENDIF.
  308.   IF NOT <et_permit_issue>[] IS INITIAL. PERFORM create_excel TABLES <et_permit_issue> USING 'BAPI_ALM_ORDER_PER_ISSUE_R'. ENDIF.
  309.  
  310.   IF NOT <t_customeraddress>[] IS INITIAL. PERFORM create_excel TABLES <t_customeraddress> USING 'BAPICUSTOMER_04'. ENDIF.
  311.  
  312.  
  313.   IF excel IS  BOUND.
  314.  
  315.  
  316.     TRY.
  317.         excel->set_active_sheet_index( i_active_worksheet = 1 ).
  318.       CATCH zcx_excel .
  319.     ENDTRY.
  320.  
  321.     PERFORM write_excel_file USING lv_pfad.
  322.  
  323.     FREE excel.
  324.  
  325.   ENDIF.
  326.  
  327.  
  328. PERFORM collect_log USING 'X' g_msg .
  329.  
  330. *&---------------------------------------------------------------------*
  331. *&      Form  stxcompare
  332. *&---------------------------------------------------------------------*
  333. *       text
  334. *----------------------------------------------------------------------*
  335.  
  336. FORM fill_itab.
  337.   DATA lt TYPE TABLE OF tab512.
  338.   DATA ls TYPE tab512.
  339.   DATA l_idx TYPE i.
  340.  
  341.   DATA s_fields TYPE rfc_db_fld.
  342.  
  343.   FIELD-SYMBOLS <ls> TYPE tab512.
  344.  
  345.   LOOP AT tdata INTO sdata.
  346.  
  347.     SPLIT sdata-wa AT htab INTO TABLE lt.
  348.  
  349.     " delete all ":" in time vars
  350.     LOOP AT tfields INTO s_fields WHERE type = 'T'.
  351.       READ TABLE lt ASSIGNING <ls> INDEX sy-tabix.
  352.       IF sy-subrc = 0.
  353.         REPLACE ALL OCCURRENCES OF ':' IN <ls> WITH ''.
  354.       ENDIF.
  355.     ENDLOOP.
  356.  
  357.     LOOP AT lt INTO ls.
  358.       l_idx = sy-tabix.
  359.       ASSIGN COMPONENT l_idx OF STRUCTURE <dyn_rec> TO <dyn_field>.
  360.       <dyn_field> = ls-wa.
  361.     ENDLOOP.
  362.  
  363.     INSERT <dyn_rec> INTO TABLE <dyn_tab>.
  364.  
  365.  
  366. *&---------------------------------------------------------------------*
  367. *&      Form  stxcompare
  368. *&---------------------------------------------------------------------*
  369. *       text
  370. *----------------------------------------------------------------------*
  371.  
  372. FORM create_excel TABLES i_tab  USING i_sheetname TYPE zexcel_sheet_title.
  373.   CONSTANTS    : first_column   TYPE char1     VALUE 'A'
  374.                .
  375.   DATA         : worksheet      TYPE REF TO zcl_excel_worksheet
  376.                , worksheet_title TYPE zexcel_sheet_title
  377.                , table_settings TYPE zexcel_s_table_settings
  378.                .
  379.   DATA           lo_theme       TYPE REF TO zcl_excel_theme.
  380.  
  381.   DATA: lv_row   TYPE zexcel_cell_row,
  382.         lv_col   TYPE i,
  383.         lv_value TYPE string.
  384.  
  385.  
  386.   table_settings-table_style    = zcl_excel_table=>builtinstyle_medium2.
  387.   table_settings-show_row_stripes
  388.                                 = abap_true.
  389.   table_settings-nofilters      = abap_true.
  390.   table_settings-top_left_column
  391.                                 = first_column.
  392.   table_settings-top_left_row   = 02.
  393.  
  394.   TRY.
  395.  
  396.       IF excel IS NOT BOUND.
  397.         CREATE OBJECT excel.
  398.         worksheet                   = excel->get_active_worksheet( ).
  399.       ELSE.
  400.         worksheet                   = excel->add_new_worksheet( ).
  401.       ENDIF.
  402.  
  403. *      lo_theme->set_latin_font(
  404. *       EXPORTING
  405. *         iv_type        = zcl_excel_theme_font_scheme=>c_minor
  406. *         iv_typeface    = 'Arial monospaced for SAP'
  407. **      iv_panose      =
  408. **      iv_pitchfamily =
  409. **      iv_charset     =
  410. *     ).
  411.  
  412.       worksheet_title               = i_sheetname.
  413.       worksheet->set_title( worksheet_title ).
  414.       worksheet->bind_table(
  415.         ip_table                    = i_tab[]
  416.         is_table_settings           = table_settings
  417.         ).
  418.  
  419.       lv_row = 1.
  420.       LOOP AT lt_hdr INTO ls_hdr WHERE tablname = i_sheetname.
  421.  
  422.         lv_col = lv_col + 1.
  423.         lv_value = ls_hdr-reptext.
  424.  
  425.         worksheet->set_cell( ip_column = lv_col ip_row = lv_row ip_value = lv_value ).
  426.  
  427.         worksheet->change_cell_style(  ip_column           = lv_col
  428.                                        ip_row              = lv_row
  429.                                        ip_fill_filltype    = zcl_excel_style_fill=>c_fill_solid
  430.                                        ip_fill_fgcolor_rgb = zcl_excel_style_color=>c_gray ).
  431.       ENDLOOP.
  432.  
  433.     CATCH zcx_excel .
  434.   ENDTRY.
  435.  
  436.  
  437. FORM write_excel_file USING i_file TYPE string.
  438.  
  439. *** Create output
  440.   DATA:
  441.     cl_writer TYPE REF TO zif_excel_writer.
  442.  
  443.   DATA: xdata     TYPE xstring.         " Will be used for sending as email
  444.   " t_rawdata TYPE solix_tab,           " Will be used for downloading or open directly
  445.   " bytecount TYPE i.                   " Will be used for downloading or open directly
  446.  
  447.   CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
  448.  
  449.   xdata = cl_writer->write_file( excel ).
  450.   i_excel = cl_bcs_convert=>xstring_to_solix( iv_xstring  = xdata ).
  451.   i_excel_len = xstrlen( xdata ).
  452.  
  453. * Get trailing blank
  454.   cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = i_excel_len
  455.                                                     filename     = i_file
  456.                                                     filetype     = 'BIN'
  457.                                            CHANGING data_tab     = i_excel ).
  458.  
  459.  
  460.  
  461. FORM upload_keys.
  462.   DATA l_path TYPE string.
  463.   l_path = p_upl.
  464.  
  465.   CALL METHOD cl_gui_frontend_services=>gui_upload
  466.     EXPORTING
  467.       filename                = l_path
  468. *     filetype                = 'ASC'
  469.       has_field_separator     = 'X'
  470. *     header_length           = 0
  471. *     read_by_line            = 'X'
  472. *     dat_mode                = SPACE
  473. *     codepage                = SPACE
  474. *     ignore_cerr             = ABAP_TRUE
  475. *     replacement             = '#'
  476. *     virus_scan_profile      =
  477. *  IMPORTING
  478. *     filelength              =
  479. *     header                  =
  480.     CHANGING
  481.       data_tab                = lt_key
  482. *     isscanperformed         = SPACE
  483.     EXCEPTIONS
  484.       file_open_error         = 1
  485.       file_read_error         = 2
  486.       no_batch                = 3
  487.       gui_refuse_filetransfer = 4
  488.       invalid_type            = 5
  489.       no_authority            = 6
  490.       unknown_error           = 7
  491.       bad_data_format         = 8
  492.       header_not_allowed      = 9
  493.       separator_not_allowed   = 10
  494.       header_too_long         = 11
  495.       unknown_dp_error        = 12
  496.       access_denied           = 13
  497.       dp_out_of_memory        = 14
  498.       disk_full               = 15
  499.       dp_timeout              = 16
  500.       not_supported_by_gui    = 17
  501.       error_no_gui            = 18
  502.       OTHERS                  = 19.
  503.   IF sy-subrc <> 0.
  504.   ENDIF.
  505.  
  506.  
  507.  
  508.  
  509. *&---------------------------------------------------------------------*
  510. *&      Form  create_itab
  511. *&---------------------------------------------------------------------*
  512. *       text
  513. *----------------------------------------------------------------------*
  514.  
  515. FORM create_itab USING i_tdef TYPE dd02l-tabname .
  516.   DATA t_field_def TYPE TABLE OF rpy_fiel.
  517.   DATA s_field_def TYPE rpy_fiel.
  518.  
  519.   UNASSIGN:
  520.     <dyn_field>,
  521.     <dyn_rec>,
  522.     <dyn_tab>.
  523.  
  524.  
  525.   CALL FUNCTION 'RPY_TABLE_READ' DESTINATION p_des1
  526.     EXPORTING
  527. *     ACTIVATION_TYPE_I     = 'M'
  528.       language              = p_lang
  529.       table_name            = i_tdef
  530. *     WITH_DOCU             = ' '
  531. *     DOCUTYPE              = 'U'
  532. * IMPORTING
  533. *     TABL_INF              =
  534. *     TABL_TECHNICS         =
  535. *     ACTIVATION_TYPE_O     =
  536.     TABLES
  537.       tabl_fields           = t_field_def
  538. *     DOCU_TABLE_USER       =
  539. *     DOCU_TABLE_TECH       =
  540.     EXCEPTIONS
  541.       cancelled             = 1
  542.       not_found             = 2
  543.       permission_error      = 3
  544.       illegal_type          = 4
  545.       communication_failure = 5 MESSAGE msg_text
  546.       system_failure        = 6 MESSAGE msg_text
  547.       OTHERS                = 7.
  548.  
  549.   IF sy-subrc <> 0.
  550.     EXIT.
  551.   ENDIF.
  552.  
  553.  
  554.   DATA:
  555.     ldp_dyn_table     TYPE REF TO data,
  556.     lop_tabledescr    TYPE REF TO cl_abap_tabledescr,
  557.     ldp_dynaic_record TYPE REF TO data,
  558.     lop_strucdescr    TYPE REF TO cl_abap_structdescr,
  559.     ls_component      TYPE abap_componentdescr,
  560.     lt_component      TYPE abap_component_tab,
  561.     lv_desc           TYPE REF TO cl_abap_typedescr.
  562.  
  563.   DATA l_field_length   TYPE i.
  564.   DATA l_decimals   TYPE i.
  565.   DATA fields TYPE  rfc_db_fld.
  566.  
  567.   DATA lv_int1 TYPE int1.
  568.   DATA lv_int2 TYPE int2.
  569.   DATA lv_int4 TYPE int4.
  570.  
  571.  
  572.   LOOP AT t_field_def INTO s_field_def WHERE intlength > 0.
  573.  
  574.     MOVE-CORRESPONDING s_field_def TO ls_hdr.
  575.     APPEND ls_hdr TO lt_hdr.
  576.  
  577.     "    READ TABLE t_field_def INTO s_field_def WITH KEY fieldname = fields-fieldname.
  578.  
  579.     IF sy-subrc = 0.
  580.       CASE s_field_def-datatype.
  581.         WHEN 'CHAR' OR 'UNIT' OR 'CUKY'
  582. .         l_field_length = s_field_def-length.
  583.           ls_component-name = s_field_def-fieldname.
  584.           ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
  585.           APPEND ls_component TO lt_component.
  586.  
  587.         WHEN 'LANG'.
  588.           l_field_length = s_field_def-length.
  589.           ls_component-name = s_field_def-fieldname.
  590.           ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
  591.           APPEND ls_component TO lt_component.
  592.  
  593.         WHEN 'DEC' OR 'QUAN' OR 'CURR'.
  594.           l_field_length = s_field_def-intlength
  595. .           l_decimals = s_field_def-decimals.
  596.           ls_component-name = s_field_def-fieldname.
  597.           ls_component-type = cl_abap_elemdescr=>get_p( p_length = l_field_length  p_decimals = l_decimals ).
  598.           APPEND ls_component TO lt_component.
  599.  
  600.         WHEN 'NUMC'.
  601.           l_field_length = s_field_def-length.
  602.           ls_component-name = s_field_def-fieldname.
  603.           ls_component-type = cl_abap_elemdescr=>get_n( p_length = l_field_length ).
  604.           APPEND ls_component TO lt_component.
  605.  
  606.         WHEN 'INT1' OR 'INT2' OR 'INT4'.
  607.  
  608.           ls_component-name = s_field_def-fieldname.
  609.  
  610.           IF s_field_def-datatype = 'INT1'.
  611.             ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int1 ).
  612.           ELSEIF  s_field_def-datatype = 'INT2'.
  613.             ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int2 ).
  614.           ELSEIF  s_field_def-datatype = 'INT4'.
  615.             ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int4 ).
  616.           ENDIF.
  617.  
  618.           APPEND ls_component TO lt_component.
  619.  
  620.         WHEN 'DATS'.
  621.           l_field_length = s_field_def-length.
  622.           ls_component-name = s_field_def-fieldname.
  623.           ls_component-type = cl_abap_elemdescr=>get_d( ).
  624.           APPEND ls_component TO lt_component.
  625.  
  626.         WHEN 'TIMS'.
  627.           l_field_length = s_field_def-length.
  628.           ls_component-name = s_field_def-fieldname.
  629.           ls_component-type = cl_abap_elemdescr=>get_t( ).
  630.           APPEND ls_component TO lt_component.
  631.  
  632.         WHEN OTHERS.
  633.           break-point.
  634.           l_field_length = s_field_def-length.
  635.           ls_component-name = s_field_def-fieldname.
  636.           ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
  637.           APPEND ls_component TO lt_component.
  638.  
  639.       ENDCASE.
  640.     ENDIF.
  641.  
  642.  
  643.   lop_strucdescr = cl_abap_structdescr=>create( lt_component ).
  644.   CREATE DATA ldp_dynaic_record TYPE HANDLE lop_strucdescr.
  645.   lop_tabledescr ?= cl_abap_tabledescr=>create( p_line_type = lop_strucdescr ).
  646.  
  647.   CREATE DATA ldp_dyn_table TYPE HANDLE lop_tabledescr.
  648.  
  649.   ASSIGN ldp_dynaic_record->* TO <dyn_rec>.
  650.   ASSIGN ldp_dyn_table->* TO <dyn_tab>.
  651.  
  652. *    ACCP
  653. *    CHAR
  654. *    CLNT
  655. *    CUKY
  656. *    CURR
  657. *    DATS
  658. *    DEC
  659. *    DF16_DEC
  660. *    DF16_RAW
  661. *    DF16_SCL
  662. *    DF34_DEC
  663. *    DF34_RAW
  664. *    DF34_SCL
  665. *    FLTP
  666. *    INT1
  667. *    INT2
  668. *    INT4
  669. *    LANG
  670. *    LCHR
  671. *    LRAW
  672. *    NUMC
  673. *    PREC
  674. *    QUAN
  675. *    RAW
  676. *    RAWSTRING
  677. *    SSTRING
  678. *    STRING
  679. *    TIMS
  680. *    UNIT
  681. *    VARC
  682.  
  683.  
  684. FORM collect_log USING i_coll TYPE xfeld i_msg TYPE tab512.
  685.   "PARAMETERS p_log(40) TYPE c LOWER CASE OBLIGATORY DEFAULT 'C:\Temp\STULI\'.
  686.   DATA fn TYPE string.
  687.   DATA s_msg TYPE tab512.
  688.  
  689.   STATICS t_msg TYPE TABLE OF tab512.
  690.  
  691.   IF i_coll IS INITIAL.
  692.     s_msg-wa = i_msg.
  693.     APPEND s_msg TO t_msg.
  694.   ELSE.
  695.     IF NOT t_msg[] IS INITIAL.
  696.       CONCATENATE p_log 'stueli_log' sy-datum '_' sy-uzeit '.txt' INTO fn.
  697.  
  698.       CALL METHOD cl_gui_frontend_services=>gui_download
  699.         EXPORTING
  700.           filename                = fn
  701.           trunc_trailing_blanks   = 'X'
  702.         CHANGING
  703.           data_tab                = t_msg
  704.         EXCEPTIONS
  705.           file_write_error        = 1
  706.           no_batch                = 2
  707.           gui_refuse_filetransfer = 3
  708.           invalid_type            = 4
  709.           no_authority            = 5
  710.           unknown_error           = 6
  711.           header_not_allowed      = 7
  712.           separator_not_allowed   = 8
  713.           filesize_not_allowed    = 9
  714.           header_too_long         = 10
  715.           dp_error_create         = 11
  716.           dp_error_send           = 12
  717.           dp_error_write          = 13
  718.           unknown_dp_error        = 14
  719.           access_denied           = 15
  720.           dp_out_of_memory        = 16
  721.           disk_full               = 17
  722.           dp_timeout              = 18
  723.           file_not_found          = 19
  724.           dataprovider_exception  = 20
  725.           control_flush_error     = 21
  726.           not_supported_by_gui    = 22
  727.           error_no_gui            = 23
  728.           OTHERS                  = 24.
  729.  
  730.       IF sy-subrc <> 0.
  731.       ENDIF.
  732.     ENDIF.
  733.  
  734.   ENDIF.
  735.  
  736.  
  737. *FORM append_to_file.
  738. *  DATA fn TYPE string.
  739. *
  740. *  CONCATENATE p_dwn 'STKO_API.txt' INTO fn.
  741. *
  742. *  CALL METHOD cl_gui_frontend_services=>gui_download
  743. *    EXPORTING
  744. **     bin_filesize              =
  745. *      filename                  = fn
  746. *      filetype                  = 'ASC'
  747. *      append                    = 'X'
  748. *      write_field_separator     = 'X'
  749. **     header                    = '00'
  750. *      trunc_trailing_blanks     = 'X'
  751. *      trunc_trailing_blanks_eol = 'X'
  752. *    CHANGING
  753. *      data_tab                  = <t_stko>[]
  754. *    EXCEPTIONS
  755. *      file_write_error          = 1
  756. *      no_batch                  = 2
  757. *      gui_refuse_filetransfer   = 3
  758. *      invalid_type              = 4
  759. *      no_authority              = 5
  760. *      unknown_error             = 6
  761. *      header_not_allowed        = 7
  762. *      separator_not_allowed     = 8
  763. *      filesize_not_allowed      = 9
  764. *      header_too_long           = 10
  765. *      dp_error_create           = 11
  766. *      dp_error_send             = 12
  767. *      dp_error_write            = 13
  768. *      unknown_dp_error          = 14
  769. *      access_denied             = 15
  770. *      dp_out_of_memory          = 16
  771. *      disk_full                 = 17
  772. *      dp_timeout                = 18
  773. *      file_not_found            = 19
  774. *      dataprovider_exception    = 20
  775. *      control_flush_error       = 21
  776. *      not_supported_by_gui      = 22
  777. *      error_no_gui              = 23
  778. *      OTHERS                    = 24.
  779. *  .
  780. *  IF sy-subrc <> 0.
  781. ** Implement suitable error handling here
  782. *  ENDIF.
  783. *
  784. *
  785. *  CONCATENATE p_dwn 'STPO_API.txt' INTO fn.
  786. *
  787. *  CALL METHOD cl_gui_frontend_services=>gui_download
  788. *    EXPORTING
  789. **     bin_filesize              =
  790. *      filename                  = fn
  791. *      filetype                  = 'ASC'
  792. *      append                    = 'X'
  793. *      write_field_separator     = 'X'
  794. **     header                    = '00'
  795. *      trunc_trailing_blanks     = 'X'
  796. *      trunc_trailing_blanks_eol = 'X'
  797. *    CHANGING
  798. *      data_tab                  = <t_stpo>[]
  799. *    EXCEPTIONS
  800. *      file_write_error          = 1
  801. *      no_batch                  = 2
  802. *      gui_refuse_filetransfer   = 3
  803. *      invalid_type              = 4
  804. *      no_authority              = 5
  805. *      unknown_error             = 6
  806. *      header_not_allowed        = 7
  807. *      separator_not_allowed     = 8
  808. *      filesize_not_allowed      = 9
  809. *      header_too_long           = 10
  810. *      dp_error_create           = 11
  811. *      dp_error_send             = 12
  812. *      dp_error_write            = 13
  813. *      unknown_dp_error          = 14
  814. *      access_denied             = 15
  815. *      dp_out_of_memory          = 16
  816. *      disk_full                 = 17
  817. *      dp_timeout                = 18
  818. *      file_not_found            = 19
  819. *      dataprovider_exception    = 20
  820. *      control_flush_error       = 21
  821. *      not_supported_by_gui      = 22
  822. *      error_no_gui              = 23
  823. *      OTHERS                    = 24.
  824. *  .
  825. *  IF sy-subrc <> 0.
  826. ** Implement suitable error handling here
  827. *  ENDIF.
  828. *
  829. *ENDFORM.
  830.  
  831.   DATA:
  832.     l_report   TYPE raldb_repo,
  833.     l_variante TYPE raldb_vari,
  834.     BEGIN OF t_info OCCURS 0,
  835.       typ(1),
  836.       null(1),
  837.       line(120),
  838.     END OF t_info.
  839.  
  840. ** Lade Variante sy-uname
  841.   l_report   = sy-repid.
  842.   l_variante = sy-uname.
  843.  
  844.  
  845.   CALL FUNCTION 'RS_COVERPAGE_SELECTIONS'
  846.     EXPORTING
  847.       report  = l_report
  848.       variant = l_variante
  849.     TABLES
  850.       infotab = t_info
  851.     EXCEPTIONS
  852.       OTHERS  = 3.
  853.  
GeSHi ©
Attachments
Create Excel Sheets from any Table.zip
(6.28 KiB) Downloaded 276 times
Tron
.....
.....
 
Posts: 1112
Joined: Sat Aug 04, 2007 10:21 pm

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest