tate
19/12/07, 11:16:15
Hola, os cuento:
me pasas un excel con datos, y lo recojo en una tabla interna, y lo que kiero hacer ahora es generar automaticamente un batchinput de carga de materiales, por debajo, que el programa solo pida la ruta del fichero (que eso esta echo) y que genere un juego de datos.
Espero haberme explicado.
mi codigo es:
REPORT ZFICHERO_EXCEL_1 .
TABLES: MARA, T179, EKPO, MBEW .
********************* Tabla interna
DATA: BEGIN OF itab OCCURS 0,
matnr LIKE mara-matnr,
mtart LIKE mara-mtart,
meins LIKE mara-meins,
bismt LIKE mara-bismt,
matkl LIKE mara-matkl,
text_es TYPE char70,
text_pt TYPE char70,
extwg LIKE mara-extwg,
prodh LIKE t179-prodh,
bwtar LIKE ekpo-bwtar,
kzkfg LIKE mara-kzkfg,
bklas LIKE mbew-bklas,
bwtar1 LIKE ekpo-bwtar,
bklas1 LIKE mbew-bklas.
DATA: END OF itab.
*********** Tabla interna que tendrá los campos 'formateados'
DATA: BEGIN OF t_vista OCCURS 0,
campo1(18),
campo2(4),
campo3(3),
campo4(18),
campo5(9),
campo6(70),
campo7(70),
campo8(18),
campo9(18),
campo10(10),
campo11(1),
campo12(4),
campo13(10),
campo14(4),
END OF t_vista.
********* Campos para la funcion ALSM_EXCEL_TO_INTERNAL_TABLE
DATA: it_intern TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE,
l_start_col TYPE i VALUE 1,
l_start_row TYPE i VALUE 1,
l_end_col TYPE i VALUE 256,
l_end_row TYPE i VALUE 65536.
DATA l_filename LIKE rlgrap-filename.
*-----------------------------------------------------------------------
* selection-screen
*-----------------------------------------------------------------------
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETER p_nombre LIKE rlgrap-filename default 'c:\da.xls'. "fichero
SELECTION-SCREEN END OF BLOCK b1.
*-----------------------------------------------------------------------
* at selection-screen
*-----------------------------------------------------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_nombre.
******************** match code para el fichero ************************
CALL FUNCTION 'F4_FILENAME' "Captura el fichero
IMPORTING
FILE_NAME = p_nombre.
" F4_FILENAME
*-----------------------------------------------------------------------
* start-of-selection.
*-----------------------------------------------------------------------
START-OF-SELECTION.
PERFORM ALSM_EXCEL. "coger el fichero excel
PERFORM BORRAR_CABECERA. "no cuenta la 1º linea, cabecera
PERFORM RELLENAR_ITAB. "rellana la tabla interna
*BREAK-POINT 'z13k'.
*&---------------------------------------------------------------------*
*& Form ALSM_EXCEL
*&---------------------------------------------------------------------*
FORM ALSM_EXCEL.
l_filename = p_nombre.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = l_filename
i_begin_col = l_start_col
i_begin_row = l_start_row
i_end_col = l_end_col
i_end_row = l_end_row
TABLES
intern = it_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc = 1 OR sy-subrc = 2 OR sy-subrc = 3.
MESSAGE i000(zmm) WITH 'No se pudo cargar el fichero '
p_nombre.
ENDIF.
ENDFORM. " ALSM_EXCEL
*&---------------------------------------------------------------------*
*& Form BORRAR_CABECERA
*&---------------------------------------------------------------------*
FORM BORRAR_CABECERA.
*-----------------------------------------------------------------------
* Si la primera linea no es un registro, y es una titulo de cabecera,
* esa linea no la tendra en cuenta e ira a la segunda linea
*-----------------------------------------------------------------------
DELETE it_intern WHERE ROW = '0001'. "Borrar Titulos (cabeceras)
LOOP AT it_intern.
CASE it_intern-COL.
WHEN 1. T_VISTA-CAMPO1 = it_intern-VALUE.
WHEN 2. T_VISTA-CAMPO2 = it_intern-VALUE.
WHEN 3. T_VISTA-CAMPO3 = it_intern-VALUE.
WHEN 4. T_VISTA-CAMPO4 = it_intern-VALUE.
WHEN 5. T_VISTA-CAMPO5 = it_intern-VALUE.
WHEN 6. T_VISTA-CAMPO6 = it_intern-VALUE.
WHEN 7. T_VISTA-CAMPO7 = it_intern-VALUE.
WHEN 8. T_VISTA-CAMPO8 = it_intern-VALUE.
WHEN 9. T_VISTA-CAMPO9 = it_intern-VALUE.
WHEN 10. T_VISTA-CAMPO10 = it_intern-VALUE.
WHEN 11. T_VISTA-CAMPO11 = it_intern-VALUE.
WHEN 12. T_VISTA-CAMPO12 = it_intern-VALUE.
WHEN 13. T_VISTA-CAMPO13 = it_intern-VALUE.
WHEN 14. T_VISTA-CAMPO14 = it_intern-VALUE.
ENDCASE.
AT END OF ROW.
APPEND T_VISTA.
CLEAR T_VISTA.
ENDAT.
ENDLOOP.
ENDFORM. " BORRAR_CABECERA
*&---------------------------------------------------------------------*
*& Form RELLENAR_ITAB
*&---------------------------------------------------------------------*
FORM RELLENAR_ITAB.
LOOP AT it_intern.
CASE it_intern-col.
WHEN 1.
itab-matnr = it_intern-value.
WHEN 2.
itab-mtart = it_intern-value.
WHEN 3.
if it_intern-value = '#'. "si unidad de medida es igual a '€'
itab-meins = 'EUR'.
else.
itab-meins = it_intern-value.
endif.
WHEN 4.
itab-bismt = it_intern-value.
WHEN 5.
itab-matkl = it_intern-value.
WHEN 6.
itab-text_es = it_intern-value.
WHEN 7.
itab-text_pt = it_intern-value.
WHEN 8.
itab-extwg = it_intern-value.
WHEN 9.
itab-prodh = it_intern-value.
WHEN 10.
itab-bwtar = it_intern-value.
WHEN 11.
itab-kzkfg = it_intern-value.
WHEN 12.
itab-bklas = it_intern-value.
WHEN 13.
itab-bwtar1 = it_intern-value.
WHEN 14.
itab-bklas1 = it_intern-value.
APPEND itab.
CLEAR itab.
ENDCASE.
ENDLOOP.
ENDFORM. " RELLENAR_ITAB
me pasas un excel con datos, y lo recojo en una tabla interna, y lo que kiero hacer ahora es generar automaticamente un batchinput de carga de materiales, por debajo, que el programa solo pida la ruta del fichero (que eso esta echo) y que genere un juego de datos.
Espero haberme explicado.
mi codigo es:
REPORT ZFICHERO_EXCEL_1 .
TABLES: MARA, T179, EKPO, MBEW .
********************* Tabla interna
DATA: BEGIN OF itab OCCURS 0,
matnr LIKE mara-matnr,
mtart LIKE mara-mtart,
meins LIKE mara-meins,
bismt LIKE mara-bismt,
matkl LIKE mara-matkl,
text_es TYPE char70,
text_pt TYPE char70,
extwg LIKE mara-extwg,
prodh LIKE t179-prodh,
bwtar LIKE ekpo-bwtar,
kzkfg LIKE mara-kzkfg,
bklas LIKE mbew-bklas,
bwtar1 LIKE ekpo-bwtar,
bklas1 LIKE mbew-bklas.
DATA: END OF itab.
*********** Tabla interna que tendrá los campos 'formateados'
DATA: BEGIN OF t_vista OCCURS 0,
campo1(18),
campo2(4),
campo3(3),
campo4(18),
campo5(9),
campo6(70),
campo7(70),
campo8(18),
campo9(18),
campo10(10),
campo11(1),
campo12(4),
campo13(10),
campo14(4),
END OF t_vista.
********* Campos para la funcion ALSM_EXCEL_TO_INTERNAL_TABLE
DATA: it_intern TYPE alsmex_tabline OCCURS 0 WITH HEADER LINE,
l_start_col TYPE i VALUE 1,
l_start_row TYPE i VALUE 1,
l_end_col TYPE i VALUE 256,
l_end_row TYPE i VALUE 65536.
DATA l_filename LIKE rlgrap-filename.
*-----------------------------------------------------------------------
* selection-screen
*-----------------------------------------------------------------------
SELECTION-SCREEN BEGIN OF BLOCK b1.
PARAMETER p_nombre LIKE rlgrap-filename default 'c:\da.xls'. "fichero
SELECTION-SCREEN END OF BLOCK b1.
*-----------------------------------------------------------------------
* at selection-screen
*-----------------------------------------------------------------------
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_nombre.
******************** match code para el fichero ************************
CALL FUNCTION 'F4_FILENAME' "Captura el fichero
IMPORTING
FILE_NAME = p_nombre.
" F4_FILENAME
*-----------------------------------------------------------------------
* start-of-selection.
*-----------------------------------------------------------------------
START-OF-SELECTION.
PERFORM ALSM_EXCEL. "coger el fichero excel
PERFORM BORRAR_CABECERA. "no cuenta la 1º linea, cabecera
PERFORM RELLENAR_ITAB. "rellana la tabla interna
*BREAK-POINT 'z13k'.
*&---------------------------------------------------------------------*
*& Form ALSM_EXCEL
*&---------------------------------------------------------------------*
FORM ALSM_EXCEL.
l_filename = p_nombre.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = l_filename
i_begin_col = l_start_col
i_begin_row = l_start_row
i_end_col = l_end_col
i_end_row = l_end_row
TABLES
intern = it_intern
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc = 1 OR sy-subrc = 2 OR sy-subrc = 3.
MESSAGE i000(zmm) WITH 'No se pudo cargar el fichero '
p_nombre.
ENDIF.
ENDFORM. " ALSM_EXCEL
*&---------------------------------------------------------------------*
*& Form BORRAR_CABECERA
*&---------------------------------------------------------------------*
FORM BORRAR_CABECERA.
*-----------------------------------------------------------------------
* Si la primera linea no es un registro, y es una titulo de cabecera,
* esa linea no la tendra en cuenta e ira a la segunda linea
*-----------------------------------------------------------------------
DELETE it_intern WHERE ROW = '0001'. "Borrar Titulos (cabeceras)
LOOP AT it_intern.
CASE it_intern-COL.
WHEN 1. T_VISTA-CAMPO1 = it_intern-VALUE.
WHEN 2. T_VISTA-CAMPO2 = it_intern-VALUE.
WHEN 3. T_VISTA-CAMPO3 = it_intern-VALUE.
WHEN 4. T_VISTA-CAMPO4 = it_intern-VALUE.
WHEN 5. T_VISTA-CAMPO5 = it_intern-VALUE.
WHEN 6. T_VISTA-CAMPO6 = it_intern-VALUE.
WHEN 7. T_VISTA-CAMPO7 = it_intern-VALUE.
WHEN 8. T_VISTA-CAMPO8 = it_intern-VALUE.
WHEN 9. T_VISTA-CAMPO9 = it_intern-VALUE.
WHEN 10. T_VISTA-CAMPO10 = it_intern-VALUE.
WHEN 11. T_VISTA-CAMPO11 = it_intern-VALUE.
WHEN 12. T_VISTA-CAMPO12 = it_intern-VALUE.
WHEN 13. T_VISTA-CAMPO13 = it_intern-VALUE.
WHEN 14. T_VISTA-CAMPO14 = it_intern-VALUE.
ENDCASE.
AT END OF ROW.
APPEND T_VISTA.
CLEAR T_VISTA.
ENDAT.
ENDLOOP.
ENDFORM. " BORRAR_CABECERA
*&---------------------------------------------------------------------*
*& Form RELLENAR_ITAB
*&---------------------------------------------------------------------*
FORM RELLENAR_ITAB.
LOOP AT it_intern.
CASE it_intern-col.
WHEN 1.
itab-matnr = it_intern-value.
WHEN 2.
itab-mtart = it_intern-value.
WHEN 3.
if it_intern-value = '#'. "si unidad de medida es igual a '€'
itab-meins = 'EUR'.
else.
itab-meins = it_intern-value.
endif.
WHEN 4.
itab-bismt = it_intern-value.
WHEN 5.
itab-matkl = it_intern-value.
WHEN 6.
itab-text_es = it_intern-value.
WHEN 7.
itab-text_pt = it_intern-value.
WHEN 8.
itab-extwg = it_intern-value.
WHEN 9.
itab-prodh = it_intern-value.
WHEN 10.
itab-bwtar = it_intern-value.
WHEN 11.
itab-kzkfg = it_intern-value.
WHEN 12.
itab-bklas = it_intern-value.
WHEN 13.
itab-bwtar1 = it_intern-value.
WHEN 14.
itab-bklas1 = it_intern-value.
APPEND itab.
CLEAR itab.
ENDCASE.
ENDLOOP.
ENDFORM. " RELLENAR_ITAB