ABAP read excel(.XLSX) file to internal table in ABAP using CL_FDT_XL_SPREADSHEET

Posted by -

You are given a requirement to upload data to a custom DB table where the user provides you with the file path of the excel on his PC and the report loads the data from it to the Custom table.

For experienced Abapers, the requirement is quite straightforward, except that the user wants to upload an XLSX file.

There are not many options to read data from XLSX/XLS file.
The TEXT_CONVERT_XLS_TO_SAP function module is one of them but is a performance killer and uses OLE.
In this post, I wanted to showcase How we can read data from the XLSX files using CL_FDT_SPOREADSHEET class.

Let's start with uploading the file.

DATA:
lt_itab TYPE solix_tab.
"Get Data.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = p_file " Name of file
filetype = 'BIN' " File Type (ASCII, Binary)
CHANGING data_tab = lt_itab " Transfer table for file contents
EXCEPTIONS OTHERS = 1
).
IF sy-subrc <> 0.
RETURN.
ENDIF.
view raw load_xlsx_ex.abap hosted with ❤ by GitHub

Now that we have the file in xstring table, let's pass it to CL_FDT_XL_SPREADSHEET to get its object reference. From the object reference, we can retrieve each worksheet data.

The data is returned in the form of an anonymous data object which can be dereferenced into a field symbol and passed to the target internal table. Below is the code snippet to convert VBAK data from XLSX to an internal table.

FIELD-SYMBOLS:
<lfs_tab> TYPE STANDARD TABLE.
"Get excel reference.
TRY .
DATA(lo_excel_ref) = NEW cl_fdt_xl_spreadsheet( document_name = p_file
xdocument = cl_bcs_convert=>solix_to_xstring( lt_itab ) ) .
CATCH cx_fdt_excel_core.
RETURN.
ENDTRY .
"GEt work sheets.
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = DATA(worksheet_names) ).
"Get first work sheet (You can iterate over multiple if there are more than one work sheets.
DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( worksheet_names[ 1 ] ).
ASSIGN lo_data_ref->* TO <lfs_tab>.
LOOP AT <lfs_tab> ASSIGNING FIELD-SYMBOL(<lfs_data>) FROM 2."Skip Header
DO.
ASSIGN COMPONENT syst-index OF STRUCTURE <lfs_data> TO FIELD-SYMBOL(<lfs_field>).
IF syst-subrc <> 0
OR <lfs_field> IS NOT ASSIGNED.
EXIT.
ENDIF.
CHECK <lfs_field> IS NOT INITIAL.
ASSIGN COMPONENT syst-index OF STRUCTURE ls_vbak TO FIELD-SYMBOL(<lfs_field2>).
IF syst-subrc <> 0
OR <lfs_field2> IS NOT ASSIGNED.
EXIT.
ENDIF.
<lfs_field2> = <lfs_field>.
ENDDO.
APPEND ls_vbak TO lt_vbak.
clear:
ls_vbak.
ENDLOOP.
view raw xlsx_2_itab.abap hosted with ❤ by GitHub

And that is it. It's that easy to read data from XLSX to your internal table. However, you have to be careful around date and time fields and make sure to convert them to their internal format.

Below is a complete ready to execute program code with date and time field handling. It reads data from the VBAK type structure table stored in the XLSX file. Try it out in your system.

