Solved: Read multiple excel sheets using dataset...

aris_hidalgo · ‎2009 Sep 22

Hello Experts,

I've searched the forums and I cannot find any suitable threads for regarding my question. Anyway, I wanto to read and .XLS file residing in the application server using the DATASET commands.However, I am having a shortdump saying "An exception with the type CX_SY_CONVERSION_CODEPAGE".

venkat_o · ‎2009 Sep 22

Hi,

Try to check this link. It will be helpful.

Thanks

Venkat.O

Former Member · ‎2009 Sep 22

Hi,

Try using the open dataset like this,


OPEN DATASET  filename for input IGNORING CONVERSION ERRORS.

Regards,

Vikranth

aris_hidalgo · ‎2009 Sep 22

Hi Vikrnath,

I followed what you said but when I checked it it has a lof of '#' values? this is an XLS file.

Former Member · ‎2009 Sep 22

Hi,

is nothing but Horizontal tab. After reading the dataset into a internal table, use this



loop at it_file.
replace all occurences of CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB in it_file with space.
modify it_file.
endloop.

Regards,

Vikranth

venkat_o · ‎2009 Sep 22

Hi,

Try to check this link. It will be helpful.

Thanks

Venkat.O

venkat_o · ‎2009 Sep 22

Hi, Try this way to download excel file from application server


 REPORT ztest_program.
DATA: BEGIN OF it_t001 OCCURS 0,
        bukrs TYPE t001-bukrs,
        butxt TYPE t001-butxt,
        ort01 TYPE t001-ort01,
        land1 TYPE t001-land1,
        waers TYPE t001-waers,
        spras TYPE t001-spras,
        ktopl TYPE t001-ktopl,
        waabw TYPE t001-waabw,
      END OF it_t001.
DATA: BEGIN OF it_app_file OCCURS 0,
        data TYPE string,
      END OF it_app_file.
CLASS cl_abap_char_utilities DEFINITION LOAD.
CONSTANTS:hor_tab  TYPE c VALUE cl_abap_char_utilities=>horizontal_tab.
PARAMETERS : p_file(150) TYPE c.
START-OF-SELECTION.
  "Open file for reading
  OPEN DATASET p_file FOR INPUT IN TEXT MODE ENCODING DEFAULT.
  IF sy-subrc EQ 0.
    DO.
      READ DATASET p_file INTO it_app_file-data.
      IF it_app_file IS INITIAL.
        "If file ends or if does not have data
        EXIT.
      ELSE.
        "Splitting TAB delimited data into into internal table
        SPLIT it_app_file-data AT hor_tab INTO it_t001-bukrs
                                               it_t001-butxt
                                               it_t001-ort01
                                               it_t001-land1
                                               it_t001-waers
                                               it_t001-spras
                                               it_t001-ktopl
                                               it_t001-waabw.
        APPEND it_t001.
        CLEAR  it_t001.
      ENDIF.
    ENDDO.
  ENDIF.
  "close file
  CLOSE DATASET p_file.
  LOOP AT it_t001.
    WRITE:/ it_t001-bukrs,
            it_t001-butxt,
            it_t001-ort01,
            it_t001-land1,
            it_t001-waers,
            it_t001-spras,
            it_t001-ktopl,
            it_t001-waabw.
  ENDLOOP.

Thanks Venkat.O

aris_hidalgo · ‎2009 Sep 22

Hi again,

I tried using command 'REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab IN lw_string_itab WITH space' but the '#' is still not replaced.

Former Member · ‎2009 Sep 22

Hi,

Can you post the code you tried with?

aris_hidalgo · ‎2009 Sep 22

Here it is:

OPEN DATASET im_filename FOR INPUT IGNORING CONVERSION ERRORS IN TEXT MODE ENCODING DEFAULT.
DO.
            READ DATASET im_filename INTO lw_string_itab.
            IF sy-subrc <> 0.
              EXIT.
            ELSE.
              ADD 1 TO lv_row.
