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: 

How to prepare excels based on records of internal table.

Former Member
0 Kudos

Hi abapers,

I have a scenario as follows:

In my internal table the records are as follows.

col1 col2 col3

1 a1 b1

2 a2 b1

3 a3 b2

4 a4 b3.

Now want i need is i have to break the records on the basis of col3 . All b1 rows are to be taken and dumped in a excel. b2 rows are to be taken and dumped in an excel and similarly for b3. And the name of excel will should be b1, b2 , b3 respectively.

How do i go for the case?

Help would be appreciated..

Regards,

Navin C

Edited by: navin.c on Jun 12, 2009 7:41 AM

1 ACCEPTED SOLUTION

kesavadas_thekkillath
Active Contributor
0 Kudos

put col3 as the first field in your itab.


TYPES:BEGIN OF ty3,
col3(3) TYPE c,
col2(3) TYPE c,
col1(3) TYPE c,
END OF ty3.

DATA:itab3 TYPE TABLE OF ty3.
DATA:it_xl TYPE TABLE OF ty3.
DATA:wa3 TYPE ty3.
DATA:file_name TYPE string.

SORT itab3 BY col3.

LOOP AT itab3 INTO wa3.
  APPEND wa3 TO it_xl.
  AT END OF col3.
    READ TABLE itab3 INTO wa3 INDEX sy-tabix.
    CHECK sy-subrc = 0.
    file_name  = wa3-col3.
    PERFORM download_xl USING file_name.
    REFRESH it_xl.
  ENDAT.
ENDLOOP.

*&---------------------------------------------------------------------*
*&      Form  download_xl
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_FILE     text
*----------------------------------------------------------------------*
FORM download_xl USING p_file type any.

  DATA:lv_path TYPE string VALUE 'c:\'.
  CONCATENATE lv_path  p_file '.xls' INTO p_file.
  CONDENSE p_file NO-GAPS.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename              = p_file
      filetype              = 'DAT'
      write_field_separator = '#'
    TABLES
      data_tab              = it_xl.

ENDFORM.                    "download_xl

12 REPLIES 12

Former Member
0 Kudos

loop at internal table where col3 = 'b1'.

append the data in one internal table and dump in excel.

endloop.

same for b2 and b3

loop at internal table where col3 = 'b2'.

append the data in one internal table and dump in excel.

endloop.

loop at internal table where col3 = 'b3'.

append the data in one internal table and dump in excel.

endloop.

or

loop at internal table.

case col3.

when 'b1'.

append to itab1.

when 'b2'.

apend to itab2

when 'b3'.

append to itab3.

endloop.

dump all three itabs in different excels.

0 Kudos

Hi ,

I am sorry guys for not being clear with my question.

Actually i cannot determine how many b1, b2,b3 ........bn will be there in the internal table.

so i cannot create n no of internal tables for each b1 b2 bn respectively..

How do i go thru for the scenario.

Regards,

navin

Former Member
0 Kudos

HI,

try something like this.


constants c_filepath type rlgrap-filename.

 loop at itab into wa_tab1.
   loop at itab into wa_tab2 "Read all the  records from itab with similar values in col3
                  where col3 = wa_tab1-col3.
   append wa_tab2 to itab_new.
  endloop.
  delete itab where col3 = wa_tab1-col3 
   concatenate p_fname
              '\' 
              wa_tab1-col3
              '.xls'
              to p_fname.
call function 'GUI_DOWNLOAD'
  exporting
 filename = p_fname
 filetype =  'ASC'
 tables
 tab  = tab_new
 refresh : tab_new[],
          p_fname.
 endloop.

Hope this helps

kesavadas_thekkillath
Active Contributor
0 Kudos

U mean to different work sheets or different excel files

0 Kudos

different excel files..

0 Kudos

You can create all multiple worksheets in single excel file using OLE automation.

and you will have to create only one internal table - new-tab.

0 Kudos

The real scenario is as ..

I wanted to split the records from the internal table based on b1 b2 b3.... and put in in different excel files and then attach each excel file as attachment in a mail and send it .

Hence i cannot put it in worksheets.

Regards

Navin

Former Member
0 Kudos

Hi ,

I am sorry guys for not being clear with my question.

Actually i cannot determine how many b1, b2,b3 ........bn will be there in the internal table.

so i cannot create n no of internal tables for each b1 b2 bn respectively..

How do i go thru for the scenario.

Regards,

navin

Former Member
0 Kudos

OLE2_OBJECT class can help you. With this class u can access all sort of excel things like workbooks, cells, font.

Former Member
0 Kudos

Hello Navin,

you try the below code.


REFRESH itab1.
SORT itab BY col3.
LOOP AT itab.
  APPEND itab TO itab1.
  AT END OF col3.
    CONCATENATE p_fname itab-col3 '.xls' INTO p_fname.
    CALL FUNCTION 'GUI_DOWNLOAD'
         EXPORTING
              filename = p_fname
              filetype = 'ASC'
         TABLES
              tab      = itab1.
    CLEAR : itab1[],itab,  p_fname.
  ENDAT.
ENDLOOP.

Hope this helps you.

Regards,

Sachinkumar Mehta.

kesavadas_thekkillath
Active Contributor
0 Kudos

put col3 as the first field in your itab.


TYPES:BEGIN OF ty3,
col3(3) TYPE c,
col2(3) TYPE c,
col1(3) TYPE c,
END OF ty3.

DATA:itab3 TYPE TABLE OF ty3.
DATA:it_xl TYPE TABLE OF ty3.
DATA:wa3 TYPE ty3.
DATA:file_name TYPE string.

SORT itab3 BY col3.

LOOP AT itab3 INTO wa3.
  APPEND wa3 TO it_xl.
  AT END OF col3.
    READ TABLE itab3 INTO wa3 INDEX sy-tabix.
    CHECK sy-subrc = 0.
    file_name  = wa3-col3.
    PERFORM download_xl USING file_name.
    REFRESH it_xl.
  ENDAT.
ENDLOOP.

*&---------------------------------------------------------------------*
*&      Form  download_xl
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      -->P_FILE     text
*----------------------------------------------------------------------*
FORM download_xl USING p_file type any.

  DATA:lv_path TYPE string VALUE 'c:\'.
  CONCATENATE lv_path  p_file '.xls' INTO p_file.
  CONDENSE p_file NO-GAPS.

  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
      filename              = p_file
      filetype              = 'DAT'
      write_field_separator = '#'
    TABLES
      data_tab              = it_xl.

ENDFORM.                    "download_xl

Former Member
0 Kudos

thread closed