Excel Upload Using RAP Application

[sonchitchadha]

Hello Experts,

We are currently facing an issue with the file upload requirement using RAP model where the business needs to upload .XLS/.XLSX file from a custom RAP application. At the moment, we have successfully built the custom RAP app, which allows the upload of .CSV files, and we are able to process and alter the data as required. However, we are struggling to find a solution for uploading .XLS/.XLSX files.

Although we receive the data in RAWSTRING format using the Read Entity statement, we are unable to convert the RAWSTRING to our internal table. We are using the cl_abap_conv_codepage=>create_in()->convert method, but the system throws a dump at line: 'SYSTEM-CALL CONVERT ID 44' when attempting to call the convert method for .XLS/.XLSX files.

I have gone through multiple blogs shared on SAP Communities, but most solutions focus on uploading .CSV files. Could anyone provide guidance on this issue? We have several custom app development requirements, and this functionality will serve as a base for all of our file upload processes. Any priority support on this matter would be greatly appreciated!

For reference, I am pasting my uploadExcel method code below:

METHOD uploadexceldata.

READ ENTITIES OF zc_head_upd IN LOCAL MODE

ENTITY zc_head_upd

ALL FIELDS WITH

CORRESPONDING #( keys )

RESULT DATA(lt_inv).

** Get attachment value from the instance

DATA(lv_attachment) = lt_inv[ 1 ]-attachment.

** Data declarations

TYPES: BEGIN OF ty_excel_data,

mandt TYPE mandt,

svono TYPE aufnr,

startday TYPE char10,

zgroup TYPE char10,

area TYPE char10,

revtx TYPE revtx,

duration TYPE char2,

shift TYPE char2,

pitchid TYPE char50,

startpitch TYPE char4,

seq TYPE char2,

id TYPE char50,

lastchangedat TYPE abp_lastchange_tstmpl,

END OF ty_excel_data.

DATA: rows TYPE STANDARD TABLE OF string,

content TYPE string,

conv TYPE REF TO cl_abap_conv_codepage,

ls_excel_data TYPE ty_excel_data,

lt_excel_data TYPE STANDARD TABLE OF ty_excel_data, "zps_dataupd_itm,

lt_excel_data_final TYPE TABLE OF zps_dataupd_itm,

lv_date TYPE char40,

lv_svono TYPE aufnr,

lv_timestamp TYPE timestampl.

DATA: lt_table TYPE TABLE OF standard.

GET TIME STAMP FIELD lv_timestamp.

lv_date = lv_timestamp.

content = cl_abap_conv_codepage=>create_in( )->convert( lv_attachment ).

** Split the string table to rows

SPLIT content AT cl_abap_char_utilities=>cr_lf INTO TABLE rows.

** Process the rows and append to the internal table

LOOP AT rows INTO DATA(ls_row) FROM 2.

SPLIT ls_row AT ',' INTO ls_excel_data-revtx

ls_excel_data-zgroup

ls_excel_data-svono

ls_excel_data-area

ls_excel_data-startday

ls_excel_data-duration

ls_excel_data-shift

ls_excel_data-pitchid

ls_excel_data-startpitch

ls_excel_data-seq

ls_excel_data-id.

ls_excel_data-lastchangedat = lv_date.

APPEND ls_excel_data TO lt_excel_data.

* LOOP AT lt_excel_data ASSIGNING FIELD-SYMBOL(<fs>).

* <fs>-last_changed_at = lv_date.

* ENDLOOP.

CLEAR: ls_row, ls_excel_data.

LOOP AT lt_excel_data ASSIGNING FIELD-SYMBOL(<fs_excel_data>).

APPEND INITIAL LINE TO lt_excel_data_final ASSIGNING FIELD-SYMBOL(<fs_excel_final>).

MOVE-CORRESPONDING <fs_excel_data> TO <fs_excel_final>.

ENDLOOP.

MODIFY zps_dataupd_itm FROM TABLE lt_excel_data_final.

CLEAR: lt_excel_data, lt_excel_data_final.

ENDLOOP.

ENDMETHOD.

Best Regards

[Marian_Zeis]

Not using the code block but posting the code in in red and Comic Sans is just double evil 😄

You can use https://spreadsheet-importer.com/ 

[Sandra_Rossi]

Please use the buttons "..." and "</>" to display your code nicely, to make it legible. See how to do it here: https://community.sap.com/t5/questions-about-sap-websites/how-to-post-code-in-sap-community-gt-2024/...

[Sandra_Rossi]

Why do you need to convert XLSX to an internal table, and why do you need to convert bytes into characters? XLSX is binary format. If you want to read the XLSX file, use XCO or abap2xlsx.

[sonchitchadha]

Hi Sandra,

