topic Re: to send data to excel in Application Development and Automation Discussions

to send data to excel

Former Member — Wed, 11 Jul 2007 09:55:52 GMT

hai all

urgent i had a problem to send sap data(created in modulepool programming) to excelsheet.

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 09:58:29 GMT

Use FM <b>SAP_CONVERT_TO_XLS_FORMAT</b>

reward if useful..

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 09:59:51 GMT

Hello Anand,

Either in Report or in MPP u'l be having thedata to be transported to XL in the Internal Table.

Check this :


REPORT ztest1 .
 
 
 
 
 
* this report demonstrates how to send some ABAP data to an
 
* EXCEL sheet using OLE automation.
 
include ole2incl.
 
* handles for OLE objects
 
data: h_excel type ole2_object,        " Excel object
 
      h_mapl type ole2_object,         " list of workbooks
 
      h_map type ole2_object,          " workbook
 
      h_zl type ole2_object,           " cell
 
      h_f type ole2_object,            " font
 
      h_c type ole2_object.            " color
 
 
 
DATA: FILENAME LIKE RLGRAP-FILENAME.
 
 
 
tables: spfli.
 
data  h type i.
 
* table of flights
 
data: it_spfli like spfli occurs 10 with header line.
 
 
 
*&---------------------------------------------------------------------*
 
 
 
*&   Event START-OF-SELECTION
 
*&---------------------------------------------------------------------*
 
 
 
start-of-selection.
 
* read flights
 
  select * from spfli into table it_spfli.
 
* display header
 
  uline (61).
 
  write: /     sy-vline no-gap,
 
          (3)  'Flg'(001) color col_heading no-gap, sy-vline no-gap,
 
          (4)  'Nr'(002) color col_heading no-gap, sy-vline no-gap,
 
          (20) 'Von'(003) color col_heading no-gap, sy-vline no-gap,
 
          (20) 'Nach'(004) color col_heading no-gap, sy-vline no-gap,
 
          (8)  'Zeit'(005) color col_heading no-gap, sy-vline no-gap.
 
  uline /(61).
 
* display flights
 
  loop at it_spfli.
 
    write: / sy-vline no-gap,
 
             it_spfli-carrid color col_key no-gap, sy-vline no-gap,
 
             it_spfli-connid color col_normal no-gap, sy-vline no-gap,
 
             it_spfli-cityfrom color col_normal no-gap, sy-vline no-gap,
 
             it_spfli-cityto color col_normal no-gap, sy-vline no-gap,
 
             it_spfli-deptime color col_normal no-gap, sy-vline no-gap.
 
  endloop.
 
  uline /(61).
 
* tell user what is going on
 
  call function 'SAPGUI_PROGRESS_INDICATOR'
 
     exporting
 
*           PERCENTAGE = 0
 
           text       = text-007
 
       exceptions
 
            others     = 1.
 
* start Excel
 
  create object h_excel 'EXCEL.APPLICATION'.
 
*  PERFORM ERR_HDL.
 
 
 
  set property of h_excel  'Visible' = 1.
 
*  CALL METHOD OF H_EXCEL 'FILESAVEAS' EXPORTING #1 = 'c:kis_excel.xls'
 
  .
 
 
 
*  PERFORM ERR_HDL.
 
* tell user what is going on
 
  call function 'SAPGUI_PROGRESS_INDICATOR'
 
     exporting
 
*           PERCENTAGE = 0
 
           text       = text-008
 
       exceptions
 
            others     = 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.
 
* tell user what is going on
 
  call function 'SAPGUI_PROGRESS_INDICATOR'
 
     exporting
 
*           PERCENTAGE = 0
 
           text       = text-009
 
       exceptions
 
            others     = 1.
 
* output column headings to active Excel sheet
 
  perform fill_cell using 1 1 1 200 'Carrier id'(001).
 
  perform fill_cell using 1 2 1 200 'Connection id'(002).
 
  perform fill_cell using 1 3 1 200 'City from'(003).
 
  perform fill_cell using 1 4 1 200 'City to'(004).
 
  perform fill_cell using 1 5 1 200 'Dep. Time'(005).
 
  loop at it_spfli.
 
