[abap2xlsx] How to loop over all existing worksheets and save their content in internal tables?

[Vlad_Ghitulescu]

Hey,

with the help of local repository of abap2xlsx and an example I found on codezentrale.de I managed to read an XLSX-file containing only one worksheet.

In order to get all the worksheets in a multi-worksheet XLSX-file I used something like:

DATA(o_reader) = CAST zif_excel_reader( NEW zcl_excel_reader_2007( ) ).
DATA(o_excel) = o_reader->load_file( p_fname ).
      
DATA(o_worksheet_1) = o_excel->get_worksheet_by_index( iv_index = 1 ).
DATA(o_worksheet_2) = o_excel->get_worksheet_by_index( iv_index = 2 ).
DATA(o_worksheet_3) = o_excel->get_worksheet_by_index( iv_index = 3 ).
(…)

but these workaround implies that I know exactly how many worksheets will I have in the XLSX-file I get as an input.

What I really want is to loop over all existing worksheets, load them all in internal tables and further deal with each of them according to worksheet-title, field-content in the header-row etc.

The above mentioned example on codezentrale.de has this commented line

* DATA(o_worksheet) = CAST zcl_excel_worksheet( o_excel->get_worksheets_iterator( )->get_next( ) ).

hinting at multiple worksheets but I couldn't get it done what I want to.

Could anybody help with this?

Thanks!

Regards,

Vlad

[Sandra_Rossi]

Iterator:

    DATA(iterator) = o_excel->get_worksheets_iterator( ).
    WHILE iterator->has_next( ).
      DATA(sheet) = CAST zcl_excel_worksheet( iterator->get_next( ) ).
      IF sheet->get_title( ) = 'Sheet1'.
      ENDIF.
    ENDWHILE.

[Vlad_Ghitulescu]

Thank you very much sandra.rossi - it works like a charm!

[muffy13]

Thank you both, you helped me a lot 😮 I tried to figure that out for 2 days.