.xlsx file upload to oData create stream - error in xstring conversion

[former_member518365]

Scenario: Using sap.ui.unified.FileUploader in my UI5 application, the user can select an .xlsx file with multiple tabs to upload to the back end oData service. In the back end oData service I have implemented CREATE_STREAM method to receive the data. Here I want to create the Excel file using class "cl_fdt_xl_spreadsheet", read the sheets and data etc. and do some processing.

Problem: When hitting the back end CREATE_STREAM method, I assume the data in is_media_resource-value is in xstring format BUT when trying to create the object with cl_fdt_xl_spreadsheet, I get an error "Invalid document format".

View Code:

<code><u:FileUploader id="xlsUploader" 
                                name="iwbChecklistUpload" 
                                fileType="xlsx"
                                maximumFilenameLength="250"
                                maximumFileSize="2000"
                                uploadOnChange="false"
                                uploadUrl="{uploadModel>/xlsUrl}"
                                filenameLengthExceed=".filenameTooLong"
                                fileSizeExceed=".fileSizeExceeded"
                                typeMissmatch=".fileTypeMismatch"
                                uploadComplete=".xlsComplete"
                                uploadProgress=".xlsProgress"
                                sendXHR="true"/>

Controller:

<code>onUpload: function(oEvent){
        var oModel = this.getOwnerComponent().getModel();
        var uploadModel = this.getView().getModel("uploadModel");
        var xlsUploader = this.getView().byId("xlsUploader");
        var xlsDomRef = xlsUploader.getFocusDomRef();

        var xlsFile = xlsDomRef.files[0]; //Only one

        if(xlsFile){
            var oHeaderToken = new sap.ui.unified.FileUploaderParameter({
                name: "x-csrf-token",
                value: oEvent.getSource().getModel().getSecurityToken()
            });
            var oHeaderSlug = new sap.ui.unified.FileUploaderParameter({
                name: "slug",
                value: xlsFile.size + "|" + xlsUploader.getProperty("value") 
            });
        
            xlsUploader.removeAllHeaderParameters();
            xlsUploader.addHeaderParameter(oHeaderToken);
            xlsUploader.addHeaderParameter(oHeaderSlug); 
            xlsUploader.upload()
        }

Back end oData CREATE_STREAM:

<code>DATA: lo_converter TYPE REF TO cl_abap_conv_in_ce,
          lv_xstring   TYPE xstring,
          lv_string type string.

    DATA : lo_excel_ref TYPE REF TO cl_fdt_xl_spreadsheet .

    lv_xstring = is_media_resource-value.


    TRY .
        lo_excel_ref = NEW cl_fdt_xl_spreadsheet(
                                document_name = 'iwbcl.xlsx'
                                xdocument     = lv_xstring ) .
      CATCH cx_fdt_excel_core into data(oRef).
        "Error caught here - Invalid document format
    ENDTRY .

Any help will be much appreciated. I do upload images in a similiar fashion and there are no problems there.
My thoughts are that somehow the stream in is_media_resource-value is not xstring (using sendXHR="true" in my fileuploader may cause this). Should I specify a mime type somewhere ?

[former_member518365]

BTW on this topic a great post and help was this one:

https://www.samplecodeabap.com/how-to-upload-excel-to-sapusing-abap/

[abo]

One quick comment: apparently that class has a very specific use case and is not recommended for generic Excel handling.

Have you tried with abap2xlsx?

[former_member518365]

Hi Andrea

Yes I tried that since I have been using it for other projects as well but I am still not getting my stream to convert correctly. For this specific application I will not be able to ship the third party libs with though.

Thanks !

[abo]

Ok, then the conversion issue is independent of the XLSX generation, shutting up 🙂

[former_member518365 · Accepted solution]

Thank you to Marc on Stackoverflow that provided the correct answer. Here his answer verbatim:

We have a very similar use case. The only difference I see is that we have useMultipart="false". With multipart activated (the default) the xstring probably looks different because it contains more than just the XSLX file. When looking at the xstring a proper XLSX file will begin with 50 4B 03 04.

See the docs for more information about the useMultipart property.

Then my 10 sents worth:

This ended up being the issue. With default (true) the request is still sent as Multipart Form/Data which makes the format incorrect. Setting to true sending the file only with correct format (wo the form details). Works in tandem with sendXHR (which is what I missed)

[former_member750776 · Accepted solution]

Hi Carstens,

Can you let me know, how can I read the data once I saved the file in lo_excel_ref.

thanks,

Anudeep

[former_member518365 · Accepted solution]

Anudeep

The class CL_FDT_XL_SPREADSHEET has all the methods you need. Look at GET_WORKSHEET_NAMES and GET_ITAB_FROM_SHEET, especially handy.

Johann