REPORT syst-repid.
START-OF-SELECTION.
PARAMETER:
p_file TYPE string DEFAULT `C:\Users\user\Desktop\VBAK.XLSX`.
CLASS lcl_main DEFINITION CREATE PRIVATE.
PUBLIC SECTION.
CLASS-METHODS:
init RETURNING VALUE(ro_self) TYPE REF TO lcl_main.
METHODS:
start IMPORTING iv_filename TYPE string.
PROTECTED SECTION.
PRIVATE SECTION.
DATA:
lt_itab TYPE solix_tab,
lt_vbak TYPE STANDARD TABLE OF vbak.
DATA:
ls_vbak TYPE vbak.
METHODS:
load_file IMPORTING iv_filename TYPE string,
convert_2_itab,
convert_date_2_internal IMPORTING iv_ext_date TYPE char10
RETURNING VALUE(rv_int_date) TYPE sydatum,
convert_time_2_internal IMPORTING VALUE(iv_ext_time) TYPE char8
RETURNING VALUE(rv_int_data) TYPE syuzeit,
display_data.
ENDCLASS.
START-OF-SELECTION.
lcl_main=>init( )->start( iv_filename = p_file ).
CLASS lcl_main IMPLEMENTATION.
METHOD init.
ro_self = NEW lcl_main( ).
ENDMETHOD.
METHOD start.
me->load_file( iv_filename = iv_filename ).
CHECK me->lt_itab IS NOT INITIAL.
me->convert_2_itab( ).
me->display_data( ).
ENDMETHOD.
METHOD load_file.
"Get Data.
cl_gui_frontend_services=>gui_upload( EXPORTING filename = p_file " Name of file
filetype = 'BIN' " File Type (ASCII, Binary)
CHANGING data_tab = lt_itab " Transfer table for file contents
EXCEPTIONS OTHERS = 1
).
IF sy-subrc <> 0.
RETURN.
ENDIF.
ENDMETHOD.
METHOD convert_2_itab.
FIELD-SYMBOLS:
<lfs_tab> TYPE STANDARD TABLE.
"Get excel reference.
TRY .
DATA(lo_excel_ref) = NEW cl_fdt_xl_spreadsheet( document_name = p_file
xdocument = cl_bcs_convert=>solix_to_xstring( lt_itab )
) .
CATCH cx_fdt_excel_core.
RETURN.
ENDTRY .
"GEt work sheets.
lo_excel_ref->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = DATA(worksheet_names) ).
"Get first work sheet (You can iterate over multiple if there are more than one work sheets.
DATA(lo_data_ref) = lo_excel_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( worksheet_names[ 1 ] ).
ASSIGN lo_data_ref->* TO <lfs_tab>.
LOOP AT <lfs_tab> ASSIGNING FIELD-SYMBOL(<lfs_data>) FROM 2."Skip Header
*
DO.
ASSIGN COMPONENT syst-index OF STRUCTURE <lfs_data> TO FIELD-SYMBOL(<lfs_field>).
IF syst-subrc <> 0
OR <lfs_field> IS NOT ASSIGNED.
EXIT.
ENDIF.
CHECK <lfs_field> IS NOT INITIAL.
ASSIGN COMPONENT syst-index OF STRUCTURE ls_vbak TO FIELD-SYMBOL(<lfs_field2>).
IF syst-subrc <> 0
OR <lfs_field2> IS NOT ASSIGNED.
EXIT.
ENDIF.
CASE CAST cl_abap_datadescr( cl_abap_typedescr=>describe_by_data( <lfs_field2> ) )->get_data_type_kind( <lfs_field2> ).
WHEN cl_abap_typedescr=>typekind_date.
<lfs_field2> = me->convert_date_2_internal( CONV #( <lfs_field> ) ).
CONTINUE.
WHEN cl_abap_typedescr=>typekind_time.
CONDENSE <lfs_field>.
<lfs_field2> = me->convert_time_2_internal( CONV #( <lfs_field> ) ).
CONTINUE.
ENDCASE.
<lfs_field2> = <lfs_field>.
ENDDO.
APPEND ls_vbak TO lt_vbak.
clear:
ls_vbak.
ENDLOOP.
ENDMETHOD.
METHOD convert_date_2_internal.
CASE iv_ext_date+2(1).
WHEN '.'."DD
rv_int_date = iv_ext_date+6(4) && iv_ext_date+3(2) && iv_ext_date(2).
WHEN '/'"MM
OR '-'.
rv_int_date = iv_ext_date+6(4) && iv_ext_date(2) && iv_ext_date+3(2).
WHEN OTHERS.
IF iv_ext_date+4(1) = '.' " 'YYYY?'
OR iv_ext_date+4(1) = '/'
OR iv_ext_date+4(1) = '-'.
rv_int_date = iv_ext_date(4) && iv_ext_date+5(2) && iv_ext_date+8(2).
ELSE.
RETURN.
ENDIF.
ENDCASE.
* Check the valid date.
CALL FUNCTION 'DATE_CHECK_PLAUSIBILITY'
EXPORTING
date = rv_int_date
EXCEPTIONS
OTHERS = 2.
IF syst-subrc <> 0.
rv_int_date = iv_ext_date.
ENDIF.
ENDMETHOD.
METHOD convert_time_2_internal.
IF iv_ext_time+6(2) >= 60.
iv_ext_time+7(1) = iv_ext_time+6(1).
iv_ext_time+6(1) = 0.
ENDIF.
TRY.
cl_abap_timefm=>conv_time_ext_to_int( EXPORTING time_ext = iv_ext_time " External Represenation of Time
is_24_allowed = 'X' " Is 24:00 permitted?
IMPORTING time_int = rv_int_data " Internal Represenation of Time
).
CATCH cx_abap_timefm_invalid. "
rv_int_data = iv_ext_time.
ENDTRY.
ENDMETHOD.
METHOD display_data.
try.
cl_salv_table=>factory( IMPORTING r_salv_table = data(lo_salv) " Basis Class Simple ALV Tables
CHANGING t_table = lt_vbak
).
lo_salv->display( ).
CATCH cx_salv_msg. "
endtry.
ENDMETHOD.
ENDCLASS.
view raw report_2_upload_xlsx_vbak.abap hosted with ❤ by GitHub

As always, Hope you like the post and use this information in your day to day work.

 

Popular posts from this blog

ABAP convert internal table to excel (.xlsx) format and Send email or download

Posted by -
Reporting is incomplete without file interfacing. Sometimes the requirement is to send a file via email or download it to a PC. but what to do when the user requests the file in XLSX format? In today's post, I am sharing how to convert an internal table to .xlsx format and then send it to a PC or email. One of the quickest ways I found it been done is using SALV classes. Below is the Code snippet Now once you have the xstring with you, simply pass it to relevant libraries to handle the output. Downloading to PC: Sending via Email It's that simple. Hope you like the post and use it in your day to day work.
Read more

Use CL_BCS library to send email with ABAP

Posted by -
Many a time you need a report for which the output needs to be an email. SO_NEW_DOCUMENT_SEND_API1 is heavily used to achieve the task but let's explore another way of achieving the same using CL_BCS. This post talks about how to use the CL_BCS library provided by SAP to easily send emails. CL_BCS Library The class serves as the interface from BCS to the applications. The methods of the class cater for the send functions. Using CL_BCS, You can: – Attach files to email – Build the HTML/raw of the body. – Set email’s Senders & Receiver – Send an email, etc. Let's see the steps one by one: Initiating CL_BCS: The first thing you need is to initiate a persistent send request. Creating BCS Document: Create the BCS document object which will be sent with the CL_BCS interface to BCS. In this case the Email. SET_DOCUMENT Once we have the document, It needs to be set to the send request. SET_SENDER Assign the sender to the send request. ADD_RECIPIENT Pass ...
Read more