Skip to Content
0
Nov 14, 2012 at 03:29 PM

Downloading report results to Excel with multiple tabs

46 Views

Hi Experts,

I have a report that was developed way back in 2008. When the user executes the report it would generate an excel with 3 tabs within it. I surfed through the code & see that the excel is built & filled with data in the below subroutine by using the OLE2_OBJECT. Now the issue is that the report is ending up in issues with different versions of MS office installed on the end-users systems. Now they want to come up with a report that would essentially do the same but work for any version. Could you please suggest as to which method I ought to adopt for living up to that?

Regards,

Uday

FORM create_excel_sheets TABLES lm111 STRUCTURE zhr111

USING lmhdr TYPE t_t_e

lmcol TYPE t_t_e.


DATA: lvhdr TYPE t_s_col ,

lvcol TYPE t_s_col ,

lvinx TYPE i .


DATA: lvapp TYPE ole2_object ,

lvbok TYPE ole2_object ,

lvsht TYPE ole2_object ,

lvcls TYPE ole2_object .

DATA: k TYPE ole2_object .



CONSTANTS: row_max TYPE i VALUE 256.


CREATE OBJECT lvapp 'excel.application'.


SET PROPERTY OF lvapp 'visible' = 1.


CALL METHOD OF lvapp 'Workbooks' = lvbok.

CALL METHOD OF lvbok 'Add'.


LOOP AT lm111.


IF sy-tabix EQ 1.


* Create first Excel Sheet

CALL METHOD OF lvapp 'Worksheets' = lvsht

EXPORTING #1 = 1.


ELSEIF sy-tabix EQ 2.


* Create second Excel Sheet

CALL METHOD OF lvapp 'Worksheets' = lvsht

EXPORTING #1 = 2.


ELSEIF sy-tabix EQ 3.


* Create third Excel Sheet

CALL METHOD OF lvapp 'Worksheets' = lvsht

EXPORTING #1 = 3.


ENDIF.


CALL METHOD OF lvsht 'Activate'.


SET PROPERTY OF lvsht 'Name' = lm111-fname.


lvinx = 0.

* Create header liner

LOOP AT lmhdr INTO lvhdr WHERE recno EQ lm111-recno.


lvinx = lvinx + 1.


CALL METHOD OF lvsht 'Cells' = lvcls EXPORTING #1 = lvinx.

SET PROPERTY OF lvcls 'Value' = lvhdr-value.


ENDLOOP.


* Insert detail info > Add 256 to LVINX to get next line


lvinx = 0.

* Create header liner

LOOP AT lmcol INTO lvcol WHERE recno EQ lm111-recno.


lvinx = ccmax * lvcol-linno + lvcol-colno.


CALL METHOD OF lvsht 'Cells' = lvcls EXPORTING #1 = lvinx.

SET PROPERTY OF lvcls 'Value' = lvcol-value.



* * Format CPR number

IF lvcol-recno = 1 AND lvcol-colno = 1.

SET PROPERTY OF lvcls 'NumberFormat' = '0000000000' .

ENDIF.

IF lvcol-recno = 3 AND lvcol-colno = 1.

SET PROPERTY OF lvcls 'NumberFormat' = '0000000000' .

ENDIF.


ENDLOOP.


ENDLOOP.


* Save manually


** Save excel speadsheet to particular filename

* CALL METHOD OF lvsht 'SaveAs'

* EXPORTING #1 = 'c:\exceldoc1.xls' "filename

* #2 = 1.

*

** Close Excel

* " SET PROPERTY OF lvapp 'visible' = 0.


ENDFORM.