Create XLSX from any table X-Systems using ABAP2XLSX
Posted: 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
Example to download Service Orders with BAPI_ALM_ORDER_GET_DETAIL and BAPI_CUSTOMER_GETDETAIL2
Special thanks to SAP Mentors and Stefan Schmöcker for continously working on ABAP2XLSX Project
Example to download Service Orders with BAPI_ALM_ORDER_GET_DETAIL and BAPI_CUSTOMER_GETDETAIL2
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- *&---------------------------------------------------------------------*
- *& Report ZBC_XLSX_DOWNLOAD_FUMO
- *&
- *&---------------------------------------------------------------------*
- *&
- *&
- *&---------------------------------------------------------------------*
- REPORT zbc_xlsx_download_fumo_csord.
- *&---------------------------------------------------------------------*
- *&---------------------------------------------------------------------*
- "USAGE: m_selopt 'FIELD' S_SELOPT.
- DEFINE m_selopt .
- CLEAR ls_sel.
- ls_sel-shlpfield = &1.
- *&---------------------------------------------------------------------*
- *&---------------------------------------------------------------------*
- act_worksheet TYPE REF TO zcl_excel_worksheet.
- **** Keyfields
- BEGIN OF ty_key,
- aufnr TYPE aufnr,
- END OF ty_key.
- **** for Excel Headline
- BEGIN OF ty_hdr,
- tablname TYPE tabname,
- fieldname TYPE fieldname,
- ddtext TYPE ddtext,
- reptext TYPE reptext,
- END OF ty_hdr.
- <dyn_field> TYPE any,
- <dyn_rec> TYPE any,
- <dyn_tab> TYPE STANDARD TABLE,
- " table for BAPI_ALM_ORDER_GET_DETAIL
- <et_partner> TYPE STANDARD TABLE,
- <et_components> TYPE STANDARD TABLE,
- <et_operations> TYPE STANDARD TABLE,
- <et_relations> TYPE STANDARD TABLE,
- <et_srules> TYPE STANDARD TABLE,
- <et_olist> TYPE STANDARD TABLE,
- <et_oprol> TYPE STANDARD TABLE,
- <et_texts> TYPE STANDARD TABLE,
- <et_text_lines> TYPE STANDARD TABLE,
- <et_prts> TYPE STANDARD TABLE,
- <et_costs_sum> TYPE STANDARD TABLE,
- <et_costs_details> TYPE STANDARD TABLE,
- <et_reforder_serno_olist> TYPE STANDARD TABLE,
- <et_serviceoutline> TYPE STANDARD TABLE,
- <et_servicelines> TYPE STANDARD TABLE,
- <et_servicelimit> TYPE STANDARD TABLE,
- <et_servicecontractlimits> TYPE STANDARD TABLE,
- <et_permit> TYPE STANDARD TABLE,
- <et_permit_issue> TYPE STANDARD TABLE,
- " Header table dummy
- <et_header> TYPE STANDARD TABLE,
- <et_srvdata> TYPE STANDARD TABLE,
- <et_reforder_item> TYPE STANDARD TABLE,
- <t_customeraddress> TYPE STANDARD TABLE,
- " Header structure
- <es_header> TYPE any,
- <es_srvdata> TYPE any,
- <es_reforder_item> TYPE any,
- <es_partner> TYPE any,
- <s_customeraddress> TYPE any.
- DATA:
- toptions TYPE TABLE OF rfc_db_opt,
- tfields TYPE TABLE OF rfc_db_fld,
- tdata TYPE TABLE OF ytab4000,
- sdata TYPE ytab4000,
- i_excel TYPE solix_tab,
- i_excel_len TYPE i.
- lv_path = p_dwn.
- * Tables
- PERFORM upload_keys.
- <et_partner> ,
- <et_operations>,
- <et_components>,
- <et_relations>,
- <et_srules>,
- <et_olist>,
- <et_oprol>,
- <et_texts>,
- <et_text_lines>,
- <et_prts>,
- <et_costs_sum>,
- <et_costs_details>,
- <et_reforder_serno_olist>,
- <et_serviceoutline>,
- <et_servicelines>,
- <et_servicelimit>,
- <et_servicecontractlimits>,
- <et_permit>,
- <et_permit_issue>,
- <t_customeraddress>,
- <et_header>,
- <et_srvdata>,
- <et_reforder_item>.
- " Header structure
- <s_customeraddress>,
- <es_header>,
- <es_srvdata>,
- <es_reforder_item>.
- CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'
- EXPORTING
- IMPORTING
- output = ls_key-aufnr.
- CALL FUNCTION 'BAPI_ALM_ORDER_GET_DETAIL' DESTINATION p_des1
- EXPORTING
- number = ls_key-aufnr
- IMPORTING
- es_header = <es_header>
- es_srvdata = <es_srvdata>
- es_reforder_item = <es_reforder_item>
- et_partner = <et_partner>
- et_operations = <et_operations>
- et_components = <et_components>
- et_relations = <et_relations>
- et_srules = <et_srules>
- et_olist = <et_olist>
- et_oprol = <et_oprol>
- et_texts = <et_texts>
- et_text_lines = <et_text_lines>
- et_prts = <et_prts>
- et_costs_sum = <et_costs_sum>
- et_costs_details = <et_costs_details>
- return = t_bapiret
- et_reforder_serno_olist = <et_reforder_serno_olist>
- et_serviceoutline = <et_serviceoutline>
- et_servicelines = <et_servicelines>
- et_servicelimit = <et_servicelimit>
- et_servicecontractlimits = <et_servicecontractlimits>
- et_permit = <et_permit>
- et_permit_issue = <et_permit_issue>
- EXCEPTIONS
- OTHERS = 3.
- * CONCATENATE 'BOM:' gv_matnr
- * 'Plant:' ls_mast-werks
- * 'Type:' ls_mast-stlan
- * 'Altern.:' ls_mast-stlal
- * '===> failed to read' INTO
- * g_msg SEPARATED BY space.
- * PERFORM collect_log USING ' ' g_msg .
- * Get Partner AG Address
- UNASSIGN <dyn_field>.
- UNASSIGN <dyn_field>.
- CLEAR s_bapiret.
- l_cust = <dyn_field>.
- CALL FUNCTION 'BAPI_CUSTOMER_GETDETAIL2' DESTINATION p_des1
- EXPORTING
- customerno = l_cust
- IMPORTING
- customeraddress = <s_customeraddress>
- return = s_bapiret
- EXCEPTIONS
- OTHERS = 3.
- * PERFORM append_to_file.
- " Header Structures
- TRY.
- excel->set_active_sheet_index( i_active_worksheet = 1 ).
- CATCH zcx_excel .
- FREE excel.
- *&---------------------------------------------------------------------*
- *& Form stxcompare
- *&---------------------------------------------------------------------*
- * text
- *----------------------------------------------------------------------*
- FORM fill_itab.
- " delete all ":" in time vars
- READ TABLE lt ASSIGNING <ls> INDEX sy-tabix.
- l_idx = sy-tabix.
- <dyn_field> = ls-wa.
- *&---------------------------------------------------------------------*
- *& Form stxcompare
- *&---------------------------------------------------------------------*
- * text
- *----------------------------------------------------------------------*
- .
- , worksheet_title TYPE zexcel_sheet_title
- , table_settings TYPE zexcel_s_table_settings
- .
- lv_col TYPE i,
- lv_value TYPE string.
- table_settings-table_style = zcl_excel_table=>builtinstyle_medium2.
- table_settings-show_row_stripes
- = abap_true.
- table_settings-nofilters = abap_true.
- table_settings-top_left_column
- = first_column.
- table_settings-top_left_row = 02.
- TRY.
- CREATE OBJECT excel.
- worksheet = excel->get_active_worksheet( ).
- ELSE.
- worksheet = excel->add_new_worksheet( ).
- * lo_theme->set_latin_font(
- * EXPORTING
- * iv_type = zcl_excel_theme_font_scheme=>c_minor
- * iv_typeface = 'Arial monospaced for SAP'
- ** iv_panose =
- ** iv_pitchfamily =
- ** iv_charset =
- * ).
- worksheet_title = i_sheetname.
- worksheet->set_title( worksheet_title ).
- worksheet->bind_table(
- ip_table = i_tab[]
- is_table_settings = table_settings
- ).
- lv_row = 1.
- lv_col = lv_col + 1.
- lv_value = ls_hdr-reptext.
- worksheet->set_cell( ip_column = lv_col ip_row = lv_row ip_value = lv_value ).
- worksheet->change_cell_style( ip_column = lv_col
- ip_row = lv_row
- ip_fill_filltype = zcl_excel_style_fill=>c_fill_solid
- ip_fill_fgcolor_rgb = zcl_excel_style_color=>c_gray ).
- CATCH zcx_excel .
- *** Create output
- DATA:
- cl_writer TYPE REF TO zif_excel_writer.
- " t_rawdata TYPE solix_tab, " Will be used for downloading or open directly
- " bytecount TYPE i. " Will be used for downloading or open directly
- CREATE OBJECT cl_writer TYPE zcl_excel_writer_2007.
- xdata = cl_writer->write_file( excel ).
- i_excel = cl_bcs_convert=>xstring_to_solix( iv_xstring = xdata ).
- i_excel_len = xstrlen( xdata ).
- * Get trailing blank
- cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = i_excel_len
- filename = i_file
- filetype = 'BIN'
- CHANGING data_tab = i_excel ).
- FORM upload_keys.
- l_path = p_upl.
- CALL METHOD cl_gui_frontend_services=>gui_upload
- EXPORTING
- filename = l_path
- * filetype = 'ASC'
- has_field_separator = 'X'
- * header_length = 0
- * read_by_line = 'X'
- * dat_mode = SPACE
- * codepage = SPACE
- * ignore_cerr = ABAP_TRUE
- * replacement = '#'
- * virus_scan_profile =
- * IMPORTING
- * filelength =
- * header =
- CHANGING
- data_tab = lt_key
- * isscanperformed = SPACE
- EXCEPTIONS
- file_open_error = 1
- file_read_error = 2
- no_batch = 3
- gui_refuse_filetransfer = 4
- invalid_type = 5
- no_authority = 6
- unknown_error = 7
- bad_data_format = 8
- header_not_allowed = 9
- separator_not_allowed = 10
- header_too_long = 11
- unknown_dp_error = 12
- access_denied = 13
- dp_out_of_memory = 14
- disk_full = 15
- dp_timeout = 16
- not_supported_by_gui = 17
- error_no_gui = 18
- OTHERS = 19.
- *&---------------------------------------------------------------------*
- *& Form create_itab
- *&---------------------------------------------------------------------*
- * text
- *----------------------------------------------------------------------*
- UNASSIGN:
- <dyn_field>,
- <dyn_rec>,
- <dyn_tab>.
- CALL FUNCTION 'RPY_TABLE_READ' DESTINATION p_des1
- EXPORTING
- * ACTIVATION_TYPE_I = 'M'
- language = p_lang
- table_name = i_tdef
- * WITH_DOCU = ' '
- * DOCUTYPE = 'U'
- * IMPORTING
- * TABL_INF =
- * TABL_TECHNICS =
- * ACTIVATION_TYPE_O =
- tabl_fields = t_field_def
- * DOCU_TABLE_USER =
- * DOCU_TABLE_TECH =
- EXCEPTIONS
- cancelled = 1
- not_found = 2
- permission_error = 3
- illegal_type = 4
- OTHERS = 7.
- EXIT.
- DATA:
- ldp_dyn_table TYPE REF TO data,
- lop_tabledescr TYPE REF TO cl_abap_tabledescr,
- ldp_dynaic_record TYPE REF TO data,
- lop_strucdescr TYPE REF TO cl_abap_structdescr,
- ls_component TYPE abap_componentdescr,
- lt_component TYPE abap_component_tab,
- lv_desc TYPE REF TO cl_abap_typedescr.
- " READ TABLE t_field_def INTO s_field_def WITH KEY fieldname = fields-fieldname.
- . l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
- l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
- l_field_length = s_field_def-intlength
- . l_decimals = s_field_def-decimals.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_p( p_length = l_field_length p_decimals = l_decimals ).
- l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_n( p_length = l_field_length ).
- ls_component-name = s_field_def-fieldname.
- ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int1 ).
- ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int2 ).
- ls_component-type ?= cl_abap_elemdescr=>describe_by_data( p_data = lv_int4 ).
- l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_d( ).
- l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_t( ).
- l_field_length = s_field_def-length.
- ls_component-name = s_field_def-fieldname.
- ls_component-type = cl_abap_elemdescr=>get_c( p_length = l_field_length ).
- lop_strucdescr = cl_abap_structdescr=>create( lt_component ).
- CREATE DATA ldp_dynaic_record TYPE HANDLE lop_strucdescr.
- lop_tabledescr ?= cl_abap_tabledescr=>create( p_line_type = lop_strucdescr ).
- CREATE DATA ldp_dyn_table TYPE HANDLE lop_tabledescr.
- * ACCP
- * CHAR
- * CLNT
- * CUKY
- * CURR
- * DATS
- * DEC
- * DF16_DEC
- * DF16_RAW
- * DF16_SCL
- * DF34_DEC
- * DF34_RAW
- * DF34_SCL
- * FLTP
- * INT1
- * INT2
- * INT4
- * LANG
- * LCHR
- * LRAW
- * NUMC
- * PREC
- * QUAN
- * RAW
- * RAWSTRING
- * SSTRING
- * STRING
- * TIMS
- * UNIT
- * VARC
- "PARAMETERS p_log(40) TYPE c LOWER CASE OBLIGATORY DEFAULT 'C:\Temp\STULI\'.
- s_msg-wa = i_msg.
- ELSE.
- CALL METHOD cl_gui_frontend_services=>gui_download
- EXPORTING
- filename = fn
- trunc_trailing_blanks = 'X'
- CHANGING
- data_tab = t_msg
- EXCEPTIONS
- file_write_error = 1
- no_batch = 2
- gui_refuse_filetransfer = 3
- invalid_type = 4
- no_authority = 5
- unknown_error = 6
- header_not_allowed = 7
- separator_not_allowed = 8
- filesize_not_allowed = 9
- header_too_long = 10
- dp_error_create = 11
- dp_error_send = 12
- dp_error_write = 13
- unknown_dp_error = 14
- access_denied = 15
- dp_out_of_memory = 16
- disk_full = 17
- dp_timeout = 18
- file_not_found = 19
- dataprovider_exception = 20
- control_flush_error = 21
- not_supported_by_gui = 22
- error_no_gui = 23
- OTHERS = 24.
- *FORM append_to_file.
- * DATA fn TYPE string.
- *
- * CONCATENATE p_dwn 'STKO_API.txt' INTO fn.
- *
- * CALL METHOD cl_gui_frontend_services=>gui_download
- * EXPORTING
- ** bin_filesize =
- * filename = fn
- * filetype = 'ASC'
- * append = 'X'
- * write_field_separator = 'X'
- ** header = '00'
- * trunc_trailing_blanks = 'X'
- * trunc_trailing_blanks_eol = 'X'
- * CHANGING
- * data_tab = <t_stko>[]
- * EXCEPTIONS
- * file_write_error = 1
- * no_batch = 2
- * gui_refuse_filetransfer = 3
- * invalid_type = 4
- * no_authority = 5
- * unknown_error = 6
- * header_not_allowed = 7
- * separator_not_allowed = 8
- * filesize_not_allowed = 9
- * header_too_long = 10
- * dp_error_create = 11
- * dp_error_send = 12
- * dp_error_write = 13
- * unknown_dp_error = 14
- * access_denied = 15
- * dp_out_of_memory = 16
- * disk_full = 17
- * dp_timeout = 18
- * file_not_found = 19
- * dataprovider_exception = 20
- * control_flush_error = 21
- * not_supported_by_gui = 22
- * error_no_gui = 23
- * OTHERS = 24.
- * .
- * IF sy-subrc <> 0.
- ** Implement suitable error handling here
- * ENDIF.
- *
- *
- * CONCATENATE p_dwn 'STPO_API.txt' INTO fn.
- *
- * CALL METHOD cl_gui_frontend_services=>gui_download
- * EXPORTING
- ** bin_filesize =
- * filename = fn
- * filetype = 'ASC'
- * append = 'X'
- * write_field_separator = 'X'
- ** header = '00'
- * trunc_trailing_blanks = 'X'
- * trunc_trailing_blanks_eol = 'X'
- * CHANGING
- * data_tab = <t_stpo>[]
- * EXCEPTIONS
- * file_write_error = 1
- * no_batch = 2
- * gui_refuse_filetransfer = 3
- * invalid_type = 4
- * no_authority = 5
- * unknown_error = 6
- * header_not_allowed = 7
- * separator_not_allowed = 8
- * filesize_not_allowed = 9
- * header_too_long = 10
- * dp_error_create = 11
- * dp_error_send = 12
- * dp_error_write = 13
- * unknown_dp_error = 14
- * access_denied = 15
- * dp_out_of_memory = 16
- * disk_full = 17
- * dp_timeout = 18
- * file_not_found = 19
- * dataprovider_exception = 20
- * control_flush_error = 21
- * not_supported_by_gui = 22
- * error_no_gui = 23
- * OTHERS = 24.
- * .
- * IF sy-subrc <> 0.
- ** Implement suitable error handling here
- * ENDIF.
- *
- *ENDFORM.
- DATA:
- l_report TYPE raldb_repo,
- l_variante TYPE raldb_vari,
- BEGIN OF t_info OCCURS 0,
- typ(1),
- null(1),
- line(120),
- END OF t_info.
- ** Lade Variante sy-uname
- l_report = sy-repid.
- l_variante = sy-uname.
- CALL FUNCTION 'RS_COVERPAGE_SELECTIONS'
- EXPORTING
- report = l_report
- variant = l_variante
- infotab = t_info
- EXCEPTIONS
- OTHERS = 3.
- GeSHi ©