topic Re: Convert internal table to excel format in Application Development and Automation Discussions

Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:09:54 GMT

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.

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:13:08 GMT

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

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:13:38 GMT

Hi Vineeta,

Try changing the file type to 'XLS'

CALL FUNCTION 'GUI_DOWNLOAD'

EXPORTING

filename = filename

filetype = 'XLS'

Hope this helps.

Regards

Gowthami

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:14:05 GMT

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

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:15:56 GMT

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>

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:25:20 GMT

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

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:26:01 GMT

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?

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 09:36:11 GMT

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.

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 10:03:14 GMT

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.

Re: Convert internal table to excel format

I355602 — Fri, 27 Feb 2009 10:04:58 GMT

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

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 10:06:42 GMT

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

Regards,

Vineeta.

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 10:10:17 GMT

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.

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 10:20:53 GMT

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.

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 10:37:31 GMT

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?

Re: Convert internal table to excel format

awin_prabhu — Fri, 27 Feb 2009 10:56:22 GMT

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..

Re: Convert internal table to excel format

Former Member — Fri, 27 Feb 2009 11:38:10 GMT

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.

Re: Convert internal table to excel format

Former Member — Fri, 21 Jun 2013 09:00:56 GMT

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.

Re: Convert internal table to excel format

former_member209120 — Fri, 21 Jun 2013 10:20:57 GMT

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.

Re: Convert internal table to excel format

former_member286345 — Fri, 21 Jun 2013 10:46:39 GMT

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

Re: Convert internal table to excel format

Former Member — Fri, 18 Dec 2015 07:14:35 GMT

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