Skip to Content
Technical Articles
Author's profile photo Benu Mariantony

Excel XLSX Upload – Unified approach for different ABAP Versions

It is all about XLSX files and a unified approach to upload XLSX files into SAP GUI and Webdynpro ABAP / Floor Plan Manager using XSL Transformation.

In this document, I explained the steps to import the XLSX file.

View Document

If you are on the higher version, you can make use of the APIs

METHOD import.
    CHECK ip_xlsx_filename IS NOT INITIAL.
    TRY.
        DATA(lv_xlsx_data) = cl_openxml_helper=>load_local_file( ip_xlsx_filename ).
        DATA(lo_xlsx_data) = load_xlsx_data( ip_xlsx_data = lv_xlsx_data ip_xlsx_filename = ip_xlsx_filename ).
        IF lo_xlsx_data IS BOUND.
          rt_jobdoc_data = me->do_mapping_import( lo_xlsx_data ).
        ENDIF.
      CATCH cx_openxml_not_found.
    ENDTRY.
  ENDMETHOD.
  METHOD load_xlsx_data.
    TRY.
        DATA(lo_xlsx_ref) = NEW cl_fdt_xl_spreadsheet(
            document_name     = ip_xlsx_filename
            xdocument         = ip_xlsx_data ).
        IF lo_xlsx_ref IS BOUND.
          lo_xlsx_ref->if_fdt_doc_spreadsheet~get_worksheet_names(
            IMPORTING
              worksheet_names = DATA(lt_worksheets) ).
          ro_xlsx_data = lo_xlsx_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet('Sheet1').
        ENDIF.
      CATCH cx_openxml_not_found cx_fdt_excel_core.
    ENDTRY.
  ENDMETHOD.
  METHOD do_mapping_import.
    DATA: ls_jobdoc_data  TYPE if_jsm_jobdoc_mass_maintenance=>ts_jobdoc_master,
          lt_jobmon_data  TYPE if_jsm_jobdoc_mass_maintenance=>tt_jobdoc_master,
          lv_language_iso TYPE char2,
          lv_language_sap TYPE lang1.
    FIELD-SYMBOLS: <lt_worksheet_data> TYPE STANDARD TABLE,
                   <lv_data>           TYPE string.
    ASSIGN it_data->* TO <lt_worksheet_data>.
    LOOP AT <lt_worksheet_data> ASSIGNING FIELD-SYMBOL(<ls_worksheet_data>) FROM 2.
      APPEND INITIAL LINE TO lt_jobmon_data ASSIGNING FIELD-SYMBOL(<ls_jobdoc_data>).
      DO 52 TIMES.
        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_worksheet_data> TO <lv_data>.
        ASSIGN COMPONENT sy-index OF STRUCTURE <ls_jobdoc_data> TO FIELD-SYMBOL(<lv_value>).
        <lv_value> = <lv_data>.
      ENDDO.
    ENDLOOP.
...
...
    ENDMETHOD

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Andrea Borgia
      Andrea Borgia

      For Sylvester to be good, we need two things:

      1. "Dinner for one"
      2. a new blogpost on XLSX handling which does not mention abap2xlsx

      So far it's gonna be great 🙂

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Do you really have a sheet whose name varies according to language? (text-001)

      ro_xlsx_data = 
        lo_xlsx_ref->if_fdt_doc_spreadsheet~get_itab_from_worksheet( CONV #( text-001 ) ).

      That would be more simple to unify with this abap2xlsx snippet, at least abap2xlsx is officially released compared to SAP code:

      DATA(reader) = NEW zcl_excel_reader_2007( ).
      DATA(workbook) = reader->zif_excel_reader~load_file( ip_xlsx_filename ).
      DATA(sheet) = workbook->get_worksheet_by_name( 'Sheet3' ).
      sheet->get_table( EXPORTING iv_skipped_rows = 1
                        IMPORTING ip_table = lt_jobmon_data ).
      Author's profile photo Benu Mariantony
      Benu Mariantony
      Blog Post Author

      The text element is changed. I prefer to use the SAP's standard class cl_openxml_helper in my projects. 

      Author's profile photo Paul Hardy
      Paul Hardy

      Fun fact - the FDT in the class names above is the internal SAP name for "BRF+". In that framework you upload and download spreadsheets all the time for assorted rule tables.

      It's amazing. Ten years. Ten years since ABAP2XLSX came out. Ten years during which period every month - every single month - someone new posts a blog on the SCN with a new way to upload and download XLSX files to and from an SAP system because they have never heard of ABAP2XLSX. Afterwards of course they say they knew about ABAP2XLSX all along and don't use it because (INSERT BOGUS EXCUSE) but I wonder because, as has been mentioned, none of these blogs mention ABAP2XLSX in the first published version.

      There will be another such blog next month. And then the month after that and so on, probably for at least the next ten years.

       

      Author's profile photo Shai Sinai
      Shai Sinai

      More amazing is the fact that after all these years SAP hasn't bothered to release an official API to read/write XLSX files (or adopt ABAP2XLSX)...

      Author's profile photo Paul Hardy
      Paul Hardy

      The funny thing is that since ABAP2XLSX was originally published on the "code exchange" SAP had the legal right to just take the code and make it an official SAP product and even charge for it if they so desired. That was all in the small print and SAP did in fact take some code exchange things and turn them into standard SAP classes.

      It is probably good they did not with ABAP2XLSX because once something becomes an official SAP thingy it tends to fossilize whereas open source projects constantly evolve. as an example I recently submitted a minor improvement to ABAP2XLSX leading the community to make a total redesign of a non-optimal section of the code. I could not have done that to an official SAP product.

      On the B side if ABAP2XLSX was an official SAP product maybe someone would have heard about it after all these years stopping the constant stream of blogs re-inventing it.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      I feel the same way as Paul Hardy and others: monthly XLSX blogs have become a running joke in the Community. And there was even a blog post about the same cl_fdt_xl_spreadsheet class just in April 2021.

      I posted about this on Twitter and since not everyone is following me there (strangely 🙂 ), thought I'd share a link for additional discussion there: https://twitter.com/JelenaAtLarge/status/1478425778513719307?s=20

      I also encourage all the readers to take a look at the second blog post from link above and the comments there to make better informed decisions.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      I'm not on Twitter so answering here: I had never heard of the artifact of the month! And I do try to actively follow all new shiny stuff on the forum and around!

      Author's profile photo Custodio de Oliveira
      Custodio de Oliveira

      When I read "unified approach" I can only think of this:

      source%3A%20https%3A//xkcd.com/927/

      source: https://xkcd.com/927/