{"id":30,"date":"2023-04-27T16:23:52","date_gmt":"2023-04-27T16:23:52","guid":{"rendered":"https:\/\/sapabap.dwimansolution.com\/?p=30"},"modified":"2023-04-28T02:14:39","modified_gmt":"2023-04-28T02:14:39","slug":"upload-excel-file-with-multi-sheet","status":"publish","type":"post","link":"https:\/\/sapabap.dwimansolution.com\/index.php\/2023\/04\/27\/upload-excel-file-with-multi-sheet\/","title":{"rendered":"Read EXCEL File with Multi Sheet"},"content":{"rendered":"\n<p>Program ini dibuat untuk keperluan membaca semua data pada beberapa sheet yang ada di dalam file excel.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited.png\" alt=\"\" class=\"wp-image-40\" width=\"840\" height=\"454\" srcset=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited.png 1139w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited-300x162.png 300w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited-1024x554.png 1024w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited-768x415.png 768w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_2-edited-850x460.png 850w\" sizes=\"(max-width: 840px) 100vw, 840px\" \/><figcaption class=\"wp-element-caption\">Contoh file excel dengan 3 sheet (sflight.xlsx)<\/figcaption><\/figure>\n\n\n\n<ul><\/ul>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_4.png\" alt=\"\" class=\"wp-image-36\" width=\"612\" height=\"253\" srcset=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_4.png 705w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_4-300x124.png 300w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img decoding=\"async\" src=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited.png\" alt=\"\" class=\"wp-image-38\" width=\"840\" srcset=\"https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited.png 1254w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited-300x158.png 300w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited-1024x540.png 1024w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited-768x405.png 768w, https:\/\/sapabap.dwimansolution.com\/wp-content\/uploads\/2023\/04\/Screenshot_3-edited-850x448.png 850w\" sizes=\"(max-width: 1254px) 100vw, 1254px\" \/><figcaption class=\"wp-element-caption\">Program akan membaca sheet sheet yang terdapat pada file excel sekaligus, yang kemudian dapat kita prosess sesuai dengan kebutuhan.<\/figcaption><\/figure>\n\n\n\n<p>Berikut contoh program untuk membaca file excel yang terdiri dari beberapa sheet<\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;mode&quot;:&quot;htmlmixed&quot;,&quot;mime&quot;:&quot;text\/html&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:true,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;showPanel&quot;:false,&quot;language&quot;:&quot;HTML&quot;,&quot;modeName&quot;:&quot;html&quot;}\">*---------------------------------------------------------------------*\n* Report ZTEST_EXCEL_MULTISHEET\n*---------------------------------------------------------------------*\n*\n*---------------------------------------------------------------------*\nREPORT ZTEST_EXCEL_MULTISHEET NO STANDARD PAGE HEADING.\n\nDATA:\n  oref_container   TYPE REF TO cl_gui_custom_container,\n  iref_control     TYPE REF TO i_oi_container_control,\n  iref_document    TYPE REF TO i_oi_document_proxy,\n  iref_spreadsheet TYPE REF TO i_oi_spreadsheet,\n  iref_error       TYPE REF TO i_oi_error.\n\nDATA:\n  v_document_url TYPE c LENGTH 256,\n  i_sheets       TYPE soi_sheets_table,\n  wa_sheets      TYPE soi_sheets,\n  i_data         TYPE soi_generic_table,\n  wa_data        TYPE soi_generic_item,\n  i_ranges       TYPE soi_range_list.\n\nPARAMETERS:\n  p_file TYPE  localfile OBLIGATORY,\n  p_rows TYPE i DEFAULT 100 OBLIGATORY, &quot;Rows (Maximum 65536)\n  p_cols TYPE i DEFAULT 10 OBLIGATORY.    &quot;Columns (Maximum 256)\n\nINITIALIZATION.\n\n  CALL METHOD c_oi_container_control_creator=&gt;get_container_control\n    IMPORTING\n      control = iref_control\n      error   = iref_error\n*     retcode =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'E'.\n  ENDIF.\n\n\n  CREATE OBJECT oref_container\n    EXPORTING\n*     parent                      =\n      container_name              = 'CONT'\n*     style                       =\n*     lifetime                    = lifetime_default\n*     repid                       =\n*     dynnr                       =\n*     no_autodef_progid_dynnr     =\n    EXCEPTIONS\n      cntl_error                  = 1\n      cntl_system_error           = 2\n      create_error                = 3\n      lifetime_error              = 4\n      lifetime_dynpro_dynpro_link = 5\n      OTHERS                      = 6.\n  IF sy-subrc &lt;&gt; 0.\n    MESSAGE e001(00) WITH 'Error while creating container'.\n  ENDIF.\n\n  CALL METHOD iref_control-&gt;init_control\n    EXPORTING\n*     dynpro_nr            = SY-DYNNR\n*     gui_container        = ' '\n      inplace_enabled      = 'X'\n*     inplace_mode         = 0\n*     inplace_resize_documents = ' '\n*     inplace_scroll_documents = ' '\n*     inplace_show_toolbars    = 'X'\n*     no_flush             = ' '\n*     parent_id            = cl_gui_cfw=&gt;dynpro_0\n      r3_application_name  = 'EXCEL CONTAINER'\n*     register_on_close_event  = ' '\n*     register_on_custom_event = ' '\n*     rep_id               = SY-REPID\n*     shell_style          = 1384185856\n      parent               = oref_container\n*     name                 =\n*     autoalign            = 'x'\n    IMPORTING\n      error                = iref_error\n*     retcode              =\n    EXCEPTIONS\n      javabeannotsupported = 1\n      OTHERS               = 2.\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'E'.\n  ENDIF.\n\n  CALL METHOD iref_control-&gt;get_document_proxy\n    EXPORTING\n*     document_format    = 'NATIVE'\n      document_type  = soi_doctype_excel_sheet\n*     no_flush       = ' '\n*     register_container = ' '\n    IMPORTING\n      document_proxy = iref_document\n      error          = iref_error\n*     retcode        =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'E'.\n  ENDIF.\n\nAT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.\n\n* To provide F4 help for the file\n  PERFORM sub_file_f4.\n\nSTART-OF-SELECTION.\n\n  CONCATENATE 'FILE:\/\/' p_file INTO v_document_url.\n\n  CALL METHOD iref_document-&gt;open_document\n    EXPORTING\n      document_title = 'Excel'\n      document_url   = v_document_url\n*     no_flush       = ' '\n      open_inplace   = 'X'\n*     open_readonly  = ' '\n*     protect_document = ' '\n*     onsave_macro   = ' '\n*     startup_macro  = ''\n*     user_info      =\n    IMPORTING\n      error          = iref_error\n*     retcode        =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'I'.\n    LEAVE LIST-PROCESSING.\n  ENDIF.\n\n  CALL METHOD iref_document-&gt;get_spreadsheet_interface\n    EXPORTING\n      no_flush        = ' '\n    IMPORTING\n      error           = iref_error\n      sheet_interface = iref_spreadsheet\n*     retcode         =\n    .\n\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'I'.\n    LEAVE LIST-PROCESSING.\n  ENDIF.\n\n  CALL METHOD iref_spreadsheet-&gt;get_sheets\n    EXPORTING\n      no_flush = ' '\n*     updating = -1\n    IMPORTING\n      sheets   = i_sheets\n      error    = iref_error\n*     retcode  =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'I'.\n    LEAVE LIST-PROCESSING.\n  ENDIF.\n  LOOP AT i_sheets INTO wa_sheets.\n    CALL METHOD iref_spreadsheet-&gt;select_sheet\n      EXPORTING\n        name  = wa_sheets-sheet_name\n*       no_flush = ' '\n      IMPORTING\n        error = iref_error\n*       retcode  =\n      .\n    IF iref_error-&gt;has_failed = 'X'.\n      EXIT.\n*      call method iref_error-&gt;raise_message\n*        exporting\n*          type = 'E'.\n    ENDIF.\n    CALL METHOD iref_spreadsheet-&gt;set_selection\n      EXPORTING\n        top     = 1\n        left    = 1\n        rows    = p_rows\n        columns = p_cols.\n\n    CALL METHOD iref_spreadsheet-&gt;insert_range\n      EXPORTING\n        name     = 'Test'\n        rows     = p_rows\n        columns  = p_cols\n        no_flush = ''\n      IMPORTING\n        error    = iref_error.\n    IF iref_error-&gt;has_failed = 'X'.\n      EXIT.\n*      call method iref_error-&gt;raise_message\n*        exporting\n*          type = 'E'.\n    ENDIF.\n\n    REFRESH i_data.\n\n    CALL METHOD iref_spreadsheet-&gt;get_ranges_data\n      EXPORTING\n*       no_flush = ' '\n        all      = 'X'\n*       updating = -1\n*       rangesdef =\n      IMPORTING\n        contents = i_data\n        error    = iref_error\n*       retcode  =\n      CHANGING\n        ranges   = i_ranges.\n\n* Remove ranges not to be processed else the data keeps on adding up\n    CALL METHOD iref_spreadsheet-&gt;delete_ranges\n      EXPORTING\n        ranges = i_ranges.\n\n    DELETE i_data WHERE value IS INITIAL OR value = space.\n    ULINE.\n    WRITE:\/1 wa_sheets-sheet_name COLOR 3.\n    ULINE.\n\n    LOOP AT i_data INTO wa_data.\n      WRITE:(50) wa_data-value.\n      AT END OF row.\n        NEW-LINE.\n      ENDAT.\n    ENDLOOP.\n  ENDLOOP.\n\n  CALL METHOD iref_document-&gt;close_document\n*  EXPORTING\n*    do_save     = ' '\n*    no_flush    = ' '\n    IMPORTING\n      error = iref_error\n*     has_changed =\n*     retcode     =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'I'.\n    LEAVE LIST-PROCESSING.\n  ENDIF.\n  CALL METHOD iref_document-&gt;release_document\n*  EXPORTING\n*    no_flush = ' '\n    IMPORTING\n      error = iref_error\n*     retcode  =\n    .\n  IF iref_error-&gt;has_failed = 'X'.\n    CALL METHOD iref_error-&gt;raise_message\n      EXPORTING\n        type = 'I'.\n    LEAVE LIST-PROCESSING.\n  ENDIF.\n\n*---------------------------------------------------------------------*\n*      Form  SUB_FILE_F4\n*---------------------------------------------------------------------*\n*       F4 help for file path\n*----------------------------------------------------------------------*\nFORM sub_file_f4 .\n  DATA:\n    l_desktop  TYPE string,\n    l_i_files  TYPE filetable,\n    l_wa_files TYPE file_table,\n    l_rcode    TYPE int4.\n\n* Finding desktop\n  CALL METHOD cl_gui_frontend_services=&gt;get_desktop_directory\n    CHANGING\n      desktop_directory    = l_desktop\n    EXCEPTIONS\n      cntl_error           = 1\n      error_no_gui         = 2\n      not_supported_by_gui = 3\n      OTHERS               = 4.\n  IF sy-subrc &lt;&gt; 0.\n    MESSAGE e001(00) WITH\n        'Desktop not found'.\n  ENDIF.\n\n* Update View\n  CALL METHOD cl_gui_cfw=&gt;update_view\n    EXCEPTIONS\n      cntl_system_error = 1\n      cntl_error        = 2\n      OTHERS            = 3.\n\n  CALL METHOD cl_gui_frontend_services=&gt;file_open_dialog\n    EXPORTING\n      window_title            = 'Select Excel file'\n      default_extension       = '.xls'\n*     default_filename        =\n      file_filter             = '.xls'\n*     with_encoding           =\n      initial_directory       = l_desktop\n*     multiselection          =\n    CHANGING\n      file_table              = l_i_files\n      rc                      = l_rcode\n*     user_action             =\n*     file_encoding           =\n    EXCEPTIONS\n      file_open_dialog_failed = 1\n      cntl_error              = 2\n      error_no_gui            = 3\n      not_supported_by_gui    = 4\n      OTHERS                  = 5.\n  IF sy-subrc &lt;&gt; 0.\n    MESSAGE e001(00) WITH 'Error while opening file'.\n  ENDIF.\n\n  READ TABLE l_i_files INDEX 1 INTO l_wa_files.\n  IF sy-subrc = 0.\n    p_file = l_wa_files-filename.\n  ELSE.\n    MESSAGE e001(00) WITH 'Error while opening file'.\n  ENDIF.\n\nENDFORM.                    &quot; SUB_FILE_F4<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Program ini dibuat untuk keperluan membaca semua data pada beberapa sheet yang ada di dalam file excel. Berikut contoh program untuk membaca file excel yang terdiri dari beberapa sheet<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/posts\/30"}],"collection":[{"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":5,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"predecessor-version":[{"id":72,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/posts\/30\/revisions\/72"}],"wp:attachment":[{"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sapabap.dwimansolution.com\/index.php\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}