topic Re: Excel down load. in Application Development and Automation Discussions

Excel down load.

Former Member — Mon, 03 Apr 2006 11:16:05 GMT

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.

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:19:41 GMT

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

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:22:13 GMT

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

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:22:22 GMT

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.

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:22:49 GMT

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

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:25:48 GMT

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

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:27:37 GMT

Hai Sagar,

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

Regards,

Srikanth.

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:29:58 GMT

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

Re: Excel down load.

Former Member — Mon, 03 Apr 2006 11:39:44 GMT

hi,

check this sample code...

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

regards

vijay