* copy flights to active EXCEL sheet
 
    h = sy-tabix + 1.
 
    if it_spfli-carrid cs 'AA'.
 
      perform fill_cell using h 1 0 000255000 it_spfli-carrid.
 
    elseif it_spfli-carrid cs 'AZ'.
 
      perform fill_cell using h 1 0 168000000 it_spfli-carrid.
 
    elseif it_spfli-carrid cs 'JL'.
 
      perform fill_cell using h 1 0 168168000 it_spfli-carrid.
 
    elseif it_spfli-carrid cs 'LH'.
 
      perform fill_cell using h 1 0 111111111 it_spfli-carrid.
 
    elseif it_spfli-carrid cs 'SQ'.
 
      perform fill_cell using h 1 0 100100100 it_spfli-carrid.
 
    else.
 
      perform fill_cell using h 1 0 000145000 it_spfli-carrid.
 
    endif.
 
 
 
    if it_spfli-connid lt 400.
 
      perform fill_cell using h 2 0 255000255 it_spfli-connid.
 
    elseif it_spfli-connid lt 800.
 
      perform fill_cell using h 2 0 077099088 it_spfli-connid.
 
    else.
 
      perform fill_cell using h 2 0 246156138 it_spfli-connid.
 
    endif.
 
 
 
    if it_spfli-cityfrom cp 'S*'.
 
      perform fill_cell using h 3 0 155155155 it_spfli-cityfrom.
 
    elseif it_spfli-cityfrom cp 'N*'.
 
      perform fill_cell using h 3 0 189111222 it_spfli-cityfrom.
 
    else.
 
      perform fill_cell using h 3 0 111230222 it_spfli-cityfrom.
 
    endif.
 
 
 
    if it_spfli-cityto cp 'S*'.
 
      perform fill_cell using h 4 0 200200200 it_spfli-cityto.
 
    elseif it_spfli-cityto cp 'N*'.
 
      perform fill_cell using h 4 0 000111222 it_spfli-cityto.
 
    else.
 
      perform fill_cell using h 4 0 130230230 it_spfli-cityto.
 
    endif.
 
 
 
    if it_spfli-deptime lt '020000'.
 
      perform fill_cell using h 5 0 145145145 it_spfli-deptime.
 
    elseif it_spfli-deptime lt '120000' .
 
      perform fill_cell using h 5 0 015215205 it_spfli-deptime.
 
    elseif it_spfli-deptime lt '180000' .
 
      perform fill_cell using h 5 0 000215205 it_spfli-deptime.
 
    else.
 
      perform fill_cell using h 5 0 115115105 it_spfli-deptime.
 
    endif.
 
  endloop.
 
 
 
 
 
* EXCEL FILENAME
 
  CONCATENATE SY-REPID '_' SY-DATUM+6(2) '_' SY-DATUM+4(2) '_'
 
              SY-DATUM(4) '_' SY-UZEIT '.XLS' INTO FILENAME.
 
 
 
  CALL METHOD OF H_MAP 'SAVEAS' EXPORTING #1 = FILENAME.
 
 
 
 
 
  free object h_excel.
 
  perform 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
 
 
 
*&---------------------------------------------------------------------*
 
 
 
*&      Form  ERR_HDL
 
*&---------------------------------------------------------------------*
 
 
 
*       outputs OLE error if any                                       *
 
 
 
*----------------------------------------------------------------------*
 
 
 
*  -->  p1        text
 
*  <--  p2        text
 
*----------------------------------------------------------------------*
 
 
 
form err_hdl.
 
  if sy-subrc <> 0.
 
    write: / 'OLE-Automation Error:'(010), sy-subrc.
 
    stop.
 
  endif.
 
endform.                    " ERR_HDL

regards,

Deepu.K

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 09:59:58 GMT

hi,

chk this simple eg. will work for u.

REPORT  Z_GUI_DOWNLOAD_TO_EXCEL.
 
data : itab type mara occurs 0 with header line.
 
select * into itab
         from mara
         up to 10 rows.
         append itab.
endselect.
 
data : begin of itab1 occurs 0,
         line(50) type c,
       end of itab1.
 
itab1-line = 'field1 description'.
append itab1.
itab1-line = 'field2 desc'.
append itab1.
itab1-line = 'field3 desc'.
append itab1.
*--and so on you have to add up the records in itab1.
 
CALL FUNCTION 'GUI_DOWNLOAD'
 EXPORTING
*   BIN_FILESIZE                  = ' '
*   CODEPAGE                      = ' '
   FILENAME                      = 'C:ABCD.XLS '
   FILETYPE                      = 'DAT'
*   MODE                          = ' '
*   WK1_N_FORMAT                  = ' '
*   WK1_N_SIZE                    = ' '
*   WK1_T_FORMAT                  = ' '
*   WK1_T_SIZE                    = ' '
*   COL_SELECT                    = ' '
*   COL_SELECTMASK                = ' '
*   NO_AUTH_CHECK                 = ' '
* IMPORTING
*   FILELENGTH                    = FILELENGTH
  TABLES
    DATA_TAB                      = ITAB
   FIELDNAMES                    = ITAB1
 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
   NO_AUTHORITY                  = 10
   OTHERS                        = 11
          .
IF SY-SUBRC <> 0.
 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

Rgds

Reshma

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 10:00:27 GMT

Use the function module XXL_SIMPLE_API

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 10:00:39 GMT

Hi,

refer to the following link:

http://sap-img.com/abap/download-in-background-in-excel-format.htm

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 10:01:13 GMT

Hi,

go through the following example-----

----

T A B L E S D E C L E R A T I O N

----

