Download internal table to excel and arrange fields horizontally

[Former Member]

Hi all,

I am downloading an internal table into an excel file. My problem is I need to arrange the fields horizontally.

For example my internal table looks like:

123 A DOG

456 B CAT

789 C BIRD

My output excel file should look like this:

123 456 789

A B C

DOG CAT BIRD

I am using GUI download. But I need to have this output.

[naimesh_patel]

Excel can handle this very well...

Select all data by CRTL+A.

press CRTL+C to copy it

Go to next sheet..

Right click .. paste special...

select "transpose" check box in bottom right of the pop-up.

If you want to do it via ABAP, than you need to write some code to handle these functionality.

Regards,

Naimesh Patel.

[Former Member]

I need to do it in ABAP. Do you have an idea about this?

[naimesh_patel]

Ok...

I tried to make some sort of code which can download the CSV file .. comma separated values...

The problem to make the file other than CSV is we don't know the max length of the upcoming data. When you double click on this generated .CSV file it will open in EXCEL

REPORT  ZTEST_NP.

DATA: BEGIN OF ITAB OCCURS 0,
      FLD1(10),
      FLD2(1),
      FLD3(10),
      END OF ITAB.

DATA: BEGIN OF IT_DOWN  OCCURS 0,
      FLD TYPE STRING,
      END   OF IT_DOWN.

DATA: IT_COMP LIKE RSTRUCINFO OCCURS 0 WITH HEADER LINE.

ITAB-FLD1 = '123'.
ITAB-FLD2 = 'A'.
ITAB-FLD3 = 'ddd'.
APPEND ITAB.

ITAB-FLD1 = '456'.
ITAB-FLD2 = 'B'.
ITAB-FLD3 = 'eee'.
APPEND ITAB.

ITAB-FLD1 = '789'.
ITAB-FLD2 = 'c'.
ITAB-FLD3 = 'fff'.
APPEND ITAB.

CALL FUNCTION 'GET_COMPONENT_LIST'
  EXPORTING
    PROGRAM    = SY-REPID
    FIELDNAME  = 'ITAB'
  TABLES
    COMPONENTS = IT_COMP.
.

FIELD-SYMBOLS: <FS> TYPE ANY.

DATA: L_APPEND ,
      L_CNT TYPE I.


LOOP AT IT_COMP.
  L_CNT = L_CNT + 1.
  CLEAR IT_DOWN.
  REFRESH IT_DOWN.
  LOOP AT ITAB.
    ASSIGN COMPONENT IT_COMP-COMPNAME OF STRUCTURE ITAB TO <FS>.
    CONCATENATE IT_DOWN-FLD <FS>  INTO IT_DOWN-FLD SEPARATED BY ','.
  ENDLOOP.
  IT_DOWN-FLD = IT_DOWN-FLD+1.
  APPEND IT_DOWN.

  IF L_CNT = 1.
  ELSE.
    L_APPEND = 'A'.
  ENDIF.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      FILENAME = 'C:temptext.csv'
      FILETYPE = 'ASC'
      APPEND   = L_APPEND
    TABLES
      DATA_TAB = IT_DOWN.
  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.


ENDLOOP.

Regards,

Naimesh Patel

[ferry_lianto]

Hi,

Does pivot table format meet your requirement?

If it does, you can try to use FM XXL_SIMPLE_API.

Regards,

Ferry Lianto

[Former Member]

Hi

see this program it wil help u very clearly

excel to internal table and then to application

*&---------------------------------------------------------------------*
*& Report  ZSD_EXCEL_INT_APP
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT  ZSD_EXCEL_INT_APP.

parameter: file_nm type localfile.

types : begin of it_tab1,
        f1(20),
        f2(40),
        f3(20),
       end of it_tab1.


data : it_tab type table of ALSMEX_TABLINE with header line,
       file type rlgrap-filename.

data : it_tab2 type it_tab1 occurs 1,
       wa_tab2 type it_tab1,
       w_message(100)  TYPE c.


at selection-screen on value-request for file_nm.

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
 EXPORTING
*   PROGRAM_NAME        = SYST-REPID
*   DYNPRO_NUMBER       = SYST-DYNNR
*   FIELD_NAME          = ' '
   STATIC              = 'X'
*   MASK                = ' '
  CHANGING
   file_name           = file_nm
 EXCEPTIONS
   MASK_TOO_LONG       = 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.


start-of-selection.

refresh it_tab2[].clear wa_tab2.

file = file_nm.

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
  EXPORTING
    filename                      = file
    i_begin_col                   = '1'
    i_begin_row                   =  '1'
    i_end_col                     = '10'
    i_end_row                     = '35'
  tables
    intern                        = it_tab
 EXCEPTIONS
   INCONSISTENT_PARAMETERS       = 1
   UPLOAD_OLE                    = 2
   OTHERS                        = 3
          .
IF sy-subrc <> 0.
 MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

loop at it_tab.

  case it_tab-col.

   when '002'.

    wa_tab2-f1 = it_tab-value.

   when '004'.

    wa_tab2-f2 = it_tab-value.

  when '008'.

    wa_tab2-f3 = it_tab-value.

 endcase.

 at end of row.

  append wa_tab2 to it_tab2.
 clear wa_tab2.
  endat.

endloop.

data : p_file TYPE  rlgrap-filename value 'TEST3.txt'.


OPEN DATASET p_file FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
*--- Display error messages if any.
  IF sy-subrc NE 0.
    MESSAGE e001(zsd_mes).
    EXIT.
  ELSE.

*---Data is downloaded to the application server file path
    LOOP AT it_tab2 INTO wa_tab2.
      TRANSFER wa_tab2 TO p_file.
    ENDLOOP.
  ENDIF.

*--Close the Application server file (Mandatory).
  CLOSE DATASET p_file.

 loop at it_tab2 into wa_tab2.

  write : / wa_tab2-f1,wa_tab2-f2,wa_tab2-f3.

 endloop.

<b>Reward ifusefull</b>