This discussion is locked
5 Replies Latest reply: Jul 23, 2008 2:24 PM by twinkal patel RSS

Colors in Excel Sheet

Kangana Rowal
Currently Being Moderated

I am using the type pools OLE2 to put colors in the Excel sheet.

What is the property to fill color in the Excel sheet cell.

 

Also I need to merge cells in the excel sheet.

 

Can some one send me some sample code if you have?

Please help.Its urgent.

  • Re: Colors in Excel Sheet
    Raja Babu
    Currently Being Moderated

    Hi Kangana,

     

    Check this .

     

    SET_COLOR

    Sets the font and background colors for a particular area. The values come from the color

    palette in Excel, not RGB values, even if the spreadsheet in question only accepts RGB values.

     

    CALL METHOD spreadsheet->set_color
    EXPORTING rangename = rangename
    front = front
    back = back
    no_flush = no_flush
    IMPORTING error = error
    retcode = retcode.

     

    Check this link below : [OLE Objects|http://help.sap.com/printdocu/core/Print46c/en/data/pdf/BCCIOFFI/BCCIOFFI.pdf]

  • Re: Colors in Excel Sheet
    kalyan annapareddy
    Currently Being Moderated

    hi rowal,

    Hi Ashutosh...Use the below code i think it will be helpful

     

     

    This program demonstrates how to send abap data to excel sheet

    using OLE automation

     

    include ole2incl.

     

    *handles for OLE object

    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

     

    data: h type i.

     

    types: begin of t_bkpf,

    bukrs type bkpf-bukrs,

    belnr type bkpf-belnr,

    gjahr type bkpf-gjahr,

    blart type bkpf-blart,

    budat type bkpf-budat,

     

    end of t_bkpf.

     

    data: it_bkpf type standard table of t_bkpf,

    wa_bkpf type t_bkpf.

     

    ********start-of-selection*******************

    start-of-selection.

     

    select bukrs

    belnr

    gjahr

    blart

    budat into table it_bkpf

    from bkpf

    up to 10 rows.

     

    call function 'SAPGUI_PROGRESS_INDICATOR'

    exporting

     

     

    PERCENTAGE = 0

    text = text-001

    exceptions

    others = 1.

     

    Start excel

    create object h_excel 'EXCEL.APPLICATION'.

    perform err_hdl.

    set property of h_excel 'Visible' = 1.

    perform err_hdl.

     

    call function 'SAPGUI_PROGRESS_INDICATOR'

    exporting

     

    PERCENTAGE = 0

    text = text-002

    exceptions

    others = 1.

     

    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.

     

    call function 'SAPGUI_PROGRESS_INDICATOR'

    exporting

     

    PERCENTAGE = 0

    text = text-003

    exceptions

    others = 1.

     

     

    Output Column Headings to active excel sheet

    perform fill_cell using 1 1 1 text-004.

    perform fill_cell using 1 2 1 text-005.

    perform fill_cell using 1 3 1 text-006.

    perform fill_cell using 1 4 1 text-007.

    perform fill_cell using 1 5 1 text-008.

     

     

    Copy internal table to excel sheet

     

    loop at it_bkpf into wa_bkpf.

    h = sy-tabix + 1.

     

    perform fill_cell1 using h 1 0 wa_bkpf-bukrs.

    perform fill_cell2 using h 2 0 wa_bkpf-belnr.

    perform fill_cell1 using h 3 0 wa_bkpf-gjahr.

    perform fill_cell1 using h 4 0 wa_bkpf-blart.

    perform fill_cell1 using h 5 0 wa_bkpf-budat.

     

    endloop.

     

     

     

    disconnect from Excel

    free object h_excel.

    perform err_hdl.

     

    &----


    *& Form err_hdl

    &----


     

     

    text

    -


     

    --> p1 text

    <-- p2 text

    -


    form err_hdl .

     

    if sy-subrc <> 0.

    write: / 'Error in OLE Automation'.

    stop.

    endif.

     

    endform. " err_hdl

    &----


    *& Form fill_cell

    &----


     

    text

    -


     

    -->P_1 text

    -->P_1 text

    -->P_1 text

    -->P_TEXT_004 text

    -


    form fill_cell using i j bold 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 'colorindex' = 10.

     

    endform. " fill_cell

    &----


    *& Form fill_cell1

    &----


     

    text

    -


     

    -->P_H text

    -->P_1 text

    -->P_0 text

    -->P_WA_BKPF_BUKRS text

    -


    form fill_cell1 using i j bold 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.

     

    endform. " fill_cell1

    &----


    *& Form fill_cell2

    &----


     

    text

    -


     

    -->P_H text

    -->P_2 text

    -->P_0 text

    -->P_WA_BKPF_BELNR text

    -


    form fill_cell2 using i j bold 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 'colorindex' = 13.

     

    endform. " fill_cell2

     

    hope the code will help you to solve the problem,

    reward points if usefull,

    Thanks ,

    kalyan.

  • Re: Colors in Excel Sheet
    harish kumar
    Currently Being Moderated

    Hi kalyan , found your solution very useful.

    but i have one more issue.

     

    for example:

    In your output i need to get the first row in complete yellow.ie the back ground should be in yellow and text should be in black..

    PLz post me the answers..i have an urgent requirement.

  • Re: Colors in Excel Sheet
    twinkal patel
    Currently Being Moderated

    hiii

     

    you can refer to following link for colours in EXCEL

     

    http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

     

    regards

    twinkal

  • Re: Colors in Excel Sheet
    twinkal patel
    Currently Being Moderated

    hiii

     

    please refer to following code.execute this program first and check with output that it satisfies with your requirement or not as it is having background colour as different and font in different colour.

     

    http://sap.ittoolbox.com/code/archives.asp?d=3027&a=s&i=10

     

    regards

    twinkal

Actions