Tables : MARA , MAKT , MARD .

----

T Y P E - P O O L S D E C L E R A T I O N

----

TYPE-POOLS OLE2 .

----

D A T A D E C L E R A T I O N

----

*--~~-handles for OLE objects~~--

DATA: H_EXCEL TYPE OLE2_OBJECT, " Excel object

H_MAPL TYPE OLE2_OBJECT, " list of workbooks

H_MAP TYPE OLE2_OBJECT, " workbook

H_ZL TYPE OLE2_OBJECT, " cell

H_FORMAT2 TYPE OLE2_OBJECT,

H_FORMAT3 TYPE OLE2_OBJECT,

H_FORMAT4 TYPE OLE2_OBJECT,

H_FORMAT1 TYPE OLE2_OBJECT.

----

I N T E R N A L T A B L E

----

Data : Begin of it_1 occurs 0 ,

matnr like mara-matnr ,

maktx like makt-maktx ,

werks like mard-werks ,

labst like mard-labst ,

End of it_1.

----

S E L E C T I O N S C R E E N

----

SELECT-OPTIONS : S_MATNR FOR MARA-MATNR.

----

S T A R T - O F - S E L E C T I O N

----

START-OF-SELECTION.

SELECT

A~MATNR

B~MAKTX

C~WERKS

SUM( C~LABST )

INTO TABLE IT_1

FROM ( ( mara as a inner join makt as b on

a_{matnr = b}matnr and b~spras = 'EN' )

INNER JOIN MARD AS C ON A_{MATNR = C}MATNR )

WHERE A~MATNR IN S_MATNR

GROUP BY A_{MATNR B}MAKTX C~WERKS.

SORT IT_1 BY MATNR WERKS.

*CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING
PERCENTAGE = 100
TEXT = TEXT-I08
EXCEPTIONS
OTHERS = 1.

*--~~-REATE AN EXCEL DOCUMENT~~--

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

*--~~-SET AN OBJECT FOR WORKBOOK~~---

CALL METHOD OF H_EXCEL 'Workbooks' = H_MAPL.

*----~~CREATE A WORKBOOK~~-----

CALL METHOD OF H_MAPL 'ADD' = h_map.

CALL METHOD OF H_EXCEL 'Cells' = H_ZL EXPORTING #1 = 1 #2 = 1.

*--~~-CHANGE THE PROPERTY OF THE CELL~~--

GET PROPERTY OF H_ZL 'INTERIOR' = H_FORMAT4.

SET PROPERTY OF H_FORMAT4 'COLORINDEX' = 22.

GET PROPERTY OF H_ZL 'COLUMNS' = H_FORMAT4.

CALL METHOD OF H_FORMAT4 'AUTOFIT'.

*--~~FOR MATERIAL NUMBER~~---

SET PROPERTY OF H_ZL 'VALUE' = 'Material Number'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 2 .

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT4.

*--~~FOR MATERIAL DESCRIPTION~~---

SET PROPERTY OF H_ZL 'VALUE' = 'Material Description'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 3 .

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT4.

*--~~FOR PLANT~~---

SET PROPERTY OF H_ZL 'VALUE' = 'Plant'.

call method of h_excel 'Cells' = h_zl exporting #1 = 1 #2 = 4 .

*--~~FOR STOCK~~----

SET PROPERTY OF H_ZL 'VALUE' = 'Stock'.

*----~~DECLEARING A VARIABLE~~----

data : v_t type i.

clear v_t.

----

P R I N T T H E O U T P U T

----

LOOP AT IT_1.

v_t = sy-tabix + 1.

*--~~for columns format~~---

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT1.

SET PROPERTY OF H_FORMAT1 'ColumnWidth' = 20.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 1.

*--~~for material number~~---

SET PROPERTY OF H_ZL 'VALUE' = it_1-matnr.

CALL METHOD OF H_EXCEL 'COLUMNS' = H_FORMAT1.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 2 .

*--~~for material description~~----

SET PROPERTY OF H_ZL 'VALUE' = it_1-maktx.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 3 .

*--~~for plants~~---

SET PROPERTY OF H_ZL 'VALUE' = it_1-werks.

call method of h_excel 'Cells' = h_zl exporting #1 = v_t #2 = 4 .

*--~~for stock~~--

SET PROPERTY OF H_ZL 'VALUE' = it_1-labst.

ENDLOOP.

*-------METHOD FOR SAVE THE BOOK -

call method of h_excel 'SAVE' = H_FORMAT4.

*----~~DECLARE WHERE ITS SAVE~~--

SET PROPERTY OF H_FORMAT4 'FILENAME' = 'C:\TESTS.XLS'..

*******do rewards if usefull

Regards,

vijay

Re: to send data to excel

Former Member — Wed, 11 Jul 2007 10:02:38 GMT

Hi,

try For uploading use the FM TEXT_CONVERT_XLS_TO_SAP

<b>Reward points</b>

Regards