Easy Excel-creation from ABAP using transportable templates

[former_member182889]

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).

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)

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 )   
).

Can you do this any easier?

Feedback appreciated!

Oliver