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

Snippet Creating More than 1 Sheet in a EXCEL Workbook



 
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: Sat Sep 08, 2007 7:26 am    Post subject: Snippet Creating More than 1 Sheet in a EXCEL Workbook Reply with quote

Author: Sudheer Junnuthula

This code will generate 3 Sheets in a Excel .......

Code:
*&---------------------------------------------------------------------*
*& Report ZETA_EXCEL_DOWNLOAD_CLIPBOARD *
*& *
*&---------------------------------------------------------------------*
*& *
*& *
*&---------------------------------------------------------------------*
 
report zeta_excel_download_clipboard .
include ole2incl.

data: w_cell1 type ole2_object,
      w_cell2 type ole2_object.
 
*--- Ole data Declarations

data: h_excel type ole2_object, " Excel object
      h_mapl  type ole2_object, " list of workbooks
      h_map   type ole2_object, " workbook
      h_zl    type ole2_object, " cell
       h_f    type ole2_object, " font
  gs_interior type ole2_object, " Pattern
  worksheet   type ole2_object,
  h_cell      type ole2_object,
  h_cell1     type ole2_object,
  range       type ole2_object,
  h_sheet2    type ole2_object,
  h_sheet3    type ole2_object,
  gs_font     type ole2_object,
  flg_stop(1) type c.
 
*********************************************************************
** Internal table Declaration
*********************************************************************
 
data: begin of t_excel occurs 0,
 
     vkorg(20) type c, "Sales Org
 
     vbtyp(20) type c, "Document Category
 
     auart(20) type c, "Document Type
 
     ernam(20) type c, "Created By
 
     vbeln(20) type c, "Document Number
 
     posnr(20) type c, "Item Number
 
     erdat(20) type c, "Created Date
 
     vdatu(20) type c, "Header Requested Delivery Date
 
     reqdat(20) type c, "Request date
 
     condat(20) type c, "Confirm date
 
     lifsk(20) type c, "Header Block
 
     txt30(30) type c, "Order User Status Description
 
     lifsp(20) type c, "Line Block
 
     dispo(20) type c, "MRP Controller
 
     dsnam(20) type c, "MRP Controller Description
 
     vmsta(20) type c, "Material Sales Status
 
     kunnr(20) type c, "Sold To
 
     cname(35) type c, "Sold To Name
 
     regio(20) type c, "State
 
     cufd(10) type c, "CUD
 
     bstnk(20) type c, "PO#
 
     bsark(20) type c, "Ordering Method
 
     matnr(20) type c, "Material
 
     maktx(35) type c, "Material Description
 
     t200(20) type c, "T200
 
     vtext(20) type c, "T200 Description
 
    matkl(20) type c, "Material Group
 
    zzbomind(7) type c, "BOM Indicator
 
    ostat(20) type c, "Order Status
 
    cmgst(20) type c, "CRD
 
    inco1(20) type c, "Incoterms
 
    oqty(20) type c, "Order Quantity
 
    pqty(20) type c, "Open Quantity
 
    unit(20) type c, "UOM
 
    onet(20) type c, "Order Value
 
    pnet(20) type c, "Open Value
 
    curr(20) type c, "Currency key
 
    so_bezei like tvkbt-bezei,"Sales Office
 
    sg_bezei like tvgrt-bezei,"Sales Group
 
    bname(20) type c, "Ordering Party
 
    contact(20) type c, "Contact Name
 
    telf1(20) type c, "Contact telf1
 
    reqqty(20) type c, "Item Request qty
 
    reqval(20) type c, "Item Request value
 
    conqty(20) type c, "Item Confirm qty
 
    conval(20) type c, "Item Confirm value
 
    zzrev(02) type c, "Revenue recognition acceptance
 
    bezei(20) type c, "Revenue recognition text
 
    vgbel(20) type c, "Reference Order for RETURNS
 
    0008text(255) type c, "Internal Order Comment Text
 
end of t_excel.
 
data: t_excel_bckord like t_excel occurs 0 with header line,
      t_excel_bcklog like t_excel occurs 0 with header line,
      t_excel_blkord like t_excel occurs 0 with header line.
 
types: data1(1500) type c,
       ty          type table of data1.
 
data: it   type ty with header line,
      it_2 type ty with header line,
      it_3 type ty with header line,
 
      rec  type sy-tfill,
   deli(1) type c,
 l_amt(18) type c.

data: begin of hex,
        tab type x,
      end of hex.
 
field-symbols: <fs> .
 
constants cns_09(2) type n value 09.
 
assign deli to <fs> type 'X'.
hex-tab = cns_09.
<fs> = hex-tab.
 
