ABAP Code to Internal Table as Excel File on SAP Application Server

[jpsapabap21]

I recently got a requirement to write a file on application server in excel format. This post explains how I reached to a solution and how to code the solution.

Process

At first, I thought its very simple. The first thing I did - created a tab separated file with ‘.xlsx’ extension.

I could write the file. There is no issue there. The problem is, Excel does not open this file and simply says, it is not Excel file.

Then, I started experimenting or simply put Trial and Error method.

• Changed extension to ‘.xls’


• Toggled between writing modes TEXT and BINARY in OPEN DATASET


• Used SMART LINEFEED in OPEN DATASET


• Toggled between downloading options - ASC and BIN formats (Using CG3Y) 


• Tried accessing file at Unix Level and downloaded the file using WINSCP


• Used Tab, Comma, Semi-Colon to separate file

None of these worked. I also searched over SAP blogs for some help. I did find below which says you can do this with using tabs.

Writing Excel file into Application Server using horizontal tab | SAP Blogs

But this method did not work for me. It continued to give the same error.

I did find few more discussions like below, but none with a satisfactory outcome and also some of them were very old.

Create XLS file on Application Server (no tab-delimited format) | SAP Community

Create excel file on application server | SAP Community

At this point, I was convinced that I should now try to convince the user who requested this to use .csv file. As .csv can be opened in Excel. I did try that but that did not work either.

Then I took a step back, started looking at it from different point of view and it struct me. Creating excel attachment works. Why not application server file?

Yes, I was providing the extension as ‘.xlsx’, but what I was writing was not Excel. The missing piece was that the contents were not transformed to Excel content. So, there it was – the solution.

So, I created below method for the transformation.

Code

CLASS zcl_itab_to_excel DEFINITION PUBLIC FINAL.

  PUBLIC SECTION.

    METHODS:

      itab_to_xstring 

        IMPORTING ir_data_ref       TYPE REF TO data

        RETURNING VALUE(rv_xstring) TYPE xstring.

ENDCLASS.



CLASS zcl_itab_to_excel IMPLEMENTATION.

  METHOD itab_to_xstring.



    FIELD-SYMBOLS: <fs_data> TYPE ANY TABLE.



    CLEAR rv_xstring.

    ASSIGN ir_data_ref->* TO <fs_data>.



    TRY.

        cl_salv_table=>factory(

          IMPORTING r_salv_table = DATA(lo_table)

          CHANGING  t_table      = <fs_data> ).

  

        DATA(lt_fcat) = 

          cl_salv_controller_metadata=>get_lvc_fieldcatalog(

            r_columns      = lo_table->get_columns( )

            r_aggregations = lo_table->get_aggregations( ) ).



        DATA(lo_result) =

          cl_salv_ex_util=>factory_result_data_table( 

            r_data         = ir_data_ref

            t_fieldcatalog = lt_fcat ).



        cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform(

          EXPORTING

            xml_type      = if_salv_bs_xml=>c_type_xlsx

            xml_version   = cl_salv_bs_a_xml_base=>get_version( )

            r_result_data = lo_result

            xml_flavour   = if_salv_bs_c_tt=>c_tt_xml_flavour_export

            gui_type      = if_salv_bs_xml=>c_gui_type_gui

          IMPORTING

            xml           = rv_xstring ).

      CATCH cx_root.

        CLEAR rv_xstring.

    ENDTRY.

  ENDMETHOD.

ENDCLASS.

And it worked very well. You simply need to pass your internal table reference to the method, get the excel content in xstring and transfer it to application server.

GET REFERENCE OF it_out_rec INTO DATA(lo_data_ref).

DATA(lv_xstring) = NEW zcl_itab_to_excel( )->itab_to_xstring( lo_data_ref ). 

OPEN DATASET lv_xls_file FOR OUTPUT IN BINARY MODE.

IF sy-subrc EQ 0.

  TRANSFER lv_xstring TO lv_xls_file.

  CLOSE DATASET.

ENDIF.

Conclusion

• It is easy to create excel file on application server with help of the SALV and related classes


• This method also works well if xlsx attachment is required to be sent in an email


• This method also helps formatting the excel with aggregations and layout settings. Explore class cl_salv_controller_metadata for more details.

Your comments and suggestions are welcome to further improve this code. Please use the comment section below or you can also post your questions here.

 

– Jagdish Patil