Cargar varias hojas de Excel

[Seraph]

Buenas tardes a todos,

Quisiera saber si alguno me puede ayudar con el siguiente tema: estoy tratando de cargar un archivo excel de varias hojas. Las soluciones que he encontrado son para programación orientada a objetos (con metodos y clases...), pero mi programa Z es estructurado y no conozco como programar orientado a objetos en ABAP. Tengo una función que me sirve solo para cargar la primera hoja pero necesito consultar las otras.

Alguno tiene una solución??

[rbustamante_sap]

Fijate este link

Saludos

Rodrigo

[damlaplata]

Que tal macho...te paso este codigo que va como piña!! funca, lo use y me sirvio, tenes que hacer tus modificaciones, es objetos, lamentablemente vas a tener que familiarizarte con objetos, porque tiene muchas potencialidades y se viene....

*&---------------------------------------------------------------------*
*& Report Z_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT Z_EXCEL LINE-SIZE 150.
*&---------------------------------------------------------------------*

*& Include Z_EXCEL_TOP Report Z_EXCEL

*&

*&---------------------------------------------------------------------*

************************************************************************

* TYPE-POOLS

************************************************************************

* INCLUDE NECESARIO

INCLUDE ole2incl.

************************************************************************

* TYPES

************************************************************************

TYPES: ty_sflig TYPE sflight,

ty_spfli TYPE spfli,

ty_sbook TYPE sbook,

ty_t_sflig TYPE STANDARD TABLE OF ty_sflig,

ty_t_spfli TYPE STANDARD TABLE OF ty_spfli,

ty_t_sbook TYPE STANDARD TABLE OF ty_sbook.



************************************************************************
* DATA
************************************************************************

* TABLAS INTERNAS PARA CADA HOJA

DATA: it_sflig TYPE ty_t_sflig,

it_spfli TYPE ty_t_spfli,

it_sbook TYPE ty_t_sbook.



* VARIABLES PARA CREAR EL ARCHIVO EXCEL

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

cells TYPE ole2_object,

int TYPE ole2_object,

font TYPE ole2_object,

we TYPE ole2_object,

borders TYPE ole2_object.



DATA: index TYPE i,

lineas TYPE i.



* CONSTANTS

CONSTANTS: row_max TYPE i VALUE 256.



************************************************************************

* SELECTION-SCREEN

************************************************************************

PARAMETERS: p_conn TYPE s_conn_id DEFAULT '0017' OBLIGATORY,

p_file TYPE rlgrap-filename

DEFAULT 'C: /TABLAS.XLS' OBLIGATORY.






************************************************************************

* START-OF-SELECTION

************************************************************************

START-OF-SELECTION.

************************************************************************

* Recolección de datos en tres tablas internas *

************************************************************************

PERFORM f_querys USING p_conn

CHANGING it_sflig

it_spfli

it_sbook.

************************************************************************

* END-OF-SELECTION

************************************************************************

END-OF-SELECTION.

************************************************************************

* Generación del Libro Excel con tres hojas *

************************************************************************

PERFORM f_hojas_excel USING p_file

it_sflig

it_spfli

it_sbook.





*&---------------------------------------------------------------------*

*& Include Z_EXCEL_F01

*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*

*& Form f_querys

*&---------------------------------------------------------------------*

FORM f_querys USING p_conn TYPE s_conn_id

CHANGING pt_sflig TYPE ty_t_sflig

pt_spfli TYPE ty_t_spfli

pt_sbook TYPE ty_t_sbook.

SELECT *

INTO TABLE pt_sflig

FROM sflight

WHERE connid EQ p_conn

.

IF sy-subrc <> 0.

MESSAGE e888(sabapdocu) WITH 'error 1'.

ENDIF.



SELECT *

INTO TABLE pt_spfli

FROM spfli

WHERE connid EQ p_conn

.

IF sy-subrc <> 0.

MESSAGE e888(sabapdocu) WITH 'error 2'.

ENDIF.



SELECT *

INTO TABLE pt_sbook

FROM sbook UP TO 50 ROWS

WHERE connid EQ p_conn

.

IF sy-subrc <> 0.

MESSAGE e888(sabapdocu) WITH 'error 3'.

ENDIF.



ENDFORM. " f_querys

*&---------------------------------------------------------------------*

*& Form f_hojas_excel

*&---------------------------------------------------------------------*

FORM f_hojas_excel USING pv_file TYPE rlgrap-filename

pt_sflig TYPE ty_t_sflig

pt_spfli TYPE ty_t_spfli

pt_sbook TYPE ty_t_sbook.



DATA: pw_sflig TYPE ty_sflig,

pw_spfli TYPE ty_spfli,

pw_sbook TYPE ty_sbook.



* CREA EL OBJETO EXCEL

CREATE OBJECT application 'excel.application'.



* HACE VISIBLE LA APLICACION

SET PROPERTY OF application 'visible' = 1.



* LLAMA AL METODO WORKSBOOKS PARA CREAR EL LIBRO

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Add'.



* AGREGA UNA HOJA

CALL METHOD OF application 'Worksheets' = sheet.

CALL METHOD OF sheet 'Add'.

CLEAR lineas.

DESCRIBE TABLE pt_sflig LINES lineas.



IF lineas <> 0.

