ole excel rows and columns autofit

[Former Member]

Hi all,

In my output iam getting the excel sheet with headings and data. headings are getting autofit but the data in the rows is not getting autofit. i want it to be autofit. how to do it? below is the part of the code of my program........can you all tell me as where to change the code so that everything in the excel is autofit.

&----

---

*& Form excel_download

&----

---

• Downloading the data to Excel

----

---

FORM excel_download.

*--M A C R O Declaration

DEFINE ole_check_error.

if &1 ne 0.

message e00 1(zz) with &1.

exit.

endif.

END-OF-DEFINITION.

LOOP AT it_final INTO gf_final.

CONCATENATE

gf_final-matnr

gf_final-maktx

gf_final-mtart

gf_final-atwtb

gf_final-mtpos_mara

gf_final-revlv1

gf_final-revlv2

INTO gf_final_client

SEPARATED BY g_deli.

APPEND gf_final_client TO gf_final_client1.

CLEAR gf_final_client.

ENDLOOP.

CONCATENATE text-006

text-007

text-008

text-019

text-009

text-004

text-017

INTO gf_final_client1 SEPARATED BY g_deli.

INSERT gf_final_client1 INDEX 1.

LOOP AT it_final1 INTO gf_final1.

CONCATENATE

gf_final1-werks

gf_final1-matnr

gf_final1-stlan

gf_final1-stlal

gf_final1-stlst

gf_final1-sobsl

gf_final1-eknam

gf_final1-dsnam

INTO gf_final_plant

SEPARATED BY g_deli.

APPEND gf_final_plant TO gf_final_plant1.

CLEAR gf_final_plant.

ENDLOOP.

CONCATENATE text-010

text-011

text-012

text-013

text-018

text-014

text-015

text-016

INTO gf_final_plant1 SEPARATED BY g_deli.

INSERT gf_final_plant1 INDEX 1.

*----Start Excel

IF gf_excel-header = space OR gf_excel-handle = -1.

CREATE OBJECT gf_excel 'EXCEL.APPLICATION'.

ENDIF.

*--- Get list of workbooks, initially empty

CALL METHOD OF gf_excel 'Workbooks' = gf_mapl.

SET PROPERTY OF gf_excel 'Visible' = 1.

CALL METHOD OF gf_mapl 'Add' = gf_map.

g_sheet_name = text-020.

GET PROPERTY OF gf_excel 'ACTIVESHEET' = gf_worksheet.

SET PROPERTY OF gf_worksheet 'Name' = g_sheet_name .

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

CALL METHOD OF gf_excel 'Cells' = gf_cell11

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Cells' = gf_cell21

EXPORTING

#1 = 1

#2 = 50.

CALL METHOD OF gf_excel 'Range' = gf_cell

EXPORTING

#1 = gf_cell11

#2 = gf_cell21.

GET PROPERTY OF gf_cell 'Font' = gf_font .

SET PROPERTY OF gf_font 'Bold' = 1 .

DATA l_rc TYPE i.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = gf_final_client1[]

CHANGING

rc = l_rc

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

OTHERS = 4.

CALL METHOD OF gf_excel 'Cells' = gf_cell11

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Cells' = gf_cell21

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Range' = gf_range

EXPORTING

#1 = gf_cell11

#2 = gf_cell21.

CALL METHOD OF gf_range 'Select'.

CALL METHOD OF gf_worksheet 'Paste'.

*-------Autofit

CALL METHOD OF gf_cell 'Select' .

GET PROPERTY OF gf_cell 'COLUMNS' = gf_auto.

SET PROPERTY OF gf_auto 'AutoFit' = 2.

g_sheet_name = text-021.

GET PROPERTY OF gf_excel 'Sheets' = gf_sheet2 .

CALL METHOD OF gf_sheet2 'Add' = gf_map.

SET PROPERTY OF gf_map 'Name' = g_sheet_name .

GET PROPERTY OF gf_excel 'ACTIVESHEET' = gf_worksheet.

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

CALL METHOD OF gf_excel 'Cells' = gf_cell11

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Cells' = gf_cell21

EXPORTING

#1 = 1

#2 = 50.

CALL METHOD OF gf_excel 'Range' = gf_cell

EXPORTING

#1 = gf_cell11

#2 = gf_cell21.

GET PROPERTY OF gf_cell 'Font' = gf_font .

SET PROPERTY OF gf_font 'Bold' = 1 .

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = gf_final_plant1[]

CHANGING

rc = l_rc

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

OTHERS = 4.

CALL METHOD OF gf_excel 'Cells' = gf_cell11

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Cells' = gf_cell21

EXPORTING

#1 = 1

#2 = 1.

CALL METHOD OF gf_excel 'Range' = gf_range

EXPORTING

#1 = gf_cell11

#2 = gf_cell21.

CALL METHOD OF gf_range 'Select'.

CALL METHOD OF gf_worksheet 'Paste'.

*--- Autofit

CALL METHOD OF gf_cell 'Select' .

GET PROPERTY OF gf_cell 'COLUMNS' = gf_auto.

SET PROPERTY OF gf_auto 'AutoFit' = 2.

*--- Disconnect from Excel

FREE OBJECT gf_zl.

FREE OBJECT gf_mapl.

FREE OBJECT gf_map.

FREE OBJECT gf_excel.

ENDFORM. "excel_download

Regards,

swathi

[naimesh_patel]

In your second AUTOFIT, the range should be the range of the data.

CALL METHOD OF gf_excel 'Range' = gf_range
EXPORTING
#1 = gf_cell11    " << Starting point of the data say A2
#2 = gf_cell21.  " << Ending point of the data   say  D10

Regards,

Naimesh Patel

[Former Member]

hi,

i did not understand what to do. can you please explain it to me clearly.

CALL METHOD OF gf_excel 'Range' = gf_range

EXPORTING

#1 = gf_cell11

#2 = gf_cell21.

what should i change here?

Thanks & regards,

Swathi

[naimesh_patel]

GF_CELL11 should have the value of the CELL on the Excel from where your data will be started.

E.g. You have 5 columns in your internal table.

You have created header in the first row. So, for the header the GF_CELL11 = 'A1' and GF_CELL21 = 'E1'.

Now, you have 10 entries in your internal table.

So, your GF_CELL11 = 'B1' and GF_CELL21 = 'E11'.

You need to pass these values to the method to get the RANGE. Now, you can use the AUTOFIT for this range.

Regards,

Naimesh Patel