Downloading data into excel

[Former Member]

Hi,

I would like to download data into excel and would like to prompt the user to save the file into a location of their choice. How can I achieve this

CREATE OBJECT H_EXCEL 'EXCEL.APPLICATION'.

PERFORM ERR_HDL.

SET PROPERTY OF H_EXCEL 'Visible' = 1.

CALL METHOD OF H_WORK 'ADD'.

PERFORM ERR_HDL.

• Get the created worksheet

CALL METHOD OF H_EXCEL 'WORKSHEETS' = H_SHEET EXPORTING #1 = 1.

PERFORM ERR_HDL.

• Activate (select) the first sheet

CALL METHOD OF H_SHEET 'ACTIVATE'.

PERFORM ERR_HDL.

LOOP AT ITAB.

V_COL = SY-TABIX.

PERFORM FILL_CELL USING 1 V_COL ITAB-vbeln.

PERFORM FILL_CELL USING 3 V_COL ITAB-posnr.

ENDLOOP.

The above code creates an excel file and writes the code in intercative fashion i.e the excel opens and we can view the downloading of data directly. But instead I would like this process to happen in the background and then be prompted to save the file in some location.

Please help.

[suresh_datti]

Hi Jayanthi,

Have you tried uding the method FILE_SAVE_DIALOG in the class CL_GUI_FRONTEND_SERVICES ?

Regards,

Suresh Datti

[Former Member]

Hi Suresh,

I do not know anything abt this method. How do I integrate this method with my OLE logic which I had specified earlier?

[athavanraja]

Why not simply use GUI_DOWNLOAD ,

to get the file save dialog

data: wf_filetab type filetable .
data: wf_filerc type i ,
      wf_filename type string ,
      wf_dest_fname type string ,
      wf_path type string ,
      wf_full_path type string .

data: wf_extension type string ,
      wf_fname type string .
clear : wf_path, wf_full_path , wf_extension, wf_fname, wf_filename .
  call method cl_gui_frontend_services=>file_save_dialog
    exporting
      window_title         = 'Select the File'
      default_extension    = 'XLS'
*     DEFAULT_FILE_NAME    =
*     FILE_FILTER          =
*     INITIAL_DIRECTORY    =
    changing
      filename             = wf_filename
      path                 = wf_path
      fullpath             = wf_full_path
*     USER_ACTION          =
    exceptions
      cntl_error           = 1
      error_no_gui         = 2
      not_supported_by_gui = 3
      others               = 4
          .

  if sy-subrc <> 0.
    message e398(00) with 'Error Opening File' .
  else .

    split wf_filename at '.' into wf_fname wf_extension .
    translate wf_extension to upper case .

    if wf_extension ne 'XLS' .
      concatenate wf_fname '.xls' into wf_fname .
    else .
      move: wf_filename to wf_fname .
    endif .

*    MOVE: wf_full_path TO p_file .
    concatenate wf_path wf_fname into p_file .
  endif.

then call gui_download

call function 'GUI_DOWNLOAD'
    exporting
*   BIN_FILESIZE                  =
      filename                      = wf_filename
*      filetype                      = 'ASC'
*   APPEND                        = ' '
   write_field_separator         = ','
*   HEADER                        = '00'
*   TRUNC_TRAILING_BLANKS         = ' '
*   WRITE_LF                      = 'X'
*   COL_SELECT                    = ' '
*   COL_SELECT_MASK               = ' '
*   DAT_MODE                      = ' '
*   CONFIRM_OVERWRITE             = ' '
*   NO_AUTH_CHECK                 = ' '
*   CODEPAGE                      = ' '
*   IGNORE_CERR                   = ABAP_TRUE
*   REPLACEMENT                   = '#'
*   WRITE_BOM                     = ' '
* IMPORTING
*   FILELENGTH                    =
    tables
      data_tab                      = outtab
   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
     others                        = 22
            .

Regards

Raja

[suresh_datti]

Hi Jayanthi,

With cl_gui_frontend_services, you don't need to use the OLE logic any more.. Pl take a look at the code in the Program RS_DBG_TABLE_TO_EXCEL and follow a similar approach to meet your requirement.

Regards,

Suresh Datti

[Former Member]

I need to write data into multiple sheets from different internal tables. So, I think only OLE will be better. Can we achieve this through any other way?

[athavanraja]

check out this weblog

/people/sap.user72/blog/2006/02/07/downloading-data-into-excel-with-format-options

and this article.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c1d54348-0601-0010-3e98-bd2...

Regards

Raja

[Former Member]

I think the best way is to create a .csv file which will work well for both application server and unix server.

Good luck.

Venu