SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Upload from Excel to Internal table (different functions)



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Tue Dec 13, 2016 4:30 pm    Post subject: Upload from Excel to Internal table (different functions) Reply with quote

Code:
*&---------------------------------------------------------------------*
*& Report  ZUPLOAD_XLS
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZUPLOAD_XLS LINE-SIZE 512..

TYPES:
  tv_data(256)  TYPE c,

  BEGIN OF ts_data,
    value_0001 TYPE tv_data,
    value_0002 TYPE tv_data,
    value_0003 TYPE tv_data,
    value_0004 TYPE tv_data,
    value_0005 TYPE tv_data,
    value_0006 TYPE tv_data,
    value_0007 TYPE tv_data,
    value_0008 TYPE tv_data,
    value_0009 TYPE tv_data,
    value_0010 TYPE tv_data,
    value_0011 TYPE tv_data,
    value_0012 TYPE tv_data,
    value_0013 TYPE tv_data,
    value_0014 TYPE tv_data,
    value_0015 TYPE tv_data,
    value_0016 TYPE tv_data,
    value_0017 TYPE tv_data,
    value_0018 TYPE tv_data,
    value_0019 TYPE tv_data,
    value_0020 TYPE tv_data,
  END OF ts_data,

  tt_data     TYPE TABLE OF ts_data,
  tv_index(4) TYPE n.

CONSTANTS:
  co_max_col TYPE i VALUE 20,
  co_max_row TYPE i VALUE 9999.

*&---------------------------------------------------------------------*
*&      SELECTION-SCREEN
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_fname FOR FIELD pa_fname.
PARAMETERS pa_fname TYPE localfile OBLIGATORY LOWER CASE MEMORY ID fnm.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (83) tx_read0.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read1 FOR FIELD pa_read1.
PARAMETERS pa_read1 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read2 FOR FIELD pa_read2.
PARAMETERS pa_read2 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read3 FOR FIELD pa_read3.
PARAMETERS pa_read3 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read4 FOR FIELD pa_read4.
PARAMETERS pa_read4 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read5 FOR FIELD pa_read5.
PARAMETERS pa_read5 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT (33) tx_read6 FOR FIELD pa_read6.
PARAMETERS pa_read6 RADIOBUTTON GROUP rad1.
SELECTION-SCREEN END OF LINE.

AT SELECTION-SCREEN OUTPUT.
  tx_fname = 'Path and name of Excel to open'.
  tx_read0 = 'Function:'.
  tx_read1 = 'ALSM_EXCEL_TO_INTERNAL_TABLE'.
  tx_read2 = 'FILE_READ_AND_CONVERT_SAP_DATA'.
  tx_read3 = 'IMPORT_FROM_SPREADSHEET'.
  tx_read4 = 'KCD_EXCEL_OLE_TO_INT_CONVERT'.
  tx_read5 = 'TEXT_CONVERT_XLS_TO_SAP'.
  tx_read6 = 'UPLOAD_XLS_FILE_2_ITAB'.

*&---------------------------------------------------------------------*
*&      START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

  DATA:
    lt_data  TYPE tt_data.

  IF pa_read1 = 'X'.
    WRITE: / 'Function ALSM_EXCEL_TO_INTERNAL_TABLE'.
    SKIP.
    PERFORM excel_read1 CHANGING lt_data.

  ELSEIF pa_read2 = 'X'.
    WRITE: / 'Function FILE_READ_AND_CONVERT_SAP_DATA'.
    SKIP.
    PERFORM excel_read2 CHANGING lt_data.

  ELSEIF pa_read3 = 'X'.
    WRITE: / 'Function IMPORT_FROM_SPREADSHEET'.
    SKIP.
    PERFORM excel_read3 CHANGING lt_data.

  ELSEIF pa_read4 = 'X'.
    WRITE: / 'Function KCD_EXCEL_OLE_TO_INT_CONVERT'.
    SKIP.
    PERFORM excel_read4 CHANGING lt_data.

  ELSEIF pa_read5 = 'X'.
    WRITE: / 'Function TEXT_CONVERT_XLS_TO_SAP'.
    SKIP.
    PERFORM excel_read5 CHANGING lt_data.

  ELSEIF pa_read6 = 'X'.
    WRITE: / 'Function UPLOAD_XLS_FILE_2_ITAB'.
    SKIP.
    PERFORM excel_read6 CHANGING lt_data.
  ENDIF.

  PERFORM itab_display USING lt_data.

*&---------------------------------------------------------------------*
*&      Form  excel_read1
*&---------------------------------------------------------------------*
FORM excel_read1 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE STANDARD TABLE OF alsmex_tabline,
    ls_excel TYPE alsmex_tabline,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read1

*&---------------------------------------------------------------------*
*&      Form  excel_read2
*&---------------------------------------------------------------------*
FORM excel_read2 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_fname TYPE filename-fileintern,
    lv_error TYPE string.

  lv_fname = pa_fname.
  CALL FUNCTION 'FILE_READ_AND_CONVERT_SAP_DATA'
    EXPORTING
      i_filename           = lv_fname
      i_servertyp          = 'OLE2'
      i_fileformat         = 'XLS'
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
    TABLES
      i_tab_receiver       = pt_data
    EXCEPTIONS
      file_not_found       = 1
      close_failed         = 2
      authorization_failed = 3
      open_failed          = 4
      conversion_failed    = 5
      OTHERS               = 6.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read2

