Skip to Content
Technical Articles
Author's profile photo Jagdish Patil

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

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

Assigned tags

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

      Uhm, have you tried abap2xlsx ?

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Yes, I checked it, but I had to do this in a day and did not have time to try it out. And from the experience - we wont be able to use this on client's system. 

      It is however very interesting and I am currently trying to understand it. 

      Author's profile photo Andrea Borgia
      Andrea Borgia

      If I may ask, what was the issue with the client system, too old perhaps?

      I've had a colleague extend an existing custom report to use abap2xlsx in about half a day but I had already done the installation long before that, to try it out.

      Author's profile photo Bärbel Winkler
      Bärbel Winkler

      It may come down to restrictions as far as "just" grabbing source code (and potentially many other workbench objects) from "somewhere on the internet" and putting that into the productive system landscape. Not all companies will allow that.

      Author's profile photo Michael Keller
      Michael Keller

      That's really true!

      Author's profile photo Matthew Billingham
      Matthew Billingham

      I managed to get it into a validated system of a regulated industry with very tight processes.

      I asked the person responsible for the development platforms, and they wrote an exception to usual naming standards. Far too useful not to use abap2xlsx - saves an absolute fortune in programming and maintenance as well...

      I must admit when it came to trying to get it done another time, I just used the technique described here. But from one of these blogs...

       

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Yes, I had used this technique to attach xlsx for an email. Tried the same thing and it worked.

      The only reason I posted it here that I thought it will help someone some day.

      Author's profile photo Michael Keller
      Michael Keller

      I've experienced once system owners who were against the use of open source solutions in ABAP. For example no abapGit. Crazy but true: the note of a lawyer. Liability for the use of open source must first be clarified. Technically it was all ok, but neither organizationally nor legally. What a day 😉

      Author's profile photo Andrea Borgia
      Andrea Borgia

      Ok, then I got lucky: customer's IT was very interested in this library! 🙂

      And also my colleague liked it, so it's already 2 installations 😀

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      @Andrea Borgia

      Not allowed to use source code which is open source.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      I guess they're in for a rude awakening with the newer SAP offerings 😀

      Thanks for the explanation and for the code, as well.

      Author's profile photo Kay Streubel
      Kay Streubel

      Nice, you can also achieve the same result by simply calling this code instead of cl_salv_bs_tt_util=>if_salv_bs_tt_util~transform

      * lo_salv TYPE REF TO cl_salv_table
      DATA(l_xml) = lo_salv->to_xml( xml_type = if_salv_bs_xml=>c_type_xslx )
      
      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      This is helpful. I will try this out. This looks so much simpler.

      Author's profile photo Andrea Borgia
      Andrea Borgia

      to_xml internally calls IF_SALV_BS_TT_UTIL~TRANSFORM so in the end it should be the same but somehow there are subtle differences: the direct call favours short (CURRENCY) / medium length (SEATSOCC) labels, the more compact, indirect call the long ones.

      well, what do you know, they're really different: on a 7.31 system the direct call to "transform" produces a file that Excel wants to repair. Using "to_xml" produces a valid file. After struggling to understand what was wrong with the original Z report, we switched to the basic SFLIGHT examples and discovered this difference in file generation.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      The Excel message is clear. "Excel cannot open the file 'Outtabxlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." means that your file has the extension xlsx but the format inside it is not Office Open XML for Excel (what xlsx means), it's a text with tab-delimited values. If you have a text file with tab-delimited values, you should use the extension txt.

      There are other blog posts or answers about the same kind of logic, but you sum up very shortly. NB: 2 little remarks about your code, zcl_itab_to_excel=>convert_to_excel doesn't exist; you may also avoid the reference parameter ir_data_ref because anyway it can still fail if the internal table is read-only -you may instead duplicate the memory (unfortunately) by defining the internal table as a pass-by-value parameter-.

      It's also a nice solution for people who can't install abap2xlsx because of company policy.

      So, thanks for that!

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Hi Sandra

      Thanks for pointing out the method name. I missed to change it where it is called. Updated now.

      On the parameter, it did not feel right to create a copy as the I was working with huge number of records, but can you explain a bit on the potential failure? In that case I would move the code where I already have the internal table instead of calling the method.

      Author's profile photo Mike Pokraka
      Mike Pokraka

      I would measure memory aspect. ABAP has optimised stuff in this area and I believe it uses a Copy-On-Write approach, meaning internally only one dataset exists until you change it. SALV doesn't change data so you should be OK there.

      What Sandra referred to was that SALV will fail on a readonly table. So if a method calls your method with its own importing parameter (these are always readonly), the ref will point to a readonly variable.

      METHOD foo.  "IMPORTING i_table
      ...
        excel = itab_to_excel->itab_to_xstring( REF #( i_table ) ).

      But anyhow my other reason for writing here was that I too started thinking "not another Excel export blog", but yours is actually a neat solution for simple 1:1 downloads. Sure abap2xlsx can do a lot more and is just as simple, but SALV is already available in every system and I learnt something new that it can do, so thanks 🙂

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Mike Pokraka I forgot that "Copy-On-Write approach", thanks for correcting me 😉

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Thanks Mike.

      This helps a lot.

      Author's profile photo Sandra Rossi
      Sandra Rossi

      2 other syntax errors found by using your code (missing parenthesis + it's an instance method):

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

      Concerning my comment about "can still fail", here is the case to make the internal table read-only, it will fail when it tries to execute cl_salv_table=>factory (can be seen in debug, xstring is returned empty):

      CLASS lcl_app DEFINITION.
        PUBLIC SECTION.
          CLASS-METHODS run IMPORTING it_out_rec TYPE any.
      ENDCLASS.
      CLASS lcl_app IMPLEMENTATION.
        METHOD run.
          DATA lo_data_ref TYPE REF TO data.
          GET REFERENCE OF it_out_rec INTO lo_data_ref.
          DATA(lv_xstring) = NEW zcl_itab_to_excel( )->itab_to_xstring( lo_data_ref ).
        ENDMETHOD.
      ENDCLASS.
      
      START-OF-SELECTION.
        SELECT * FROM sflight INTO TABLE @DATA(flights).
        lcl_app=>run( flights ).

       

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Thanks Sandra. So much clear now.

      And thanks for correcting the syntax errors.

      Author's profile photo Shai Sinai
      Shai Sinai

      Another month, another ABAP to XLSX solution..

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Not sure I get what you meant.

      Author's profile photo Gregor Wolf
      Gregor Wolf

      Try: https://community.sap.com/search/?by=updated&ct=blog&q=excel%20abap

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      As I mentioned in the post itself, I did search for this. All I could find was excel download to front end and that works fine. I did not find a single one for writing one to application server.

      If you do have it - please let me know. I will be happy to put that in the post so that we will have multiple solutions linked together when next time someone searches for this.

      Author's profile photo Matthew Billingham
      Matthew Billingham

      abap2xlsx definitely works for backend and has done since at least 2016 - it's fairly obvious that it does really, as its main output is an xstring which you can write anywhere. You could even send it out in a restful web service.

      Author's profile photo Shai Sinai
      Shai Sinai

      Well,

      Nothing personally about your own blog post,

      just mentioned the fact that every month a new post regarding export to an XLSX file is being published (and without SAP releases an official solution nor adopting the great ABAP2XLSX project).

      Author's profile photo Hamisha Malik
      Hamisha Malik

      Hi Jagdish,

      I tried the code, I was able to change Xstring data further to binary using SCMS_XSTRING_TO_BINARY and then download to the application layer. But when I am using open data set to write to AL11 in binary mode, excel is getting generated in AL11 and opening .xlsx file it shows :-

      PK#########c#RE6#c############docProps/core.xmlm##n#0##_####P####D#=###V###Kp###^#y#:#M##ͳ##H#)WW۰#`4#-y##9###6#^#Ϗ###g##T#P#a#>####Ҟ##
      K~$
      ##Q##ʘ%#%###JJ#k##:###b>###HjIR###0&rv##j##gl####4`#Q#y###L#6>###)j#u#####?~#f$۶#ڧ#M7##{###Ν##I:##*#*#P#L##x#####R##<V###}#~#+#d#####֣####Q#
      ##1##D##xP##,###M{#ğ#{۝#/3##ǁM##A[#<#8###1#+#H֜UJ#<:#d ##`C#{"##"###:#Q#Qiя5##w###hء##`I#Y##0#####}##-##D#R##π#z###Tr##`tM##:##1`Kl?k#######

      When I download it as 'Download file as text' and saving in .xlsx format it saved again in gibberish way. I am stuck. Please help.

      -Hamisha

      Author's profile photo Jagdish Patil
      Jagdish Patil
      Blog Post Author

      Hi Hamisha

      Apologies for responding so late, I believe by now you must have figured this out.

      On AL11 the file will appear like junk characters as you have shown, but if you FTP the file or download to local using tools like winscp, you can open the file properly. You can take help from a Basis Consultant as well to get the file in case you do not have any such tool.

      As per my experience, CG3Y also does not work in this case.

      Author's profile photo Sai Kalyan Billala
      Sai Kalyan Billala

      Hi Jagdish,

      My requirement is to send payment data in .xls format to application server

      from there the user downloads and upload in bank server

      i tried with horizontal tab and we are getting file - but when opening the file there is a popup coming as warning  " excel cannot open the file, because file may be corrupted"

      I tried the steps mentioned by you, and in my file all fields are the coming in first column

      Could you please help here

      steps i followed

      • Prepared  one internal table with actual data
      • Prepared final table with headings and actual data
      • used the class -> method suggested by you
      • and final result is file in single column

      we are sending the file to application server and user wants to open the .xls file without any popup warning

      Please help!!

      Regards,

       

      Author's profile photo katchak P
      katchak P

      Hi.

      I was able to create .xlsx file without problem. However, user wants to delete the first row with no data. How to remove the blank row? Please advise.

      Thanks.

      Author's profile photo Marco Sposa
      Marco Sposa

      hello Thanx for sharing and it's work perfectly but how do you manage an internal table   with millions of records ( for the fiscal administation) and i have the dump on the

      if_salv_bs_tt_util~transform

      with a SYSTEM_NO_ROLL.

      It is possible do the transformation by package?

      Please advice.

      Regards