Thanks for replying please find below my modified test code using ABAP2XLSX github repository but since still in background it is calling GUI_UPLOAD system is not allowing excel data to be even uploaded to system since it is a cloud system. Could you please share any particular method that can used within ABAP2XLSX repository to capture file data on cloud system.

    TYPES: BEGIN OF lty_excel_s,
             dummy    TYPE string,
             field    TYPE string,
             fieldtyp TYPE string,
             value    TYPE string,
             desc     TYPE string,
             dummy1   TYPE string,
             field1   TYPE string,
             fildtyp1 TYPE string,
             value1   TYPE string,
             desc1    TYPE string,
             dummy2   TYPE string,
             field2   TYPE string,
             fildtyp2 TYPE string,
             value2   TYPE string,
             desc3    TYPE string,
             dummy3   TYPE string,
             field3   TYPE string,
             fildtyp3 TYPE string,
             value3   TYPE string,
             desc4    TYPE string,
             dummy4   TYPE string,
             field4   TYPE string,
             fildtyp4 TYPE string,
             value4   TYPE string,
             desc6    TYPE string,
             dummy5   TYPE string,
             field5   TYPE string,
             fildtyp5 TYPE string,
             value5   TYPE string,
             dummy6   TYPE string,
             field6   TYPE string,
             fildtyp6 TYPE string,
             value6   TYPE string,
             desc9    TYPE string,
             dummy7   TYPE string,
             field7   TYPE string,
             fildtyp7 TYPE string,
             value7   TYPE string,
             desc17   TYPE string,
             dummy8   TYPE string,
             field8   TYPE string,
             fildtyp8 TYPE string,
             value8   TYPE string,
             desc38   TYPE string,
             dummy9   TYPE string,
             field9   TYPE string,
             fildtyp9 TYPE string,
             value9   TYPE string,
             desc39   TYPE string,
           END OF lty_excel_s.

    DATA: p_upfile TYPE string VALUE 'C:\Users\HP\Downloads\Attachment.xls'.
    FIELD-SYMBOLS: <lt_data> TYPE STANDARD TABLE.
    DATA: lo_reader    TYPE REF TO zif_excel_reader,
          lo_excel     TYPE REF TO zcl_excel,
          lo_worksheet TYPE REF TO zcl_excel_worksheet,
          lo_error     TYPE REF TO cx_root,
          lo_alv       TYPE REF TO cl_salv_table,
          lv_max_col   TYPE zexcel_cell_column,
          lv_max_row   TYPE int4.

    DATA: lt_data1 TYPE TABLE OF lty_excel_s.

    TRY.
        CREATE OBJECT lo_reader TYPE zcl_excel_reader_2007.
        lo_excel = lo_reader->load_file(  p_upfile ).

        lo_worksheet = lo_excel->get_active_worksheet(  ).
        lv_max_col = lo_worksheet->get_highest_column(  ).
        lv_max_row = lo_worksheet->get_highest_row(  ).

        lo_worksheet->get_table(
          EXPORTING
            iv_skip_bottom_empty_rows = abap_true
            iv_max_col                = lv_max_col
            iv_max_row                = lv_max_row
          IMPORTING
            et_table                  = lt_data1 ).
    ENDTRY.

 Best Regards.

[robinthakral]

@sonchitchadha pls let me help you to present you code in better way and then we can work on solution, as I have been handling the requirement to upload excel file with .XLSX library on Web IDE for a while with RAP Application - you can refer

 Excel Upload using RAP: Part - 2
Excel Upload in a Web IDE Project for a RAP-Based Odata V2 Service

although I believe, there are challenges with the XLSX library

METHOD uploadexceldata.

  READ ENTITIES OF zc_head_upd IN LOCAL MODE
    ENTITY zc_head_upd
    ALL FIELDS WITH
    CORRESPONDING #( keys )
    RESULT DATA(lt_inv).

  " Get attachment value from the instance
  DATA(lv_attachment) = lt_inv[ 1 ]-attachment.

  " Data declarations
  TYPES: BEGIN OF ty_excel_data,
           mandt TYPE mandt,
           svono TYPE aufnr,
           startday TYPE char10,
           zgroup TYPE char10,
           area TYPE char10,
           revtx TYPE revtx,
           duration TYPE char2,
           shift TYPE char2,
           pitchid TYPE char50,
           startpitch TYPE char4,
           seq TYPE char2,
           id TYPE char50,
           lastchangedat TYPE abp_lastchange_tstmpl,
         END OF ty_excel_data.

  DATA: rows TYPE STANDARD TABLE OF string,
        content TYPE string,
        conv TYPE REF TO cl_abap_conv_codepage,
        ls_excel_data TYPE ty_excel_data,
        lt_excel_data TYPE STANDARD TABLE OF ty_excel_data,
        lt_excel_data_final TYPE TABLE OF zps_dataupd_itm,
        lv_date TYPE char40,
        lv_svono TYPE aufnr,
        lv_timestamp TYPE timestampl.

  GET TIME STAMP FIELD lv_timestamp.
  lv_date = lv_timestamp.

  content = cl_abap_conv_codepage=>create_in( )->convert( lv_attachment ).

  " Split the string table to rows
  SPLIT content AT cl_abap_char_utilities=>cr_lf INTO TABLE rows.

  " Process the rows and append to the internal table
  LOOP AT rows INTO DATA(ls_row) FROM 2.
    SPLIT ls_row AT ',' INTO ls_excel_data-revtx
                                ls_excel_data-zgroup
                                ls_excel_data-svono
                                ls_excel_data-area
                                ls_excel_data-startday
                                ls_excel_data-duration
                                ls_excel_data-shift
                                ls_excel_data-pitchid
                                ls_excel_data-startpitch
                                ls_excel_data-seq
                                ls_excel_data-id.

    ls_excel_data-lastchangedat = lv_date.
    APPEND ls_excel_data TO lt_excel_data.
    CLEAR: ls_row, ls_excel_data.
  ENDLOOP.

  LOOP AT lt_excel_data ASSIGNING FIELD-SYMBOL(<fs_excel_data>).
    APPEND INITIAL LINE TO lt_excel_data_final ASSIGNING FIELD-SYMBOL(<fs_excel_final>).
    MOVE-CORRESPONDING <fs_excel_data> TO <fs_excel_final>.
  ENDLOOP.

  MODIFY zps_dataupd_itm FROM TABLE lt_excel_data_final.
  CLEAR: lt_excel_data, lt_excel_data_final.

ENDMETHOD.