display header data in multiple xlsheets

[Former Member]

hi all,

my rewquirement is i need to display header data in different xlsheets.

anybody can send me the sample code.

thanks,

maheedhar.t

[Former Member]

Hi,

Hi,

Look at the Sample program which will create 3 sheets in a Excel .. Just copy the code and execute it

*&---------------------------------------------------------------------*
*& 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,
      column      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,
      int         type ole2_object,
      cells       type ole2_object,
      selection   type ole2_object,
      columns     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 = 'ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
t_excel_bckord-vbtyp = 'DEF'.
t_excel_bckord-auart = 'GHI'.
t_excel_bckord-ernam = 'JKLABCABCABCABCABCABCABCvABCvvvABCABCABCABCABCABC'.
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 = 'DEF2ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
t_excel_bckord-auart = 'GHI2'.
t_excel_bckord-ernam = 'JKL2ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
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.
 
 
* start Excel
  if h_excel-header = space or h_excel-handle = -1.
    create object h_excel 'EXCEL.APPLICATION'.
  endif.
 
*--- get list of workbooks, initially empty
  call method of h_excel 'Workbooks' = h_mapl.
  set property of h_excel 'Visible' = 1.
  call method of h_mapl 'Add' = h_map.
  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 = 4
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
 
*CALL METHOD OF H_EXCEL 'Columns' = COLUMN.
*
*CALL METHOD OF COLUMN 'Autofit'.
*FREE OBJECT COLUMN.
 
*Call Method of H_Cell 'Select'    .
 
 
*GET PROPERTY OF H_CELL 'Font' = GS_FONT .
*SET PROPERTY OF GS_FONT 'Bold' = 1 .
 
*
*Call Method of H_EXCEL 'Cells'    = Cells.
*Call Method of H_Cell 'Select'    .
*Get property of H_CELL 'Columns' = Columns.
*SET PROPERTY of Columns 'Autofit' = 1.
*Free Object Columns.
*Free Object Selection.
*Free Object Cells.
 
 
 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_Cell 'Select'    .
 
 
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
 
 
 
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 4
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
 
get property of h_cell  'Columns' = columns.
set property of columns 'AutoFit' = 1.
 
*********Ramesh
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 4
#2 = 1.
 
*CALL METHOD OF h_excel 'Cells' = w_cell2
*EXPORTING
*#1 = 4.
*#2 = 4.
 
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell1.
 
call method of range 'Select'.
 
 
*********Ramesh
 
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
 
 
*--- disconnect from Excel
  free object h_zl.
  free object h_mapl.
  free object h_map.
  free object h_excel.

Regards

Sudheer

[Former Member]

Hi,

Hi,

Look at the Sample program which will create 3 sheets in a Excel .. Just copy the code and execute it

*&---------------------------------------------------------------------*
*& 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,
      column      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,
      int         type ole2_object,
      cells       type ole2_object,
      selection   type ole2_object,
      columns     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 = 'ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
t_excel_bckord-vbtyp = 'DEF'.
t_excel_bckord-auart = 'GHI'.
t_excel_bckord-ernam = 'JKLABCABCABCABCABCABCABCvABCvvvABCABCABCABCABCABC'.
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 = 'DEF2ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
t_excel_bckord-auart = 'GHI2'.
t_excel_bckord-ernam = 'JKL2ABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABCABC'.
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.
 
 
* start Excel
  if h_excel-header = space or h_excel-handle = -1.
    create object h_excel 'EXCEL.APPLICATION'.
  endif.
 
*--- get list of workbooks, initially empty
  call method of h_excel 'Workbooks' = h_mapl.
  set property of h_excel 'Visible' = 1.
  call method of h_mapl 'Add' = h_map.
  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 = 4
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
 
*CALL METHOD OF H_EXCEL 'Columns' = COLUMN.
*
*CALL METHOD OF COLUMN 'Autofit'.
*FREE OBJECT COLUMN.
 
