topic Re: Problem Downloading data to Excel in Application Development and Automation Discussions

Problem Downloading data to Excel

PS_1978 — Thu, 16 Apr 2009 17:10:56 GMT

Hi,

I am downloading data from internal table to Excel file. there are 3 records in the internal table. Material Number, Material Description, Characteristic Number and so on.

I have used the method cl_gui_frontend_services=>gui_download for downloading.

table has following data for material number -B1AP, -C4CR, -T1HB. When I see the data in excel, i can see the value -C4CR correctly. But not the other two. The other two values are replaced by string #NAME?. If I see the value in cell, it is =-B1AP. The value is concatenated with =.

Could some one help me in resolving this issue?

Best Regards,

Phani.

Edited by: Sivapuram Phani Kumar on Apr 16, 2009 10:43 PM

Re: Problem Downloading data to Excel

Former Member — Thu, 16 Apr 2009 17:16:25 GMT

Hi,

Try this

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = 'c:\temp\abcd.xls'

filetype = 'ASC'

write_field_separator = 'X'

confirm_overwrite = 'X'

TABLES

data_tab = t_output

fieldnames = t_field

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

Krishna

Re: Problem Downloading data to Excel

Former Member — Thu, 16 Apr 2009 17:16:43 GMT

Hi Sivapuram,


Try to use FM "TEXT_CONVERT_SAP_TO_XLS" with the use of this function module you can download long texts and headers and data..

Regards,

Prabhudas

Edited by: Prabhu Das on Apr 16, 2009 10:54 PM

Re: Problem Downloading data to Excel

Former Member — Thu, 16 Apr 2009 17:22:16 GMT

Excel does not understand character starting with negative.

It will show invalid name as an error.

Is your material number actually starting with -.

Regards,

Lalit Mohan Gupta.

Re: Problem Downloading data to Excel

PS_1978 — Thu, 16 Apr 2009 17:26:45 GMT

Hi Lalit Mohan Gupta.,

Yes, my material number is starting with '-'. But C4CR is also starting with '-'. This is downloaded correctly.

Regards,

Phani

Re: Problem Downloading data to Excel

Former Member — Fri, 17 Apr 2009 09:57:03 GMT

Hi,

One Simple function module is there use that 'WS_DOWNLOAD'.

IST_DWN = ur data table

CALL FUNCTION 'WS_DOWNLOAD'

EXPORTING

FILENAME = PATH

FILETYPE = RLGRAP-FILETYPE

MODE = XMODE

IMPORTING

FILELENGTH = L_FILELENGTH

TABLES

DATA_TAB = IST_DWN

EXCEPTIONS

FILE_OPEN_ERROR = 1

FILE_WRITE_ERROR = 2

OTHERS = 3.

Salil....

Re: Problem Downloading data to Excel

I355602 — Fri, 17 Apr 2009 09:58:17 GMT

Hi,

Refer FM:-

GUI_DOWNLOAD

TEXT_CONVERT_SAP_TO_XLS

Hope this helps you.

Regards,

Tarun

Re: Problem Downloading data to Excel

Former Member — Fri, 17 Apr 2009 10:04:00 GMT

hi ,

Try this FM


CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
*   BIN_FILESIZE                    =
      filename                        = p_err
     filetype                        = 'DAT'
*   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                       = ' '
     trunc_trailing_blanks_eol       = 'X'
*   WK1_N_FORMAT                    = ' '
*   WK1_N_SIZE                      = ' '
*   WK1_T_FORMAT                    = ' '
*   WK1_T_SIZE                      = ' '
*   WRITE_LF_AFTER_LAST_LINE        = ABAP_TRUE
*   SHOW_TRANSFER_STATUS            = ABAP_TRUE
* IMPORTING
*   FILELENGTH                      =
    TABLES
      data_tab                        = it_temp
*   FIELDNAMES                      =
* 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
            .
  IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

Thanks

Arun

Re: Problem Downloading data to Excel

Former Member — Fri, 17 Apr 2009 10:07:20 GMT

Hi,

For Excel file Download,try this sample code below:






