Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

interface that will send the data from excel to logical file

Former Member

‎2008 Jul 08 1:40 PM

0 Kudos
484

hai guys,

i have to develop an output interface in which i will fetch the data from a z table and place it in an internal table.then i have to generate an excel file using the sap standard function module and it should have the data which i have in my internal table and i have to create output file(excel) in the given format .then i have to transfer this to the logical file destination.

here the main issue is i need the function module which will create excel file.

and also i need ur suggestions how to develop this object

reward points will b there for the solution.

regards

Mai

5 REPLIES 5
Read only

Former Member

‎2008 Jul 08 8:42 PM

0 Kudos
417

Hi,

Check this example:

&----

*& Report ZEXCELTEST *

*& *

&----

*& *

*& *

&----

REPORT ZEXCELTEST .

TABLES:

sflight.

parameters : p_file like GXXLT_F-FILE.

  • header data................................

DATA :

header1 LIKE gxxlt_p-text VALUE 'NAME',

header2 LIKE gxxlt_p-text VALUE 'Excel sheet'.

  • Internal table for holding the SFLIGHT data

DATA BEGIN OF t_sflight OCCURS 0.

INCLUDE STRUCTURE sflight.

DATA END OF t_sflight.

  • Internal table for holding the horizontal key.

DATA BEGIN OF t_hkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_h.

DATA END OF t_hkey .

  • Internal table for holding the vertical key.

DATA BEGIN OF t_vkey OCCURS 0.

INCLUDE STRUCTURE gxxlt_v.

DATA END OF t_vkey .

  • Internal table for holding the online text....

DATA BEGIN OF t_online OCCURS 0.

INCLUDE STRUCTURE gxxlt_o.

DATA END OF t_online.

  • Internal table to hold print text.............

DATA BEGIN OF t_print OCCURS 0.

INCLUDE STRUCTURE gxxlt_p.

DATA END OF t_print.

  • Internal table to hold SEMA data..............

DATA BEGIN OF t_sema OCCURS 0.

INCLUDE STRUCTURE gxxlt_s.

DATA END OF t_sema.

  • Retreiving data from sflight.

SELECT * FROM sflight

INTO TABLE t_sflight.

  • Text which will be displayed online is declared here....

t_online-line_no = '1'.

t_online-info_name = 'test'.

t_online-info_value = 'excel'.

APPEND t_online.

  • Text which will be printed out..........................

t_print-hf = 'H'.

t_print-lcr = 'L'.

t_print-line_no = '1'.

t_print-text = 'This is the header'.

APPEND t_print.

t_print-hf = 'F'.

t_print-lcr = 'C'.

t_print-line_no = '1'.

t_print-text = 'This is the footer'.

APPEND t_print.

  • Defining the vertical key columns.......

t_vkey-col_no = '1'.

t_vkey-col_name = 'MANDT'.

APPEND t_vkey.

t_vkey-col_no = '2'.

t_vkey-col_name = 'CARRID'.

APPEND t_vkey.

t_vkey-col_no = '3'.

t_vkey-col_name = 'CONNID'.

APPEND t_vkey.

t_vkey-col_no = '4'.

t_vkey-col_name = 'FLDATE'.

APPEND t_vkey.

  • Header text for the data columns................

t_hkey-row_no = '1'.

t_hkey-col_no = 1.

t_hkey-col_name = 'PRICE'.

APPEND t_hkey.

t_hkey-col_no = 2.

t_hkey-col_name = 'CURRENCY'.

APPEND t_hkey.

t_hkey-col_no = 3.

t_hkey-col_name = 'PLANETYPE'.

APPEND t_hkey.

t_hkey-col_no = 4.

t_hkey-col_name = 'SEATSMAX'.

APPEND t_hkey.

t_hkey-col_no = 5.

t_hkey-col_name = 'SEATSOCC'.

APPEND t_hkey.

t_hkey-col_no = 6.

t_hkey-col_name = 'PAYMENTSUM'.

