Problem with FM : GUI_DOWNLOAD

Former Member · ‎2013 Aug 06

Hello Experts,

I am using gui_download function module.I need to download a .XLSX format.if i download .XLS format it will open but i download in .XLSX format showing error.i wrote this below code

*concatenating the filepath and name
     CALL FUNCTION 'GUI_DOWNLOAD'
       EXPORTING
         FILENAME              = v_fname

         FILETYPE              = 'ASC'
         WRITE_FIELD_SEPARATOR = 'X'
       TABLES
         DATA_TAB              = IT_ERRMSGLOG[]
         FIELDNAMES            = it_msgheader.

     IF SY-SUBRC = 0.
       MESSAGE 'FILE DOWNLOADED SUCCESSFULLY' TYPE 'I'.
     ENDIF.

pls find the below error.

Regards,

OmChandra.

FredericGirod · ‎2013 Aug 06

Hi,

first, remove the field separator, Excel file is not a flat file with separator. You could use the CVS format with a separator ";" but it's not an Excel file.

Try to download it in binary format

regards

Fred

Former Member · ‎2013 Aug 06

Hi Frederic,

CSV format is working fine but my end user asking .XLSX format only.so i am trying to download in .XLSX format.it's showing error.

Regards,

OmChnadra.

FredericGirod · ‎2013 Aug 06

Try

FILETYPE = 'BIN'

(and remove separator)

regards

Fred

arindam_m · ‎2013 Aug 06

Hi,

Try putting the header as a TAB delimited line before table.

Cheers,

Arindam

Former Member · ‎2013 Aug 06

Hi OmChandra L,

Use function module SAP_CONVERT_TO_XLS_FORMAT instead of GUI_DOWNLOAD to convert your internal table into excel sheet.

Coding:

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

EXPORTING

i_filename = <Filename with extension .xlsx >

TABLES

i_tab_sap_data = <Internal_table>

EXCEPTIONS

conversion_failed = 1

OTHERS = 2.

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 & Regards,

T. Prasanna Kumar

former_member209120 · ‎2013 Aug 06

Hi om

it is not possible with GUI_DOWNLOAD.

USE

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

      EXPORTING
        i_filename                 = 'C:/test.xlsx'
      TABLES
        i_tab_sap_data             = it_excel
     EXCEPTIONS
       conversion_failed          = 1
       OTHERS                     = 2.

    IF sy-subrc <> 0.

      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

    ENDIF.

Example

TYPES: BEGIN OF ty_excel,
        fld1(20) TYPE c,
        fld2(20) TYPE c,
        fld3(20) TYPE c,
        END OF ty_excel.

DATA: it_t001 TYPE TABLE OF t001.
DATA: wa_t001 TYPE t001.
DATA: it_excel TYPE TABLE OF ty_excel.
DATA: wa_excel TYPE ty_excel.

SELECT * INTO TABLE it_t001 FROM t001.

wa_excel-fld1 = 'Field 1'.
wa_excel-fld2 = 'Field 2'.
wa_excel-fld3 = 'Field 3'.
APPEND wa_excel TO it_excel.

LOOP AT it_t001 INTO wa_t001.
   wa_excel-fld1 = wa_t001-bukrs.
   wa_excel-fld2 = wa_t001-butxt.
   wa_excel-fld3 = wa_t001-ort01.
   APPEND wa_excel TO it_excel.
ENDLOOP.

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

      EXPORTING
        i_filename                 = 'C:/test.xlsx'
      TABLES
        i_tab_sap_data             = it_excel
     EXCEPTIONS
       conversion_failed          = 1
       OTHERS                     = 2.

    IF sy-subrc <> 0.
      MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
              WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
    ENDIF.

output

Former Member · ‎2013 Aug 06

XLSX format can't be created using standard download FMs.

You can use a custom project named abap2xlsx for doing it.

EDIT: Code given by Prasanna and Ramesh T works if you want only data in xlsx file, and no formatting.

Former Member · ‎2013 Aug 06

Hi Om,

You can achieve your requirement using "OLE ABAP Code ".

Sample Code :

GET PROPERTY OF W_EXCEL 'ActiveWorkbook' = W_WORKBOOK.

CALL METHOD OF W_WORKBOOK 'SAVEAS'

EXPORTING #1 = P_FILE " 'C:\TEMP\TEXT.XLSX'

#2 = 51 . " Used for ".XLSX format "

===================

TYPE-POOLS OLE2.

************************************************************************

* Types Definitions

************************************************************************

TYPES: TY_LINE(1500) TYPE C.

