Excel down load.

[Former Member]

Hi, Is there any FM that will transfer the data(rows)from an internal table as columns of an Excel sheet? Please help me...Thanks.

[former_member181962]

Hi Sagar,

What you can do is to dynamically build another internal table which has the columns built accorduing to the rows in the first itab.

Then use the NOrmal GUI_DOWNLOAD function module .

Regards,

Ravi

[Former Member]

HI

YOU CAN USE THE FM <b>MS_EXCEL_OLE_STANDARD_DAT</b> TO Download internal table and opens it in MS Excel

IF THIS FINDS USEFUL PLEASE REWARD POINTS

REGARDS

ANOOP

[Former Member]

Hai Sagar,,

Just try this sample code..

TYPE : BEGIN OF ty_download_table,

rec(2000) type c.

END OF ty_download_table.

DATA : lt_download_table type table of

ty_download_table with header line.

DATA : lv_col type i.

DESCRIBE TABLE ITAB1 LINES lv_no_of_columns.

LOOP AT ITAB2.

lv_col = lv_col + 1.

• Append row and start a new row

if lv_col eq lv_no_of_columns.

APPEND lt_download_table.

lv_col = 0.

clear : lt_download_table.

endif.

concatenate ITAB2-column_value lt_download_table-rec

into lt_download_table-rec separated by ','.

ENDLOOP.

Now use GUI_DOWNLOAD to download lt_download_table as 'TXT'.

Also just check out this link...

http://www.sapdevelopment.co.uk/programs/custom/zdownempdata.htm

Regards,

Srikanth.

[Former Member]

Hai Sagar,

Just check out these links.......

Regards,

Srikanth.

[Former Member]

hi sagar,

data: begin of itab occurs 0,

vbeln like vbak-vbeln,

posnr like vbap-posnr,

end of itab.

select vbeln posnr from vbap up to 20 rows into table

itab.

INCLUDE OLE2INCL.

• EXCEL sheet using OLE automation.INCLUDE OLE2INCL.

• handles for OLE objects

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_WORK TYPE OLE2_OBJECT,

H_SHEET TYPE OLE2_OBJECT,

H_CELL TYPE OLE2_OBJECT,

V_COL LIKE SY-TABIX. " column number of the cell

DATA: V_STEP(30),

V_FILE LIKE RLGRAP-FILENAME.

• tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = 'Creating Excel...'

EXCEPTIONS

OTHERS = 1.

• start Excel

V_STEP = 'Starting Excel'.

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

CALL METHOD OF H_EXCEL 'APPEND'

EXPORTING

#1 = 'D:\Sappdf\ABAP Training\sheettr.xls'.

• PERFORM ERR_HDL.* tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = 'Adding Data to Excel...'

EXCEPTIONS

OTHERS = 1.

• Get the list of workbooks

V_STEP = 'Preaparing Excel'.

CALL METHOD OF H_EXCEL 'WORKBOOKS' = H_WORK.

PERFORM ERR_HDL.

• Add new workbook (create a file)

CALL METHOD OF H_WORK 'ADD'.

PERFORM ERR_HDL.

• Get the created worksheet************************Sheet Number

CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET

EXPORTING

#1 = 3.

************************Sheet Number

PERFORM ERR_HDL.

• Activate (select) the first sheet

CALL METHOD OF H_SHEET 'ACTIVATE'.

PERFORM ERR_HDL.

• tell user what is going on

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

• PERCENTAGE = 0

TEXT = 'Adding Data to Excel...'

EXCEPTIONS

OTHERS = 1.

• output column headings to active Excel sheet

V_STEP = 'Adding data to Excel'.

LOOP AT ITAB.

V_COL = SY-TABIX.

PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.

PERFORM FILL_CELL USING 2 V_COL ITAB-posnr.

ENDLOOP.

V_STEP = 'Releasing Excel'.

FREE OBJECT H_EXCEL.

PERFORM ERR_HDL.

H_EXCEL-HANDLE = -1.

&----

---

*& Form ERR_HDL

&----

---

• text

----

---

FORM ERR_HDL.

IF SY-SUBRC <> 0.

WRITE: / 'Error in processing Excel File:', V_STEP.

STOP.

ENDIF.

