A função tem como objetivo importar dados de uma planilha Excel.
Parametros da Função:
Import-
– nome da planilha
– numero de linhas que deseja importar
– numero de colunas que deseja inportar
Export-
– tabela interna com as informações carregadas da planilha
FUNCTION yo_carga_excel.
*"----------------------------------------------------------------------
*"*"Local interface:
*" IMPORTING
*" REFERENCE(P_CAMI) TYPE CHAR50 localizacao e nome da planilha
*" REFERENCE(P_NCOLN) TYPE INT4 numero de colunas
*" REFERENCE(P_NLINE) TYPE INT4 numero de linhas
*" TABLES
*" P_TBXLS tabela de saida com os dados
*" importados
*"----------------------------------------------------------------------
************************************************************************
* Programa: YO_CARGA_EXCEL
* Objetivo: Importar planilha Excel para Tabela Interna
* Desenv. : Antonio Mosca Junior
* Data : 03/08/2007
************************************************************************
************************************************************************
* Em anexo segue o programa de exemplo para a utizacao da funcao.
************************************************************************
INCLUDE ole2incl.
INCLUDE docsincl.
DATA: BEGIN OF tbxls OCCURS 0,
line LIKE sy-tabix,
coln TYPE i,
string(1024) TYPE c,
END OF tbxls.
DATA: BEGIN OF tb_saida OCCURS 0,
cells(1024) TYPE c,
END OF tb_saida.
* Ole objects declaration
DATA: h_appl LIKE obj_record,
h_work LIKE obj_record,
h_cell LIKE obj_record,
excel_line LIKE sy-index,
excel_coln LIKE sy-index,
cell_value(132) TYPE c.
DATA: wk_count TYPE i.
******** 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.
* se 'VISIBLE' = 1., o programa abre o EXCEL.
* 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.
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.
DO p_nline TIMES.
excel_line = sy-index.
DO p_ncoln TIMES.
excel_coln = sy-index.
READ TABLE tbxls WITH KEY line = excel_line
coln = excel_coln.
CONCATENATE tb_saida-cells
tbxls-string
'|'
INTO tb_saida-cells.
ENDDO.
APPEND tb_saida.
CLEAR tb_saida.
ENDDO.
* Release Excel
CALL METHOD OF h_appl 'QUIT'.
FREE OBJECT h_appl.
h_appl-handle = -1.
* Se a primeira linha for comentários (nome dos campos)
* DELETE tbxls WHERE ( string = space ) OR
* ( line = 1 ).
SORT tbxls BY line coln.
MOVE tb_saida[] TO p_tbxls[].
ENDFUNCTION.
