@gregorwolf @larshp I just saw that the demos repository was created under the MIT license, although the abap2xlsx repository is under Apache 2.0. It's weird because the demo programs were initially created in the abap2xlsx repository under the Apache 2.0 license, so I'm expecting that the demos repository should be under the same license.

I have nothing against changing the license, but is it an error or did you both agree? (and same question for any other owner I'm not aware of).

demos:

abap2xlsx:

The program ZDEMO_EXCEL30 has the following lines of code:

CONSTANTS: ...
           lc_typekind_s_ls   TYPE string VALUE 's_leading_blanks'.
...
  lo_worksheet->set_cell( ip_column = 'B' ip_row = 2 ip_value = ' String with leading spaces'
                          ip_data_type = lc_typekind_s_ls ).

and the generated Excel shows "0 %" in cell B2:

Why?

The following line of code is missing in program ZDEMO_EXCEL_COMMENTS:

  CONCATENATE 'A comment split' cl_abap_char_utilities=>cr_lf 'on 2 lines?' INTO lv_comment.
  lo_comment->set_text( ip_ref = 'F6' ip_text = lv_comment ).
****** The following line must be added: ******
  lo_worksheet->add_comment( lo_comment ).

  " Second sheet
  lo_worksheet = lo_excel->add_new_worksheet( ).

I would be happy to submit a pull request for this change if I had permissions.

Running the program ZDEMO_EXCEL_CHECKER shows a regression on the result of demo program ZDEMO_EXCEL15_04 after abap2xlsx/abap2xlsx#1144:

After splitting the demos from the library, we probably need a way to inform users about the appropriate version of the demos for each release.
I propose tagging this repo with release tags (es. v7.16.0), corresponding to the latest commit before the tagging in main library. We could probably do this now only for 7.16.0 but in the future it should probably be noted in the release checklist and in the Readme.

If choosing to save to frontend or backend, with a Writer Class which is not "Autodetect", the file is created with the name 37- Read template and output.&, which is suffixed .& (better choose .xlsx or .xlsm):

1. Run ZDEMO_EXCEL37:
   
2. If "save to frontend":
   
3. If "save to backend":
   

Depending on the selected writer class, instead of .&, I would expect .xlsm for classes containing XLSM in their name, and .xlsx otherwise.

Also, that would be nice if it was possible to choose a file name, instead of the constant name 37- Read template and output.&, to make it easier to play with several input files when analyzing abap2xlsx if it generates an invalid Excel file from an Excel template (programs using both reader and writer classes). It may be very useful as there are probably many features supported only partially by abap2xlsx (e.g. abap2xlsx/abap2xlsx#1048).

To reproduce, run the program ZDEMO_EXCEL43:

• Case 1 (short dumps if input Excel file has more than 1 column):
  • select this Excel file below (has more than 1 column), and the below short dump is raised.
    Several columns.xlsx

    UNCAUGHT_EXCEPTION
    ZCX_EXCEL
    ...
    Chain of Exception Objects
    ...
    Level    1     Class    ZCX_EXCEL
    ...
    Internal table has less columns than excel
    ...
    ERROR                          Internal table has less columns than excel
    ...
    Active Calls/Events
    ...
    3 METHOD       ZCX_EXCEL=====================CP    ZCX_EXCEL=====================CM005     2
      ZCX_EXCEL=>RAISE_TEXT
    2 METHOD       ZCL_EXCEL_WORKSHEET===========CP    ZCL_EXCEL_WORKSHEET===========CM01M    63
      ZCL_EXCEL_WORKSHEET=>GET_TABLE
    1 EVENT        ZDEMO_EXCEL43                       ZDEMO_EXCEL43                          48
      START-OF-SELECTION

• Case 2 (outputs only 1st character if 1 column)
  • Select this Excel file below:
    One column.xlsx
    
    Actual result by ZDEMO_EXCEL43
    

Expected results:

• Case 1: change the program ZDEMO_EXCEL43 so that:
  • the user can choose the number of columns for the internal table (selection screen parameter)
  • any exception is displayed instead of a short dump
• Case 2:
  • the columns of the internal table are of type string instead of 1 character, or let the user choose the type of each column.

ZDEMO_EXCEL9, all values entered in A8 are rejected, whatever B8 contains a "-" or not:

Details of the data validation in cell A8 (Custom, formula IF(B8<>"";INDIRECT(LEFT(B8;SEARCH("-";B8;1)));EMPTY)):

After abap2xlsx/abap2xlsx#1159 which makes the Excel reader get the tables, the demo program ZDEMO_EXCEL15 now copies correctly the table generated by the program ZDEMO_EXCEL3:

BUT the new Excel file generated has not been set as the new reference, so ZDEMO_EXCEL_CHECKER now returns a difference:

I just ran ZDEMO_EXCEL_CHECKER and I see two regressions:

• ZDEMO_EXCEL42
  
  Clicking on theme1.xml opens Visual Code but no difference is shown. In fact, there are lots of trailing spaces after some of the XML elements of the old version, which are ignored by the checker, and no trailing spaces in the new version. I feel it's due to abap2xlsx/abap2xlsx#1046.
• ZDEMO_EXCEL_COMMENTS
  
  I guess it was due to #34.

Probably, regenerating the reference Excel files of these 2 programs in the Web repository will be fine, via ZDEMO_EXCEL_CHECKER:

The program zdemo_excel_checker which is used by the contributors after they did a fix, to verify that there are no regressions, is currently reporting regressions everywhere concerning the element <cp:lastModifiedBy> in the file core.xml:

For zdemo_excel_comments, we can also see this error:

Analysis: this happens when running the tests with a SAP user that is not DEVELOPER.

It can be fixed by adding CLEAR docprops_core-last_modified_by:

    CLEAR: docprops_core-creator,
           docprops_core-description,
           docprops_core-last_modified_by, "<==== add this line
           docprops_core-created,
           docprops_core-modified.

and by adding this code to clear the XML element value <comments ...><authors><author> in the files xl/comments*.xml:

    LOOP AT zip->files ASSIGNING <file>
        WHERE name CP 'xl/comments*.xml'.

      zip->get(
        EXPORTING
          name                    = <file>-name
        IMPORTING
          content                 = content
        EXCEPTIONS
          zip_index_error         = 1
          zip_decompression_error = 2
          OTHERS                  = 3 ).
      IF sy-subrc <> 0.
        RAISE EXCEPTION TYPE zcx_excel EXPORTING error = |{ <file>-name } not found|.
      ENDIF.

      lo_ixml = cl_ixml=>create( ).
      lo_streamfactory = lo_ixml->create_stream_factory( ).
      lo_istream = lo_streamfactory->create_istream_xstring( content ).
      lo_document = lo_ixml->create_document( ).
      lo_parser = lo_ixml->create_parser(
                  document       = lo_document
                  istream        = lo_istream
                  stream_factory = lo_streamfactory ).
      lo_parser->parse( ).

      lo_element = lo_document->find_from_path_ns(
                  default_uri = ''
                  path = '/"http://schemas.openxmlformats.org/spreadsheetml/2006/main:comments"'
                      && '/"http://schemas.openxmlformats.org/spreadsheetml/2006/main:authors"'
                      && '/"http://schemas.openxmlformats.org/spreadsheetml/2006/main:author"' ).
      IF lo_element IS BOUND.
        lo_element->set_value( '' ).
      ENDIF.

      CLEAR content.
      lo_ostream = lo_streamfactory->create_ostream_xstring( content ).
      lo_renderer = lo_ixml->create_renderer(
                  document = lo_document
                  ostream  = lo_ostream ).
      lo_renderer->render( ).

      ls_file-name = <file>-name.
      ls_file-content = content.
      APPEND ls_file TO lt_file.

    ENDLOOP.

While reviewing #16 , I noticed that ZDEMO_EXCEL_CHECKER reported differences on ZDEMO_EXCEL36, which should be investigated, either fixing the cause or updating the reference file. This happened with both versions of the checker, so it's not a new issue with the checker itself.

I don't understand what this program should demonstrate. Can anyone explain?
What means this cell with the text "This cell is unlocked"? In fact it's not unlocked at all. Unlocked cells are part of worksheet protection, not workbook protection (worksheet protection is demonstrated in programs ZDEMO_EXCEL17, ZDEMO_EXCEL22).

abap2xlsx/abap2xlsx#444 was closed a long time ago, but no explanation. I could verify that workbook protection works if a password is setup, but ZDEMO_EXCEL18 doesn't define one.

After I pull the demos repository to SAP BTP Cloud / SAP S/4HANA Public Cloud (via the abapGit Eclipse plugin), almost all objects are not imported.

Any chance to enhance the repository to have the demos be supported in the Cloud?

Log texts:

PROG
     ZABAP2XLSX_DEMO_SHOW | PROG | W | Object type PROG not supported in SAP Cloud Platform, ABAP Environment. The import of this object is skipped
     ...
SICF
     NPL0018        BD88061CD29B341257671DC5C | SICF | W | Namespace of object NPL0018        BD88061CD29B341257671DC5C (type SICF) not allowed in SAP Cloud Platform, ABAP Environment
W3MI
     ZDEMO_EXCEL1 | W3MI | W | Object type W3MI not supported in SAP Cloud Platform, ABAP Environment. The import of this object is skipped
     ...
WDYA
     ZDEMO_EXCEL_WDA01 | WDYA | W | Object type WDYA not supported in SAP Cloud Platform, ABAP Environment. The import of this object is skipped
WDYN
     ZDEMO_EXCEL_WDA01 | WDYN | W | Object type WDYN not supported in SAP Cloud Platform, ABAP Environment. The import of this object is skipped

The program zdemo_excel_checker which is used by the contributors after they did a fix, to verify that there are no regressions, is showing one for zdemo_excel33:

For instance, the program generates the text Super region which is not expected:

Analysis: this happens when running zdemo_excel_checker in S/4HANA, there is an extension of table T005T (or any system where the table T005T is defined differently):

This can be fixed by replacing:

TYPES: ty_t005t_lines TYPE TABLE OF t005t.

with:

TYPES: BEGIN OF ty_t005t_line,
         mandt   TYPE mandt,
         spras   TYPE spras,
         land1   TYPE land1,
         landx   TYPE landx,
         natio   TYPE natio,
         landx50 TYPE landx50,
         natio50 TYPE natio50,
       END OF ty_t005t_line,
       ty_t005t_lines TYPE TABLE OF ty_t005t_line.

After PR abap2xlsx/abap2xlsx#1046, by running ZDEMO_EXCEL_CHECKER we can see these changes in XLSX files produced by demo programs:

This is due to a harmless change in zcl_excel_theme_fmt_scheme code which removed the spaces:

I've looked at ZDEMO_EXCEL30 for hints on how to use the decimal places but it's not working for me when data is coming from SAP sources.

For example, when I have a value of .90, the spreadsheet shows .9 and the customer wants to see .90

I've used the following and it's not working.
lo_worksheet->set_cell( ip_column = lv_col ip_row = lv_row ip_value = l_charact-qcres-mvalue
ip_abap_type = lc_typekind_packed ).

l_charact-qcres-mvalue is type CHAR132 coming from SAP database.

I've also tried lc_typekind_num without success.

Has any one else had this issue and if so, how did they fix it?

Regards,

Greg Sharpe

While reviewing #16 , I noticed that ZDEMO_EXCEL_CHECKER reported differences on ZDEMO_EXCEL3 and ZDEMO_EXCEL15_03, which should be investigated, either fixing the cause or updating the reference file. This happened with both versions of the checker, so it's not a new issue with the checker itself.

in worksheets/sheet1.xml
Old: <pane ySplit="3" topLeftCell="A4" activePane="bottomRight" state="frozen"/>
New: <pane ySplit="3" xSplit="1" topLeftCell="B4" activePane="bottomRight" state="frozen"/>

Followup to abap2xlsx bug 972, as suggested by @sandraros :

Concerning ZIP-DIFF, I agree that we should copy it to the demos repository, maybe doing it more specific as an XLSX-DIFF (this way, we may also solve diff issues which occur only in XLSX), which might both evolve separately.

To reproduce:

1. Run ZDEMO_EXCEL37, click on the default Autodetect value of the Reader Class field to display the possible values, you can see the value Autodetect among others:
   
2. Select the value ZCL_EXCEL_READER_2007 of the Reader Class field
3. Press Enter
4. Click again on the Reader Class field, you can see that the value Autodetect is not possible anymore:
   

Same issue with the Writer Class field.

You might be aware of the abapGit CI Backend which allows to execute integration tests for all object types supported by abapGit. It even supports a REST API or running as a Job. Maybe we can reuse this infrastructure. Perhaps @mbtools can give some guidance.

Should we expect drop downs in Excel for the Airline column after executing ZDEMO_EXCEL3 and ZDEMO_EXCEL50.

I think we need to modify the ZDEMO_EXCEL3 with as from line 108

lo_data_validation->cell_row = 1. " Was 4
lo_data_validation->cell_column = 'A'. " Was C
lo_data_validation->cell_row_to = 9. " Added
lo_data_validation->cell_column_to = 'A'. " Added

Then we have dropdowns as shown in DDL1

As ZDEMO_EXCEL50 is using the Huge File writer is this also possible to have DataValidations with this Writer?

Greetings,

?

When running ZDEMO_EXCEL_CHECKER, in the list of demo programs, the following programs don't appear although the program tries internally to check them:

• ZDEMO_EXCEL37
• ZDEMO_EXCEL41
• ZDEMO_EXCEL42
• ZDEMO_EXCEL45
• ZDEMO_EXCEL46
• ZDEMO_EXCEL47
• ZDEMO_EXCEL48