*&---------------------------------------------------------------------*
*&      Form  excel_read3
*&---------------------------------------------------------------------*
FORM excel_read3 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_url(256) TYPE c,
    ls_range    TYPE soi_dimension_item,
    lt_range    TYPE soi_dimension_table,
    lt_excel    TYPE soi_generic_table,
    ls_excel    TYPE soi_generic_item,
    lv_data     TYPE tv_data,
    lv_col      TYPE tv_index,
    lv_row      TYPE tv_index.

  CONCATENATE 'file://' pa_fname INTO lv_url.

  ls_range-row     = 1.
  ls_range-column  = 1.
  ls_range-rows    = co_max_row.
  ls_range-columns = co_max_col.
  APPEND ls_range TO lt_range.

  CALL FUNCTION 'IMPORT_FROM_SPREADSHEET'
    EXPORTING
      item_url      = lv_url
      document_type = 'Excel.Sheet'
    TABLES
      data_table    = lt_excel
      ranges        = lt_range.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    lv_col = ls_excel-column.
    lv_row = ls_excel-row.
    PERFORM itab_insert_value USING lv_col lv_row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read3

*&---------------------------------------------------------------------*
*&      Form  excel_read4
*&---------------------------------------------------------------------*
FORM excel_read4 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_excel TYPE kcde_intern,
    ls_excel TYPE kcde_intern_struc,
    lv_data  TYPE tv_data,
    lv_error TYPE string.

  CALL FUNCTION 'KCD_EXCEL_OLE_TO_INT_CONVERT'
    EXPORTING
      filename                = pa_fname
      i_begin_col             = 1
      i_begin_row             = 1
      i_end_col               = co_max_col
      i_end_row               = co_max_row
    TABLES
      intern                  = lt_excel
    EXCEPTIONS
      inconsistent_parameters = 1
      upload_ole              = 2
      OTHERS                  = 3.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.

  LOOP AT lt_excel INTO ls_excel.
    lv_data = ls_excel-value.
    PERFORM itab_insert_value USING ls_excel-col ls_excel-row lv_data
                              CHANGING pt_data.
  ENDLOOP.
ENDFORM.                    "excel_read4

*&---------------------------------------------------------------------*
*&      Form  excel_read5
*&---------------------------------------------------------------------*
FORM excel_read5 CHANGING pt_data TYPE tt_data.

  DATA:
    lt_raw_data(4096) TYPE c OCCURS 0,
    lt_data           TYPE STANDARD TABLE OF string,
    lv_error          TYPE string.

  CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
    EXPORTING
*     I_FIELD_SEPERATOR    =
*     I_LINE_HEADER        =
      i_tab_raw_data       = lt_raw_data
      i_filename           = pa_fname
    TABLES
      i_tab_converted_data = pt_data
    EXCEPTIONS
      conversion_failed    = 1
      OTHERS               = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read5

*&---------------------------------------------------------------------*
*&      Form  excel_read6
*&---------------------------------------------------------------------*
FORM excel_read6 CHANGING pt_data TYPE tt_data.

  DATA:
    lv_error          TYPE string.

  CALL FUNCTION 'UPLOAD_XLS_FILE_2_ITAB'
    EXPORTING
      i_filename = pa_fname
    TABLES
      e_itab     = pt_data
    EXCEPTIONS
      file_error = 1
      OTHERS     = 2.

  IF sy-subrc <> 0.
    WRITE: / 'SY-SUBRC = ', sy-subrc.
    MESSAGE ID sy-msgid TYPE 'E' NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4 INTO lv_error.
    WRITE: / lv_error.
    STOP.
  ENDIF.
ENDFORM.                    "excel_read6

*&---------------------------------------------------------------------*
*&      Form  itab_insert_value
*&---------------------------------------------------------------------*
FORM itab_insert_value USING    pi_col   TYPE tv_index
                                pi_row   TYPE tv_index
                                pi_value TYPE tv_data
                       CHANGING pt_data  TYPE tt_data.

  DATA:
    lv_fieldname TYPE string.

  FIELD-SYMBOLS:
    <fs_data>  TYPE ts_data,
    <fs_value> TYPE tv_data.

  CHECK pi_value <> ''.

  WHILE pi_row > lines( pt_data ).
    INSERT INITIAL LINE INTO TABLE pt_data.
  ENDWHILE.

  READ TABLE pt_data ASSIGNING <fs_data> INDEX pi_row.
  ASSERT sy-subrc = 0.
  CONCATENATE 'VALUE_' pi_col INTO lv_fieldname.
  ASSIGN COMPONENT lv_fieldname OF STRUCTURE <fs_data> TO <fs_value>.
  ASSERT sy-subrc = 0.
  <fs_value> = pi_value.
ENDFORM.                    "itab_insert_value

*&---------------------------------------------------------------------*
*&      Form  itab_display
*&---------------------------------------------------------------------*
FORM itab_display USING pt_data  TYPE tt_data.

  DATA:
    ls_data TYPE ts_data,
    lv_value TYPE tv_data.

  LOOP AT pt_data INTO ls_data.

    WRITE / ''. " sy-tabix.
    DO co_max_col TIMES VARYING lv_value FROM ls_data-value_0001
                                         NEXT ls_data-value_0002.
      WRITE: lv_value(10).
    ENDDO.
  ENDLOOP.
ENDFORM.                    "itab_display
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.