Categorias

Importar Planilha Excel no SAP

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.

Arquivos para Download:

yo_excel_sample.txt