Skip to Content
author's profile photo Former Member
Former Member

Add borders to Excel using OLE

Hi,

I've created a report in which I use OLE in order to print some information on an excel sheet. However, the user wants excel sheet to be printed with borders. Any idea about how to do this?

Regards,

Roberto.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jul 08, 2008 at 07:59 PM

    I found out how.

    Check this out.

    REPORT ZEXOLE2.

    parameters: p_file like RLGRAP-FILENAME

    default 'C:\exceldata\Customerdata.xls'.

    data: d_file like p_file,

    d_exsheet(10) value 'Customers',c_row type i,

    d_scnt type i,

    d_val(20),

    d_wb(2).

    parameters: p_exvis as checkbox default 'X',

    p_workbk(2) type p default '01',

    p_wsheet(2) type p default '01'.

    CONSTANTS: OK TYPE I VALUE 0.

    INCLUDE OLE2INCL.

    DATA: EXCEL TYPE OLE2_OBJECT,

    WORKBOOK TYPE OLE2_OBJECT,

    SHEET TYPE OLE2_OBJECT,

    CELL TYPE OLE2_OBJECT,

    CELL1 TYPE OLE2_OBJECT,

    COLUMN TYPE OLE2_OBJECT,

    RANGE TYPE OLE2_OBJECT,

    BORDERS TYPE OLE2_OBJECT,

    BUTTON TYPE OLE2_OBJECT,

    INT TYPE OLE2_OBJECT,

    FONT TYPE OLE2_OBJECT,

    ROW TYPE OLE2_OBJECT.

    data: application type ole2_object,

    book type ole2_object,

    ole_book TYPE ole2_object.

    do p_workbk times.

    move p_file to d_file.

    unpack sy-index to d_wb.

    replace 'NN' with d_wb into d_file.

    *

    perform create_EXCEL.

    • create sheets and save

    perform sheet.

    perform save_book.

    enddo.

    write: ' Done'.

    ----


    • FORM create_excel *

    ----


    • ........ *

    ----


    form create_excel.

    CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

    if sy-subrc ne 0.

    write: / 'No EXCEL creation possible'.

    stop.

    endif.

    set property of EXCEL 'DisplayAlerts' = 0.

    CALL METHOD OF EXCEL 'WORKBOOKS' = WORKBOOK .

    • Put Excel in background

    if p_exvis eq 'X'.

    SET PROPERTY OF EXCEL 'VISIBLE' = 1.

    else.

    SET PROPERTY OF EXCEL 'VISIBLE' = 0.

    endif.

    • Create worksheet

    set property of excel 'SheetsInNewWorkbook' = 1.

    call method of workbook 'ADD'.

    endform.

    ----


    • FORM save_book *

    ----


    • ........ *

    ----


    form save_book.

    get property of excel 'ActiveSheet' = sheet.

    free object sheet.

    free object workbook.

    GET PROPERTY OF EXCEL 'ActiveWorkbook' = WORKBOOK.

    call method of workbook 'SAVEAS' exporting #1 = p_file #2 = 1.

    call method of workbook 'CLOSE'.

    call method of excel 'QUIT'.

    free object sheet.

    free object workbook.

    free object excel.

    endform.

    ----


    • FORM sheet *

    ----


    • ........ *

    ----


    form sheet.

    do p_wsheet times.

    unpack sy-index to d_exsheet+5(2).

    if sy-index gt 1.

    CALL METHOD OF EXCEL 'WORKSHEETS' = sheet.

    call method of sheet 'ADD'.

    free object sheet.

    endif.

    d_scnt = sy-index.

    call method of excel 'WORKSHEETS' = SHEET EXPORTING #1 = d_scnt

    .

    call method of sheet 'ACTIVATE'.

    SET PROPERTY OF SHEET 'NAME' = d_exsheet.

    free object sheet. "OK

    perform fill_sheet.

    *

    CALL METHOD OF EXCEL 'Columns' = COLUMN.

    CALL METHOD OF COLUMN 'Autofit'.

    free object COLUMN.

    *

    free object button.

    free object font.

    free object int.

    free object cell.

    free object: cell1.

    free object range.

    free object borders.

    free object: column, row.

    enddo.

    free object font.

    free object int.

    free object cell.

    free object cell1.

    free object range.

    free object borders.

    free object column.

    free object row.

    free object sheet.

    endform.

    ----


    • FORM border *

    ----


    • ........ *

    ----


    • --> we *

    ----


    form border using we.

    *left

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

    set property of borders 'LineStyle' = '1'.

    set property of borders 'WEIGHT' = we. "4=max

    free object borders.

    • right

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

    set property of borders 'LineStyle' = '2'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • top

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

    set property of borders 'LineStyle' = '3'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • bottom

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

    set property of borders 'LineStyle' = '4'.

    set property of borders 'WEIGHT' = we.

    • set property of borders 'ColorIndex' = 'xlAutomatic'.

    free object borders.

    endform.

    ----


    • FORM border2 *

    ----


    • ........ *

    ----


    • --> we *

    ----


    form border2 using we.

    *left

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

    set property of borders 'LineStyle' = '5'.

    set property of borders 'WEIGHT' = we. "4=max

    free object borders.

    • right

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

    set property of borders 'LineStyle' = '6'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • top

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

    set property of borders 'LineStyle' = '7'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • bottom

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

    set property of borders 'LineStyle' = '8'.

    set property of borders 'WEIGHT' = we.

    • set property of borders 'ColorIndex' = 'xlAutomatic'.

    free object borders.

    endform.

    ----


    • FORM border3 *

    ----


    • ........ *

    ----


    • --> we *

    ----


    form border3 using we.

    *left

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '1'.

    set property of borders 'LineStyle' = '10'.

    set property of borders 'WEIGHT' = we. "4=max

    free object borders.

    • right

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '2'.

    set property of borders 'LineStyle' = '10'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • top

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '3'.

    set property of borders 'LineStyle' = '11'.

    set property of borders 'WEIGHT' = we.

    free object borders.

    • bottom

    call method of CELL 'BORDERS' = BORDERS exporting #1 = '4'.

    set property of borders 'LineStyle' = '12'.

    set property of borders 'WEIGHT' = we.

    • set property of borders 'ColorIndex' = 'xlAutomatic'.

    free object borders.

    endform.

    ----


    • FORM fill_cell *

    ----


    • ........ *

    ----


    • --> color *

    • --> pattern *

    ----


    form fill_cell using color pattern.

    call method of cell 'INTERIOR' = int.

    set property of int 'ColorIndex' = color.

    set property of int 'Pattern' = pattern.

    free object int.

    endform.

    ----


    • FORM font *

    ----


    • ........ *

    ----


    • --> bold *

    • --> size *

    ----


    form font using bold size.

    call method of CELL 'FONT' = font.

    set property of font 'BOLD' = bold.

    set property of font 'SIZE' = size.

    free object font.

    endform.

    ----


    • FORM fill_sheet *

    ----


    • ........ *

    ----


    form fill_sheet.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'A1'.

    perform font using 1 '10'.

    SET PROPERTY OF CELL 'VALUE' = 'Counter'.

    perform fill_cell using '20' '1'.

    perform border using '2'.

    free object cell.

    d_val = 'Workbook-Count'.

    move d_wb to d_val+16.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'B1'.

    SET PROPERTY OF CELL 'VALUE' = d_val.

    perform fill_cell using '14' '1'.

    perform border using '4'.

    free object cell.

    d_val = 'Sheet-Count'.

    unpack sy-index to d_val+12.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'C1'.

    SET PROPERTY OF CELL 'VALUE' = d_val.

    perform fill_cell using '21' '1'.

    perform border using '4'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E3'.

    perform border using '1'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E5'.

    perform border using '2'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E7'.

    perform border using '3'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'E9'.

    perform border using '4'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F3'.

    perform border2 using '1'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F5'.

    perform border2 using '2'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F7'.

    perform border2 using '3'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'F9'.

    perform border2 using '4'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G3'.

    perform border3 using '1'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G5'.

    perform border3 using '2'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G7'.

    perform border3 using '3'.

    free object cell.

    CALL METHOD OF EXCEL 'RANGE' = CELL EXPORTING #1 = 'G9'.

    perform border3 using '4'.

    free object cell.

    d_val = 'ROW-Count'.

    do 19 times.

    c_row = sy-index + 1.

    unpack c_row to d_val+12(4).

    CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 2.

    SET PROPERTY OF CELL1 'VALUE' = d_val.

    free object cell1.

    CALL METHOD OF excel 'CELLS' = CELL1 EXPORTING #1 = c_row #2 = 4.

    SET PROPERTY OF CELL1 'VALUE' = d_val.

    free object cell1.

    enddo.

    endform.

    Cheers,

    Roberto

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.