SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Download to excel



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1639

PostPosted: Mon Jan 14, 2008 1:53 pm    Post subject: Download to excel Reply with quote

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.

CONSTANTS: gen_nome TYPE char10 VALUE 'gen_nome'.

*----------------------------------------------------------------------
DEFINE $cls.
  refresh &1.
END-OF-DEFINITION.

*----------------------------------------------------------------------
*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 .

  FREE OBJECT worksheet .
  FREE OBJECT worksheets .
  FREE OBJECT workbook .
  FREE OBJECT workbooks .
  FREE OBJECT excel .

ENDFORM. " download_excel

*&---------------------------------------------------------------------
*& Form write_excel
*&---------------------------------------------------------------------
* Create excel data
*----------------------------------------------------------------------
FORM write_excel.

  PERFORM ex_rda_tes.
  PERFORM ex_oda_tes.
  PERFORM ex_em_tes.
  PERFORM ex_fat_tes.

  PERFORM header.

  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'.

  FREE OBJECT bord .
  FREE OBJECT range .
  FREE OBJECT cell2 .
  FREE OBJECT cell1 .

  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' .

  FREE OBJECT bord .
  FREE OBJECT range .
  FREE OBJECT cell2 .
  FREE OBJECT cell1 .

  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' .

  FREE OBJECT bord .
  FREE OBJECT range .
  FREE OBJECT cell2 .
  FREE OBJECT cell1 .

  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' .

  FREE OBJECT bord .
  FREE OBJECT range .
  FREE OBJECT cell2 .
  FREE OBJECT cell1 .

  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'.

  FREE OBJECT bord .
  FREE OBJECT range .
  FREE OBJECT cell1 .
  FREE OBJECT cell2 .

ENDFORM. " scrittura_excel
*&---------------------------------------------------------------------
*& Form Header
*&---------------------------------------------------------------------
*Print Header
*----------------------------------------------------------------------
FORM header.

  DATA: BEGIN OF tab_sint OCCURS 0,
  banfn LIKE eban-banfn,
  END OF tab_sint.

  DATA: n(5) TYPE c.
  DATA: riga(5) TYPE c.
  DATA: per TYPE i.


  LOOP AT t_stampa_sint WHERE flag_scar = 'X'.
    MOVE-CORRESPONDING t_stampa_sint TO tab_sint.
    APPEND tab_sint.
    CLEAR tab_sint.
  ENDLOOP.

  SORT tab_sint.
  DELETE ADJACENT DUPLICATES FROM tab_sint COMPARING ALL FIELDS.

  SORT t_stampa_sint ASCENDING BY banfn ebeln belnr fatno.
  DELETE ADJACENT DUPLICATES FROM t_stampa_sint COMPARING ALL FIELDS.

  DESCRIBE TABLE tab_sint LINES n.

  ADD 10 TO n.
  LOOP AT tab_sint .

    ADD 1 TO riga.
    per = TRUNC( ( ( riga / n ) * 100 ) ).

    CLEAR: rda, oda, em, fat.

    IF NOT tab_sint-banfn IS INITIAL.
      LOOP AT t_stampa_sint WHERE banfn = tab_sint-banfn.

        PERFORM scar_intes .
        ADD 1 TO i.
      ENDLOOP.

    ELSE.

      LOOP AT t_stampa_sint WHERE banfn = tab_sint-banfn
      AND flag_scar = 'X'.

        PERFORM scar_intes.
        PERFORM stampa_tes_pos.
        j = 1.

        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.

ENDFORM. " scar_pos
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
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.