Skip to Content

how can we upload an excel data with more than one worksheet into an Internal table?

I am working on a report..my requirement is...I have to upload an excel data [where data are being maintained in more than one worksheet in a single excel] into an Internal table. Is there any Function Module or is there any other alternative to achieve this. kindly, guide

please note: data are being maintained in single excel with more than one worksheet

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Oct 23, 2018 at 08:52 AM

    using abap2xlsx or cl_xlsx_document. xlsx workbench...alot of tool. just try to search first.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 24, 2018 at 05:08 AM

    a simple class based approach is show below. It is not a complete code but it shows till the point where you read multiple excel sheets

    " Read the excel data
    cl_gui_frontend_services=>gui_upload(
    
          EXPORTING
    
            filename                = iv_file_name    " Name of file
    
            filetype                = 'BIN'
    
          IMPORTING
    
            filelength              = DATA(lv_length)
    
          CHANGING
    
            data_tab                = lt_data    " Transfer table for file contents
    
          EXCEPTIONS
    
            file_open_error         = 1
    
            file_read_error         = 2
    
            no_batch                = 3
    
            gui_refuse_filetransfer = 4
    
            invalid_type            = 5
    
            no_authority            = 6
    
            unknown_error           = 7
    
            bad_data_format         = 8
    
            header_not_allowed      = 9
    
            separator_not_allowed   = 10
    
            header_too_long         = 11
    
            unknown_dp_error        = 12
    
            access_denied           = 13
    
            dp_out_of_memory        = 14
    
            disk_full               = 15
    
            dp_timeout              = 16
    
            not_supported_by_gui    = 17
    
            error_no_gui            = 18
    
            OTHERS                  = 19
    
        ).
    
        IF sy-subrc <> 0.
    
          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
    
                     WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO DATA(lv_dummy).
    
    * Add Message
    
          append_message(
    
            CHANGING
    
              ct_messages = et_messages    " Return parameter table
    
          ).
    
          RETURN.
    
        ENDIF.
    
    * Show provgress indicator
    
        cl_progress_indicator=>progress_indicate(
    
          EXPORTING
    
            i_text               = 'Loading the excel data....'
    
            i_processed          = 50
    
            i_total              = 100
    
            i_output_immediately = abap_true ).
    
    
    
    * Convert it to xstring
    
        CALL FUNCTION 'SCMS_BINARY_TO_XSTRING'
    
          EXPORTING
    
            input_length = lv_length
    
          IMPORTING
    
            buffer       = lv_content
    
          TABLES
    
            binary_tab   = lt_data
    
          EXCEPTIONS
    
            failed       = 1
    
            OTHERS       = 2.
    
        IF sy-subrc NE 0.
    
    * Add Message
    
          append_message(
    
            CHANGING
    
              ct_messages = et_messages    " Return parameter table
    
          ).
    
          RETURN.
    
        ENDIF.
    
    
    
    * User Excel functions to read the worksheet
    
        TRY.
    
            DATA(lr_excel) = NEW cl_fdt_xl_spreadsheet( document_name = iv_file_name xdocument = lv_content ).
    
            IF lr_excel IS BOUND.
    
              lr_excel->if_fdt_doc_spreadsheet~get_worksheet_names( IMPORTING worksheet_names = DATA(lt_worksheets) ).
    
            ENDIF.
    
          CATCH cx_root.
    
    * Add Message
    
    
            append_message(
    
              CHANGING
    
                ct_messages = et_messages    " Return parameter table
    
            ).
    
            RETURN.
    
        ENDTRY.
    
    
    
        mt_excel_read_data = ct_excel_read_data.
    
        LOOP AT lt_worksheets REFERENCE INTO DATA(lo_worksheets).
    
    
    
          CHECK line_exists( mt_excel_read_data[ name = lo_worksheets->* ] ).
    
    
    
          DATA(lr_worksheet_itab) = lr_excel->if_fdt_doc_spreadsheet~get_itab_from_worksheet( lo_worksheets->* ).
    
          ASSIGN lr_worksheet_itab->* TO <ft_excel_data>.
    
    
    
          DATA(lo_data) = mt_excel_read_data[ name = lo_worksheets->* ]-data
    endloop.
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.