Posted: Mon Jan 14, 2008 1:53 pm Post subject: Download to excel
For example i give to you an include of a module pool which download in excell format usin OLE integration Purchase order history.
Code:
REPORT z_download_to_excel.
*----------------------------------------------------------------------
INCLUDE zcs01_mm_repacq_form_excel IF FOUND.
TYPE-POOLS: ole2.
TYPES: fatno TYPE ebeln.
TYPES: fatpo TYPE ebelp.
DATA: i TYPE i,
j TYPE i,
vn_nrotb TYPE i.
DATA: excel TYPE ole2_object,
workbook TYPE ole2_object,
workbooks TYPE ole2_object,
worksheet TYPE ole2_object,
worksheets TYPE ole2_object,
font TYPE ole2_object,
bord TYPE ole2_object,
range TYPE ole2_object,
cell TYPE ole2_object,
cell1 TYPE ole2_object,
cell2 TYPE ole2_object.
DATA: file TYPE filename VALUE 'c:\\1.xls'.
DATA: BEGIN OF i_tab OCCURS 0,
i TYPE i,
END OF i_tab.
DATA: BEGIN OF t_stampa_sint OCCURS 0,
banfn TYPE banfn,
ebeln TYPE ebeln,
ebelp TYPE ebelp,
belnr TYPE belnr,
buzei TYPE buzei,
fatno TYPE fatno,
fatpo TYPE fatpo,
badat TYPE badat,
bedat TYPE bedat,
budat TYPE budat,
bnfpo TYPE bnfpo,
kostl TYPE kostl,
sakto TYPE sakto,
lifnr TYPE lifnr,
matnr TYPE matnr,
txz01 TYPE txz01,
menger TYPE menge,
meinsr TYPE meins,
mengeo TYPE menge,
meinso TYPE meins,
mengee TYPE menge,
meinse TYPE meins,
preis TYPE preis,
dmbtr TYPE dmbtr,
flag_scar TYPE as4flag,
END OF t_stampa_sint.
DATA: t_stampa_ana LIKE t_stampa_sint OCCURS 0 WITH HEADER LINE.
DATA: rda TYPE banfn,
oda TYPE ebeln,
em TYPE belnr,
fat TYPE fatno,
flag_error_qta TYPE as4flag.
*----------------------------------------------------------------------
*1 - MACRO -- " CELLA " -- *
*----------------------------------------------------------------------
*&1 --> row index *
*&2 --> column index *
*&3 --> TRUE FALSE bold or not *
*&4 --> cell value *
*----------------------------------------------------------------------
DEFINE $cella.
call method of worksheet 'CELLS' = cell
exporting
#1 = &1
#2 = &2.
set property of cell 'VALUE' = &4 .
call method of cell 'FONT' = font .
set property of font 'bold' = &3 .
free object font .
free object cell .
END-OF-DEFINITION.
*&---------------------------------------------------------------------
*& Form download_excel
*&---------------------------------------------------------------------
*download in excel format using OLE
*----------------------------------------------------------------------
FORM download_excel.
i = 1.
j = 1.
"Creatin an Excell
CREATE OBJECT excel 'EXCEL.application'.
"Opening file and sheet
SET PROPERTY OF excel 'VISIBLE' = 1. " SALVE E NON APRE IL FILE
vn_nrotb = 1.
SET PROPERTY OF excel 'SHEETSINNEWWORKBOOK' = vn_nrotb.
CALL METHOD OF excel 'WORKBOOKS' = workbooks .
CALL METHOD OF workbooks 'ADD' = workbook .
SET PROPERTY OF excel 'DisplayAlerts' = 0.
CALL METHOD OF workbook 'WORKSHEETS' = worksheets .
" Naming the sheet
CALL METHOD OF worksheets 'ITEM' = worksheet EXPORTING #1 = 1.
SET PROPERTY OF worksheet 'NAME' = 'Document Flush'.
CALL METHOD OF worksheet 'ACTIVATE' .
PERFORM write_excel.
CALL METHOD OF workbook 'SAVEAS' EXPORTING #1 = file .
*&---------------------------------------------------------------------
*& Form write_excel
*&---------------------------------------------------------------------
* Create excel data
*----------------------------------------------------------------------
FORM write_excel.
SUBTRACT 1 FROM i.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 1.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2'.
SET PROPERTY OF bord 'LINESTYLE' = '7'.
SET PROPERTY OF bord 'WEIGHT' = '3'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 9.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1 #2 = 12.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 12.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1
#2 = 15.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i
#2 = 15.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2' .
SET PROPERTY OF bord 'LINESTYLE' = '7' .
SET PROPERTY OF bord 'WEIGHT' = '3' .
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = 1
#2 = 18.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i
#2 = 18.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'BORDERS' = bord EXPORTING #1 = 1.
SET PROPERTY OF bord 'COLOR' = '2'.
SET PROPERTY OF bord 'LINESTYLE' = '7'.
SET PROPERTY OF bord 'WEIGHT' = '3'.
LOOP AT t_stampa_ana WHERE ebeln = t_stampa_sint-ebeln.
PERFORM scar_pos.
ADD 1 TO i.
ENDLOOP.
ADD 1 TO i.
ENDLOOP.
ENDIF.
j = 1.
DATA:i1 TYPE i.
MOVE i TO i1.
PERFORM posizione USING tab_sint-banfn.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i1 #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 17.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ADD 1 TO i.
LOOP AT i_tab.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i_tab-i
#2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i_tab-i
#2 = 16.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'FONT' = font .
SET PROPERTY OF font 'COLORINDEX' = 3.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDLOOP.
$cls i_tab.
ENDLOOP.
ENDFORM. " testata
*&---------------------------------------------------------------------
*& Form posizione
*&---------------------------------------------------------------------
*form per lo scarico delle posizioni
*----------------------------------------------------------------------
FORM posizione USING banfn.
PERFORM stampa_tes_pos.
IF NOT banfn IS INITIAL.
LOOP AT t_stampa_ana WHERE banfn = banfn.
PERFORM scar_pos.
ADD 1 TO i.
ENDLOOP.
ENDIF.
ENDFORM. " posizione
*&---------------------------------------------------------------------
*& Form stampa_tes_pos
*&---------------------------------------------------------------------
*form per la stampa su excel della testata delle posizioni
*----------------------------------------------------------------------
FORM stampa_tes_pos.
$cella i 1 'TRUE' 'Pur. Req. Item'.
$cella i 2 'TRUE' 'Cost center'.
$cella i 3 'TRUE' 'G/L Account.'.
$cella i 4 'TRUE' 'Vendor'.
$cella i 5 'TRUE' 'Material'.
$cella i 6 'TRUE' 'Description'.
$cella i 7 'TRUE' 'PR Quantity'.
$cella i 8 'TRUE' 'Net Amount'.
$cella i 9 'TRUE' 'Pur. Ord. Number'.
$cella i 10 'TRUE' 'Pur. Ord. Item'.
$cella i 11 'TRUE' 'PO quantity' .
$cella i 12 'TRUE' 'Good Receive'.
$cella i 13 'TRUE' 'Item'.
$cella i 14 'TRUE' 'GR Quantity'.
$cella i 15 'TRUE' 'Invoice'.
$cella i 16 'TRUE' 'Inveoice Item'.
$cella i 17 'TRUE' 'Gross Amount'.
ADD 1 TO i.
ENDFORM. " stampa_tes_pos
*&---------------------------------------------------------------------
*& Form EX_RDA_TES
*&---------------------------------------------------------------------
*scrittura e formattazione automatica della testata generale rda
*----------------------------------------------------------------------
FORM ex_rda_tes.
$cella i j 'TRUE' 'Data: '.
$cella i 2 'TRUE' sy-datum.
ADD 1 TO i.
$cella i 1 'TRUE' 'Purchase Requisition'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 1.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDFORM. " EX_RDA_TES
*&---------------------------------------------------------------------
*& Form EX_ODA_TES
*&---------------------------------------------------------------------
*scrittura e formattazione automatica della testata generale oda
*----------------------------------------------------------------------
FORM ex_oda_tes.
$cella i 9 'TRUE' 'Ordine di Acquisto'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 9.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 11.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDFORM. " EX_ODA_TES
*&---------------------------------------------------------------------
*& Form EX_EM_TES
*&---------------------------------------------------------------------
*scrittura e formattazione automatica della testata generale e.m.
*----------------------------------------------------------------------
FORM ex_em_tes.
$cella i 12 'TRUE' 'Entrata merci'.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 12.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 14.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDFORM. " EX_EM_TES
*&---------------------------------------------------------------------
*& Form EX_FAT_TES
*&---------------------------------------------------------------------
*scrittura e formattazione automatica della testata generale fatture
*----------------------------------------------------------------------
FORM ex_fat_tes.
$cella i 15 'TRUE' 'Fattura'.
$cella 1 15 'TRUE' gen_nome.
CALL METHOD OF worksheet 'CELLS' = cell1 EXPORTING #1 = i #2 = 15.
CALL METHOD OF worksheet 'CELLS' = cell2 EXPORTING #1 = i #2 = 17.
CALL METHOD OF worksheet 'RANGE' = range EXPORTING #1 = cell1
#2 = cell2.
CALL METHOD OF range 'AutoFormat' = font EXPORTING #1 = 12.
FREE OBJECT font .
FREE OBJECT range .
FREE OBJECT cell2 .
FREE OBJECT cell1 .
ENDFORM. " EX_FAT_TES
*&---------------------------------------------------------------------
*& Form scar_intes
*&---------------------------------------------------------------------
*form per lo scarico delle intestazioni delle colonne (Generale)
*----------------------------------------------------------------------
FORM scar_intes.
DATA: var1(20) TYPE c.
DATA: var2(20) TYPE c.
IF t_stampa_sint-banfn <> rda.
MOVE t_stampa_sint-banfn TO rda.
CLEAR: var1, var2.
CONCATENATE 'N° :' t_stampa_sint-banfn INTO var1.
$cella i 1 'TRUE' var1.
WRITE t_stampa_sint-badat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 3 'TRUE' var2.
ENDIF.
IF t_stampa_sint-ebeln <> oda.
CLEAR: var1, var2.
MOVE t_stampa_sint-ebeln TO oda.
CONCATENATE 'N° :' t_stampa_sint-ebeln INTO var1.
$cella i 9 'TRUE' var1.
WRITE t_stampa_sint-bedat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 11 'TRUE' var2.
ENDIF.
IF t_stampa_sint-belnr <> em.
CLEAR: var1, var2.
MOVE t_stampa_sint-belnr TO em.
CONCATENATE 'N° :' t_stampa_sint-belnr INTO var1.
$cella i 12 'TRUE' var1.
WRITE t_stampa_sint-budat TO var2 USING EDIT MASK '__.__.____'.
CONCATENATE 'Data :' var2 INTO var2.
$cella i 14 'TRUE' var2.
ENDIF.
IF t_stampa_sint-fatno <> fat.
CLEAR: var1, var2.
MOVE t_stampa_sint-fatno TO fat.
CONCATENATE 'N° :' t_stampa_sint-fatno INTO var1.
$cella i 16 'TRUE' var1.
ENDIF.
ENDFORM. " scar_intes
*&---------------------------------------------------------------------
*& Form scar_pos
*&---------------------------------------------------------------------
*form per lo scarico delle posizioni
*----------------------------------------------------------------------
FORM scar_pos.
DATA: format(17) TYPE c.
CLEAR flag_error_qta.
PERFORM check_qta_rec.
$cella i 1 'FALSE' t_stampa_ana-bnfpo.
$cella i 2 'FALSE' t_stampa_ana-kostl.
$cella i 3 'FALSE' t_stampa_ana-sakto.
$cella i 4 'FALSE' t_stampa_ana-lifnr.
$cella i 5 'FALSE' t_stampa_ana-matnr.
$cella i 6 'FALSE' t_stampa_ana-txz01.
CLEAR format.
WRITE t_stampa_ana-menger TO format UNIT t_stampa_ana-meinsr.
$cella i 7 'FALSE' format.
CLEAR format.
WRITE t_stampa_ana-preis TO format CURRENCY 'EUR'.
$cella i 8 'FALSE' format.
$cella i 9 'FALSE' t_stampa_ana-ebeln.
$cella i 10 'FALSE' t_stampa_ana-ebelp.
CLEAR format.
WRITE t_stampa_ana-mengeo TO format UNIT t_stampa_ana-meinso.
$cella i 11 'FALSE' format.
$cella i 12 'FALSE' t_stampa_ana-belnr.
$cella i 13 'FALSE' t_stampa_ana-buzei.
CLEAR format.
WRITE t_stampa_ana-mengee TO format UNIT t_stampa_ana-meinse.
$cella i 14 'FALSE' format.
$cella i 15 'FALSE' t_stampa_ana-fatno.
$cella i 16 'FALSE' t_stampa_ana-fatpo.
CLEAR format.
WRITE t_stampa_ana-dmbtr TO format CURRENCY 'EUR'.
$cella i 17 'FALSE' format.
IF flag_error_qta = 'X'.
MOVE i TO i_tab-i.
APPEND i_tab.
ENDIF.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
