Convert internal table to excel format

Former Member · ‎2009 Feb 27

I have used GUI_DOWNLOAD function module to download an internal table to excel format.

Download the excel file.

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

write_field_separator = 'X'

TABLES

data_tab = <tab>

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.

And I am able to successfully download the excel file but the problem occurs when I try to open the file.

System throws the warning "The file you are trying to open, '[filename]', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

And if I click yes, it correctly displays the excel file.

I want to get rid of this warning. Is there any way I can code so that this warning does not occur. Kindly advise.

Former Member · ‎2009 Feb 27

HI,

try this

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

write_field_separator = '|'

TABLES

data_tab = <tab>

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,

Venkatesh

Former Member · ‎2009 Feb 27

Hi Vineeta,

Try changing the file type to 'XLS'

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'XLS'

Hope this helps.

Regards

Gowthami

Former Member · ‎2009 Feb 27

probably when u give filename the extension should be .xls, did u given

Former Member · ‎2009 Feb 27

HI,

Data:filename type rlgraph-filename value 'c:\temp.xls'.

Specifi the Extension : .XLS

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

*filetype = 'ASC' " Comment this

write_field_separator = 'X'

TABLES

data_tab = <tab>

Former Member · ‎2009 Feb 27

Hi Vineeta,

There are several ways to achieve it.

you can save the file with *.csv extension and the comma separator.

you can use an ALV to perform the download.

and you can use the instructions for

CREATE OBJECT EXCEL 'EXCEL.APPLICATION'.

regards,

Edgar

Former Member · ‎2009 Feb 27

I have given the filename with .xls extension.

Also I tried

- commenting filetype in GUI_DOWNLOAD function

- set filetype to 'XLS'

- set write_field_separator to '|'

but none of this helped. Any further ideas?

Former Member · ‎2009 Feb 27

Hi,

Use FM

'SAP_CONVERT_TO_XLS_FORMAT'

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_path

TABLES

i_tab_sap_data = p_table.

p_table is your internal table

p_path is the path where ur file is to be saved. Define p_path like

DATA:p_path like rlgrap-filename.

Regards,

Arun.

Former Member · ‎2009 Feb 27

Hi Arun,

I tried a sample program using SFLIGHT data to convert to excel.

DATA: l_filename like rlgrap-filename.

l_filename = 'C:\temp.xls'.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = l_filename

TABLES

i_tab_sap_data = lt_sflight.

But when I try to open the excel file, system throws error - 'temp.xls:: file format is not valid.'

Am I missing anything here?

Regards,

Vineeta.

I355602 · ‎2009 Feb 27

Hi,

Use code, its working:-


CALL FUNCTION 'GUI_DOWNLOAD'
  EXPORTING
    filename = 'C:/TEST.XLS'  "filename.xls
    filetype = 'ASC'
    write_field_separator = 'X' 
  TABLES
    data_tab = <itab> "internal table

Regards,

Tarun

Former Member · ‎2009 Feb 27

Hi Tarun,

Are you able to open the test.xls file without any warnings?

I get the warning "The file you are trying to open, 'filename', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

However if I click 'Yes', the file opens correctly. I want to get rid of this warning.

Did you face this issue?

Regards,

Vineeta.

Former Member · ‎2009 Feb 27

Hi,

Are you using Office 2007 to open the excel file ? If yes, it might be giving a compatibility problem. Try installing patches from Microsoft and try again. I dont think variations in input parameters of GUI_DOWNLOAD can help much here.

Thanks.

Former Member · ‎2009 Feb 27

this is a working example:


REPORT  zedgar_download.

DATA: lt_t001l TYPE TABLE OF t001l WITH HEADER LINE.
DATA: lt_str TYPE TABLE OF string WITH HEADER LINE.
FIELD-SYMBOLS <fs>.

START-OF-SELECTION.
  SELECT * FROM t001l INTO TABLE lt_t001l.

  LOOP AT lt_t001l.
    CLEAR lt_str.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE lt_t001l TO <fs>.
      IF sy-subrc NE 0.
        EXIT.
      ENDIF.
      CONCATENATE lt_str <fs> INTO lt_str SEPARATED BY ';'.
    ENDDO.
    APPEND lt_str.
  ENDLOOP.

  CALL METHOD cl_gui_frontend_services=>gui_download
    EXPORTING
      filename                  = 'c:\test.csv'
      filetype                  = 'ASC'              
    CHANGING
      data_tab                  = lt_str[]
    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
          .

my separator is ';' but you probably should use the most standard csv separator ','.

does this satisfy your need?

Former Member · ‎2009 Feb 27

I'll post you too the code for the excel OLE object, since I like it so much despite being so slow:


REPORT  zedgar_excel.

INCLUDE ole2incl.       " OLE-specific include file

START-OF-SELECTION.

  DATA: excel TYPE ole2_object.         " EXCEL object
  DATA: books TYPE ole2_object.         " collection of books
  DATA: book TYPE ole2_object.          " book
  DATA: cell TYPE ole2_object.          " cell

  DATA: sheet TYPE ole2_object.
  DATA: font TYPE ole2_object. "you can actually set a font if you'd like
  DATA: application TYPE ole2_object,
        workbook TYPE ole2_object.

  DATA: lt_t001l TYPE TABLE OF t001l WITH HEADER LINE.
  FIELD-SYMBOLS <fs>.
  DATA: l_tabix LIKE sy-tabix.

