Skip to Content
0
Former Member
Jan 09, 2007 at 11:21 AM

Auto Format in Excel Sheet using OLE Concepts

660 Views

Hi All,

Pl. find below a sample code. In this code I am downloading data to Excel Sheet using OLE & methods concept. Now my requirement is that while downloading the fields should get autoformatted according to their size and be displayed in the excel output.

Pl. add ur code within the code and help me in this regard.

  • 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.

  • add a new workbook

CALL METHOD OF h_mapl 'Add' = h_map.

GV_SHEET_NAME = 'BlockedOrders (D)'.

GET PROPERTY OF h_excel 'ACTIVESHEET' = worksheet.

SET PROPERTY OF worksheet 'Name' = GV_SHEET_NAME .

*--Selecting cell area to be merged.

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 h_cell 'Select' .

*--Merging

CALL METHOD OF h_cell 'Merge' .

GET PROPERTY OF H_CELL 'Font' = GS_FONT.

SET PROPERTY OF GS_FONT 'Bold' = 1.

*--Formatting the area of additional data 1 and doing the BOLD

CALL METHOD OF H_EXCEL 'Cells' = w_CELL1

EXPORTING

#1 = 3

#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_6[]

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'.