ZCL_EXCEL_WORKSHEET -> CONVERT_TO_TABLE drops initial excel lines

[gerhard-bnc]

Without any option to do different, Worksheet method CONVERT_TO_TABLE drops initial excel lines.

I expect that the table gets populated according to values of lv_maxcol, lv_maxrow that are set at beginning of the method.

In my case I am reading a supplied excel that includes empty lines (and columns), want to add data from SAP into it and finally write the enriched excel. For the SAP part of adding data the result of CONVERT_TO_TABLE is much better to use than the SHEET_CONTENT.
For adding the result back into SHEET_CONTENT and the correct excel line, I need to have the lines exactly same in the table returned by CONVERT_TO_TABLE and the excel. (of course, tedious workarounds are possible with current behavior of CONVERT_TO_TABLE)

Think this is easy to change by an additional optional parameter for CONVERT_TO_TABLE. It looks only around lines 249 change is needed:

        IF <ls_data> IS INITIAL.
          DELETE <lt_data> INDEX lv_index.
        ELSE.

Furthermore, I find it inconsistent that empty lines are dropped, but empty columns are kept.
Small sample file attached: Github-Issue-CONVERT_TO_TABLE.xlsx

[sandraros]

I recommend using methods, not SHEET_CONTENT directly (alas, for unknown reason, SHEET_CONTENT was not defined as READ-ONLY or private).

I have nothing against adding an optional parameter (it has been done so many times to permit improvements). Could you propose a PR please?

[gerhard-bnc]

Thanks for immediate reply and your comment on SHEET_CONTENT, was wondering the same (alas, too little explanation and comments in the code for such things).
I never did any PR here. Will check in next days what that means...

[sandraros]

Briefly: you have to fork the project to your GitHub account ("fork" button in GitHub), create a new branch, point ZABAPGIT to this branch, do your changes, push to your GitHub fork and then in GitHub you can click on "Create a pull request".

[gerhard-bnc]

I tried to push my changes to GitHub fork and in GitHub Create a pull request. For unknown reason too many code differences due to different capitalization of keywords (and these are in the unchanged code, not in the code I pretty printed).
I give up and would want to cancel the change, it has taken way too much time already.
The suggested coding change still attached. It takes care of this issue (1195) as well as issue1199
Github-change-method CONVERT_TO_TABLE.docx

[sandraros]

It's the problem of the Git diff which is case-sensitive and the SAP SE80 pretty printer which is unable to recognize which pretty printer method to choose (there's one by user for any repository). But ADT seems to recognize it (I never used it).

abap2xlsx requires either

• this setting in ADT (Ctrl+Shift+F1 to pretty print, it may work either on the selected lines or the whole code):
  
• or this SE80 setting (Shift+F1 to pretty print, it works on the whole code):
  

So, ADT is much better for using the pretty print of abap2xlsx.

Or you do it manually in SE80, you are helped by abapLint which checks the incorrect indenting or lower/upper case, when the pull request is created in the abap2xlsx repository. Also, note that SE80 may indent the selected lines of code by using the right context menu > Format > Format Selection.

[sandraros]

Thank you. I will try to implement your code. Adding here the lines of your DOCX document.

1 ZCL_EXCEL_WORKSHEET meth. CONVERT_TO_TABLE

1.1 New optional param.

IV_EMPTY_ROWS	TYPE ABAP_BOOL  DEFAULT ABAP_FALSE	Flag Keep empty rows

1.2 Coding change 1

1.2.1 compare

1.2.2 old

*--------------------------------------------------------------------*
* Start of convert content
*--------------------------------------------------------------------*
    READ TABLE me->sheet_content TRANSPORTING NO FIELDS WITH KEY cell_row = iv_begin_row.
    IF sy-subrc EQ 0.
      lv_index = sy-tabix.
      ENDIF.

1.2.3 new

*--------------------------------------------------------------------*
* Start of convert content
*--------------------------------------------------------------------*
    lv_index = lines( me->sheet_content ) + 1. "in case iv_begin_row too big

    LOOP AT me->sheet_content ASSIGNING <ls_sheet_content> WHERE cell_row >= iv_begin_row.
      "iv_begin_row might be an empty row with no entries in me->sheet_content
      lv_index = sy-tabix.

      IF iv_empty_rows = abap_true.
        DATA(lv_num_rows) = <ls_sheet_content>-cell_row - iv_begin_row.
        DO lv_num_rows TIMES.
          "add leading empty rows
          APPEND INITIAL LINE TO <lt_data> ASSIGNING <ls_data>.
        ENDDO.
      ENDIF.

1.3 Coding change 2

1.3.1 compare

1.3.2 old

IF <ls_data> IS INITIAL.

1.3.3 new

IF <ls_data> IS INITIAL AND iv_empty_rows = abap_false.

[gerhard-bnc]

Hi sandraros, sorry, but I now see my copying of code into the word doc was incomplete. For 1.2.3 new it should have been:

*--------------------------------------------------------------------*
* Start of convert content
*--------------------------------------------------------------------*
    lv_index = lines( me->sheet_content ) + 1. "in case iv_begin_row too big

    LOOP AT me->sheet_content ASSIGNING <ls_sheet_content> WHERE cell_row >= iv_begin_row.
      "iv_begin_row might be an empty row with no entries in me->sheet_content
      lv_index = sy-tabix.

      IF iv_empty_rows = abap_true.
        DATA(lv_num_rows) = <ls_sheet_content>-cell_row - iv_begin_row.
        DO lv_num_rows TIMES.
          "add leading empty rows
          APPEND INITIAL LINE TO <lt_data> ASSIGNING <ls_data>.
        ENDDO.
      ENDIF.

      EXIT.
    ENDLOOP.

before the loop extracting the data:

    LOOP AT me->sheet_content ASSIGNING <ls_sheet_content> FROM lv_index.
      AT NEW cell_row.
...

I had for SE80 exactly the pretty printer setting that you quote. When finally (with help from colleague of mine) getting my forked code uploaded to Github, it showed code with mixed case in parts of the class that I did not touch. At that point I quit and too hastily prepared the word doc that I had attached.

[sandraros]

Thanks for checking!