topic Re: Excel Grid in Application Development and Automation Discussions

Excel Grid

former_member351353 — Mon, 03 Aug 2015 20:52:00 GMT

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

Re: Excel Grid

Former Member — Tue, 04 Aug 2015 10:29:22 GMT

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.

Re: Excel Grid

former_member351353 — Tue, 04 Aug 2015 19:21:59 GMT

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.

Re: Excel Grid

Former Member — Thu, 06 Aug 2015 14:03:27 GMT

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.