Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Decorating EXCEL Sheet which need to be send as an attachment in EMAIL

Former Member
0 Kudos

My report program need to send the data in an internal table through EMAIL as an EXCEL Sheet attachment...

I am able to send the data perfectly....

But i need to add some look good environment (as making the header bold and with a different colour) for the EXCEL Sheet...

Please advice me on how i could do this ?

Thanks a lot,

Raj....

13 REPLIES 13

Former Member
0 Kudos

Hello Raja,

I can help you how to trigger a program which sends the records to EXCEL sheet automatically. Features that you were asking like Bold and colorful all i have shown in the excel sheet.

Just copy paste the code and execute you will see Excel sheet automatically triggered.

Modify the code accordingly to your requirement. Any issues revert me back i will help you.


REPORT  ZREDDY_VBAP                               .

* sending ABAP data to an
* EXCEL sheet using OLE automation.
include ole2incl.
* handles for OLE objects
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
      h_c type ole2_object.            " color

data: filename like rlgrap-filename.

tables: vbap.
data  h type i.
* table of flights
data: it_vbap like vbap occurs 10 with header line.

*&---------------------------------------------------------------------*

*&   Event START-OF-SELECTION
*&---------------------------------------------------------------------*

start-of-selection.
* read flights
  select * from vbap into table it_vbap.
* display header
  uline (61).
  write: /     sy-vline no-gap,
          (6)  'ITEM'(001) color col_heading no-gap, sy-vline no-gap,
          (18)  'MATERIAL'(002) color col_heading no-gap, sy-vline
no-gap
,
          (30) 'DESCRIPTION'(003) color col_heading no-gap, sy-vline
no-gap,
          (35) 'ORDER_QUANTITY'(004) color col_heading no-gap, sy-vline
no-gap,
          (8)  'S_UNIT'(005) color col_heading no-gap, sy-vline no-gap.
  uline /(61).
* display flights
  loop at it_vbap.
    write: / sy-vline no-gap,
             it_vbap-posnr color col_key no-gap, sy-vline no-gap,
             it_vbap-matwa color col_normal no-gap, sy-vline no-gap,
             it_vbap-arktx color col_normal no-gap, sy-vline no-gap,
             it_vbap-awahr color col_normal no-gap, sy-vline no-gap,
             it_vbap-vrkme color col_normal no-gap, sy-vline no-gap.
  endloop.
  uline /(61).
* tell user what is going on
  call function 'SAPGUI_PROGRESS_INDICATOR'
     exporting
*           PERCENTAGE = 0
           text       = text-007
       exceptions
            others     = 1.
* start Excel
  create object h_excel 'EXCEL.APPLICATION'.
*  PERFORM ERR_HDL.

  set property of h_excel  'Visible' = 1.
*  CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\sales.xls'
  .

*  PERFORM ERR_HDL.
* tell user what is going on
  call function 'SAPGUI_PROGRESS_INDICATOR'
     exporting
*           PERCENTAGE = 0
           text       = text-008
       exceptions
            others     = 1.
* get list of workbooks, initially empty
  call method of h_excel 'Workbooks' = h_mapl.
  perform err_hdl.
* add a new workbook
  call method of h_mapl 'Add' = h_map.
  perform err_hdl.
* tell user what is going on
  call function 'SAPGUI_PROGRESS_INDICATOR'
     exporting
*           PERCENTAGE = 0
           text       = text-009
       exceptions
            others     = 1.
* output column headings to active Excel sheet
  perform fill_cell using 1 1 1 200 'ITEM'(001).
  perform fill_cell using 1 2 1 200 'MATERIAL'(002).
  perform fill_cell using 1 3 1 200 'DESCRIPTION'(003).
  perform fill_cell using 1 4 1 200 'ORDER_QUANTITY'(004).
  perform fill_cell using 1 5 1 200 'S_UNIT'(005).
  loop at it_VBAP.
* copy flights to active EXCEL sheet
    h = sy-tabix + 1.
    if it_VBAP-POSNR cs '000010'.
      perform fill_cell using h 1 0 000255000 it_VBAP-POSNR.
    elseif it_VBAP-POSNR cs '000020'.
      perform fill_cell using h 1 0 168000000 it_VBAP-POSNR.
    elseif it_VBAP-POSNR cs '000030'.
      perform fill_cell using h 1 0 168168000 it_VBAP-POSNR.
    elseif it_VBAP-POSNR cs '000040'.
      perform fill_cell using h 1 0 111111111 it_VBAP-POSNR.
    elseif it_VBAP-POSNR cs 'SQ'.
      perform fill_cell using h 1 0 100100100 it_VBAP-POSNR.
    else.
      perform fill_cell using h 1 0 000145000 it_VBAP-POSNR.
    endif.

    if it_VBAP-MATWA lt 'P-*'.
      perform fill_cell using h 2 0 255000255 it_VBAP-MATWA.
    elseif it_VBAP-MATWA lt 'M-*'.
      perform fill_cell using h 2 0 770990885 it_VBAP-MATWA.
      elseif it_VBAP-MATWA lt 'L-*'.
      perform fill_cell using h 2 0 077099048 it_VBAP-MATWA.
    else.
      perform fill_cell using h 2 0 246156138 it_VBAP-MATWA.
    endif.

    if it_vbap-arktx cp 'S*'.
      perform fill_cell using h 3 0 155155155 it_vbap-arktx.
    elseif it_vbap-arktx cp 'F*'.
      perform fill_cell using h 3 0 189111222 it_vbap-arktx.
      elseif it_vbap-arktx cp 'J*'.
      perform fill_cell using h 3 0 289111222 it_vbap-arktx.

    else.
      perform fill_cell using h 3 0 111230222 it_vbap-arktx.
    endif.

    if it_vbap-awahr cp 'S*'.
      perform fill_cell using h 4 0 200200200 it_vbap-awahr.
    elseif it_vbap-awahr cp 'N*'.
      perform fill_cell using h 4 0 000111222 it_vbap-awahr.
    else.
      perform fill_cell using h 4 0 130230230 it_vbap-awahr.
    endif.

    if it_vbap-vrkme lt '020000'.
      perform fill_cell using h 5 0 145145145 it_vbap-vrkme.
    elseif it_vbap-vrkme lt '120000' .
      perform fill_cell using h 5 0 015215205 it_vbap-vrkme.
    elseif it_vbap-vrkme lt '180000' .
      perform fill_cell using h 5 0 000215205 it_vbap-vrkme.
    else.
      perform fill_cell using h 5 0 115115105 it_vbap-vrkme.
    endif.
  endloop.


