Skip to Content
Technical Articles
Author's profile photo Stefan Schnell

[abap2xlsx] Experience with …

At the moment I write code for a project to generate of Excel reports via abap2xlsx and it works great.


… Conditional Formatting

Today I wrote code with conditional formatting, here my ABAP code snippets as example:


  lo_style_std_bold_red      TYPE REF TO zcl_excel_style,
  lv_style_std_bold_red_guid TYPE zexcel_cell_style,
  lo_style_cond              TYPE REF TO zcl_excel_style_conditional,
  ls_style_expr              TYPE zexcel_conditional_expression


"-Create bold red font style based on standard for conditional formatting
lo_style_std_bold_red = lo_excel->add_new_style( ).
lo_style_std_bold_red->font->name = zcl_excel_style_font=>c_name_arial.
lo_style_std_bold_red->font->scheme = zcl_excel_style_font=>c_scheme_none.
lo_style_std_bold_red->font->size = 10.
lo_style_std_bold_red->font->bold = abap_true.
lo_style_std_bold_red->font->color-rgb = '00FF0000'.
lv_style_std_bold_red_guid = lo_style_std_bold_red->get_guid( ).


"-Get highest row-------------------------------------------------------
lv_row = lo_worksheet->get_highest_row( ).

"-Conditional formatting------------------------------------------------
lo_style_cond = lo_worksheet->add_new_conditional_style( ).
lo_style_cond->rule = zcl_excel_style_conditional=>c_rule_expression.

ls_style_expr-formula = 'INDIRECT(ADDRESS(ROW(),COLUMN()))<>0'.
ls_style_expr-cell_style = lv_style_std_bold_red_guid.
lo_style_cond->mode_expression = ls_style_expr.
    ip_start_row    = lv_row
    ip_start_column = 'B'
    ip_stop_row     =  lv_row
    ip_stop_column  = 'S'


To build the formula for the rule I use the Excel wizard.


As you can see it creates the formula for the rule in a localized version. When I have copied this formula in the ABAP code Excel told me, when opening the document, that it was damaged.


On this way I found out:

  • If you copy the formula from the wizard, use only the english commands and not a localized version.
  • Delete the equal sign in front of the formula.

Now it works perfect.

You can find a very interesting online tool to translate formulas into different languages here. It is also available in German here.


… Table Data Transfer

Today I wrote code to transfer data from an internal table to the Excel spreadsheet. I use a method which copies the content row by row and field by field from the source to the target. On the one hand, to be independent from the table type, I used field symbols and assign component command:

  <lt_data>      TYPE ANY TABLE,
  <ls_data>      TYPE any,
  <lv_comp>      TYPE any


LOOP AT <lt_data> ASSIGNING <ls_data>.
    ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_comp>.
    IF sy-subrc = 0.


On the other hand I use a fixed type table with a definitely structure:

  lt_data TPYE t_mytable,
  ls_data TYPE s_mystructure


LOOP AT lt_data INTO ls_data.
  lo_worksheet->set_cell( ip_column = 'A' ip_row = 1 ip_value = ls_data-my_field ).


At last I decided to use the fixed type table method. Both cases have advantages and disadvantages. But with the fixed type table method you know exactly the data types and this is for Excel very important, because numbers are right-aligned and strings are left aligned. If you use <lv_comp> with type any you loose this kind of information and it is necessary to detect the data type additionally. So I prefer the method with the fixed type table.

Now it works perfect.


… Formulas

The same things I found out for the formulas of the conditionl formatting applies also for the normal formulas in the cells.

  • Use only the english commands and not a localized version.
  • Delete the equal sign in front of the formula.
  • The separator between the arguments of a function is a comma, not a semicolon.
In the German version of Excel looks an IF function like this

=WENN(A1 = 1; "Yes"; "No")

To use this formula with abap2xlsx in an ABAP source it must looks like this:

IF(A1 = 1, "Yes", "No")

Hint: You must be careful if you copy a formula from an Excel cell into your ABAP source.


Thanks to Ivan Femia and the developer team of abap2xlsx, it is a great framework to work with Excel files in ABAP.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Ivan Femia
      Ivan Femia

      Thank you for the feedback! I really appreciate

      Very welcome to share your experience and ways to improve the product!


      Author's profile photo Former Member
      Former Member

      Hi Ivan,

      I need your help, actually the logic you have provided for excel is great but found custom classes like:

      DATA: lo_excel                TYPE REF TO zcl_excel,
            lo_excel_writer         TYPE REF TO zif_excel_writer,
            lo_worksheet            TYPE REF TO zcl_excel_worksheet,
            lo_style                TYPE REF TO zcl_excel_style,
            lv_style_guid           TYPE zexcel_cell_style.
      How to get these?
      Please help.


      Author's profile photo Ivan Femia
      Ivan Femia

      These are included in the main package via SAPLink or ABAPGit.


      Author's profile photo Francisco Miller
      Francisco Miller

      I don't have such type of experience. I'm a newbie in this field. I know a lot about window instalment and work for ARAX Windows Work company and install repair rotter wood frames sashes. But I also would like to get the new skills with the help of your website. Thank you for such an opportunity.