APPEND t_hkey.

  • populating the SEMA data..........................

t_sema-col_no = 1.

t_sema-col_typ = 'STR'.

t_sema-col_ops = 'DFT'.

APPEND t_sema.

t_sema-col_no = 2.

APPEND t_sema.

t_sema-col_no = 3.

APPEND t_sema.

t_sema-col_no = 4.

APPEND t_sema.

t_sema-col_no = 5.

APPEND t_sema.

t_sema-col_no = 6.

APPEND t_sema.

t_sema-col_no = 7.

APPEND t_sema.

t_sema-col_no = 8.

APPEND t_sema.

t_sema-col_no = 9.

APPEND t_sema.

t_sema-col_no = 10.

t_sema-col_typ = 'NUM'.

t_sema-col_ops = 'ADD'.

APPEND t_sema.

CALL FUNCTION 'XXL_FULL_API'

EXPORTING

  • DATA_ENDING_AT = 54

  • DATA_STARTING_AT = 5

filename = p_file

header_1 = header1

header_2 = header2

no_dialog = 'X'

no_start = ' '

n_att_cols = 6

n_hrz_keys = 1

n_vrt_keys = 4

sema_type = 'X'

  • SO_TITLE = ' '

TABLES

data = t_sflight

hkey = t_hkey

online_text = t_online

print_text = t_print

sema = t_sema

vkey = t_vkey

EXCEPTIONS

cancelled_by_user = 1

data_too_big = 2

dim_mismatch_data = 3

dim_mismatch_sema = 4

dim_mismatch_vkey = 5

error_in_hkey = 6

error_in_sema = 7

file_open_error = 8

file_write_error = 9

inv_data_range = 10

inv_winsys = 11

inv_xxl = 12

OTHERS = 13

.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

Read only

Former Member
In response to Former Member

‎2008 Jul 09 8:49 AM

0 Kudos
417

hai fonduer,

here it is giving type conflict problem when i call the function module in the program and it id going to dump showing as -

> in front of fm.

Read only

Former Member
In response to Former Member

‎2008 Jul 09 1:12 PM

0 Kudos
417

Can you tell me the short dump?, I tested in 4.6 and 4.7 and it works.

Please tell me the dump.

Read only

Former Member

‎2008 Jul 09 9:05 AM

0 Kudos
417

Hi Mahesh,

Check this :

PARAMETERS:
  p_matnr TYPE mara-matnr.
DATA:
  it_mara TYPE STANDARD TABLE OF mara.

SELECT *
  FROM mara
  INTO TABLE it_mara
 WHERE matnr EQ p_matnr.

CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    filename      = 'C:\Documents and Settings\adil\Desktop\test1.xls'
    write_field_separator           = 'X'
  TABLES
    data_tab                        = it_mara[].

Check this links:

Search sdn forum.

http://searchsap.techtarget.com/tip/0,289483,sid21_gci956793,00.html

Regards

Adil

Read only

Former Member

‎2008 Jul 09 1:18 PM

0 Kudos
417

Check these examples also, they are very good:

REPORT ZTESTME .

TYPE-POOLS OLE2 .

DATA: EXCEL TYPE OLE2_OBJECT,

APPLICATION TYPE OLE2_OBJECT,

BOOKS TYPE OLE2_OBJECT,

BOOK TYPE OLE2_OBJECT,

SHEET TYPE OLE2_OBJECT,

CELL TYPE OLE2_OBJECT,

COLUMN TYPE OLE2_OBJECT.

DATA: V_KUNNR LIKE BSID-KUNNR,

V_NO TYPE I,

V_LN TYPE I,

V_TABIX LIKE SY-TABIX,

V_SHEET(10),

ROW TYPE I.

DATA: BEGIN OF ITAB OCCURS 10,

KUNNR LIKE BSID-KUNNR,

END OF ITAB.

DATA: BEGIN OF JTAB OCCURS 50,

KUNNR LIKE BSID-KUNNR,

GJAHR LIKE BSID-GJAHR,

