[abap2xlsx] Experience with ...

[stefan_schnell]

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:

...



DATA:

  ...

  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.

lo_style_cond->set_range(

  EXPORTING

    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:

FIELD-SYMBOLS:

  <lt_data>      TYPE ANY TABLE,

  <ls_data>      TYPE any,

  <lv_comp>      TYPE any

  .



...



LOOP AT <lt_data> ASSIGNING <ls_data>.

  DO.

    ASSIGN COMPONENT sy-index OF STRUCTURE <ls_data> TO <lv_comp>.

    IF sy-subrc = 0.

      ...

      CONTINUE.

    ELSE.

      EXIT.

    ENDIF.

  ENDDO.

ENDLOOP.



...

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

DATA:

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

  ...

ENDLOOP.



...

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.