INCLUDE ole2incl.                       "include used for providing classes used for using create object for creating application and worksheets
DATA: application TYPE ole2_object,
       workbook TYPE ole2_object,
       sheet TYPE ole2_object,
       cells TYPE ole2_object.
CONSTANTS: row_max TYPE i VALUE 256.
DATA index TYPE i.

*DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.
*DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.
DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.
*


TABLES: vbap,mara.


TYPES: BEGIN OF itab,
       lifnr TYPE lfa1-lifnr,
       land1 TYPE lfa1-land1,
*       name1 TYPE lfa1-name1,
*       ort01 TYPE lfa1-ort01,
       END OF itab.



DATA: BEGIN OF itab2 OCCURS 0,
matnr TYPE mara-matnr,
ersda TYPE mara-ersda,
ernam TYPE mara-ernam,
END OF itab2.
 data : v_row type sy-tabix.

DATA: itab1 TYPE STANDARD TABLE OF itab WITH HEADER LINE.

DATA: IT_XLSTAB TYPE STANDARD TABLE OF ITAB ,
      WA_XLSTAB LIKE LINE OF IT_XLSTAB.

START-OF-SELECTION.

  SELECT lifnr land1 fROM lfa1  INTO CORRESPONDING FIELDS OF TABLE itab1 UP TO 5 ROWS.


  SELECT matnr
  ersda
  ernam
  FROM mara
  INTO CORRESPONDING FIELDS OF
  TABLE itab2 UP TO 5 ROWS.



************************************************************************
*START-OF-SELECTION
START-OF-SELECTION.

  CREATE OBJECT application 'excel.application'.
  SET PROPERTY OF application 'visible' = 1.
  CALL METHOD OF application 'Workbooks' = workbook.
  CALL METHOD OF workbook 'Add'.

* Create first Excel Sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 1.
  CALL METHOD OF sheet 'Activate'.
  SET PROPERTY OF sheet 'Name' = 'Sheet1'.
  clear v_row.
  LOOP AT itab1.
     v_row = sy-tabix.
      perform fill_cell  using  v_row 1  itab1-lifnr.
      perform fill_cell  using  v_row 2 itab1-land1.
  ENDLOOP.

* Create second Excel sheet
  CALL METHOD OF application 'Worksheets' = sheet
                               EXPORTING #1 = 2.
  SET PROPERTY OF sheet 'Name' = 'Sheet2'.
  CALL METHOD OF sheet 'Activate'.
  clear v_row.
  LOOP AT itab2.
     v_row = sy-tabix.

      perform fill_cell using  v_row 1 itab2-matnr.
      perform fill_cell using  v_row 2 itab2-ersda.
      perform fill_cell using  v_row 3 itab2-ernam.

  ENDLOOP.


* Save excel speadsheet to particular filename
  CALL METHOD OF sheet 'SaveAs'
                  EXPORTING #1 = 'c:\temp\excelgeet.xls'     "filename
                            #2 = 1.                          "fileFormat

*  Closes excel window, data is lost if not saved
  SET PROPERTY OF application 'visible' = 0.

*  call method of sheet 'CLOSE'
*
*  EXPORTING #1 = 'YES'.


*&---------------------------------------------------------------------*
*& both the below coding closes the apllication permanently from the task manager also.
*&---------------------------------------------------------------------*

SET PROPERTY OF application 'DisplayAlerts' = 0.
   free OBJECT application.


**  call method of application 'QUIT'.
**
**  FREE OBJECT: APPLICATION,
**               SHEET.

*&---------------------------------------------------------------------*
*&      Form  fill_cell
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_1      text
*      -->P_V_COL  text
*      -->P_ITAB1_LIFNR  text
*----------------------------------------------------------------------*
form fill_cell  using row  col val.

    CALL METHOD OF sheet 'Cells' = cells EXPORTING #1 = row  #2 = col.
    SET PROPERTY OF cells 'Value' = val.


endform.                    " fill_cell

Hope it helps

Regards

Mansi

Re: Problem Downloading data to Excel

Former Member — Fri, 17 Apr 2009 10:12:24 GMT

Hi,

Try to pass the parameter IGNORE_CERR of the function module GUI_DOWNLOAD.

There are a few parameters in this function that migth help you.

Regards.

Sandra Marques