Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Unwanted Character in excel download

former_member212705
Active Participant
0 Kudos

Hi Experts,

I am facing one issue. I am using FM EXCEL_OLE_STANDARD_DAT to download excel.

   call function 'EXCEL_OLE_STANDARD_DAT'
    exporting
      file_name                 = lv_filepath          "file path
    tables
      data_tab                  = gi_sheet_row
      fieldnames                = gi_header.

In this gi_sheet_row contains proper data but when i export this to excel few fields for example field A which has value 0.056MM (32M") shows in excel 0.056#M (32#"). Now i am confused why so. is this due to FM. I am not sure. Please help.

Regards,

Ashish

1 ACCEPTED SOLUTION

former_member184569
Active Contributor
0 Kudos

Use the function module SAP_CONVERT_TO_XLS_FORMAT to download the internal table to an excel file.


PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'c:tmptest.xls'.

DATA: t100_Lines TYPE STANDARD TABLE OF t001 WITH DEFAULT KEY.

SELECT * FROM t001 INTO TABLE t100_Lines.



CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_file

TABLES

i_tab_sap_data = t100_Lines.

If you want header, you can first populate the output table with the header line, and then loop through your data table and append all the data to the output table and pass it in the FM.

10 REPLIES 10

former_member212705
Active Participant
0 Kudos

field having this problem is of type char128

venkateswaran_k
Active Contributor
0 Kudos

Hi  

Try with this following function

    MS_EXCEL_OLE_STANDARD_DAT

However,  can you share here... the data type declaration of  -  gi_sheet_row

Make sure that all the fields you declared under this structure gi_sheet_row is type C only.

Regards,

Venkat     

0 Kudos

hi Venkat,

Yes this field in gi_sheet_row is of character type. Field value is fine when this FM is called but after exporting to excel it gives junk. I tried to debug the FM ,internally it calls  MS_EXCEL_OLE_STANDARD_DAT and then GUI_DOWNLOAD , i checked the field value in the table that is going into  GUI_DOWNLOAD is perfect. means gui_download is getting proper field value but excel is having junk.

0 Kudos

Hi.

Better use Binary mode to avoid junk characters .

Follow below code.

***Convert into BIN format

  TRY.

      cl_bcs_convert=>string_to_solix(

        EXPORTING

          iv_string   = lv_string

          iv_codepage = lv_codep

          iv_add_bom  = 'X'

        IMPORTING

          et_solix  = t_binary

          ev_size   = lv_size ).

    CATCH cx_bcs.

      MESSAGE i445(so).EXIT.

  ENDTRY.

  lv_binsize = lv_size.

***Excel download

  CALL FUNCTION 'GUI_DOWNLOAD'

    EXPORTING

    bin_filesize                    = lv_binsize

    filename                        = lv_file

    filetype                        = 'BIN'

    append                          = 'X'

    write_field_separator           = ''

*   HEADER                          = '00'

    trunc_trailing_blanks           = 'X'

*    write_lf                       = 'X'

*   COL_SELECT                      = ' '

*   COL_SELECT_MASK                 = ' '

    dat_mode                        = 'X'

*   CONFIRM_OVERWRITE               = ' '

    no_auth_check                   = 'X'

*   codepage                        = '4103'

    ignore_cerr                     = 'X'

    replacement                     = '#'

*   WRITE_BOM                       = ' '

    trunc_trailing_blanks_eol       = ' '

*   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                        = t_binary

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

Regards.

NJ

0 Kudos

Hi Ashish

Okay, Try as follows

1.  Before calling this download to Excel,  condense the field C128 fields....  meaning removing the trailing spaces

2. What is the file name you are giving?   abc.XLS   or xlsx? 

3. Try to open the excel with different format of Excel files.

Regards,

Venkat

0 Kudos

Hi Venkat,

I cheked but i think that is not issue.

0 Kudos

Hi nagraj,,

We do not have to use GUI_DOWNLOAD

Regards,

AKT

Former Member
0 Kudos

We had problems before, if any values in excel had a carriage return. I had users clean all of those before posting into SAP.

former_member184569
Active Contributor
0 Kudos

Use the function module SAP_CONVERT_TO_XLS_FORMAT to download the internal table to an excel file.


PARAMETERS: p_file LIKE rlgrap-filename DEFAULT 'c:tmptest.xls'.

DATA: t100_Lines TYPE STANDARD TABLE OF t001 WITH DEFAULT KEY.

SELECT * FROM t001 INTO TABLE t100_Lines.



CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = p_file

TABLES

i_tab_sap_data = t100_Lines.

If you want header, you can first populate the output table with the header line, and then loop through your data table and append all the data to the output table and pass it in the FM.

0 Kudos

HI Sus,

This FM 'SAP_CONVERT_TO_XLS_FORMAT' can accomodate more that 255 characters in a single column??? plz reply. As in my case values in a column may be more than 255 char.

Regards,

AKT