TYPES: BEGIN OF TY_VBAK,

VBELN TYPE VBELN,

VKORG TYPE VKORG,

VTWEG TYPE VTWEG,

SPART TYPE SPART,

END OF TY_VBAK.

************************************************************************

* Internal table and work area declarations

************************************************************************

DATA: IT_TAB1 TYPE TABLE OF TY_LINE, " Contains records for first sheet

IT_TAB2 TYPE TABLE OF TY_LINE, " Contains records for second sheet

IT_VBAK TYPE TABLE OF TY_VBAK, " Header details from VBAK table

WA_TAB TYPE TY_LINE,

WA_VBAK TYPE TY_VBAK.

************************************************************************

* OLE objects Declarations

************************************************************************

DATA: W_EXCEL TYPE OLE2_OBJECT,

W_WORKBOOK TYPE OLE2_OBJECT,

W_WORKSHEET TYPE OLE2_OBJECT,

W_COLUMNS TYPE OLE2_OBJECT,

W_COLUMN_ENT TYPE OLE2_OBJECT,

W_CELL TYPE OLE2_OBJECT,

W_INT TYPE OLE2_OBJECT,

W_RANGE TYPE OLE2_OBJECT,

W_PROTECT TYPE OLE2_OBJECT,

W_SELRANGE TYPE OLE2_OBJECT .

************************************************************************

* Data declarations

************************************************************************

DATA: W_DELI(1) TYPE C, "Delimiter

W_HEX TYPE X,

W_RC TYPE I.

************************************************************************

* Field Symbols

************************************************************************

FIELD-SYMBOLS: <FS> .

************************************************************************

* Constants

************************************************************************

CONSTANTS WL_C09(2) TYPE N VALUE 09.

************************************************************************

* File Selection

SELECTION-SCREEN BEGIN OF BLOCK BLOCK1 WITH FRAME TITLE TEXT-001.

PARAMETERS: P_FILE LIKE RLGRAP-FILENAME DEFAULT 'c:\temp\test.xlsx'.

SELECTION-SCREEN END OF BLOCK BLOCK1.

* F4 Help for File name

AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_FILE.

CALL FUNCTION 'F4_FILENAME'

EXPORTING

PROGRAM_NAME = SY-REPID

FIELD_NAME = 'P_FILE'

IMPORTING

FILE_NAME = P_FILE.

START-OF-SELECTION.

PERFORM DOWNLOAD_EXCEL.

*&---------------------------------------------------------------------*

*& Form download_excel

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM DOWNLOAD_EXCEL.

CREATE OBJECT W_EXCEL 'EXCEL.APPLICATION'. "Create object for Excel

SET PROPERTY OF W_EXCEL 'VISIBLE' = 1. "In background Mode

CALL METHOD OF W_EXCEL 'WORKBOOKS' = W_WORKBOOK.

CALL METHOD OF W_WORKBOOK 'ADD'. "Create a new Workbook

SET PROPERTY OF W_EXCEL 'SheetsInNewWorkbook' = 1. "No of sheets

PERFORM FILL_DATA. " Fill the internal tables with the req. data

* Downloading header details to first sheet

PERFORM DOWNLOAD_SHEET TABLES IT_TAB1 USING 1 'Data Details'.

GET PROPERTY OF W_EXCEL 'ActiveSheet' = W_WORKSHEET.

GET PROPERTY OF W_WORKSHEET 'Protection' = W_PROTECT.

GET PROPERTY OF W_PROTECT 'AllowEditRanges' = W_SELRANGE.

CALL METHOD OF W_EXCEL 'RANGE' = W_RANGE

EXPORTING

#1 = 'A1' " Column A1 TO D11 Range

#2 = 'D11'.

* Save the Excel file

GET PROPERTY OF W_EXCEL 'ActiveWorkbook' = W_WORKBOOK.

CALL METHOD OF W_WORKBOOK 'SAVEAS'

EXPORTING #1 = P_FILE

#2 = 51 .

FREE OBJECT: W_WORKSHEET, W_EXCEL.

ENDFORM. "download_excel

*&---------------------------------------------------------------------*

*& Form fill_data

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

FORM FILL_DATA.

SELECT VBELN

VKORG

VTWEG

SPART INTO TABLE IT_VBAK FROM VBAK UP TO 10 ROWS.

ASSIGN W_DELI TO <FS> TYPE 'X'.

W_HEX = WL_C09.

<FS> = W_HEX.

CONCATENATE 'Sales Document' 'Sales Organization'