data gv_sheet_name(20) type c .
 
*----------------------------------------------------------------------*
* M A C R O Declaration
*----------------------------------------------------------------------*
define ole_check_error.
if &1 ne 0.
message e001(zz) with &1.
exit.
endif.
end-of-definition.
 
t_excel_bckord-vkorg = 'ABC'.
t_excel_bckord-vbtyp = 'DEF'.
t_excel_bckord-auart = 'GHI'.
t_excel_bckord-ernam = 'JKL'.
t_excel_bckord-vbeln = 'MNO'.
t_excel_bckord-0008text = 'XYZ'.
append t_excel_bckord.
 
t_excel_bckord-vkorg = 'ABC1'.
t_excel_bckord-vbtyp = 'DEF1'.
t_excel_bckord-auart = 'GHI1'.
t_excel_bckord-ernam = 'JKL1'.
t_excel_bckord-vbeln = 'MNO1'.
t_excel_bckord-0008text = 'XYZ1'.
append t_excel_bckord.
 
t_excel_bckord-vkorg = 'ABC2'.
t_excel_bckord-vbtyp = 'DEF2'.
t_excel_bckord-auart = 'GHI2'.
t_excel_bckord-ernam = 'JKL2'.
t_excel_bckord-vbeln = 'MNO2'.
t_excel_bckord-0008text = 'XYZ2'.
append t_excel_bckord.
 
 
 
t_excel_bcklog-vkorg = 'ABC'.
t_excel_bcklog-vbtyp = 'DEF'.
t_excel_bcklog-auart = 'GHI'.
t_excel_bcklog-ernam = 'JKL'.
t_excel_bcklog-vbeln = 'MNO'.
t_excel_bcklog-0008text = 'XYZ'.
append t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC1'.
t_excel_bcklog-vbtyp = 'DEF1'.
t_excel_bcklog-auart = 'GHI1'.
t_excel_bcklog-ernam = 'JKL1'.
t_excel_bcklog-vbeln = 'MNO1'.
t_excel_bcklog-0008text = 'XYZ1'.
append t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC2'.
t_excel_bcklog-vbtyp = 'DEF2'.
t_excel_bcklog-auart = 'GHI2'.
t_excel_bcklog-ernam = 'JKL2'.
t_excel_bcklog-vbeln = 'MNO2'.
t_excel_bcklog-0008text = 'XYZ2'.
append t_excel_bcklog.
 
t_excel_bcklog-vkorg = 'ABC3'.
t_excel_bcklog-vbtyp = 'DEF3'..
t_excel_bcklog-auart = 'GHI3'.
t_excel_bcklog-ernam = 'JKL3'.
t_excel_bcklog-vbeln = 'MNO3'.
t_excel_bcklog-0008text = 'XYZ3'.
append t_excel_bcklog.
 
 
t_excel_blkord-vkorg = 'ABC'.
t_excel_blkord-vbtyp = 'DEF'.
t_excel_blkord-auart = 'GHI'.
t_excel_blkord-ernam = 'JKL'.
t_excel_blkord-vbeln = 'MNO'.
t_excel_blkord-0008text = 'XYZ'.
append t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC1'.
t_excel_blkord-vbtyp = 'DEF1'.
t_excel_blkord-auart = 'GHI1'.
t_excel_blkord-ernam = 'JKL1'.
t_excel_blkord-vbeln = 'MNO1'.
t_excel_blkord-0008text = 'XYZ1'.
append t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC2'.
t_excel_blkord-vbtyp = 'DEF2'.
t_excel_blkord-auart = 'GHI2'.
t_excel_blkord-ernam = 'JKL2'.
t_excel_blkord-vbeln = 'MNO2'.
t_excel_blkord-0008text = 'XYZ2'.
append t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC3'.
t_excel_blkord-vbtyp = 'DEF3'..
t_excel_blkord-auart = 'GHI3'.
t_excel_blkord-ernam = 'JKL3'.
t_excel_blkord-vbeln = 'MNO3'.
t_excel_blkord-0008text = 'XYZ3'.
append t_excel_blkord.
 
t_excel_blkord-vkorg = 'ABC4'.
t_excel_blkord-vbtyp = 'DEF4'..
t_excel_blkord-auart = 'GHI4'.
t_excel_blkord-ernam = 'JKL4'.
t_excel_blkord-vbeln = 'MNO4'.
t_excel_blkord-0008text = 'XYZ4'.
append t_excel_blkord.
 
 
loop at t_excel_bckord.
concatenate
t_excel_bckord-vkorg
 
t_excel_bckord-vbtyp
 