* CREA LA PRIMER HOJA

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 1.



* SETEA NOMBRE DE LA HOJA

SET PROPERTY OF sheet 'Name' = 'Sflight'.

CALL METHOD OF sheet 'Activate'.



* RECORRE LA TABLA INTERNA

LOOP AT pt_sflig INTO pw_sflig.



* POR CADA CAMPO DE LA TABLA INTERNA MUESTRA 4

index = row_max * ( sy-tabix - 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sflig-connid.

***** FORMATO DE CELDA

CALL METHOD OF cells 'FONT' = font.

SET PROPERTY OF font 'BOLD' = 2.

SET PROPERTY OF font 'SIZE' = '12'.

FREE OBJECT font.

***** COLOR DE FONDO

CALL METHOD OF cells 'INTERIOR' = int.

SET PROPERTY OF int 'ColorIndex' = '34'.

SET PROPERTY OF int 'Pattern' = 1.

FREE OBJECT int.

***** BORDES

* izquierdo

CALL METHOD OF cellS 'BORDERS' = borders

EXPORTING

#1 = '1'.

SET PROPERTY OF borders 'LineStyle' = '1'.

SET PROPERTY OF borders 'WEIGHT' = we.

FREE OBJECT borders.

* derecho

CALL METHOD OF cellS 'BORDERS' = borders

EXPORTING

#1 = '2'.

SET PROPERTY OF borders 'LineStyle' = '2'.

SET PROPERTY OF borders 'WEIGHT' = we.

FREE OBJECT borders.

* arriba

CALL METHOD OF cellS 'BORDERS' = borders

EXPORTING

#1 = '3'.

SET PROPERTY OF borders 'LineStyle' = '3'.

SET PROPERTY OF borders 'WEIGHT' = we.

FREE OBJECT borders.

* abajo

CALL METHOD OF cellS 'BORDERS' = borders

EXPORTING

#1 = '4'.

SET PROPERTY OF borders 'LineStyle' = '4'.

SET PROPERTY OF borders 'WEIGHT' = we.

FREE OBJECT borders.



****************************************************************

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sflig-fldate.

***** FORMATO DE CELDA

CALL METHOD OF cells 'FONT' = font.

SET PROPERTY OF font 'BOLD' = 1.

SET PROPERTY OF font 'SIZE' = '10'.

FREE OBJECT font.

***** COLOR DE FONDO

CALL METHOD OF cells 'INTERIOR' = int.

SET PROPERTY OF int 'ColorIndex' = '35'.

SET PROPERTY OF int 'Pattern' = 1.

FREE OBJECT int.



ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sflig-price.

***** FORMATO DE CELDA

CALL METHOD OF cells 'FONT' = font.

SET PROPERTY OF font 'BOLD' = 1.

SET PROPERTY OF font 'SIZE' = '8'.

FREE OBJECT font.

***** COLOR DE FONDO

CALL METHOD OF cells 'INTERIOR' = int.

SET PROPERTY OF int 'ColorIndex' = '36'.

SET PROPERTY OF int 'Pattern' = 1.

FREE OBJECT int.



ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sflig-currency.

***** FORMATO DE CELDA

CALL METHOD OF cells 'FONT' = font.

SET PROPERTY OF font 'BOLD' = 2.

SET PROPERTY OF font 'SIZE' = '10'.

FREE OBJECT font.

***** COLOR DE FONDO

CALL METHOD OF cells 'INTERIOR' = int.

SET PROPERTY OF int 'ColorIndex' = '37'.

SET PROPERTY OF int 'Pattern' = 1.

FREE OBJECT int.



ENDLOOP.

ENDIF.

CLEAR lineas.

DESCRIBE TABLE pt_spfli LINES lineas.

IF lineas <> 0.

* CREA LA SEGUNDA HOJA, SPFLI

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 2.

SET PROPERTY OF sheet 'Name' = 'Spfli'.

CALL METHOD OF sheet 'Activate'.

LOOP AT pt_spfli INTO pw_spfli.

index = row_max * ( sy-tabix - 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_spfli-connid.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_spfli-countryfr.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_spfli-cityfrom.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_spfli-airpfrom.



ENDLOOP.

ENDIF.



* CREA LA TERCER HOJA, SBOOK

CLEAR lineas.

DESCRIBE TABLE pt_sbook LINES lineas.

IF lineas <> 0.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 3.

SET PROPERTY OF sheet 'Name' = 'Sbook'.

CALL METHOD OF sheet 'Activate'.



LOOP AT pt_sbook INTO pw_sbook.



index = row_max * ( sy-tabix - 1 ) + 1.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sbook-connid.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sbook-fldate.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sbook-bookid.

ADD 1 TO index.

CALL METHOD OF sheet 'Cells' = cells

EXPORTING

#1 = index.

SET PROPERTY OF cells 'Value' = pw_sbook-customid.



ENDLOOP.



ENDIF.



* GUARDA EN EL EXCEL

CALL METHOD OF sheet 'SaveAs'

EXPORTING

#1 = pv_file "PATH A DONDE VA A SER GUARDADO

#2 = 1.



* CIERRA LA VENTANA

SET PROPERTY OF application 'visible' = 0.



* LIBERA EL OBJETO

FREE OBJECT application.



ENDFORM. " f_hojas_excel

* FIN


Saludos.