Technical Articles
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.
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
For Sylvester to be good, we need two things:
So far it's gonna be great 🙂
Do you really have a sheet whose name varies according to language? (text-001)
That would be more simple to unify with this abap2xlsx snippet, at least abap2xlsx is officially released compared to SAP code:
The text element is changed. I prefer to use the SAP's standard class cl_openxml_helper in my projects.
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.
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)...
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.
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.
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!
When I read "unified approach" I can only think of this:
source: https://xkcd.com/927/
Hi, Mariantony,
I was able to upload xlsx file into SAP GUI using the document.
However, when I use 'Arial' font in the excel, plus it contains alphabet and japanese in a cell, the cell became blank.
Please advise me this processing result is normal or not?
If not, do you have solutions?
Have you tried abap2xlsx?