Downloding from internal table to excel

[Former Member]

Hi All,

I am using ole concept to download data from internal table to excel because i want to fill few records with colors and few with bold etc.

But this ole concept is downloading to excel line by line and taking much time.

How can i improve the performance in this concept or is there any other concept please let me know.

i am using fallowng logic to download to excel.

******************************************************

*Generating the Excel report in the foreground

******************************************************

data: h_excel type ole2_object, " Excel object

h_mapl type ole2_object, " list of workbooks

h_map type ole2_object, " workbook

• start Excel

create object h_excel 'EXCEL.APPLICATION'.

set property of h_excel 'Visible' = 1.

• get list of workbooks, initially empty

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.

• output column headings to active Excel sheet

perform fill_cell using 1 1 1 000 'Job Name'(001).

perform fill_cell using 1 2 1 000 'Variant'(002).

perform fill_cell using 1 3 1 000 'Description'(003).

perform fill_cell using 1 4 1 000 'Run Date'(004).

perform fill_cell using 1 5 1 000 'Run Time'(005).

perform fill_cell using 1 6 1 000 'Duration'(006).

perform fill_cell using 1 7 1 000 'Spool'(007).

perform fill_cell using 1 8 1 000 'Records Upl'(008).

perform fill_cell using 1 9 1 000 'Error'(009).

perform fill_cell using 1 10 1 000 'Total'(010).

perform fill_cell using 1 11 1 000 'Action'(011).

perform fill_cell using 1 12 1 000 'ReRun'(012).

loop at t_final.

• copy datato active EXCEL sheet

h = sy-tabix + 1.

perform fill_cell using h 1 0 000 t_final-jobname.

perform fill_cell using h 2 0 000 t_final-variant.

perform fill_cell using h 3 0 000 t_final-description.

perform fill_cell using h 4 0 000 t_final-strtdate.

perform fill_cell using h 5 0 000 t_final-strttime.

perform fill_cell using h 6 0 000 t_final-duration.

perform fill_cell using h 7 0 000 t_final-listident.

perform fill_cell using h 8 0 000 t_final-rec_upl.

perform fill_cell using h 9 0 000 t_final-rec_err.

perform fill_cell using h 10 0 000 t_final-rec_tot.

if t_final-comment eq 'job did not run'.

perform fill_cell using h 11 0 200 t_final-comment.

elseif t_final-rec_err eq 0.

perform fill_cell using h 11 0 000 t_final-comment.

else.

perform fill_cell using h 11 0 200 t_final-comment.

endif.

perform fill_cell using h 12 0 000 t_final-rerun.

endloop.

• CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

call method of h_excel 'Worksheets' = h_mapl." EXPORTIN G #1 = 2.

perform err_hdl.

• add a new workbook

call method of h_mapl 'Add' = h_map exporting #1 = 2.

perform err_hdl.

• tell user what is going on

set property of h_map 'NAME' = 'COPY'.

loop at t_final.

• copy flights to active EXCEL sheet

h = sy-tabix + 1.

perform fill_cell using h 1 0 000 t_final-jobname.

perform fill_cell using h 2 0 000 t_final-variant.

perform fill_cell using h 3 0 000 t_final-description.

perform fill_cell using h 4 0 000 t_final-strtdate.

perform fill_cell using h 5 0 000 t_final-strttime.

perform fill_cell using h 6 0 000 t_final-duration.

perform fill_cell using h 7 0 000 t_final-listident.

perform fill_cell using h 8 0 000 t_final-rec_upl.

perform fill_cell using h 9 0 000 t_final-rec_err.

perform fill_cell using h 10 0 000 t_final-rec_tot.

if t_final-comment eq 'job did not run'.

perform fill_cell using h 11 0 200 t_final-comment.

elseif t_final-rec_err eq 0.

perform fill_cell using h 11 0 000 t_final-comment.

else.

perform fill_cell using h 11 0 200 t_final-comment.

endif.

perform fill_cell using h 12 0 000 t_final-rerun.

endloop.

free object h_excel.

perform err_hdl.

&----

---

*& Form ERR_HDL

&----

---

• outputs OLE error if any *

----

---

form err_hdl.

if sy-subrc <> 0.

write: / 'Batch Job Automation Carried Out Succesfully'.

stop.

endif.

endform. " ERR_HDL .

----

---

• FORM FILL_CELL *

----

---

• sets cell at coordinates i,j to value val boldtype bold *

----

---

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

perform err_hdl.

endform. "FILL_CELL

[Former Member]

Hi,

You are populating the Cells one by one from the internal table, and all of them are coloured and font is bold. the method is OK, but when the amount of data is huge - it is going to take a longer time. May be the performance will improve a little if you turn the visibility off.

I ll suggest you to use ALV_XXL_CALL, it can color the key columns, at the same time you can have colored headings - and the performance is good. It is the same function module called when you do a "Export to Excel" from an ALV grid. But you can compain about the Font characteristics - as this does not change the Font size etc.

Your code has got a lot of freedom as long as the formatting is concerned - for bigger data - i ll suggest you to use a WS_DOWNLOAD kind of a function module to get the data at once in the presentation server and then call Excel methods and do the formatting.