Excel Grid

[former_member351353]

Hi -  How can i convert the below grid into an internal  table.

Range of:	150	200	250
$8.15	1.00%	2.00%	0.00%
$9.00	3.00%	4.00%	5.00%

My requirement is:  I get the above data in an excel and i need to convert that excel into an internal table like below.

$8.15   150   1.00%

$8.15   200   2.00%

$8.15   250   0.00%

$8.00   150   3.00%

$8.00   200   4.00%

$8.00   250   5.00%

I am using ALSM_EXCEL_TO_INTERNAL_TABLE   to read the excel   but how can i covert the read excel into above format.

Could you please help me

[former_member351353]

Hi Venkata,

After you get excel sheet data in the internal table you can process the internal table as per your requirement.

An example can be figured out by this sample code:

REPORT  ZR_DISPLAY_DATA.

TYPES: BEGIN OF TY1,

         A TYPE C LENGTH 15,

         B TYPE C LENGTH 15,

         C TYPE C LENGTH 15,

         D TYPE C LENGTH 15,

       END OF TY1.

DATA: ITAB1 TYPE STANDARD TABLE OF TY1,

      WA1 TYPE TY1,

      WA3 TYPE TY1.

TYPES: BEGIN OF TY2,

         A TYPE C LENGTH 15,

         B TYPE C LENGTH 15,

         C TYPE C LENGTH 15,        

       END OF TY2.

DATA: ITAB2 TYPE STANDARD TABLE OF TY2,

      WA2 TYPE TY2.

WA1-A = 'RANGE OF:'.      

WA1-B = '150'.

WA1-C = '200'.

WA1-D = '250'.

APPEND WA1 TO ITAB1.

WA1-A = '$8.15'.

WA1-B = '1.00%'.

WA1-C = '2.00%'.

WA1-D = '0.00%'.

APPEND WA1 TO ITAB1.

WA1-A = '9.00%'.

WA1-B = '3.00%'.

WA1-C = '4.00%'.

WA1-D = '5.00%'.

APPEND WA1 TO ITAB1.

LOOP AT ITAB1 INTO WA1.

  IF WA1-A = 'RANGE OF:'.

    WA3-B = WA1-B.

    WA3-C = WA1-C.

    WA3-D = WA1-D.

  ELSE.

    WA2-A = WA1-A.

    WA2-B = WA3-B.

    WA2-C = WA1-B.

    APPEND WA2 TO ITAB2.

    WA2-A = WA1-A.

    WA2-B = WA3-C.

    WA2-C = WA1-C.

    APPEND WA2 TO ITAB2.

     WA2-A = WA1-A.

    WA2-B = WA3-D.

    WA2-C = WA1-D.

    APPEND WA2 TO ITAB2.

  ENDIF.     

ENDLOOP.

LOOP AT ITAB2 INTO WA2.

  WRITE: / WA2-A, WA2-B, WA2-C.

ENDLOOP.

[Former Member]

Hi Venkata,

After you get excel sheet data in the internal table you can process the internal table as per your requirement.

An example can be figured out by this sample code:

REPORT  ZR_DISPLAY_DATA.

TYPES: BEGIN OF TY1,

         A TYPE C LENGTH 15,

         B TYPE C LENGTH 15,

         C TYPE C LENGTH 15,

         D TYPE C LENGTH 15,

       END OF TY1.

DATA: ITAB1 TYPE STANDARD TABLE OF TY1,

      WA1 TYPE TY1,

      WA3 TYPE TY1.

TYPES: BEGIN OF TY2,

         A TYPE C LENGTH 15,

         B TYPE C LENGTH 15,

         C TYPE C LENGTH 15,        

       END OF TY2.

DATA: ITAB2 TYPE STANDARD TABLE OF TY2,

      WA2 TYPE TY2.

WA1-A = 'RANGE OF:'.      

WA1-B = '150'.

WA1-C = '200'.

WA1-D = '250'.

APPEND WA1 TO ITAB1.

WA1-A = '$8.15'.

WA1-B = '1.00%'.

WA1-C = '2.00%'.

WA1-D = '0.00%'.

APPEND WA1 TO ITAB1.

WA1-A = '9.00%'.

WA1-B = '3.00%'.

WA1-C = '4.00%'.

WA1-D = '5.00%'.

APPEND WA1 TO ITAB1.

LOOP AT ITAB1 INTO WA1.

  IF WA1-A = 'RANGE OF:'.

    WA3-B = WA1-B.

    WA3-C = WA1-C.

    WA3-D = WA1-D.

  ELSE.

    WA2-A = WA1-A.

    WA2-B = WA3-B.

    WA2-C = WA1-B.

    APPEND WA2 TO ITAB2.

    WA2-A = WA1-A.

    WA2-B = WA3-C.

    WA2-C = WA1-C.

    APPEND WA2 TO ITAB2.

     WA2-A = WA1-A.

    WA2-B = WA3-D.

    WA2-C = WA1-D.

    APPEND WA2 TO ITAB2.

  ENDIF.     

ENDLOOP.

LOOP AT ITAB2 INTO WA2.

  WRITE: / WA2-A, WA2-B, WA2-C.

ENDLOOP.

[former_member351353]

Hi -  Thanks for info.  I am able to solve it.

But now I have to do the reverse.

I have the data in internal table like below. 

$8.15   150   1.00%

$8.15   200   2.00%

$8.15   250   0.00%

$8.00   150   3.00%

$8.00   200   4.00%

$8.00   250   5.00%

I need to conver the able internal table and display as GRID like below.

Range of:	150	200	250
$8.15	1.00%	2.00%	0.00%
$9.00	3.00%	4.00%	5.00%

Could you please help me.

[Former Member]

Hi Venkata,

You can reverse your logic to again read the internal table in the following way:

*---- READING HEADER
CLEAR WA1.
CLEAR ITAB1.

LOOP AT ITAB2 INTO WA2.
   WA1-A = 'RANGE OF:'.
   IF WA1-B IS INITIAL.
     WA1-B = WA2-B.
   ELSEIF WA1-C IS INITIAL.
     WA1-C = WA2-B.
   ELSEIF WA1-D IS INITIAL.
     WA1-D = WA2-B.
   ENDIF.
   IF WA1-A IS NOT INITIAL AND
      WA1-B IS NOT INITIAL AND
      WA1-C IS NOT INITIAL AND
      WA1-D IS NOT INITIAL.
      APPEND WA1 TO ITAB1.
      EXIT.
   ENDIF.
ENDLOOP.

CLEAR WA1.


****READING RANGE ONLY

LOOP AT ITAB2 INTO WA2.
   READ TABLE ITAB1 INTO WA1 WITH KEY A = WA2-A.
   IF SY-SUBRC NE 0.
     WA1-A = WA2-A.
      APPEND WA1 TO ITAB1.
      CLEAR WA1.
   ENDIF.
ENDLOOP.

****READING DATA

LOOP AT ITAB2 INTO WA2.
   READ TABLE ITAB1 INTO WA1 WITH KEY A = WA2-A.
   IF SY-SUBRC = 0.
     IF WA1-B IS INITIAL.
       WA1-B = WA2-C.
     ELSEIF WA1-C IS INITIAL.
       WA1-C = WA2-C.
     ELSEIF WA1-D IS INITIAL.
       WA1-D = WA2-C.
     ENDIF.
      MODIFY ITAB1 FROM WA1 INDEX SY-tabix.
      CLEAR WA1.
   ENDIF.
ENDLOOP.

LOOP AT ITAB1 INTO WA1.
   WRITE: / WA1-A, WA1-B, WA1-C, WA1-D.
ENDLOOP.