*             Do not process rows less than the defined start row
              IF lv_row < im_start_row.
                CONTINUE.
              ENDIF.

              REFRESH lt_split_itab.

              REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab WITH space.

*             Split values by delimiter
              SPLIT lw_string_itab
                 AT im_delimiter
               INTO TABLE lt_split_itab.

              IF sy-subrc = 0.
                LOOP AT lt_split_itab INTO lw_split_itab.
                  CLEAR lw_fieldcat.
                  READ TABLE lt_fieldcat INTO lw_fieldcat INDEX sy-tabix.
                  IF sy-subrc = 0.
                    ASSIGN COMPONENT lw_fieldcat-fieldname OF STRUCTURE <fs_dyn_wa> TO <fs_field>.
                    IF <fs_field> IS ASSIGNED.
                      <fs_field> = lw_split_itab.
                    ENDIF.
                  ENDIF.
                ENDLOOP.
              ENDIF.

              APPEND <fs_dyn_wa> TO <fs_dyn_table>.
              FREE <fs_dyn_wa>.
            ENDIF.
          ENDDO.

venkat_o · ‎2009 Sep 22

Hi, Try to look into this .


      REPLACE ALL OCCURRENCES OF CL_ABAP_CHAR_UTILITIES=>HORIZONTAL_TAB
                              IN LW_STRING_ITAB WITH IM_DELIMITER."SPACE. instead of space i have added IM_DELIMITER

Thanks Venkat.O

aris_hidalgo · ‎2009 Sep 22

Hi,

It is still the same.

Former Member · ‎2009 Sep 22

Hi,

I suppose lw_string_itab is a internal table. If it is so you will have to loop at it and then try replacing



loop at lw_string_itab.
REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab-field1 WITH space.                      " Mention the particular field 
modify  lw_string_itab.
endloop.

Regards,

Vikranth

Former Member · ‎2009 Sep 22

Hii,

Can u please try the below statement.

REPLACE ALL OCCURRENCES OF '#' in test WITH space.

Thanks,

Ravindra.

venkat_o · ‎2009 Sep 22

Hi,

May I know the declaration of lw_string_itab structure.

Thanks

Venkat.O

venkat_o · ‎2009 Sep 22

Hi, As I have given example code above,


        SPLIT it_app_file-data AT hor_tab INTO it_t001-bukrs
                                               it_t001-butxt
                                               it_t001-ort01
                                               it_t001-land1
                                               it_t001-waers
                                               it_t001-spras
                                               it_t001-ktopl
                                               it_t001-waabw.

"Here I have given  it_app_file-data

You need use your structure field in the below places.

              REPLACE ALL OCCURRENCES OF cl_abap_char_utilities=>horizontal_tab
                                      IN lw_string_itab-data WITH im_delimiter. "lw_string_itab-data
 
*             Split values by delimiter
              SPLIT lw_string_itab-data   "lw_string_itab-data
                 AT im_delimiter
               INTO TABLE lt_split_itab.

I hope that you are following. Thanks Venkat.O

aris_hidalgo · ‎2009 Sep 22

Hi,

It is a type table of string.

Former Member · ‎2009 Sep 22

Continue.....


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
             .
    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     =
      .

Former Member · ‎2009 Sep 22

Continue.......


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.

Former Member · ‎2009 Sep 22

Example


REPORT  ZREAD_MULTIPLESHEETS_EXCEL.
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
       container_name              = 'CONT'
    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
       inplace_enabled          = 'X'
       r3_application_name      = 'EXCEL CONTAINER'
       parent                   = oref_container
    IMPORTING
       error                    = iref_error
    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_type      = soi_doctype_excel_sheet
     IMPORTING
       document_proxy     = iref_document
       error              = iref_error .
  IF iref_error->has_failed = 'X'.
    CALL METHOD iref_error->raise_message
      EXPORTING
        type = 'E'.
  ENDIF.

Edited by: krupa jani on Sep 22, 2009 12:17 PM

Former Member · ‎2009 Sep 22

continue........


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.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My Account

My Account

Read multiple excel sheets using dataset...