Auto Format in Excel Sheet using OLE Concepts

[Former Member]

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

[Former Member]

Hi,

I used this code to autoformat my columns! Hope this was what you were looking for!

• Optimize width of columns --------------------------------------*

DATA: column TYPE ole2_object.

CALL METHOD OF h_excel 'Columns' = column NO FLUSH.

FREE OBJECT h_excel NO FLUSH.

CALL METHOD OF column 'Autofit' NO FLUSH.

FREE OBJECT column NO FLUSH.

[Former Member]

Hi experts,

How can we place this XL sheet directly into application server? Your code download the data and places it in local system, I want to know how we can transfer this data to application server