Ver la Versión Completa : Cargar varias hojas de Excel
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?? :confused:
rbustamante_sap
22/12/11, 19:33:12
Fijate este link http://forums.sdn.sap.com/thread.jspa?threadID=1939252
Saludos
Rodrigo
damlaplata
26/12/11, 19:47:10
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.
www.mundosap.com 2006 - Spain
software crm, crm on demand, software call center, crm act, crm solutions, crm gratis, crm web