START-OF-SELECTION.

*inicialize OLE
  CREATE OBJECT excel 'EXCEL.APPLICATION'.
  CALL METHOD OF excel 'WORKBOOKS' = books.
  CALL METHOD OF books 'ADD' = book.
  CALL METHOD OF excel 'WORKSHEETS' = sheet.

*  CALL METHOD OF sheet 'ADD'.

*  SELECT * FROM t001l INTO TABLE lt_t001l.
  SELECT * FROM t001l UP TO 3 ROWS INTO TABLE lt_t001l.
  LOOP AT lt_t001l.
    l_tabix = sy-tabix.
    DO.
      ASSIGN COMPONENT sy-index OF STRUCTURE lt_t001l TO <fs>.
      IF sy-subrc NE 0.
        EXIT.
      ENDIF.
      CALL METHOD OF excel 'CELLS' = cell EXPORTING #1 = l_tabix #2 = sy-index.
      SET PROPERTY OF cell 'VALUE' =   <fs>.
    ENDDO.
  ENDLOOP.

*  SET  PROPERTY  OF  excel  'VISIBLE'  =  1.
*or.....
  CALL METHOD OF book 'SaveAs' EXPORTING #1 = 'C:\test2.XLS'
    #2 = 1.

  FREE  OBJECT  cell.
  FREE  OBJECT  sheet.
  FREE  OBJECT  book.
  FREE  OBJECT  books.
  FREE  OBJECT  excel.

there you go, quite a bit of fun.

Former Member · ‎2009 Feb 27

Also the same thing works with FM GUI_DOWNLOAD and not with FM SAP_CONVERT_TO_XLS_FORMAT.

Regards,

Vineeta.

awin_prabhu · ‎2009 Feb 27

Hi friend,

Try this simple code..

REPORT zdemodownload .

*Types

TYPES: BEGIN OF g_r_mara,

matnr LIKE mara-matnr,

ersda LIKE mara-ersda,

laeda LIKE mara-laeda,

mtart LIKE mara-mtart,

mbrsh LIKE mara-mbrsh,

END OF g_r_mara.

*Data

DATA: g_t_mara TYPE TABLE OF g_r_mara,

filename TYPE string,

s TYPE i.

*Tables

TABLES: mara, sscrfields.

*Selection Screen

SELECT-OPTIONS: s_matnr FOR mara-matnr.

SELECTION-SCREEN SKIP.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN PUSHBUTTON 10(20) name USER-COMMAND ucom.

SELECTION-SCREEN END OF LINE.

INITIALIZATION.

name = 'Spread Sheet'.

AT SELECTION-SCREEN.

IF sscrfields-ucomm = 'UCOM'.

s = 1.

ENDIF.

*Data retrival and populating internal table

IF s = 1.

SELECT matnr ersda laeda mtart mbrsh

INTO CORRESPONDING FIELDS OF TABLE g_t_mara

FROM mara

WHERE matnr IN s_matnr.

filename = 'C:\Testing.xls'.

*Downloading data from internal table to excel

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'ASC'

write_field_separator = 'X'

TABLES

data_tab = g_t_mara.

ENDIF.

Thanks..

Former Member · ‎2013 Jun 21

Hi,

If SAP_CONVERT_TO_XLS_FORMAT is not working properly then,

you can use this function module EXCEL_OLE_STANDARD_DAT'...

Also, SAP_CONVERT_TO_XLS_FORMAT converts every data into a character format.

If you want to convert the data into required types use EXCEL_OLE_STANDARD_DAT.

Former Member · ‎2015 Dec 18

I was having problems with SAP_CONVERT_TO_XLS_FORMAT, then I used EXCEL_OLE_STANDARD_DAT fm and it worked alright. Thanks Abdul.

former_member209120 · ‎2013 Jun 21

Hi,

Try Like This

data:begin of it occurs 0,

grp type c,

val type i,

end of it.

data:begin of heading occurs 0,

TEXT(10) type c,

end of heading.

it-grp = 'A'.

it-val = 100.

append it.

it-grp = 'B'.

it-val = 200.

append it.

heading-text = 'GROUP'.

append heading.

heading-text = 'VALUE'.

append heading.

call function 'GUI_DOWNLOAD'

exporting

filename = 'C:\text.xls'

FILETYPE = 'DAT'

WRITE_FIELD_SEPARATOR = '#'

SHOW_TRANSFER_STATUS = 'X'

tables

data_tab = it[]

FIELDNAMES = heading[]

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.

former_member286345 · ‎2013 Jun 21

hi,

their are so many ways(like GUI_DOWNLOAD,WS_DOWNLOAD.. ) to convert internal table to excel format .

but the thing is you have see the internal table is suitable according to downloading file then your problem is solve.

vali

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Convert internal table to excel format