ABAP Excel in Multiple Sheets
Uploaded by
ScribdTranslationsABAP Excel in Multiple Sheets
Uploaded by
ScribdTranslationsREPORT ztestme.
TYPE-POOLS ole2.
Accounting: Secondary index for debtors
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.
Customer number
v_no TYPE i,
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,
customer number
END OF itab.
DATA: BEGIN OF jtab OCCURS 50,
customer number
year LIKE bsid-year, "Exercise
date LIKE bsid-budat, "Posting date in the document
wrbtr LIKE bsid-wrbtr, "Amount in the document currency
END OF jtab.
SELECT customer_number fiscal_year posting_date amount
INTO (jtab-customer number, jtab-fiscal year, jtab-document date, jtab-amount in document currency)
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 '[Link]'.
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-customer.
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 customer_number = 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'.
END FORM. " 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.
