Do you also have the requirement to provide data from your ABAP-System to an Excel workbook? Then, I hope you heard about the fantastic abap2xlsx-library by Ivan Femia. If not, you should immediately head over to their project website. For our project, it saved a tremendous amount of effort and time. Let me briefly explain why and how it’s even more easy to provide beautiful Excel download from your ABAP system.


Starting point

During the specialization of our application some years back, users and consultants together defined complex Excel files they wanted the system to be generated. As we’re using a FPM-based Webdynpro user interface, we could not rely on using the GUI frontend services, but hat to use the OLE-based APIs. They are not only slow, but also quite clumsy to handle and need a lot of code. In  our sample, we had used more than 5.000 lines of code in order to produce a file which was at least almost matching the requested format.


ABAP2XLSX

Searching for alternative manipulation of Excel from ABAP, I came across abap2xlsx which has a beautiful API for creation and modification of Excel files. Particularly binding tabular data into a table-entity of the workbook is incredibly efficient. After having clarified the licensing questions, we communicated how much cheaper development would be if only we could rely on our customer using Excel 2007 (or at least Excel 2003 and the compatibility pack). Talking about savings, convincing them was not too difficult.


Templating

With all the facilities given by ABAP2XLSX, it still was quite some effort to define those rich files which may comprise  more than 100 fields and some tables: For each field, you as a developer have to define a label, format the cells, rows and columns and bind the data. Also for minor graphical changes (e. g. formating) or to the texts, a developer is needed. How beautiful would it be if the business expert could just provide a new version of a template? It would not even be required that the template layout remains if named cells would be used for filling the  template. We therefore decided to split up the provisioning of a the initial Excel as a view and the controller logic to fill it (which is in general a good idea).

/wp-content/uploads/2014/11/template_591770.png

Some sample template designed by a “business user”. Please appreciate the beautiful formatting…


The MIME Repository as template store

Having got a prepared template by a business user, one important question remained: How to store, access and manage the lifecycle of a template? Of course, you could simply put the file into the filesystem of your application server, but there’s a much better option: The MIME Repository is a tool integrated into the ABAP workbench for managing storage of binary data. You can simply create an own folder for your application and upload your template-files to it. This give you

  • A transportable object which integrates into the deployment (transportation) of your ABAP-application
  • Authorization mechanism in order to limit who’s allowed to access and update which template
  • A nice separation of the presentation and the logic (though of course you might have to bridge some shortages with respect to i18n, depending on your customer)

MIME_rep_se80.png

The MIME-repository UI in SE80 – and the uploaded template


There’s an ABAP-API in order to load the binary content from which you create the ZEXCEL-object. You could for example use a factory:

METHOD create_from_mime_repository. 

  DATA lv_mime_file TYPE xstring. 
  DATA lo_excel_reader TYPE REF TO zif_excel_reader.  
  DATA lx_excel TYPE REF TO zcx_excel.

    cl_mime_repository_api=>get_api( )->get( 
          EXPORTING        i_url                  =  iv_mime_path           
          IMPORTING        e_content              = lv_mime_file           
          EXCEPTIONS OTHERS = 8 ).

    IF sy-subrc <> 0.   
     RAISE EXCEPTION TYPE zcx_excel
          EXPORTING error = |File could not be found in MIME repositoy at { iv_mime_path }|. 
    ENDIF. 

    CREATE OBJECT lo_excel_reader TYPE zcl_excel_reader_2007.  

     TRY.        "Instantiate the Excel object on the basis of the binary date from the MIME-Repository
         ro_excel = lo_excel_reader->load( i_excel2007 = lv_mime_file ).   
    CATCH zcx_excel INTO lx_excel.    "excel loading error      
      RAISE EXCEPTION TYPE zcx_excel          
               EXPORTING error = |File at { iv_mime_path } could not be interpreted as Excel file|.
    ENDTRY.

ENDMETHOD.

Having done this very small coding, you’ll see your efficiency tremendously improved: You basically need one line of code per cell into which you’d like to populate data. Not for tables though: you need at least four lines of code – for the complete table.

"Load file from MIME-path

go_excel = zcl_excel_factory=>get_instance( )->create_from_mime_repository( '/SAP/PUBLIC/excel_templates/Template_Sample.xlsx' ).

"Fill some elementary data into a predefined format

go_excel->get_active_worksheet( )->set_cell(    
     ip_column    = 2       
     ip_row       = 1       
     ip_value     = 'Fruits'      ).

"Add tabular data

go_excel->get_active_worksheet( )->bind_table(     
     ip_table = gt_item
     is_table_settings = VALUE #( top_left_column = 'A' top_left_row = 4 )  
).

/wp-content/uploads/2014/11/filled_591777.png


Can you do this any easier?


Feedback appreciated!

Oliver


To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Gregory Misiorek

    Hi Ivan,

    any plans to complete the round trip, ie have excel worksheet input values write to SAP database table? i understand lsmw is not a tool of choice these days.

    thx,

    greg

    (0) 
  2. Ezequiel Renaud

    Hi Oliver,

    Can you tell me where to find the class zcl_excel_factory, Its not created from the package. Or its the class you creted to implement the method create_from_mime_repository. If that’s the case, can you tell me the parameters you used and the methods?

    Thx,

    Ezequiel.

    (0) 
    1. Oliver Jaegle Post author

      Hi Ezequiel,

      the zcl_excel_factory was just a sample implementation, even in our system we have another class do that.

      However, the code of the factory method is written above (METHOD create_from_mime_repository.), the signature is simple:

      2015-12-10 09_47_00-A05(1)_010 Class Builder_ Klasse ZCL_PRI_BO_EXCEL_FORM anzeigen.png

      Cheers, Oliver

      (0) 

Leave a Reply