Ir al contenido principal

Leer hojas dentro de un XLS - ABAP

Me llego un requerimiento, para leer puntualmente una hoja, la tercera, siempre, guarde, donde se guarde el documento xls, hay varios métodos, pero necesitaba algo que sea independiente de alguna copia Z de una función, les paso el código y la fuente.

*&---------------------------------------------------------------------*
*& Report  ZTEST_SOURAV_EXCEL
*&
*&---------------------------------------------------------------------*
*& Sourav Bhaduri 02-Dec-2008
*&---------------------------------------------------------------------*
 
REPORT  ztest_sourav_excel NO STANDARD PAGE HEADING.
 
DATA:
oref_container   TYPE REF TO cl_gui_custom_container,
iref_control     TYPE REF TO i_oi_container_control,
iref_document    TYPE REF TO i_oi_document_proxy,
iref_spreadsheet TYPE REF TO i_oi_spreadsheet,
iref_error       TYPE REF TO i_oi_error.
 
DATA:
v_document_url TYPE c LENGTH 256,
i_sheets TYPE soi_sheets_table,
wa_sheets TYPE soi_sheets,
i_data        TYPE soi_generic_table,
wa_data       TYPE soi_generic_item,
i_ranges      TYPE soi_range_list.
 
PARAMETERS:
p_file  TYPE  localfile OBLIGATORY,
p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)
p_cols TYPE i DEFAULT 10 OBLIGATORY.    "Columns (Maximum 256)
 
INITIALIZATION.
 
  CALL METHOD c_oi_container_control_creator=>get_container_control
     IMPORTING
       control = iref_control
       error   = iref_error
*      retcode =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.
 
 
  CREATE OBJECT oref_container
    EXPORTING
*      parent                      =
       container_name              = 'CONT'
*      style                       =
*      lifetime                    = lifetime_default
*      repid                       =
*      dynnr                       =
*      no_autodef_progid_dynnr     =
    EXCEPTIONS
       cntl_error                  = 1
       cntl_system_error           = 2
       create_error                = 3
       lifetime_error              = 4
       lifetime_dynpro_dynpro_link = 5
       OTHERS                      = 6.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'Error while creating container'.
  ENDIF.
 
  CALL METHOD iref_control->init_control
    EXPORTING
*      dynpro_nr                = SY-DYNNR
*      gui_container            = ' '
       inplace_enabled          = 'X'
*      inplace_mode             = 0
*      inplace_resize_documents = ' '
*      inplace_scroll_documents = ' '
*      inplace_show_toolbars    = 'X'
*      no_flush                 = ' '
*      parent_id                = cl_gui_cfw=>dynpro_0
       r3_application_name      = 'EXCEL CONTAINER'
*      register_on_close_event  = ' '
*      register_on_custom_event = ' '
*      rep_id                   = SY-REPID
*      shell_style              = 1384185856
       parent                   = oref_container
*      name                     =
*      autoalign                = 'x'
    IMPORTING
       error                    = iref_error
*      retcode                  =
    EXCEPTIONS
       javabeannotsupported     = 1
       OTHERS                   = 2
          .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.
 
  CALL METHOD iref_control->get_document_proxy
    EXPORTING
*     document_format    = 'NATIVE'
      document_type      = soi_doctype_excel_sheet
*     no_flush           = ' '
*     register_container = ' '
     IMPORTING
       document_proxy     = iref_document
       error              = iref_error
*    retcode            =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.
 
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
 
* To provide F4 help for the file
  PERFORM sub_file_f4.
 
START-OF-SELECTION.
 
  CONCATENATE 'FILE://' p_file INTO v_document_url.
 
  CALL METHOD iref_document->open_document
    EXPORTING
      document_title   = 'Excel'
      document_url     = v_document_url
*     no_flush         = ' '
      open_inplace     = 'X'
*     open_readonly    = ' '
*     protect_document = ' '
*     onsave_macro     = ' '
*     startup_macro    = ''
*     user_info        =
    IMPORTING
      error            = iref_error
*     retcode          =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
 
  CALL METHOD iref_document->get_spreadsheet_interface
     EXPORTING
       no_flush        = ' '
    IMPORTING
      error           = iref_error
      sheet_interface = iref_spreadsheet
*     retcode         =
      .
 
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
 
  CALL METHOD iref_spreadsheet->get_sheets
      EXPORTING
       no_flush = ' '
*      updating = -1
     IMPORTING
       sheets   = i_sheets
       error    = iref_error
*      retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  LOOP AT i_sheets INTO wa_sheets.
    CALL METHOD iref_spreadsheet->select_sheet
       EXPORTING
          name     = wa_sheets-sheet_name
*         no_flush = ' '
      IMPORTING
          error    = iref_error
*         retcode  =
            .
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.
    CALL METHOD iref_spreadsheet->set_selection
      EXPORTING
        top     = 1
        left    = 1
        rows    = p_rows
        columns = p_cols.
 
    CALL METHOD iref_spreadsheet->insert_range
      EXPORTING
        name     = 'Test'
        rows     = p_rows
        columns  = p_cols
        no_flush = ''
      IMPORTING
        error    = iref_error.
    IF iref_error->has_failed = 'X'.
      EXIT.
*      call method iref_error->raise_message
*        exporting
*          type = 'E'.
    ENDIF.
 
    REFRESH i_data.
 
    CALL METHOD iref_spreadsheet->get_ranges_data
       EXPORTING
*        no_flush  = ' '
         all       = 'X'
*        updating  = -1
*        rangesdef =
       IMPORTING
         contents  = i_data
         error     = iref_error
*        retcode   =
       CHANGING
         ranges    = i_ranges
  