'Distribution Channel' 'Division'

INTO WA_TAB SEPARATED BY W_DELI.

APPEND WA_TAB TO IT_TAB1.

LOOP AT IT_VBAK INTO WA_VBAK.

CONCATENATE WA_VBAK-VBELN WA_VBAK-VKORG

WA_VBAK-VTWEG WA_VBAK-SPART

INTO WA_TAB SEPARATED BY W_DELI.

APPEND WA_TAB TO IT_TAB1.

ENDLOOP.

CONCATENATE 'Sales Document' 'Item'

'Material' 'Ware House'

INTO WA_TAB SEPARATED BY W_DELI.

APPEND WA_TAB TO IT_TAB2.

ENDFORM. "fill_data

*&---------------------------------------------------------------------*

*& Form download_sheet

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* -->P_SHEET text

* -->P_NAME text

*----------------------------------------------------------------------*

FORM DOWNLOAD_SHEET TABLES P_TAB

USING P_SHEET TYPE I

P_NAME TYPE STRING.

CALL METHOD OF W_EXCEL 'WORKSHEETS' = W_WORKSHEET

EXPORTING

#1 = P_SHEET.

CALL METHOD OF W_WORKSHEET 'ACTIVATE'.

SET PROPERTY OF W_WORKSHEET 'NAME' = P_NAME.

CALL METHOD OF W_EXCEL 'Range' = W_RANGE

EXPORTING

#1 = 'A1'

#2 = 'D1'.

* Export the contents in the internal table to the clipboard

CALL METHOD CL_GUI_FRONTEND_SERVICES=>CLIPBOARD_EXPORT

IMPORTING

DATA = P_TAB[]

CHANGING

RC = W_RC

EXCEPTIONS

CNTL_ERROR = 1

ERROR_NO_GUI = 2

NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4.

* Paste the contents in the clipboard to the worksheet

CALL METHOD OF W_WORKSHEET 'Paste'.

FREE OBJECT: W_COLUMNS, W_RANGE.

ENDFORM. "download_sheet

Regard's

Smruti

former_member209120 · ‎2013 Aug 06

Hi Friends,

Through this function module we can download in .XLSX

CALL FUNCTION 'SAP_CONVERT_TO_XLS_FORMAT'

why we need to use other functions

Ms. OM using before GUI _DOWNLOAD only. so why confusion on it.

Former Member · ‎2013 Aug 06

XLSX format supports a lot of features, whereas FM SAP_CONVERT_TO_XLS_FORMAT only helps put data in xlsx file, not supporting features such as cell formatting, referencing etc.

Former Member · ‎2013 Aug 06

Hi,

Try to debugg the any table data download using sperdsheet application tool bar button.

se11-> Tbale(KNA1)-> f8->check display mode is ALVgrid-> F8-> /h(Debugging switch on) -> click on the Spred sheet icon.

Check the how the table data downloadding in XLSX file type.

Thanks & Regards,

Suresh M

Phillip_Morgan · ‎2013 Aug 06

Hi,

I can confirm all the talk about not using GUI_DOWNLOAD to create xlsx files. When we upgraded PCs to Seven and excel 2010 (so xlsx files), we had to modify many reports using GUI_DOWNLOAD.

We replaced them all with SAP_CONVERT_TO_XS_FORMAT.

However, in some cases where we need to do more sophisticated things with excel, we have adopted abap2xlsx.

danni_hardanni · ‎2024 Jul 03

To download xlsx via GUI_Download, you can translate your itab into Binary.

To have a header, you need to set your itab fields into chars or string, then apend it at first row.

CLEAR lv_len.
  TRY.
      CALL METHOD cl_salv_table=>factory
        IMPORTING
          r_salv_table = lo_salv
        CHANGING
          t_table      = <lt_output>.

      CALL METHOD lo_salv->to_xml
        EXPORTING
          xml_type = if_salv_bs_xml=>c_type_xlsx
        RECEIVING
          xml      = lv_xml.

    CATCH cx_salv_error INTO lo_ex.
      lv_error = lo_ex->get_text( ).
      BREAK-POINT.
  ENDTRY.

  CALL FUNCTION 'SCMS_XSTRING_TO_BINARY'
    EXPORTING
      buffer        = lv_xml
    IMPORTING
      output_length = lv_len
    TABLES
      binary_tab    = lt_bin.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      bin_filesize = lv_len
      filename     = p_file_str
      filetype     = 'BIN'
    TABLES
      data_tab     = lt_bin
      fieldnames   = lt_field.

Hope it help.