topic Re: download table to excel in Application Development and Automation Discussions

download table to excel

Former Member — Sun, 01 Feb 2009 21:14:37 GMT

Hi all,

I am wanting to download a table from SAP into excel, all i need to be able to do is make the first line bold, manually set the column widths and maybe draw lines around each cell (can live without this requirement though)! Would be very grateful if anyone could tell me easiest way to code this?

Regards

Mart

Re: download table to excel

Former Member — Mon, 02 Feb 2009 02:53:56 GMT

HI,

If you want to Format the excel sheet then need to write the OLE for this.

Refer to this link..

[EXCEL using OLE|http://sapnet.ru/viewforum.php?f=9]

Edited by: Avinash Kodarapu on Feb 2, 2009 8:28 AM

Re: download table to excel

Former Member — Mon, 02 Feb 2009 03:39:55 GMT

Hi,

I think this can be done if you write a report that

1. read the data from the table into an internal table having structure excatly same fields/fields required to download

2.Simply use the GUI_DOWNLOAD FM giving the name of the internal table and format as 'ASC',just mention the name of the excel file with file pathname.

3.Save it,activate the code and simply run it .

Adding a code snnipet for better understanding:

TABLES : RF02K,LFA1.<name of the table to be download>

"declaration f the structure
<mention all the fields required to download>
DATA : BEGIN OF ITAB1 OCCURS 0,
      NR LIKE RF02K-LIFNR,
       KK LIKE RF02K-KTOKK,
       E1 LIKE LFA1-NAME1,
       TL LIKE LFA1-SORTL,
       D1 LIKE LFA1-LAND1,
       AS LIKE LFA1-SPRAS,
       END OF ITAB1.

start-of-selection.
"Read the record from the table into the internal table
SELECT B~LIFNR B~KTOKK B~NAME1 B~SORTL B~LAND1 B~SPRAS FROM LFA1 AS B  INTO TABLE ITAB1.

"simply call the FM 
CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
"fiel name with extension .xls,where data from teh internal table to be downloaded
   FILENAME                      = 'C:\Documents and Settings\pmittal\Desktop\BOOK2.xls' 
   FILETYPE                      = 'ASC' 
*   HAS_FIELD_SEPARATOR           = ' '
*   HEADER_LENGTH                 = 0
*   READ_BY_LINE                  = 'X'
*   DAT_MODE                      = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   CHECK_BOM                     = ' '
*   VIRUS_SCAN_PROFILE            =
*   NO_AUTH_CHECK                 = ' '
* IMPORTING
*   FILELENGTH                    =
*   HEADER                        =
  TABLES
    DATA_TAB                      = ITAB1."name of the intrenal table
* EXCEPTIONS
*   FILE_OPEN_ERROR               = 1
*   FILE_READ_ERROR               = 2
*   NO_BATCH                      = 3
*   GUI_REFUSE_FILETRANSFER       = 4
*   INVALID_TYPE                  = 5
*   NO_AUTHORITY                  = 6
*   UNKNOWN_ERROR                 = 7
*   BAD_DATA_FORMAT               = 8
*   HEADER_NOT_ALLOWED            = 9
*   SEPARATOR_NOT_ALLOWED         = 10
*   HEADER_TOO_LONG               = 11
*   UNKNOWN_DP_ERROR              = 12
*   ACCESS_DENIED                 = 13
*   DP_OUT_OF_MEMORY              = 14
*   DISK_FULL                     = 15
*   DP_TIMEOUT                    = 16
*   OTHERS                        = 17
          .
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
Endif.

Hope this will help you out.

Pooja

Re: download table to excel

Former Member — Mon, 02 Feb 2009 04:02:49 GMT

try this...

*Write Header

CALL METHOD cl_gui_frontend_services=>gui_download

EXPORTING

filename = v_fullpath

filetype = 'ASC'

write_field_separator = 'X'

CHANGING

data_tab = it_header "header fields

EXCEPTIONS

file_write_error = 1

no_batch = 2

gui_refuse_filetransfer = 3

invalid_type = 4

no_authority = 5

unknown_error = 6

header_not_allowed = 7

separator_not_allowed = 8

filesize_not_allowed = 9

header_too_long = 10

dp_error_create = 11

dp_error_send = 12

dp_error_write = 13

unknown_dp_error = 14

access_denied = 15

dp_out_of_memory = 16

disk_full = 17

dp_timeout = 18

file_not_found = 19

dataprovider_exception = 20

control_flush_error = 21

not_supported_by_gui = 22

error_no_gui = 23

OTHERS = 24.

IF sy-subrc <> 0.

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

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

ENDIF.

*Save File

CALL METHOD cl_gui_frontend_services=>gui_download

EXPORTING

filename = v_fullpath

filetype = 'ASC'

write_field_separator = 'X'

append = 'X'

CHANGING

data_tab = it_mstrings "the records

EXCEPTIONS

file_write_error = 1

no_batch = 2

gui_refuse_filetransfer = 3

invalid_type = 4

no_authority = 5

unknown_error = 6

header_not_allowed = 7

separator_not_allowed = 8

filesize_not_allowed = 9

header_too_long = 10

dp_error_create = 11

dp_error_send = 12

dp_error_write = 13

unknown_dp_error = 14

access_denied = 15

dp_out_of_memory = 16

disk_full = 17

dp_timeout = 18

file_not_found = 19

dataprovider_exception = 20

control_flush_error = 21

not_supported_by_gui = 22

error_no_gui = 23

OTHERS = 24.

IF sy-subrc <> 0.

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

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

ENDIF.

Re: download table to excel

Former Member — Mon, 02 Feb 2009 04:04:19 GMT

check this thread

hope this helps

regards

Aakash Banga

Re: download table to excel

Former Member — Mon, 02 Feb 2009 04:34:22 GMT

Hi Mart,

As ur requirment needed,check the following website which may help u..

[http://abaplovers.blogspot.com/2008/05/abap-internal-table-to-excel-sheet.html]

Regards,

Annevit.

Re: download table to excel

Former Member — Mon, 02 Feb 2009 04:56:28 GMT

Hi,

Do it through the report programming. fetch the value from table to report and

try out the following program.

orm download.

if sy-ucomm = 'DOWNLOAD' and sy-lsind = 1.

data: fullpath type string,

filename type string,

path type string,

user_action type i,

encoding type abap_encoding.

call method cl_gui_frontend_services=>file_save_dialog

exporting

window_title = 'Gui_Download Demo'

with_encoding = 'X'

initial_directory = 'C:\'

changing

filename = filename

path = path

fullpath = fullpath

user_action = user_action

file_encoding = encoding

exceptions

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

others = 4.

if sy-subrc <> 0.

exit.

endif.

if user_action <> cl_gui_frontend_services=>action_ok.

exit.

endif.

Regards

Rajesh Kumar

Re: download table to excel

Former Member — Mon, 02 Feb 2009 05:07:06 GMT

Hi,

The following OLE program will help u to achieve ur requirement. U can set Font , Color and etc.


* 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,
      d_interior type ole2_object,          " cell
      h_f type ole2_object,
workbook type ole2_object,
sheet type ole2_object,
cell type ole2_object,
cell1 type ole2_object,
column type ole2_object,
range type ole2_object,
borders type ole2_object,
button type ole2_object,
int type ole2_object,
font type ole2_object,
row type ole2_object..            " font
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.

  select * from spfli into table it_spfli up to 10 rows.
* display header
*
  create object h_excel 'EXCEL.APPLICATION'.
*
 SET PROPERTY OF H_EXCEL  'Visible' = 0.
**
  call method of h_excel 'Workbooks' = h_mapl.

** add a new workbook
  call method of h_mapl 'Add' = h_map.

* output column headings to active Excel sheet
  perform fill_cell using 1 1 1 'Airline'.
  perform fill_cell using 1 2 1 'Flight No'.
  perform fill_cell using 1 3 1 'Country'.
  perform fill_cell using 1 4 1 'Departure City'.
  perform fill_cell using 1 5 1 'Arrival City'.

* copy flights to active EXCEL sheet
  loop at it_spfli.
    h = sy-tabix + 1.
    perform fill_cell using h 1 0 it_spfli-carrid.
    perform fill_cell using h 2 0 it_spfli-connid.
    perform fill_cell using h 3 0 it_spfli-countryfr.
    perform fill_cell using h 4 0 it_spfli-cityfrom.
    perform fill_cell using h 5 0 it_spfli-cityto.

  endloop.

  perform save_book.
  perform err_hdl.
*---------------------------------------------------------------------*
*       FORM FILL_CELL                                                
*---------------------------------------------------------------------*
*       sets cell at coordinates i,j to value val boldtype bold       
*---------------------------------------------------------------------*
form fill_cell using i j bold val.
  call method of h_excel 'Cells' = h_zl exporting #1 = i #2 = j.
  set property of h_zl 'Value' = val .
  get property of h_zl 'Font' = h_f.
  set property of h_f 'Bold' = bold .
  if i = 1.
  get property of h_zl 'Interior' = d_interior .
  set property of d_interior 'ColorIndex' = 3.
   endif.
endform.
*&---------------------------------------------------------------------*
*F56E451208D111AB09006
*&---------------------------------------------------------------------*
*       outputs OLE error if any                                       
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
form err_hdl.
if sy-subrc <> 0.
  write: / 'Fehler bei OLE-Automation:'(010), sy-subrc.
endif.
endform.                    " ERR_HDL

form save_book.
  data: p_file type rlgrap-filename.
 p_file = 'C:\Customerdata2.xls'.
*get property of h_excel 'ActiveSheet' = sheet.
*free object sheet.
*free object workbook.
get property of h_excel 'ActiveWorkbook' = h_mapl.
call method of h_mapl 'SAVEAS' exporting #1 = p_file #2 = 1.
call method of h_mapl 'CLOSE'.
call method of h_excel 'QUIT'.
free object sheet.
free object h_mapl.
free object h_excel.
endform.

Hope this helps u.

Thanks.

Re: download table to excel

awin_prabhu — Mon, 02 Feb 2009 08:16:06 GMT

Hi dude,

Just go and check the standard program RSDEMO01.

Customize it to your needs..

Thanks...

Re: download table to excel

Former Member — Mon, 02 Feb 2009 14:32:51 GMT

Excellent thanks for the replies thats great. Have now managed to create an excel spreadsheet using OLE which downloads the table, sets heading to bold and auto fits the colums.

I am using the following command to autofit the table columns but was just wondering if anyone knows if it is possible to set a specific width for a specific column.

CALL METHOD OF application 'Columns' = COLUMN.

CALL METHOD OF COLUMN 'Autofit'.

Also does anyone know how to set a cell or range of cells to wrap its text?

Regards

Mart