* Remove ranges not to be processed else the data keeps on adding up
        call method iref_spreadsheet->delete_ranges
          exporting
            ranges = i_ranges
 
 
             .
    DELETE i_data WHERE value IS INITIAL OR value = space.
    ULINE.
    WRITE:/1 wa_sheets-sheet_name COLOR 3.
    ULINE.
 
    LOOP AT i_data INTO wa_data.
      WRITE:(50) wa_data-value.
      AT END OF row.
        NEW-LINE.
      ENDAT.
    ENDLOOP.
  ENDLOOP.
 
  CALL METHOD iref_document->close_document
*  EXPORTING
*    do_save     = ' '
*    no_flush    = ' '
     IMPORTING
       error       = iref_error
*    has_changed =
*    retcode     =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
  CALL METHOD iref_document->release_document
*  EXPORTING
*    no_flush = ' '
     IMPORTING
       error    = iref_error
*    retcode  =
      .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'I'.
    LEAVE LIST-PROCESSING.
  ENDIF.
 
*&---------------------------------------------------------------------*
*&      Form  SUB_FILE_F4
*&---------------------------------------------------------------------*
*       F4 help for file path
*----------------------------------------------------------------------*
FORM sub_file_f4 .
  DATA:
  l_desktop       TYPE string,
  l_i_files       TYPE filetable,
  l_wa_files      TYPE file_table,
  l_rcode         TYPE int4.
 
* Finding desktop
  CALL METHOD cl_gui_frontend_services=>get_desktop_directory
    CHANGING
      desktop_directory    = l_desktop
    EXCEPTIONS
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      OTHERS               = 4.
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH
        'Desktop not found'.
  ENDIF.
 
* Update View
  CALL METHOD cl_gui_cfw=>update_view
    EXCEPTIONS
      cntl_system_error = 1
      cntl_error        = 2
      OTHERS            = 3.
 
  CALL METHOD cl_gui_frontend_services=>file_open_dialog
     EXPORTING
       window_title            = 'Select Excel file'
       default_extension       = '.xls'
*      default_filename        =
       file_filter             = '.xls'
*      with_encoding           =
       initial_directory       = l_desktop
*      multiselection          =
    CHANGING
      file_table              = l_i_files
      rc                      = l_rcode
*     user_action             =
*     file_encoding           =
    EXCEPTIONS
      file_open_dialog_failed = 1
      cntl_error              = 2
      error_no_gui            = 3
      not_supported_by_gui    = 4
      OTHERS                  = 5
          .
  IF sy-subrc <> 0.
    MESSAGE e001(00) WITH 'Error while opening file'.
  ENDIF.
 
  READ TABLE l_i_files INDEX 1 INTO l_wa_files.
  IF sy-subrc = 0.
    p_file = l_wa_files-filename.
  ELSE.
    MESSAGE e001(00) WITH 'Error while opening file'.
  ENDIF.
 
ENDFORM.                    " SUB_FILE_F4
The test data:
  
The selection screen:
The output:
Fuente:

Otras soluciones interesantes:

Etiquetas:

Comentarios

Publicar un comentario

Entradas más populares de este blog

A to Z of OLE Excel in ABAP 7.4

  SAP users, both business and end users always need to download the output of a report to spreadsheet and do their analytics.   The standard excel output from a report is very simple process but it is old fashioned and the spreadsheet looks quite boring.  There is no default formatting and the users have to do all the hard work of changing the fonts, coloring the texts, marking the borders etc. Updated 16th Aug 2019  –  If you are working in non-ABAP 7.4 (below), there is complete reference program for you too. Go to the end of this article.  Thank you  Legxis  ( LeonievonK ) for the share. I acknowledge, whatever I mentioned above can be achieved in many ways programmatically. We can do it in the old traditional ABAP way but  providing multiple tabs in the spreadsheet and formatting is quite tricky with non  OLE  method. OLE = Object Linking and Embedding The high level agenda of this article is to be the  G.O.A.T.  (please google it if you do not know the full form)  of OLE Excel Gu
Publicar un comentario
Leer más
How to add custom field in Additional B Tab for SAP Sales Order In this article we want to explain step by step how to add custom field in SAP Sales Order transaction VA01/VA02/VA03. This time Mr ABAPGurus will give the tutorial of enhancement to add custom field in SAP Sales order. In this sample we will add new custom field for comments and customer satisfaction, this data will save into separate table from SAP Standard table ( VBAK / VBAP ). SAP provided us with ADDITIONAL TAB in the sales order transaction ( VA01/VA02/VA03 ) which allow customer to add custom fields.  T he different between ADDITIONAL A and ADDITIONAL B is the ADDITIONAL A for field which already predefine in Sales Order Header ( VBAK ) fields and ADDITIONAL B is for field that freely define. This sample we will use ADDITIONAL B because we will store data from customer satisfaction into Z database table. 1. Create one Z table using SE11 SAP Transaction code. 2.Using SE38 Transaction code and ope
Publicar un comentario
Leer más

VOFM

Creating VOFM custom routine, functionality same as SAP standard routine 103 with Additional Functionality 1.    Introduction 1.1   Document Purpose 1.2    This is the document helps you to create custom routine for sales order for below specific requirement: Functional description: In case of sale order with the SD document category ‘K’ or ‘L’ or ‘H’ related to an invoice, we need to fill the fixed value date (VBKD_VALDT) with the billing reference date (VBRK_FKDAT). For example: Invoice 90001111, the billing date is 22.07.2013 If we create a sale order related to this invoice, we want the fixed value date equal to the billing reference date. As per the requirement: Sap standard routine ‘103’ triggers whenever sales order is being created with reference. Sap standard routine is configured in VOFM transaction as: Sap standard routine ‘103’ has the functionality of copying the billing document header data and billing document line item data into the
Publicar un comentario
Leer más