*Call Method of H_Cell 'Select'    .
 
 
*GET PROPERTY OF H_CELL 'Font' = GS_FONT .
*SET PROPERTY OF GS_FONT 'Bold' = 1 .
 
*
*Call Method of H_EXCEL 'Cells'    = Cells.
*Call Method of H_Cell 'Select'    .
*Get property of H_CELL 'Columns' = Columns.
*SET PROPERTY of Columns 'Autofit' = 1.
*Free Object Columns.
*Free Object Selection.
*Free Object Cells.
 
 
 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_Cell 'Select'    .
 
 
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
 
 
 
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 1
#2 = 1.
call method of h_excel 'Cells' = w_cell2
exporting
#1 = 4
#2 = 50.
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell2.
 
get property of h_cell  'Columns' = columns.
set property of columns 'AutoFit' = 1.
 
*********Ramesh
call method of h_excel 'Cells' = w_cell1
exporting
#1 = 4
#2 = 1.
 
*CALL METHOD OF h_excel 'Cells' = w_cell2
*EXPORTING
*#1 = 4.
*#2 = 4.
 
call method of h_excel 'Range' = h_cell
exporting
#1 = w_cell1
#2 = w_cell1.
 
call method of range 'Select'.
 
 
*********Ramesh
 
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
  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.
 
   call method of h_excel 'Range' = range
    exporting
      #1 = w_cell1
      #2 = w_cell2.
 
  call method of range 'Select'.
  call method of worksheet 'Paste'.
 
 
*--- disconnect from Excel
  free object h_zl.
  free object h_mapl.
  free object h_map.
  free object h_excel.

Regards

Sudheer

[Former Member]

Hi,

Refer sample code below. Hope it helps.

>

• Multiple excel sheets generation in a workbook

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.

CALL METHOD OF BOOK 'WORKSHEETS' = SHEET.

CALL METHOD OF SHEET 'ADD'.

• Fill all the sheets with relavant data

PERFORM SHEET1 TABLES ITAB1.

PERFORM SHEET2 TABLES ITAB2.

PERFORM SHEET3 TABLES ITAB3.

PERFORM SHEET4 TABLES ITAB4.

• Quit the excel after use

CALL METHOD OF EXCEL 'QUIT'.

FREE OBJECT: COLUMN,SHEET,BOOK,BOOKS,APPLICATION,EXCEL. "NO FLUSH.

CLEAR V_SHEET.

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

FORM SHEET1 TABLES ITAB1 STRUCTURE ITAB1.

V_SHEET = Sheet Name.

V_NO = V_NO + 1.

CALL METHOD OF BOOK 'worksheets' = SHEET NO FLUSH EXPORTING #1 = V_NO.

SET PROPERTY OF SHEET 'Name' = V_SHEET NO FLUSH.

PERFORM FILL_SHEET1 TABLES ITAB1 USING V_NO V_SHEET.

CALL METHOD OF SHEET 'Columns' = COLUMN.

FREE OBJECT SHEET.

CALL METHOD OF COLUMN 'Autofit'.

FREE OBJECT COLUMN.

ENDFORM.

Repeat above procedure for all sheets you want to add

FORM FILL_SHEET1

TABLES ITAB1 STRUCTURE ITAB1

USING V_NO V_SHEET.

ROW = 1.

PERFORM FILL_CELL USING ROW 1 'Column1 Name'.

PERFORM FILL_CELL USING ROW 2 'Column2 Name'.

PERFORM FILL_CELL USING ROW 3 'Column3 Name'.

ROW = ROW + 1.

LOOP AT ITAB1.

PERFORM FILL_CELL USING ROW 1 ITAB1-Column1.

PERFORM FILL_CELL USING ROW 2 ITAB1-Column2.

PERFORM FILL_CELL USING ROW 3 ITAB1-Column3.

ROW = ROW + 1.

ENDLOOP.

ENDFORM.

Repeat above procedure for all sheets you want to add

Reward points if this helps.

Manish