Upload an Excel File to Internal Table

• 
• 

Details

Category: ABAP

Published on Wednesday, 29 April 2009 16:20

Written by Anon

Hits: 15281

REPORT ZEXCINT LINE-SIZE 330.

DATA: BEGIN OF RECORD OCCURS 0,
          define the internal table with the structure u want.
END OF RECORD.

SELECTION-SCREEN: BEGIN OF BLOCK bl1 WITH FRAME TITLE text-000.

PARAMETER: dataset LIKE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN: END OF BLOCK bl1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR dataset.

CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
def_filename = ' '
def_path = ' '
mask= ' '
mode= '0'
title= 'Archivo a importar'
IMPORTING
filename= dataset
EXCEPTIONS
inv_winsys= 01
no_batch= 02
selection_cancel= 03
selection_error= 04.

START-OF-SELECTION.
REFRESH record.
CLEAR record.
PERFORM call_excel TABLES record USING dataset 2 1 9999 100.

LOOP AT RECORD.
WRITE:/(20) RECORD-STATUS,
(10) RECORD-PODAT ,
(10) RECORD-VDAT ,
(30) RECORD-NARR,
(30) RECORD-TCODE,
(30) RECORD-INPUT,
(30) RECORD-DEBIT,
(30) RECORD-CREDIT,
(40) RECORD-BALANCE.

ENDLOOP.
******************
CONSTANTS: tabu TYPE x VALUE 9.
FIELD-SYMBOLS: <pointer>.
DATA: sep(1).
*&--------------------------------------------------------------------*
*& * * *Form CALL_EXCEL
*&--------------------------------------------------------------------*
* * * *-->ITAB * * * text
* * * *-->VALUE(ARCH)text
* * * *-->VALUE(FILA_text
* * * *-->VALUE(COL_1text
* * * *-->VALUE(FILA_text
*-->VALUE(COL_2text
*---------------------------------------------------------------------*
FORM call_excel TABLES itab
USING value(arch)
value(fila_1)
value(col_1)
value(fila_2)
value(col_2).
DATA: excel_line(4096) OCCURS 10 WITH HEADER LINE.
DATA: app TYPE obj_record,
workbook TYPE obj_record,
worksheet TYPE obj_record,
cell_1  TYPE obj_record,
cell_2 TYPE obj_record,
range TYPE obj_record.

CREATE OBJECT app 'EXCEL.APPLICATION'.
IF sy-subrc NE 0.
" error.
EXIT.
ENDIF.

CALL METHOD OF app 'WORKBOOKS' = workbook.
CALL METHOD OF workbook 'OPEN' * *EXPORTING * * *#1 = arch.

IF sy-subrc NE 0.
WRITE:/ 'Error en excel'.
ENDIF.

GET PROPERTY OF app 'ACTIVESHEET' = worksheet.

CALL METHOD OF worksheet 'Cells' = cell_1
EXPORTING
#1 = fila_1
#2 = col_1.

CALL METHOD OF worksheet 'Cells' = cell_2
EXPORTING
#1 = fila_2
#2 = col_2.

CALL METHOD OF worksheet 'RANGE' = range
EXPORTING
#1 = cell_1
#2 = cell_2.

CALL METHOD OF range 'SELECT'.
CALL METHOD OF range 'COPY'.

CALL FUNCTION 'CLPB_IMPORT'
TABLES
data_tab * = excel_line
EXCEPTIONS
clpb_error = 1
OTHERS * * = 2.
ASSIGN sep TO <pointer> TYPE 'X'.
<pointer> = tabu.

PERFORM delimit_columns TABLES excel_line itab USING sep.

SET PROPERTY OF app 'CutCopyMode' = 0.

CALL METHOD OF app 'QUIT'.
FREE OBJECT app.
ENDFORM. "CALL_EXCEL
*&--------------------------------------------------------------------*
*& * * *Form *DELIMIT_COLUMNS
*&--------------------------------------------------------------------*

FORM delimit_columns TABLES excel_line itab USING sep.

DATA: BEGIN OF xls OCCURS 5,
value(1024),
END OF xls.
DATA: len TYPE i.
LOOP AT excel_line.
SHIFT excel_line LEFT DELETING LEADING sep.
SHIFT excel_line LEFT DELETING LEADING ' '.
CONDENSE excel_line.
len = STRLEN( excel_line ).

IF len EQ 0.
CONTINUE.
ENDIF.

SPLIT excel_line AT sep INTO TABLE xls.
LOOP AT xls.
ASSIGN COMPONENT sy-tabix OF STRUCTURE itab TO <pointer>.
<pointer> = xls-value.
ENDLOOP.
APPEND itab.
CLEAR itab.
REFRESH xls.
Endif.
ENDLOOP.
ENDFORM. "DELIMIT_COLUMNS

• < Prev
• Next >