ENDFORM. "ERR_HDL

&----

---

*& Form FILL_CELL

&----

---

• text

----

---

• -->ROW text

• -->COL text

• -->VAL text

----

---

FORM FILL_CELL USING ROW COL VAL.

CALL METHOD OF H_EXCEL 'Cells' = H_CELL

EXPORTING

#1 = ROW

#2 = COL.

PERFORM ERR_HDL.

SET PROPERTY OF H_CELL 'Value' = VAL .

PERFORM ERR_HDL.

endform. "FILL_CELL

in this program change the ROW and COL in the sub-routine FILL_CELL by which you can get the row as col adn col as row..

u can change it as per ur requirement..

[Former Member]

Hi,

Use this fn module: EXCEL_OLE_STANDARD_DAT

TABLES: USR03,DD02L.

DATA: ZX030L LIKE X030L.

DATA BEGIN OF ZDFIES OCCURS 0.
     INCLUDE STRUCTURE DFIES.
DATA END OF ZDFIES.

DATA: BEGIN OF FLDITAB OCCURS 0,
      FLDNAME(11) TYPE C,
      END OF FLDITAB.

DATA ITABUSR03 LIKE USR03 OCCURS 0 WITH HEADER LINE.

DATA TNAME LIKE DD02L-TABNAME.

SELECT * FROM USR03 INTO TABLE ITABUSR03.

TNAME = 'USR03'.

PERFORM GETFIELEDS.
PERFORM SHOW123.
********************************************
FORM GETFIELEDS.
     CALL FUNCTION 'GET_FIELDTAB'
      EXPORTING
          LANGU              = SY-LANGU
          ONLY               = SPACE
          TABNAME            = TNAME
          WITHTEXT           = 'X'
      IMPORTING
          HEADER             = ZX030L
      TABLES
          FIELDTAB           = ZDFIES
      EXCEPTIONS
          INTERNAL_ERROR      = 01
          NO_TEXTS_FOUND      = 02
          TABLE_HAS_NO_FIELDS = 03
          TABLE_NOT_ACTIV     = 04.

     CASE SY-SUBRC.
        WHEN 0.
          LOOP AT ZDFIES.
               FLDITAB-FLDNAME = ZDFIES-FIELDNAME.
               APPEND FLDITAB.
          ENDLOOP.
        WHEN OTHERS.
             MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
              with  SY-SUBRC.
      ENDCASE.

ENDFORM.
***********************************
FORM SHOW123.

CALL FUNCTION 'EXCEL_OLE_STANDARD_DAT'
     EXPORTING
          FILE_NAME                 = 'C:USR03.XLS'
          DATA_SHEET_NAME           = 'USER LIST'
    TABLES
         DATA_TAB                  =  ITABUSR03
         FIELDNAMES                =  FLDITAB

    EXCEPTIONS
         FILE_NOT_EXIST            = 1
         FILENAME_EXPECTED         = 2
         COMMUNICATION_ERROR       = 3
         OLE_OBJECT_METHOD_ERROR   = 4
         OLE_OBJECT_PROPERTY_ERROR = 5
         INVALID_FILENAME          = 6
         INVALID_PIVOT_FIELDS      = 7
         DOWNLOAD_PROBLEM          = 8
         OTHERS                    = 9.
IF SY-SUBRC <> 0.
 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.


ENDFORM.

Regards,

Gayathri

Message was edited by: Gayathri Hariharan

[Former Member]

DATA: g_v_excel TYPE ole2_object,

g_v_books TYPE ole2_object,

g_fullpt TYPE rlgrap-filename,

charts TYPE ole2_object ,

addchart TYPE ole2_object ,

g_v_range TYPE ole2_object,

select TYPE ole2_object,

worksheet TYPE ole2_object,

activesheet TYPE ole2_object,

activechart TYPE ole2_object,

applycustomtype TYPE ole2_object,

g_chk_change TYPE c. " SMRX++ 27.01.06

&----

---

*& Form DOWNLOAD_DATA_INTO_EXCEL

&----

---

FORM download_data_into_excel .

DATA :lws_cnt TYPE i,

lws_cntc(5),

lws_rng(5),

lws_chtitle(50).

• Opens the Excel file