t_excel_bckord-auart
 
t_excel_bckord-ernam
 
t_excel_bckord-vbeln
 
t_excel_bckord-posnr
 
t_excel_bckord-erdat
 
t_excel_bckord-vdatu
 
t_excel_bckord-reqdat
 
t_excel_bckord-condat
 
t_excel_bckord-lifsk
 
t_excel_bckord-txt30
 
t_excel_bckord-lifsp
 
t_excel_bckord-dispo
 
t_excel_bckord-dsnam
 
t_excel_bckord-vmsta
 
t_excel_bckord-kunnr
 
t_excel_bckord-cname
 
t_excel_bckord-regio
 
t_excel_bckord-cufd
 
t_excel_bckord-bstnk
 
t_excel_bckord-bsark
 
t_excel_bckord-matnr
 
t_excel_bckord-maktx
 
t_excel_bckord-t200
 
t_excel_bckord-vtext
 
t_excel_bckord-matkl
 
t_excel_bckord-zzbomind
 
t_excel_bckord-ostat
 
t_excel_bckord-cmgst
 
t_excel_bckord-inco1
 
t_excel_bckord-oqty
 
t_excel_bckord-pqty
 
t_excel_bckord-unit
 
t_excel_bckord-onet
 
t_excel_bckord-pnet
 
t_excel_bckord-curr
 
t_excel_bckord-so_bezei
 
t_excel_bckord-sg_bezei
 
t_excel_bckord-bname
 
t_excel_bckord-contact
 
t_excel_bckord-telf1
 
t_excel_bckord-reqqty
 
t_excel_bckord-reqval
 
t_excel_bckord-conqty
 
t_excel_bckord-conval
 
t_excel_bckord-zzrev
 
t_excel_bckord-bezei
 
t_excel_bckord-vgbel
 
t_excel_bckord-0008text
 
into it
separated by deli.
 
append it.
clear it.
endloop.
 
 
loop at t_excel_bcklog.
concatenate
t_excel_bcklog-vkorg
 
t_excel_bcklog-vbtyp
 
t_excel_bcklog-auart
 
t_excel_bcklog-ernam
 
t_excel_bcklog-vbeln
 
t_excel_bcklog-posnr
 
t_excel_bcklog-erdat
 
t_excel_bcklog-vdatu
 
t_excel_bcklog-reqdat
 
t_excel_bcklog-condat
 
t_excel_bcklog-lifsk
 
t_excel_bcklog-txt30
 
t_excel_bcklog-lifsp
 
t_excel_bcklog-dispo
 
t_excel_bcklog-dsnam
 
t_excel_bcklog-vmsta
 
t_excel_bcklog-kunnr
 
t_excel_bcklog-cname
 
t_excel_bcklog-regio
 
t_excel_bcklog-cufd
 
t_excel_bcklog-bstnk
 
t_excel_bcklog-bsark
 
t_excel_bcklog-matnr
 
t_excel_bcklog-maktx
 
t_excel_bcklog-t200
 
t_excel_bcklog-vtext
 
t_excel_bcklog-matkl
 
t_excel_bcklog-zzbomind
 
t_excel_bcklog-ostat
 
t_excel_bcklog-cmgst
 
t_excel_bcklog-inco1
 
t_excel_bcklog-oqty
 
t_excel_bcklog-pqty
 
t_excel_bcklog-unit
 
t_excel_bcklog-onet
 
t_excel_bcklog-pnet
 
t_excel_bcklog-curr
 
t_excel_bcklog-so_bezei
 
t_excel_bcklog-sg_bezei
 
t_excel_bcklog-bname
 
t_excel_bcklog-contact
 
t_excel_bcklog-telf1
 
t_excel_bcklog-reqqty
 
t_excel_bcklog-reqval
 
t_excel_bcklog-conqty
 
t_excel_bcklog-conval
 
t_excel_bcklog-zzrev
 
t_excel_bcklog-bezei
 
t_excel_bcklog-vgbel
 
t_excel_bcklog-0008text
 
into it_2
separated by deli.
 
append it_2.
clear it_2.
endloop.
 
 
loop at t_excel_blkord.
concatenate
t_excel_blkord-vkorg
 
t_excel_blkord-vbtyp
 
t_excel_blkord-auart
 
t_excel_blkord-ernam
 
t_excel_blkord-vbeln
 
t_excel_blkord-posnr
 
t_excel_blkord-erdat
 
t_excel_blkord-vdatu
 
t_excel_blkord-reqdat
 
t_excel_blkord-condat
 
t_excel_blkord-lifsk
 
t_excel_blkord-txt30
 
