Cargar varias hojas de Excel

**rbustamante_sap** · 22/12/2011, 19:33:12

Solucion

Fijate este link http://forums.sdn.sap.com/thread.jspa?threadID=1939252

Saludos

Rodrigo

**damlaplata** · 26/12/2011, 19:47:10

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.

Cargar varias hojas de Excel

Cargar varias hojas de Excel

Comentario

Comentario