|
Written by Anon.
|
|
Saturday, 20 January 2007 |
Export data from Excel
REPORT ZEXCEL LINE-SIZE 170 LINE-COUNT 58
NO STANDARD PAGE HEADING.
DATA: BEGIN OF TBXLS OCCURS 5,
LINE LIKE SY-TABIX,
COLN TYPE I,
STRING(1024) TYPE C,
END OF TBXLS,
BEGIN OF TABXLS OCCURS 5,
LINEA TYPE I,
CODIGO(10) TYPE C,
NUMLINEA TYPE I,
TEXTO(80),
END OF TABXLS.
DATA: P_NCOLN TYPE I.
* Ole objects declaration
DATA: H_APPL LIKE OBJ_RECORD,
H_WORK LIKE OBJ_RECORD,
H_CELL LIKE OBJ_RECORD.
INCLUDE OLE2INCL.
INCLUDE DOCSINCL.
SELECTION-SCREEN BEGIN OF BLOCK B0 WITH FRAME.
PARAMETERS: P_CAMI LIKE RLGRAP-FILENAME. "Archivo Excel
PARAMETERS: P_NLINE LIKE SY-INDEX. "Numero aproximado de lineas
SELECTION-SCREEN END OF BLOCK B0.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_CAMI.
CALL FUNCTION 'WS_FILENAME_GET'
EXPORTING
DEF_FILENAME = ' '
DEF_PATH = P_CAMI
MASK = ',*.xls.'
MODE = 'O'
TITLE = 'Archivo a importar'
IMPORTING
FILENAME = P_CAMI
EXCEPTIONS
INV_WINSYS = 01
NO_BATCH = 02
SELECTION_CANCEL = 03
SELECTION_ERROR = 04.
INITIALIZATION.
P_CAMI = 'C:\PLANTILLAS.XLS'.
IF P_NLINE IS INITIAL.
P_NLINE = 1000.
ENDIF.
* Numero de columnas
P_NCOLN = 4.
START-OF-SELECTION.
PERFORM PROCESSA_PLANILHA.
PERFORM IMPRIMIR_PLANILHA.
IF SY-SUBRC = 0.
ENDIF.
* Criar remessas de Exporta‡Æo.
END-OF-SELECTION.
*&---------------------------------------------------------------------*
*& Form PROCESSA_PLANILHA
*&---------------------------------------------------------------------*
FORM PROCESSA_PLANILHA.
* Start Excel
IF H_APPL-HEADER = SPACE OR H_APPL-HANDLE = -1.
CREATE OBJECT H_APPL 'EXCEL.APPLICATION'.
IF SY-SUBRC NE 0. MESSAGE I002(SY) WITH SY-MSGLI. ENDIF.
SET PROPERTY OF H_APPL 'VISIBLE' = 0.
ENDIF.
* Open file
CALL METHOD OF H_APPL 'WORKBOOKS' = H_WORK.
CALL METHOD OF H_WORK 'OPEN' EXPORTING #1 = P_CAMI.
* Ler dados da tabela Excel.
PERFORM CAPTURAR_DADOS.
* Release Excel
CALL METHOD OF H_APPL 'QUIT'.
FREE OBJECT H_APPL.
H_APPL-HANDLE = -1.
* Se a primeira linha for comentarios (nome dos campos)
* DELETE tbxls WHERE ( string = space ) OR
* ( line = 1 ).
SORT TBXLS BY LINE COLN.
REFRESH TABXLS.
CLEAR TABXLS.
LOOP AT TBXLS.
AT NEW LINE.
CLEAR TABXLS.
ENDAT.
IF ( TBXLS-COLN = 1 ).
MOVE TBXLS-STRING TO TABXLS-LINEA.
ELSEIF ( TBXLS-COLN = 2 ).
MOVE TBXLS-STRING TO TABXLS-CODIGO.
ELSEIF ( TBXLS-COLN = 3 ).
MOVE TBXLS-STRING TO TABXLS-NUMLINEA.
ELSEIF ( TBXLS-COLN = 4 ).
MOVE TBXLS-STRING TO TABXLS-TEXTO.
ENDIF.
AT END OF LINE.
APPEND TABXLS.
ENDAT.
ENDLOOP.
ENDFORM. " PROCESSA_PLANILHA
*---------------------------------------------------------------------*
* FORM CAPTURAR_DADOS *
*---------------------------------------------------------------------*
* ........ *
*---------------------------------------------------------------------*
FORM CAPTURAR_DADOS.
DATA: EXCEL_LINE LIKE SY-INDEX,
EXCEL_COLN LIKE SY-INDEX,
CELL_VALUE(132) TYPE C.
DO P_NLINE TIMES.
EXCEL_LINE = SY-INDEX.
* Display indicator
DO P_NCOLN TIMES.
EXCEL_COLN = SY-INDEX.
* Get cell value data
CALL METHOD OF H_APPL 'CELLS' = H_CELL
EXPORTING #1 = EXCEL_LINE
#2 = EXCEL_COLN.
GET PROPERTY OF H_CELL 'VALUE' = CELL_VALUE.
CLEAR: TBXLS.
TBXLS-LINE = EXCEL_LINE.
TBXLS-COLN = EXCEL_COLN.
TBXLS-STRING = CELL_VALUE.
APPEND TBXLS.
ENDDO.
ENDDO.
ENDFORM. " Capturar_dados
*&---------------------------------------------------------------------*
*& Form IMPRIMIR_PLANILHA
*&---------------------------------------------------------------------*
FORM IMPRIMIR_PLANILHA.
LOOP AT TABXLS.
WRITE: / TABXLS-LINEA, TABXLS-CODIGO, TABXLS-NUMLINEA, TABXLS-TEXTO.
ENDLOOP.
ENDFORM. " IMPRIMIR_PLANILHA
|
