REPORT ztestme.

 TYPE-POOLS ole2.

TABLES: BSID. "Contabilidad: índice secundario para deudores

SELECT-OPTIONS: s_kunnr FOR bsid-kunnr.

DATA: excel TYPE ole2_object,
       application TYPE ole2_object,
       books TYPE ole2_object,
       book TYPE ole2_object,
       sheet TYPE ole2_object,
       cell TYPE ole2_object,
       column TYPE ole2_object.

DATA: V_KUNNR LIKE BSID-KUNNR, "Nº de cliente
       v_no TYPE i,
       v_ln TYPE i,
       v_tabix LIKE SY-TABIX,
       v_index LIKE sy-index,
       v_sheet(10),
       row TYPE i.

DATA: BEGIN OF itab OCCURS 10,
        INDEX LIKE sy-index,
         KUNNR LIKE BSID-KUNNR, "Nº de cliente
      END OF itab.

DATA: BEGIN OF jtab OCCURS 50,
         KUNNR LIKE BSID-KUNNR, "Nº de cliente
         GJAHR LIKE BSID-GJAHR, "Ejercicio
         BUDAT LIKE BSID-BUDAT, "Fecha de contabilización en el documento
         WRBTR LIKE BSID-WRBTR, "Importe en la moneda del documento
      END OF jtab.

SELECT kunnr gjahr budat wrbtr
  INTO (jtab-kunnr, jtab-gjahr, jtab-budat, jtab-wrbtr)
  FROM bsid
 WHERE bukrs = '0010' 
   AND kunnr IN s_kunnr
  ORDER BY KUNNR.

  IF SY-SUBRC EQ 0.
     itab-kunnr = jtab-kunnr.
    APPEND itab.
    CLEAR itab.
    APPEND jtab.
    CLEAR jtab.
  ENDIF.
ENDSELECT.

DELETE ADJACENT DUPLICATES FROM itab.

LOOP AT itab.
   v_index = v_index + 1.
   itab-index = v_index.
  MODIFY itab TRANSPORTING INDEX.
ENDLOOP.

DELETE itab WHERE INDEX > 3.

IF NOT itab[] IS INITIAL.
   CREATE OBJECT excel 'EXCEL.SHEET'. 
   GET PROPERTY OF excel 'Application'  = application.
   SET PROPERTY OF application 'Visible'  = 1.
  CALL METHOD OF application 'Workbooks'  = books.
  CALL METHOD OF books 'Add'  = book.



  LOOP AT itab.
     v_sheet = itab-kunnr.
     v_no = v_no + 1.  row = 1.
    PERFORM fill_sheet USING v_no v_sheet.
  ENDLOOP.

  FREE OBJECT:  column,  sheet,  book,  books,  application,
                excel NO FLUSH.

  CALL FUNCTION 'FLUSH'. 

ENDIF.

*&---------------------------------------------------------------------*
*& Form FILL_SHEET
*&---------------------------------------------------------------------*
FORM fill_sheet USING v_no v_sheet.

  CALL METHOD OF book 'worksheets'  = sheet NO FLUSH EXPORTING #1 = v_no.
   SET PROPERTY OF sheet 'Name'  = v_sheet no flush.
  LOOP AT jtab WHERE kunnr = v_sheet.
    PERFORM fill_cell USING row 1 jtab-kunnr.
    PERFORM fill_cell USING row 2 jtab-gjahr.
    PERFORM fill_cell USING row 3 jtab-budat.
    PERFORM fill_cell USING row 4 jtab-wrbtr.
     row = row + 1.
  ENDLOOP.

  CALL METHOD OF sheet 'Columns'  = column NO FLUSH.
  FREE OBJECT sheet NO FLUSH.
  CALL METHOD OF column 'Autofit' NO FLUSH.
  FREE OBJECT column NO FLUSH.

  CALL FUNCTION 'FLUSH'. 

ENDFORM. " FILL_SHEET

*&---------------------------------------------------------------------*
*& Form FILL_CELL
*&---------------------------------------------------------------------*
FORM fill_cell USING row col val.

  CALL METHOD OF sheet 'cells'  = cell NO FLUSH
    EXPORTING #1 = row #2 = col.
   SET PROPERTY OF cell 'value'  = val.
  FREE OBJECT cell NO FLUSH.

ENDFORM. " FILL_CELL