Total and subtotals problem in Export Excel from A...

former_member839256 · ‎2023 Feb 04

I hope I can find the solution here, 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 and I'm using classes in my program, move-corresponding is not recommended because it only brings me the data that is first.

Astashonok · ‎2023 Aug 22

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

Total and subtotals problem in Export Excel from ALV tree