BUDAT LIKE BSID-BUDAT,

WRBTR LIKE BSID-WRBTR,

END OF JTAB.

SELECT KUNNR

GJAHR

BUDAT

WRBTR

INTO (JTAB-KUNNR,

JTAB-GJAHR,

JTAB-BUDAT,

JTAB-WRBTR)

FROM BSID UP TO 10 ROWS

ORDER BY KUNNR .

IF SY-SUBRC EQ 0.

ITAB-KUNNR = JTAB-KUNNR.

APPEND ITAB.

CLEAR ITAB.

APPEND JTAB.

CLEAR JTAB.

ENDIF.

ENDSELECT.

DELETE ADJACENT DUPLICATES FROM ITAB.

IF NOT ITAB[] IS INITIAL.

CREATE OBJECT EXCEL 'EXCEL.SHEET'.

GET PROPERTY OF EXCEL 'Application' = APPLICATION.

SET PROPERTY OF APPLICATION 'Visible' = 1.

CALL METHOD OF APPLICATION 'Workbooks' = BOOKS.

CALL METHOD OF BOOKS 'Add' = BOOK.

LOOP AT ITAB.

V_SHEET = ITAB-KUNNR.

V_NO = V_NO + 1. ROW = 1.

PERFORM FILL_SHEET USING V_NO V_SHEET.

ENDLOOP.

FREE OBJECT: COLUMN,

SHEET,

BOOK,

BOOKS,

APPLICATION,

EXCEL NO FLUSH.

CALL FUNCTION 'FLUSH'.

ENDIF.

=======================================================================

REPORT ZZBGS010 .

----

  • Example: Interface between Microsoft Excel and ABAP/4 with up- and *

  • downloading of data plus executing Microsoft Excel. *

----

TABLES: USR04.

DATA: SIZE TYPE I.

DATA: BEGIN OF USER OCCURS 100.

INCLUDE STRUCTURE USR04.

DATA: END OF USER.

  • ---------------------------------------------------------------------*

  • Example: Select some data into an internal table. *

  • ---------------------------------------------------------------------*

SELECT * FROM USR04 INTO TABLE USER .

  • ---------------------------------------------------------------------*

  • Example: Downloading data in Microsoft Excel Format with automatic *

  • prompt popup dialog. *

  • ---------------------------------------------------------------------*

CALL FUNCTION 'DOWNLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL1.XLS'

FILETYPE = 'WK1' "ASC, WK1, DBF, DAT, bin

MODE = ' ' "Mode ' ' = Rewrite Mode 'A' = Appending

TABLES

DATA_TAB = USER.

  • ---------------------------------------------------------------------*

  • Example: Downloading data in Microsoft Excel Format without automatic*

  • prompt popup. *

  • ---------------------------------------------------------------------*

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL2.XLS'

FILETYPE = 'WK1' "ASC, WK1, DBF, DAT, bin

MODE = ' ' "Mode ' ' = Rewrite Mode 'A' = Appending

TABLES

DATA_TAB = USER.

CLEAR USER. "Clear buffer

REFRESH USER. "Refresh, empty internal table

----

  • Example: Uploading Microsoft Excel to ABAP/4 internal table. *

----

CALL FUNCTION 'UPLOAD'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL.prn'

FILETYPE = 'ASC'

IMPORTING

FILESIZE = SIZE

TABLES

DATA_TAB = USER.

----

  • Example: Starting Microsoft Excel and load sheet. *

----

CALL FUNCTION 'WS_EXECUTE'

EXPORTING

COMMANDLINE = 'C:\tmp\SAPEXL1.XLS'

PROGRAM = 'F:\APPL\WINDOWS\EXCEL5DK\EXCEL.EXE'

.

----

  • Example: Starting Microsoft Excel and load internal table as sheet *

----

CALL FUNCTION 'WS_EXCEL'

EXPORTING

FILENAME = 'C:\tmp\SAPEXL.XLS'

SYNCHRON = ' '

TABLES

DATA = USER.