PERFORM fill_excelfile_headings.

PERFORM download_data_to_excel.

CREATE OBJECT g_v_excel 'Excel.Application'.

CALL METHOD OF g_v_excel 'workbooks' = g_v_books.

CALL METHOD OF g_v_books 'Open'

EXPORTING

#1 = g_fullpt.

SET PROPERTY OF g_v_excel 'Visible' = 1.

IF sy-subrc <> 0 .

FREE OBJECT g_v_books .

EXIT .

ENDIF .

ENDFORM. " DOWNLOAD_DATA_INTO_EXCEL

FORM fill_excelfile_headings .

CLEAR g_v_header.

REFRESH: g_t_header.

g_v_header-colname = 'CUstomer no'. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-029. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-021. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-019. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-020. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-022. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-023. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-025. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-032. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-026. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-032. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-027. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-032. APPEND g_v_header TO g_t_header.

g_v_header-colname = text-028. APPEND g_v_header TO g_t_header.

ENDFORM. " FILL_EXCELFILE_HEADINGS

FORM download_data_to_excel.

DATA: l_filename TYPE string,

l_fullpath TYPE string.

CONCATENATE 'DATA' sy-uzeit+2(4) '.DAT' INTO l_filename.

CALL FUNCTION 'RH_COMPILE_FILENAME'

EXPORTING

filename = l_filename

path_flag = 'W'

IMPORTING

fullpath = l_fullpath

EXCEPTIONS

OTHERS = 1.

CLEAR: g_fullpt.

MOVE l_fullpath TO g_fullpt.

REFRESH: g_t_download_data.

CLEAR: g_v_screen3.

LOOP AT g_t_screen3 INTO g_v_screen3.

MOVE-CORRESPONDING g_v_screen3 TO g_v_download_data.

APPEND g_v_download_data TO g_t_download_data.

CLEAR: g_v_download_data, g_v_screen3.

ENDLOOP.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = g_fullpt

filetype = 'DAT'

TABLES

data_tab = g_t_download_data

fieldnames = g_t_header

EXCEPTIONS

file_open_error = 1

file_write_error = 2

invalid_filesize = 3

invalid_type = 4

no_batch = 5

unknown_error = 6

invalid_table_width = 7

gui_refuse_filetransfer = 8

customer_error = 9

OTHERS = 10.

IF sy-subrc <> 0.

ELSE.

g_chk_download = 'X'.

ENDIF.

ENDFORM. "DOWNLOAD_DATA_TO_EXCEL

&----

---

*& Form DOWNLOAD_DATA_TO_EXCEL

&----

---

FORM download_data_to_excel.

DATA: l_filename TYPE string,

l_fullpath TYPE string.

CONCATENATE 'DATA' sy-uzeit+2(4) '.DAT' INTO l_filename.

CALL FUNCTION 'RH_COMPILE_FILENAME'

EXPORTING

filename = l_filename

path_flag = 'W'

IMPORTING

fullpath = l_fullpath

EXCEPTIONS

OTHERS = 1.

CLEAR: g_fullpt.

MOVE l_fullpath TO g_fullpt.

REFRESH: g_t_download_data.

CLEAR: g_v_screen3.

LOOP AT g_t_screen3 INTO g_v_screen3.

MOVE-CORRESPONDING g_v_screen3 TO g_v_download_data.

APPEND g_v_download_data TO g_t_download_data.

CLEAR: g_v_download_data, g_v_screen3.

ENDLOOP.

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

filename = g_fullpt

filetype = 'DAT'

TABLES

data_tab = g_t_download_data

fieldnames = g_t_header

EXCEPTIONS

file_open_error = 1

file_write_error = 2

invalid_filesize = 3

invalid_type = 4

no_batch = 5

unknown_error = 6

invalid_table_width = 7

gui_refuse_filetransfer = 8

customer_error = 9

OTHERS = 10.

IF sy-subrc <> 0.

ELSE.

g_chk_download = 'X'.

ENDIF.

ENDFORM. "DOWNLOAD_DATA_TO_EXCEL

[former_member188685]

hi,

check this sample code...

http://www.sap-img.com/abap/download-to-excel-with-format-border-color-cell-etc.htm

regards

vijay