t_excel_blkord-lifsp
 
t_excel_blkord-dispo
 
t_excel_blkord-dsnam
 
t_excel_blkord-vmsta
 
t_excel_blkord-kunnr
 
t_excel_blkord-cname
 
t_excel_blkord-regio
 
t_excel_blkord-cufd
 
t_excel_blkord-bstnk
 
t_excel_blkord-bsark
 
t_excel_blkord-matnr
 
t_excel_blkord-maktx
 
t_excel_blkord-t200
 
t_excel_blkord-vtext
 
t_excel_blkord-matkl
 
t_excel_blkord-zzbomind
 
t_excel_blkord-ostat
 
t_excel_blkord-cmgst
 
t_excel_blkord-inco1
 
t_excel_blkord-oqty
 
t_excel_blkord-pqty
 
t_excel_blkord-unit
 
t_excel_blkord-onet
 
t_excel_blkord-pnet
 
t_excel_blkord-curr
 
t_excel_blkord-so_bezei
 
t_excel_blkord-sg_bezei
 
t_excel_blkord-bname
 
t_excel_blkord-contact
 
t_excel_blkord-telf1
 
t_excel_blkord-reqqty
 
t_excel_blkord-reqval
 
t_excel_blkord-conqty
 
t_excel_blkord-conval
 
t_excel_blkord-zzrev
 
t_excel_blkord-bezei
 
t_excel_blkord-vgbel
 
t_excel_blkord-0008text
 
into it_3
separated by deli.
 
append it_3.
clear it_3.
endloop.
 
 
 
if h_excel-header = space or h_excel-handle = -1.
* start Excel
create object h_excel 'EXCEL.APPLICATION'.
endif.
 
* PERFORM err_hdl.
 
*--- get list of workbooks, initially empty
call method of h_excel 'Workbooks' = h_mapl.
* PERFORM err_hdl.
set property of h_excel 'Visible' = 1.
 
* add a new workbook
call method of h_mapl 'Add' = h_map.
* PERFORM err_hdl.
*GV_SHEET_NAME = '1st SHEET'.
 
gv_sheet_name = 'Back Orders'.
 
 
 
get property of h_excel 'ACTIVESHEET' = worksheet.
set property of worksheet 'Name' = gv_sheet_name .
 
 
*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
*CALL METHOD OF gs_cells 'Select' .
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .
 
 
data l_rc type i.
call method cl_gui_frontend_services=>clipboard_export
importing
data = it[]
changing
rc = l_rc
exceptions
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4.
 
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
 
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 1.
* PERFORM err_hdl.
 
call method of h_excel 'Range' = range
exporting
#1 = w_cell1
#2 = w_cell2.
 
call method of range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.
* PERFORM err_hdl.
 
* CALL METHOD OF h_excel 'QUIT'.
 
*GV_SHEET_NAME = '2ND SHEET'.
gv_sheet_name = 'Backlog'.
 
 
get property of h_excel 'Sheets' = h_sheet2 .
 
call method of h_sheet2 'Add' = h_map.
set property of h_map 'Name' = gv_sheet_name .
get property of h_excel 'ACTIVESHEET' = worksheet.
 
 
*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .
 
call method cl_gui_frontend_services=>clipboard_export
importing
data = it_2[]
changing
rc = l_rc
exceptions
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4.
 
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
 
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 1.
* PERFORM err_hdl.
 
call method of h_excel 'Range' = range
exporting
#1 = w_cell1
#2 = w_cell2.
 
call method of range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.
 
 
 
*GV_SHEET_NAME = '3rd SHEET'.
gv_sheet_name = 'Blocked Orders'.
 
get property of h_excel 'Sheets' = h_sheet3 .
 
call method of h_sheet3 'Add' = h_map.
set property of h_map 'Name' = gv_sheet_name .
get property of h_excel 'ACTIVESHEET' = worksheet.
 
 
*--Formatting the area of additional data 1 and doing the BOLD
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
get property of h_cell 'Font' = gs_font .
set property of gs_font 'Bold' = 1 .
 
call method cl_gui_frontend_services=>clipboard_export
importing
data = it_3[]
changing
rc = l_rc
exceptions
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
others = 4.
 
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
 
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 1
#2 = 1.
* PERFORM err_hdl.
 
call method of h_excel 'Range' = range
exporting
#1 = w_cell1
#2 = w_cell2.
 
call method of range 'Select'.
* PERFORM err_hdl.
call method of worksheet 'Paste'.
 
 
*--- disconnect from Excel
free object h_zl.
free object h_mapl.
free object h_map.
free object h_excel.
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.