@EndUserText.label : 'Kotlin Tester'
@AbapCatalog.enhancementCategory : #EXTENSIBLE_ANY
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table ztest_kotlin {
key mandt : mandt not null;
key guid : /aif/exp_guid not null;
date_updated : sydatum;
time_updated : syuzeit;
last_updated_by : syuname;
order_number : vbeln;
customer_number : kunag;
customer_message : zz_note;
}
START-OF-SELECTION.
TRY.
NEW lcl_excel_maint( )->execute( VALUE #( upload = r_up
download = r_down
tabname = p_table
excel_file = p_file ) ).
CATCH lcl_my_exception INTO DATA(lo_exc).
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
"Old ABAP:
DATA: lo_obj TYPE REF TO lcl_excel_maint,
lo_exc TYPE REF TO lcl_my_exception,
lw_params TYPE lcl_excel_maint=>lty_param.
lw_params-upload = r_up.
lw_params-download = r_down.
lw_params-tabname = p_table.
lw_params-excel_file = p_file.
CREATE OBJECT lo_obj.
TRY.
call method lo_obj->execute( lw_params ).
CATCH lcl_my_exception INTO lo_exc.
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
PUBLIC SECTION.
METHODS:
constructor
IMPORTING i_textid LIKE textid OPTIONAL
i_previous LIKE previous OPTIONAL
i_message TYPE string OPTIONAL,
get_message RETURNING VALUE(r_message) TYPE bapi_msg.
PRIVATE SECTION.
DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
METHOD constructor.
CALL METHOD super->constructor
EXPORTING
textid = i_textid
previous = i_previous.
CLEAR gv_message.
gv_message = i_message.
ENDMETHOD.
METHOD get_message.
r_message = gv_message.
ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION
IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
ENDIF.
CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
ASSIGN lo_table->* TO <lt_table>.
"Get all records currently in our Z Table...
SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.
"Write the Excel file, and open Excel and display on the user's PC...
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
open_excel_file_on_pc( i_parameters-excel_file ).
"Old ABAP:
DATA: lv_xstring TYPE xstring,
lv_temp_string TYPE string.
lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
lv_temp_string = i_parameters-excel_file.
CALL METHOD export_excel_data(
EXPORTING
im_xstring = lv_xstring
im_file = lv_temp_string ).
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
METHOD generate_xlsx_string.
DATA: lo_data TYPE REF TO data.
DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
IF l_version <> if_salv_bs_xml=>version_25 AND
l_version <> if_salv_bs_xml=>version_26. RETURN.
ENDIF.
TRY.
cl_salv_table=>factory( EXPORTING list_display = abap_false
IMPORTING r_salv_table = DATA(lo_salv_table)
CHANGING t_table = ct_table ).
CATCH cx_salv_msg.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
ENDTRY.
GET REFERENCE OF ct_table INTO lo_data.
DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data
t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
r_columns = lo_salv_table->get_columns( )
r_aggregations = lo_salv_table->get_aggregations( ) ) ).
cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING xml = r_xstring ).
ENDMETHOD. "generate_xlsx_string
METHOD export_excel_data.
TYPES: BEGIN OF lty_line,
data(1024) TYPE x,
END OF lty_line.
DATA: lv_size TYPE i,
lt_bintab TYPE TABLE OF lty_line.
IF im_xstring IS INITIAL. RETURN. ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = im_xstring
IMPORTING
output_length = lv_size
TABLES
binary_tab = lt_bintab.
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
filename = im_file
filetype = 'BIN'
CHANGING data_tab = lt_bintab
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
OTHERS = 22 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
IF sy-subrc = 15.
lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
|If so, close the Excel file, and re-run.|.
ENDIF.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
ENDIF.
ENDMETHOD. "export_excel_data
METHOD open_excel_file_on_pc.
DATA: lv_pathname TYPE string,
lv_filename TYPE string.
CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
EXPORTING
iv_pathfile = im_file
IMPORTING
ev_pathname = lv_pathname
ev_filename = lv_filename.
cl_gui_frontend_services=>execute( EXPORTING application = 'EXCEL'
parameter = lv_filename
default_directory = lv_pathname
operation = 'OPEN'
maximized = 'X'
EXCEPTIONS cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
synchronous_failed = 8
not_supported_by_gui = 9 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
ENDIF.
ENDMETHOD. "open_excel_file_on_pc
"Upload the Excel file from the user's PC and populate our SAP table...
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
IF <lt_table> IS NOT INITIAL.
"Refresh/Delete all existing entries from the SAP table...
DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
CLEAR <lt_table>[].
ENDIF.
convert_excel_to_table( EXPORTING io_table = import_excel_data( CONV string( i_parameters-excel_file ) )
im_tabname = i_parameters-tabname
CHANGING ct_table = <lt_table> ).
METHOD import_excel_data.
DATA: lt_xtab TYPE cpt_x255,
lv_size TYPE i.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = im_file
filetype = 'BIN'
IMPORTING filelength = lv_size
CHANGING data_tab = lt_xtab ).
IF sy-subrc NE 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
ENDIF.
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xtab
im_size = lv_size
IMPORTING ex_xstring = DATA(lv_xstring) ).
DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
xdocument = lv_xstring ).
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
IF rt_table IS INITIAL.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
ENDIF.
ENDMETHOD. "import_excel_data
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
"Old ABAP:
DATA: lt_temp_table TYPE REF TO data,
lt_worksheets TYPE STANDARD TABLE OF string,
lv_worksheet TYPE string.
CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
IMPORTING
worksheet_names = lt_worksheets.
READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
EXPORTING
worksheet_name = lv_worksheet
RECEIVING
itab = rt_table.
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
METHOD convert_excel_to_table.
DATA: lo_record TYPE REF TO data.
FIELD-SYMBOLS: <lt_input_table> TYPE table.
ASSIGN io_table->* TO <lt_input_table>.
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
"Need to move the generic Excel input table into our SAP defined table...
"Get a list of fields in our SAP table...
CREATE DATA lo_record LIKE LINE OF ct_table.
ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
"Migrate each field into our table with an SAP schema...
LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
CLEAR <lw_record>.
LOOP AT components INTO DATA(lw_field).
ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
<lfs_target> = <lfs_source>.
ENDIF.
UNASSIGN: <lfs_source>, <lfs_target>.
ENDLOOP.
IF <lw_record> IS NOT INITIAL.
APPEND <lw_record> TO ct_table.
ENDIF.
ENDLOOP.
ENDMETHOD. "convert_excel_to_table
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
"Old ABAP:
data: type_ref type ref to cl_abap_typedescr,
struc_descr type ref to cl_abap_structdescr,
components type ABAP_COMPDESCR_TAB.
type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
struc_descr ?= type_ref.
components = struc_descr->components.
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
METHOD my_custom_table_logic.
LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
"Auto-generate a unique key...
ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
IF <lfs_guid> IS ASSIGNED.
IF <lfs_guid> IS INITIAL.
TRY.
<lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
CATCH cx_uuid_error INTO DATA(lo_guid_error).
MESSAGE 'GUID Generation error.' TYPE 'I'.
ENDTRY.
ENDIF.
ENDIF.
ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
IF <lfs_udate> IS ASSIGNED.
<lfs_udate> = sy-datum.
ENDIF.
ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
IF <lfs_utime> IS ASSIGNED.
<lfs_utime> = sy-uzeit.
ENDIF.
ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
IF <lfs_user> IS ASSIGNED.
<lfs_user> = sy-uname.
ENDIF.
UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
ENDLOOP.
ENDMETHOD. "my_custom_table_logic
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
You should have the following:
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ZTEST_KOTLIN {
}
@AbapCatalog.sqlViewName: 'ZCDS_KOTLIN_SE11'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Kotlin CDS View'
define view ZCDS_KOTLIN as select from ztest_kotlin {
//ZTEST_KOTLIN
key guid,
date_updated,
time_updated,
last_updated_by,
order_number,
customer_number,
customer_message
}
REPORT z_excel_maintenance.
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE fil_lbl.
PARAMETERS: r_down RADIOBUTTON GROUP g1,
r_up RADIOBUTTON GROUP g1.
PARAMETERS: p_table TYPE tabname16 DEFAULT 'ZTEST_KOTLIN',
p_file TYPE localfile DEFAULT 'c:\1\ExcelFile.xlsx'.
SELECTION-SCREEN END OF BLOCK b1.
CLASS lcl_my_exception DEFINITION INHERITING FROM cx_dynamic_check.
PUBLIC SECTION.
METHODS:
constructor
IMPORTING i_textid LIKE textid OPTIONAL
i_previous LIKE previous OPTIONAL
i_message TYPE string OPTIONAL,
get_message RETURNING VALUE(r_message) TYPE bapi_msg.
PRIVATE SECTION.
DATA: gv_message TYPE string.
ENDCLASS. "lcl_my_exception DEFINITION
CLASS lcl_my_exception IMPLEMENTATION.
METHOD constructor.
CALL METHOD super->constructor
EXPORTING
textid = i_textid
previous = i_previous.
CLEAR gv_message.
gv_message = i_message.
ENDMETHOD.
METHOD get_message.
r_message = gv_message.
ENDMETHOD.
ENDCLASS. "lcl_my_exception IMPLEMENTATION
CLASS lcl_excel_maint DEFINITION CREATE PUBLIC FINAL.
PUBLIC SECTION.
CONSTANTS: c_excel TYPE string VALUE 'Excel files(*.xlsx)|*.xlsx'.
TYPES: BEGIN OF lty_param,
upload TYPE char1,
download TYPE char1,
tabname TYPE tabname16,
excel_file TYPE localfile,
END OF lty_param.
CLASS-METHODS:
select_file
IMPORTING i_filename TYPE string
i_type TYPE string
RETURNING VALUE(re_file) TYPE localfile.
METHODS:
execute
IMPORTING i_parameters TYPE lty_param
RAISING lcl_my_exception.
PRIVATE SECTION.
METHODS:
import_excel_data
IMPORTING im_file TYPE string
RETURNING VALUE(rt_table) TYPE REF TO data
RAISING lcl_my_exception,
generate_xlsx_string
CHANGING ct_table TYPE table
RETURNING VALUE(r_xstring) TYPE xstring,
export_excel_data
IMPORTING im_xstring TYPE xstring
im_file TYPE string
RAISING lcl_my_exception,
my_custom_table_logic
CHANGING ct_table TYPE table,
open_excel_file_on_pc
IMPORTING im_file TYPE localfile
RAISING lcl_my_exception,
convert_excel_to_table
IMPORTING io_table TYPE REF TO data
im_tabname TYPE tabname16
CHANGING ct_table TYPE table
RAISING lcl_my_exception.
ENDCLASS. "lcl_excel_maint
CLASS lcl_excel_maint IMPLEMENTATION.
METHOD execute.
DATA: lo_table TYPE REF TO data,
lv_count_before TYPE i,
lv_count_after TYPE i.
FIELD-SYMBOLS: <lt_table> TYPE table.
IF i_parameters-tabname(1) <> 'Z' AND i_parameters-tabname(1) <> 'Y'.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING
i_message = |Only Custom Tables are supported (must begin with 'Y' or 'Z').|.
ENDIF.
SELECT SINGLE tabname INTO @DATA(lv_tabname) FROM dd03l
WHERE tabname = @i_parameters-tabname.
IF sy-subrc <> 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid Table Name '{ i_parameters-tabname }'.|.
ENDIF.
CREATE DATA lo_table TYPE TABLE OF (i_parameters-tabname).
ASSIGN lo_table->* TO <lt_table>.
"Get all records currently in our Z Table...
SELECT * FROM (i_parameters-tabname) INTO TABLE <lt_table>.
IF i_parameters-download = abap_true.
"Old ABAP:
* DATA: lv_xstring TYPE xstring,
* lv_temp_string TYPE string.
* lv_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> ).
* lv_temp_string = i_parameters-excel_file.
* CALL METHOD export_excel_data(
* EXPORTING
* im_xstring = lv_xstring
* im_file = lv_temp_string ).
"Write the Excel file, and open Excel and display on the user's PC...
export_excel_data( im_xstring = generate_xlsx_string( CHANGING ct_table = <lt_table> )
im_file = CONV string( i_parameters-excel_file ) ).
open_excel_file_on_pc( i_parameters-excel_file ).
ELSEIF i_parameters-upload = abap_true.
"Upload the Excel file from the user's PC and populate our SAP table...
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_before.
IF <lt_table> IS NOT INITIAL.
"Refresh/Delete all existing entries from the SAP table...
DELETE (i_parameters-tabname) FROM TABLE <lt_table>.
CLEAR <lt_table>[].
ENDIF.
convert_excel_to_table( EXPORTING io_table = import_excel_data( CONV string( i_parameters-excel_file ) )
im_tabname = i_parameters-tabname
CHANGING ct_table = <lt_table> ).
"Adjust the table according to our requirements...
my_custom_table_logic( CHANGING ct_table = <lt_table> ).
MODIFY (i_parameters-tabname) FROM TABLE <lt_table>.
COMMIT WORK AND WAIT.
IF sy-subrc = 0.
SELECT COUNT( * ) FROM (i_parameters-tabname) INTO lv_count_after.
MESSAGE |Table { i_parameters-tabname } successfully refreshed. | &&
|{ lv_count_before } records deleted and { lv_count_after } inserted.| TYPE 'I'.
ELSE.
RAISE EXCEPTION TYPE lcl_my_exception
EXPORTING
i_message = |Failed to update table { i_parameters-tabname }.|.
ENDIF.
ENDIF.
ENDMETHOD. "execute
METHOD convert_excel_to_table.
DATA: lo_record TYPE REF TO data.
FIELD-SYMBOLS: <lt_input_table> TYPE table.
ASSIGN io_table->* TO <lt_input_table>.
"Remove the Header row, if one exists (i.e. starts with Client - MANDT)...
ASSIGN COMPONENT 1 OF STRUCTURE <lt_input_table>[ 1 ] TO FIELD-SYMBOL(<lfs_mandt>).
IF <lfs_mandt> IS ASSIGNED.
IF <lfs_mandt> = 'Client'. DELETE <lt_input_table> INDEX 1. ENDIF.
ENDIF.
"Need to move the generic Excel input table into our SAP defined table...
"Get a list of fields in our SAP table...
CREATE DATA lo_record LIKE LINE OF ct_table.
ASSIGN lo_record->* TO FIELD-SYMBOL(<lw_record>).
DATA(components) = CAST cl_abap_structdescr( cl_abap_typedescr=>describe_by_name( im_tabname ) )->components.
* "Old ABAP for the above single line of code:
* data: type_ref type ref to cl_abap_typedescr,
* struc_descr type ref to cl_abap_structdescr,
* components type ABAP_COMPDESCR_TAB.
* type_ref = cl_abap_typedescr=>describe_by_name( im_tabname ).
* struc_descr ?= type_ref.
* components = struc_descr->components.
"Migrate each field into our table with an SAP schema...
LOOP AT <lt_input_table> ASSIGNING FIELD-SYMBOL(<lw_input>).
CLEAR <lw_record>.
LOOP AT components INTO DATA(lw_field).
ASSIGN COMPONENT lw_field-name OF STRUCTURE <lw_record> TO FIELD-SYMBOL(<lfs_target>).
ASSIGN COMPONENT sy-tabix OF STRUCTURE <lw_input> TO FIELD-SYMBOL(<lfs_source>).
IF <lfs_target> IS ASSIGNED AND <lfs_source> IS ASSIGNED.
<lfs_target> = <lfs_source>.
ENDIF.
UNASSIGN: <lfs_source>, <lfs_target>.
ENDLOOP.
IF <lw_record> IS NOT INITIAL.
APPEND <lw_record> TO ct_table.
ENDIF.
ENDLOOP.
ENDMETHOD. "convert_excel_to_table
METHOD my_custom_table_logic.
LOOP AT ct_table ASSIGNING FIELD-SYMBOL(<lfs_wa>).
"Auto-generate a unique key...
ASSIGN COMPONENT 'GUID' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_guid>).
IF <lfs_guid> IS ASSIGNED.
IF <lfs_guid> IS INITIAL.
TRY.
<lfs_guid> = cl_system_uuid=>if_system_uuid_static~create_uuid_x16( ).
CATCH cx_uuid_error INTO DATA(lo_guid_error).
MESSAGE 'GUID Generation error.' TYPE 'I'.
ENDTRY.
ENDIF.
ENDIF.
ASSIGN COMPONENT 'DATE_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_udate>).
IF <lfs_udate> IS ASSIGNED.
<lfs_udate> = sy-datum.
ENDIF.
ASSIGN COMPONENT 'TIME_UPDATED' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_utime>).
IF <lfs_utime> IS ASSIGNED.
<lfs_utime> = sy-uzeit.
ENDIF.
ASSIGN COMPONENT 'LAST_UPDATED_BY' OF STRUCTURE <lfs_wa> TO FIELD-SYMBOL(<lfs_user>).
IF <lfs_user> IS ASSIGNED.
<lfs_user> = sy-uname.
ENDIF.
UNASSIGN: <lfs_udate>, <lfs_guid>, <lfs_utime>, <lfs_user>.
ENDLOOP.
ENDMETHOD. "my_custom_table_logic
METHOD open_excel_file_on_pc.
DATA: lv_pathname TYPE string,
lv_filename TYPE string.
CALL FUNCTION 'RPM_DX_PATH_FILE_SPLIT'
EXPORTING
iv_pathfile = im_file
IMPORTING
ev_pathname = lv_pathname
ev_filename = lv_filename.
cl_gui_frontend_services=>execute( EXPORTING application = 'EXCEL'
parameter = lv_filename
default_directory = lv_pathname
operation = 'OPEN'
maximized = 'X'
EXCEPTIONS cntl_error = 1
error_no_gui = 2
bad_parameter = 3
file_not_found = 4
path_not_found = 5
file_extension_unknown = 6
error_execute_failed = 7
synchronous_failed = 8
not_supported_by_gui = 9 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Excel Could not open. { lv_error_text }|.
ENDIF.
ENDMETHOD. "open_excel_file_on_pc
METHOD select_file.
re_file = cl_openxml_helper=>browse_local_file_open(
iv_title = 'Select File'
iv_filename = i_filename
iv_extpattern = i_type ).
ENDMETHOD. "select_file
METHOD generate_xlsx_string.
DATA: lo_data TYPE REF TO data.
DATA(l_version) = cl_salv_bs_a_xml_base=>get_version( ).
IF l_version <> if_salv_bs_xml=>version_25 AND
l_version <> if_salv_bs_xml=>version_26. RETURN.
ENDIF.
TRY.
cl_salv_table=>factory( EXPORTING list_display = abap_false
IMPORTING r_salv_table = DATA(lo_salv_table)
CHANGING t_table = ct_table ).
CATCH cx_salv_msg.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'ALV Table create failed.'.
ENDTRY.
GET REFERENCE OF ct_table INTO lo_data.
DATA(lo_result_data) = cl_salv_ex_util=>factory_result_data_table(
r_data = lo_data
t_fieldcatalog = cl_salv_controller_metadata=>get_lvc_fieldcatalog(
r_columns = lo_salv_table->get_columns( )
r_aggregations = lo_salv_table->get_aggregations( ) ) ).
cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform( EXPORTING xml_type = if_salv_bs_xml=>c_type_xlsx
xml_version = l_version
r_result_data = lo_result_data
xml_flavour = if_salv_bs_c_tt=>c_tt_xml_flavour_export
gui_type = if_salv_bs_xml=>c_gui_type_gui
IMPORTING xml = r_xstring ).
ENDMETHOD. "generate_xlsx_string
METHOD export_excel_data.
TYPES: BEGIN OF lty_line,
data(1024) TYPE x,
END OF lty_line.
DATA: lv_size TYPE i,
lt_bintab TYPE TABLE OF lty_line.
IF im_xstring IS INITIAL. RETURN. ENDIF.
CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
EXPORTING
buffer = im_xstring
IMPORTING
output_length = lv_size
TABLES
binary_tab = lt_bintab.
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
filename = im_file
filetype = 'BIN'
CHANGING data_tab = lt_bintab
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
OTHERS = 22 ).
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4
INTO DATA(lv_error_text).
IF sy-subrc = 15.
lv_error_text = |{ lv_error_text }. Do you have the file currently open? | &&
|If so, close the Excel file, and re-run.|.
ENDIF.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = lv_error_text.
ENDIF.
ENDMETHOD. "export_excel_data
METHOD import_excel_data.
DATA: lt_xtab TYPE cpt_x255,
lv_size TYPE i.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = im_file
filetype = 'BIN'
IMPORTING filelength = lv_size
CHANGING data_tab = lt_xtab ).
IF sy-subrc NE 0.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = |Invalid File { im_file }|.
ENDIF.
cl_scp_change_db=>xtab_to_xstr( EXPORTING im_xtab = lt_xtab
im_size = lv_size
IMPORTING ex_xstring = DATA(lv_xstring) ).
DATA(lo_excel) = NEW cl_fdt_xl_spreadsheet( document_name = im_file
xdocument = lv_xstring ).
lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names(
IMPORTING worksheet_names = DATA(lt_worksheets) ).
rt_table = lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lt_worksheets[ 1 ] ).
* "Old ABAP for the above 2 lines of code:
* DATA: lt_temp_table TYPE REF TO data,
* lt_worksheets TYPE STANDARD TABLE OF string,
* lv_worksheet TYPE string.
* CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_worksheet_names
* IMPORTING
* worksheet_names = lt_worksheets.
* READ TABLE lt_worksheets INTO lv_worksheet INDEX 1.
* CALL METHOD lo_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet
* EXPORTING
* worksheet_name = lv_worksheet
* RECEIVING
* itab = rt_table.
IF rt_table IS INITIAL.
RAISE EXCEPTION TYPE lcl_my_exception EXPORTING i_message = 'No Data found in Excel File'.
ENDIF.
ENDMETHOD. "import_excel_data
ENDCLASS. "lcl_excel_maint
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
p_file = lcl_excel_maint=>select_file( i_filename = CONV string( p_file )
i_type = lcl_excel_maint=>c_excel ).
INITIALIZATION.
%_p_file_%_app_%-text = 'Excel File'.
%_p_table_%_app_%-text = 'Z Table Name'.
%_r_up_%_app_%-text = 'Refresh SAP Table from Excel'.
%_r_down_%_app_%-text = 'Open SAP Table in Excel'.
fil_lbl = 'Excel Table Maintenance Options'.
START-OF-SELECTION.
"Old ABAP for the below execute method:
* DATA: lo_obj TYPE REF TO lcl_excel_maint,
* lo_exc TYPE REF TO lcl_my_exception,
* lw_params TYPE lcl_excel_maint=>lty_param.
* lw_params-upload = r_up.
* lw_params-download = r_down.
* lw_params-tabname = p_table.
* lw_params-excel_file = p_file.
* CREATE OBJECT lo_obj.
* TRY.
* call method lo_obj->execute( lw_params ).
* CATCH lcl_my_exception INTO lo_exc.
* MESSAGE lo_exc->get_message( ) TYPE 'I'.
* ENDTRY.
"New ABAP:
TRY.
NEW lcl_excel_maint( )->execute( VALUE #( upload = r_up
download = r_down
tabname = p_table
excel_file = p_file ) ).
CATCH lcl_my_exception INTO DATA(lo_exc).
MESSAGE lo_exc->get_message( ) TYPE 'I'.
ENDTRY.
Blog to create an xlsx file:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
10 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |