Total and subtotals are not exported to Excel from SALV_TREE

Question

I have created a SALV tree, using the CL_SALV_TREE class, the output is shown in the following image:

Now for the export in Excel that works correctly for me, I have used the following code, but the problem is that it does not export the subtotals, so how could I add the subtotals or how could I solve it? since apparently the aggregation functions can't be visible in the ALV tree.

CLASS lcl_tree IMPLEMENTATION.
METHOD export_tree.
DATA: lr_data  TYPE REF TO data,
      lt_spfli TYPE STANDARD TABLE OF spfli,
      levels   TYPE TABLE OF rsplf_srv_p.

DATA: lr_zip         TYPE REF TO cl_abap_zip,
      lr_xlnode      TYPE REF TO if_ixml_node,
      lr_xldimension TYPE REF TO if_ixml_node,
      lr_file        TYPE REF TO cl_xml_document,
      lr_xlrows      TYPE REF TO if_ixml_node_list,
      lr_xlrow       TYPE REF TO if_ixml_element,
      lr_xlformat    TYPE REF TO if_ixml_element,
      lr_xlworksheet TYPE REF TO if_ixml_element.

FIELD-SYMBOLS: <spfli> TYPE spfli.

DATA(lt_nodes) = go_alv_tree->get_nodes( )->get_all_nodes( ).
LOOP AT lt_nodes INTO DATA(ls_node).
  DATA(lr_node) = ls_node-node.
  DATA(lv_level) = 0.
  DO.
    TRY.
        lr_node = lr_node->get_parent( ).
        lv_level = lv_level + 1.
      CATCH cx_salv_msg.
        EXIT.
    ENDTRY.
  ENDDO.
  APPEND VALUE rsplf_srv_p( indx = sy-tabix value = lv_level ) TO levels.
  lr_data = ls_node-node->get_data_row( ).
  ASSIGN lr_data->* TO <spfli>.
  APPEND <spfli> TO lt_spfli.
ENDLOOP.

cl_salv_table=>factory(
  IMPORTING
    r_salv_table = DATA(lr_table)
  CHANGING
    t_table = lt_spfli ).

DATA(lv_xlsx) = lr_table->to_xml( if_salv_bs_xml=>c_type_xlsx ).
CREATE OBJECT lr_zip.
lr_zip->load( lv_xlsx ).
lr_zip->get( EXPORTING name = 'xl/worksheets/sheet1.xml' IMPORTING 
content = DATA(lv_file) ).

 CREATE OBJECT lr_file.
 lr_file->parse_xstring( lv_file ).
 * Row elements are under SheetData
 lr_xlnode = lr_file->find_node( 'sheetData' ).
 lr_xlrows = lr_xlnode->get_children( ).

DO lr_xlrows->get_length( ) TIMES.
  lr_xlrow ?= lr_xlrows->get_item( sy-index - 1 ).
  READ TABLE lt_nodes INTO ls_node INDEX sy-index - 1. "find this row 
 in tree
  IF sy-subrc = 0.
    READ TABLE levels ASSIGNING FIELD-SYMBOL(<line_level>) INDEX sy-index.
   * Find the level of the node
    CHECK <line_level>-value - 1 NE 0.
    * Assign the level to row
    lr_xlrow->set_attribute( name = 'outlineLevel' value = condense( CONV string( <line_level>-value - 1 ) ) ).
    lr_xlrow->set_attribute( name = 'hidden' value = 'true' ).
  ENDIF.
ENDDO.

 * Create new element in the XML file
lr_xlworksheet ?= lr_file->find_node( 'worksheet' ).
DATA(lr_xlsheetpr)   = cl_ixml=>create( )->create_document( )->create_element( name = 'sheetPr' ).
DATA(lr_xloutlinepr) = cl_ixml=>create( )->create_document( )->create_element( name = 'outlinePr' ).
lr_xlsheetpr->if_ixml_node~append_child( lr_xloutlinepr ).
lr_xloutlinepr->set_attribute( name = 'summaryBelow' value = 'false' ).
lr_xldimension ?= lr_file->find_node( 'dimension' ).
lr_xlworksheet->if_ixml_node~insert_child( new_child = lr_xlsheetpr ref_child = lr_xldimension ).
 * Create xstring and move it to XLSX
lr_file->render_2_xstring( IMPORTING stream = lv_file ).
lr_zip->delete( EXPORTING name = 'xl/worksheets/sheet1.xml' ).
lr_zip->add( EXPORTING name = 'xl/worksheets/sheet1.xml' content = lv_file ).
lv_xlsx = lr_zip->save( ).

DATA lv_size   TYPE i.
DATA lt_bintab TYPE solix_tab.

CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
  EXPORTING
    buffer        = lv_xlsx
  IMPORTING
    output_length = lv_size
  TABLES
    binary_tab    = lt_bintab.

CHECK lt_bintab IS NOT INITIAL.
DATA(p_file) = cl_openxml_helper=>browse_local_file_open( iv_title = 'Save to XLSX File' iv_filename = '' iv_extpattern = 'All files(*.*)|*.*' ).
cl_gui_frontend_services=>gui_download( EXPORTING bin_filesize = lv_size
                                                  filename    = p_file && `.xlsx`
                                                  filetype      = 'BIN'
                                        CHANGING  data_tab   = lt_bintab ).
 ENDMETHOD.
 ENDCLASS.

I tried to replicate the code from this link: https://blogs.sap.com/2015/07/24/salv-tree-to-excel-xlsx/comment-page-1/#comment-658453

There, what it does is add this add-corresponding statement before adding it to the ALV, but that doesn't work on classes which I'm using in my program:

"ADD-CORRESPONDING" is not supported in the OO context

Answer 1

If your question is simplified to what is the code equivalent to add-corresponding in the "OO context", this is one possible answer, I propose the method add_corresponding below, and a test code to demonstrate how it works - this code compiles in 7.40 SP08:

CLASS lcx_add_corresp_not_all_struct DEFINITION INHERITING FROM cx_static_check.
ENDCLASS.
CLASS lcl_app DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS add_corresponding IMPORTING from_struct TYPE any
                                    CHANGING  to_struct   TYPE any
                                    RAISING   lcx_add_corresp_not_all_struct
                                              cx_sy_conversion_overflow.
ENDCLASS.
CLASS lcl_app IMPLEMENTATION.
  METHOD add_corresponding.
    TYPES: ty_names            TYPE HASHED TABLE OF abap_compname WITH UNIQUE KEY table_line,
           ty_names_in_structs TYPE STANDARD TABLE OF ty_names WITH EMPTY KEY,
           ty_table_rtti       TYPE STANDARD TABLE OF REF TO cl_abap_typedescr WITH EMPTY KEY.
    DATA(rtti_from_struct) = cl_abap_typedescr=>describe_by_data( from_struct ).
    DATA(rtti_to_struct) = cl_abap_typedescr=>describe_by_data( to_struct ).
    IF rtti_from_struct->kind <> rtti_from_struct->kind_struct
        OR rtti_to_struct->kind <> rtti_to_struct->kind_struct.
      RAISE EXCEPTION NEW lcx_add_corresp_not_all_struct( ).
    ENDIF.
    DATA(names_in_structs) = VALUE ty_names_in_structs(
        FOR rtti IN VALUE ty_table_rtti( ( rtti_from_struct ) ( rtti_to_struct ) )
        ( VALUE #( FOR <comp> IN CAST cl_abap_structdescr( rtti )->components
                           WHERE ( type_kind CA '8abeFIPs' ) " all numeric types
                           ( <comp>-name ) ) ) ).
    DATA(same_names) = FILTER ty_names( names_in_structs[ 1 ] IN names_in_structs[ 2 ] WHERE table_line = table_line ).
    LOOP AT same_names REFERENCE INTO DATA(same_name).
      ASSIGN COMPONENT same_name->* OF STRUCTURE from_struct TO FIELD-SYMBOL(<from_number>).
      ASSERT sy-subrc = 0.
      ASSIGN COMPONENT same_name->* OF STRUCTURE to_struct TO FIELD-SYMBOL(<to_number>).
      ASSERT sy-subrc = 0.
      <to_number> = <to_number> + <from_number>.
    ENDLOOP.
  ENDMETHOD.
ENDCLASS.

CLASS ltc_app DEFINITION
      FOR TESTING
      DURATION SHORT
      RISK LEVEL HARMLESS.
  PRIVATE SECTION.
    METHODS test FOR TESTING RAISING cx_static_check.
    METHODS overflow FOR TESTING RAISING cx_static_check.
ENDCLASS.
CLASS ltc_app IMPLEMENTATION.
  METHOD test.
    TYPES: ty_output LIKE ls_output.
    ls_output = VALUE #( clabs = 100 ceinm = 500 ).
    DATA(ls_output2) = ls_output.
    lcl_app=>add_corresponding( EXPORTING from_struct = ls_output2 CHANGING to_struct = ls_output ).
    cl_abap_unit_assert=>assert_equals( act = ls_output exp = VALUE ty_output( clabs = 200 ceinm = 1000 ) ).
  ENDMETHOD.
  METHOD overflow.
    TYPES: BEGIN OF ty_struct,
             int1 TYPE int1,
           END OF ty_struct.
    DATA(from_struct) = VALUE ty_struct( int1 = 200 ).
    DATA(to_struct) = from_struct.
    TRY.
        lcl_app=>add_corresponding( EXPORTING from_struct = from_struct CHANGING to_struct = to_struct ).
      CATCH cx_sy_conversion_overflow INTO DATA(arithmetic_overflow).
    ENDTRY.
    cl_abap_unit_assert=>assert_bound( act = arithmetic_overflow msg = |Actual: { to_struct-int1 } ; expected: arithmetic overflow| ).
  ENDMETHOD.
ENDCLASS.

NB: instead of ADD-CORRESPONDING, you may simply use ls_output-clabs = ls_output-clabs + ls_mchb-clabs and repeat for all numeric components.

NB: ADD-CORRESPONDING and other arithmetic "corresponding" statements were made obsolete because they are considered error-prone:

"These statements are error-prone because, particularly in complex structures, it is not easy to check that identically named components have the data type and content necessary for a numeric operation."

Answer 2

You are picking the wrong row by index here:

lr_xlrow ?= lr_xlrows->get_item( sy-index - 1 ).
READ TABLE lt_nodes INTO ls_node INDEX sy-index - 1. "find this row 

It should be like this:

lr_xlrow ?= lr_xlrows->get_item( sy-index ).
READ TABLE lt_nodes INTO ls_node INDEX sy-index. "find this row