* EXCEL FILENAME
  concatenate sy-repid '_' sy-datum+6(2) '_' sy-datum+4(2) '_'
              sy-datum(4) '_' sy-uzeit '.XLS' into filename.

  call method of h_map 'SAVEAS' exporting #1 = filename.


  free object h_excel.
  perform err_hdl.
*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                *
*---------------------------------------------------------------------*
*       sets cell at coordinates i,j to value val boldtype bold       *
*---------------------------------------------------------------------*
form fill_cell using i j bold col val.
  call method of h_excel 'Cells' = h_zl
    exporting
      #1 = i
      #2 = j.
  perform err_hdl.
  set property of h_zl 'Value' = val .
  perform err_hdl.
  get property of h_zl 'Font' = h_f.
  perform err_hdl.
  set property of h_f 'Bold' = bold .
  perform err_hdl.
  set property of h_f 'Color' = col.
  perform err_hdl.
endform.                    "FILL_CELL

*&---------------------------------------------------------------------*

*&      Form  ERR_HDL
*&---------------------------------------------------------------------*

*       outputs OLE error if any                                       *

*----------------------------------------------------------------------*

*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*

form err_hdl.
  if sy-subrc <> 0.
    write: / 'OLE-Automation Error:'(010), sy-subrc.
    stop.
  endif.
endform.                    " ERR_HDL

Cheers!!

Balu

Edited by: Balu CH on Oct 21, 2008 10:54 PM

Code Formatted by: Alvaro Tejada Galindo on Jan 7, 2009 2:20 PM

Former Member
0 Kudos

Cross post:

Former Member
0 Kudos

Double-posting (and any other forum rules violations) are unfair on those who take the effort to help you by answering or giving you advise.

When people see this, they will stop answering your questions...

Former Member
0 Kudos

Hi Raj,

Got the excel sheet with your requirement or any issues.

Cheers!!

Balu

0 Kudos

Hi balu ....

thank you very much for your inputs...

i didn't yet try with its working... but, the code looks pretty logical to create clourful XLS..

But, before goin a head with trial could you please clear few queries of mine which i have got on looking at your code.. :

1. one of the method is meant to save the XLS ... where does it get saved ?

2. how could the report program send that XLS as a mail attachment ?

Please help me with these things ....

Hi All..

please suggest the relevent solution....

Thanks,

Raj ...

Edited by: raja boyapati on Oct 21, 2008 10:22 PM

0 Kudos

Hi Raj,

First let me know is excel sheet triggered or not.

Regards

Balu

0 Kudos

Hi Raj,

As in my previous post i have said i can help you in triggering the EXCEL sheet with your features what you have asked for.

Try to generate the spool request and send mail by yourself as i am not so aware of that concept.

I can asure you for EXCEL sheet as it is working fine.

And coming to your query where it is storing have to seen this in the code

CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:\kis_excel.xls'

I have given the path which my client have asked c:\kis_excel.xls

so my excel sheet will be saved in the machine the path tht i have provided.

Cheers!!

Balu

0 Kudos

Thankyou very much Balu ..

i will let u know once i could find XLS on my machine..

0 Kudos

Hi Raj,

Small change to my above post your excel sheet will be store in your MY DOCUMENTS of your system with the name of the excel sheet as your program name date and time.

Example - ZSDNF_21_10_2008_223644.XLS

I am sorry for the above post.

see this code

concatenate sy-repid '_' sy-datum6(2) '_' sy-datum4(2) '_'

sy-datum(4) '_' sy-uzeit '.XLS' into filename.

call method of h_map 'SAVEAS' exporting #1 = filename.

Let me know if know issues.

Cheers!!

Balu

0 Kudos

Hi Raj,

I will be waiting for your final answer saying excel sheet is storing in my machine of MY DOCUMENTS.

Cheers!!

Balu

0 Kudos

hI bALU

i am sorry, i did not try with your code.. My report will be scheduled as a brackground job and so creating a file on local system can't help me to send that as a mail attachment..

Thanks a lot for ur inputs.

Hi All

Could some one please out help me with a solution for the requirement?

Former Member
0 Kudos

1. Open up a sample excel with the format you want.

2. File -> Save as HTML.

3. Open that HTML

4. Do a view source. Replace data with variables

5. Copy that source and go to SAP SO10 transaction. Paste that source.

6. In your program do a read text to get that source.

7. Now replace variables with data from your program.

8. Send that email as .txt attachement

9. Give instruction in your email body for them to right clicka and save as .XLS

10. If you do this right the excel will be formatted.

0 Kudos

Thanks Alex! Your solution worked for me