topic Re: to simulate a Excel Sheet data in Application Development and Automation Discussions

to simulate a Excel Sheet data

former_member541575 — Tue, 20 Jan 2009 11:56:28 GMT

i want to add a check box in the selection screen of my program , when click on simulate and press execute then the data in the excel sheet should be shown in SAP screen.

how it is possible

Regards

Atul

Re: to simulate a Excel Sheet data

Former Member — Tue, 20 Jan 2009 12:04:58 GMT

u meant u r passing an excel sheet to the report,when u click checkbox,u want the data in the excel to be shown as output in list display?

Re: to simulate a Excel Sheet data

former_member541575 — Tue, 20 Jan 2009 12:08:44 GMT

yes , you are correct.

Re: to simulate a Excel Sheet data

Former Member — Tue, 20 Jan 2009 12:25:32 GMT

Hi Atul,

Use FM "ALSM_EXCEL_TO_INTERNAL_TABLE".

Regards,

Goutham.

Re: to simulate a Excel Sheet data

Former Member — Tue, 20 Jan 2009 13:15:22 GMT

hi...a code snippet for downloading excel content to int table...

v_filename = p_file.

*Reading from Exel File

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = v_filename

i_begin_col = c_1

i_begin_row = c_2

i_end_col = c_13

i_end_row = p_rows

TABLES

intern = it_data

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.

*Moving from excel to the internal table format

LOOP AT it_data INTO wa_data1.

wa_data = wa_data1.

AT NEW row.

CLEAR wa_bom.

ENDAT.

CASE wa_data-col.

WHEN '001'.

MOVE wa_data-value TO wa_bom-matnr.

WHEN '002'.

MOVE wa_data-value TO wa_bom-werks.

WHEN '003'.

MOVE wa_data-value TO wa_bom-stlan.

WHEN '004'.

MOVE wa_data-value TO wa_bom-stlal.

WHEN '005'.

MOVE wa_data-value TO wa_bom-datuv.

WHEN '006'.

MOVE wa_data-value TO wa_bom-postp.

WHEN '007'.

MOVE wa_data-value TO wa_bom-idnrk.

WHEN '008'.

MOVE wa_data-value TO wa_bom-ktext.

WHEN '009'.

MOVE wa_data-value TO wa_bom-menge.

WHEN '010'.

MOVE wa_data-value TO wa_bom-meins.

WHEN '011'.

MOVE wa_data-value TO wa_bom-sortf.

WHEN '012'.

MOVE wa_data-value TO wa_bom-ident.

ENDCASE.

AT END OF row.

APPEND wa_bom TO it_bom.

ENDAT.

CLEAR : wa_data.

ENDLOOP.

Re: to simulate a Excel Sheet data

Former Member — Wed, 21 Jan 2009 03:39:01 GMT

Hi Atul

Just check if the below mentioned code snippet can be helpful for you.

DATA: EXCEL_TAB TYPE KCDE_SENDER.

DATA: SEPARATOR TYPE C.

FIELD-SYMBOLS: <FIELD>.

DATA: APPLICATION TYPE OLE2_OBJECT,

WORKBOOK TYPE OLE2_OBJECT,

RANGE TYPE OLE2_OBJECT,

WORKSHEET TYPE OLE2_OBJECT.

DATA: H_CELL TYPE OLE2_OBJECT.

DATA: H_CELL1 TYPE OLE2_OBJECT.

DEFINE M_MESSAGE.

CASE SY-SUBRC.

WHEN 0.

WHEN 1.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

WHEN OTHERS. RAISE UPLOAD_OLE.

ENDCASE.

END-OF-DEFINITION.

IF I_BEGIN_ROW > I_END_ROW. RAISE INCONSISTENT_PARAMETERS. ENDIF.

IF I_BEGIN_COL > I_END_COL. RAISE INCONSISTENT_PARAMETERS. ENDIF.

IF APPLICATION-HEADER = SPACE OR APPLICATION-HANDLE = -1.

CREATE OBJECT APPLICATION 'Excel.Application'.

M_MESSAGE.

ENDIF.

CALL METHOD OF APPLICATION 'Workbooks' = WORKBOOK.

M_MESSAGE.

CALL METHOD OF WORKBOOK 'Open' EXPORTING #1 = FILENAME.

M_MESSAGE.

set property of application 'Visible' = 1.
m_message.

GET PROPERTY OF APPLICATION 'ACTIVESHEET' = WORKSHEET.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'Cells' = H_CELL

EXPORTING #1 = I_BEGIN_ROW #2 = I_BEGIN_COL.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'Cells' = H_CELL1

EXPORTING #1 = I_END_ROW #2 = I_END_COL.

M_MESSAGE.

CALL METHOD OF WORKSHEET 'RANGE' = RANGE

EXPORTING #1 = H_CELL #2 = H_CELL1.

M_MESSAGE.

CALL METHOD OF RANGE 'SELECT'.

M_MESSAGE.

copy to Clippboard

CALL METHOD OF RANGE 'COPY'.

M_MESSAGE.

free object application.

M_MESSAGE.

Without flush, CLPB_IMPORT does not find the data "SEVERING 5/99

CALL FUNCTION 'CONTROL_FLUSH' "SEVERING 5/99

EXCEPTIONS OTHERS = 3. "SEVERING 5/99

CALL FUNCTION 'CLPB_IMPORT'

TABLES

DATA_TAB = EXCEL_TAB

EXCEPTIONS

CLPB_ERROR = 1

OTHERS = 2.

IF SY-SUBRC <> 0. MESSAGE X001(KX). ENDIF.

ASSIGN SEPARATOR TO <FIELD> TYPE 'X'.

<FIELD> = C_HEX_TAB.

PERFORM SEPARATED_TO_INTERN_CONVERT TABLES EXCEL_TAB INTERN

USING SEPARATOR.

set property OF APPLICATION 'CutCopyMode' = 0.

M_MESSAGE.

CALL METHOD OF APPLICATION 'QUIT'.

M_MESSAGE.

FREE OBJECT APPLICATION.

M_MESSAGE.

ENDFUNCTION.

Use this with the following parameters.

*"----

""Local interface:

*" IMPORTING

*" VALUE(FILENAME) LIKE RLGRAP-FILENAME

*" VALUE(I_BEGIN_COL) TYPE I

*" VALUE(I_BEGIN_ROW) TYPE I

*" VALUE(I_END_COL) TYPE I

*" VALUE(I_END_ROW) TYPE I

*" TABLES

*" INTERN TYPE KCDE_INTERN

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----

Hope this helps.

Regards

Harsh

Re: to simulate a Excel Sheet data

Former Member — Wed, 21 Jan 2009 03:53:33 GMT

Hi,

Have a look @this:


REPORT ZEXCELTABC .
PARAMETERS: filename LIKE rlgrap-filename,
            begcol TYPE i DEFAULT 1 NO-DISPLAY,
            begrow TYPE i DEFAULT 1 NO-DISPLAY,
            endcol TYPE i DEFAULT 100 NO-DISPLAY,
            endrow TYPE i DEFAULT 32000 NO-DISPLAY.
PARAMETERS: kzheader AS CHECKBOX.
DATA: BEGIN OF intern OCCURS 0.
        INCLUDE STRUCTURE  alsmex_tabline.
DATA: END OF intern.
DATA: BEGIN OF intern1 OCCURS 0.
        INCLUDE STRUCTURE  alsmex_tabline.
DATA: END OF intern1.
DATA: BEGIN OF t_col OCCURS 0,
       col LIKE alsmex_tabline-col,
       size TYPE i.
DATA: END OF t_col.
DATA: zwlen TYPE i,
      zwlines TYPE i.
DATA: BEGIN OF fieldnames OCCURS 3,
        title(60),
        table(6),
        field(10),
        kz(1),
      END OF fieldnames.
DATA: BEGIN OF data_tab OCCURS 0,
       value_0001(50),
       value_0002(50),
       value_0003(50),
       value_0004(50),
       value_0005(50),
       value_0006(50),
       value_0007(50),
       value_0008(50),
       value_0009(50),
       value_0010(50),
       value_0011(50),
       value_0012(50),
       value_0013(50),
       value_0014(50),
       value_0015(50),
       value_0016(50),
       value_0017(50),
       value_0018(50),
       value_0019(50),
       value_0020(50),
       value_0021(50),
       value_0022(50),
       value_0023(50),
       value_0024(50),
       value_0025(50),
       value_0026(50),
       value_0027(50),
       value_0028(50),
       value_0029(50),
       value_0030(50),
       value_0031(50),
       value_0032(50),
       value_0033(50),
       value_0034(50),
       value_0035(50),
       value_0036(50),
       value_0037(50),
       value_0038(50),
       value_0039(50),
       value_0040(50),
       value_0041(50),
       value_0042(50),
       value_0043(50),
       value_0044(50),
       value_0045(50),
       value_0046(50),
       value_0047(50),
       value_0048(50),
       value_0049(50),
       value_0050(50),
       value_0051(50),
       value_0052(50),
       value_0053(50),
       value_0054(50),
       value_0055(50),
       value_0056(50),
       value_0057(50),
       value_0058(50),
       value_0059(50),
       value_0060(50),
       value_0061(50),
       value_0062(50),
       value_0063(50),
       value_0064(50),
       value_0065(50),
       value_0066(50),
       value_0067(50),
       value_0068(50),
       value_0069(50),
       value_0070(50),
       value_0071(50),
       value_0072(50),
       value_0073(50),
       value_0074(50),
       value_0075(50),
       value_0076(50),
       value_0077(50),
       value_0078(50),
       value_0079(50),
       value_0080(50),
       value_0081(50),
       value_0082(50),
       value_0083(50),
       value_0084(50),
       value_0085(50),
       value_0086(50),
       value_0087(50),
       value_0088(50),
       value_0089(50),
       value_0090(50),
       value_0091(50),
       value_0092(50),
       value_0093(50),
       value_0094(50),
       value_0095(50),
       value_0096(50),
       value_0097(50),
       value_0098(50),
       value_0099(50),
       value_0100(50).
DATA: END OF data_tab.
DATA: tind(4) TYPE n.
DATA: zwfeld(19).
FIELD-SYMBOLS: <fs1>.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR filename.
  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
       EXPORTING
            mask      = '*.xls'
            static    = 'X'
       CHANGING
            file_name = filename.


START-OF-SELECTION.


  CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
       EXPORTING
            filename                = filename
            i_begin_col             = begcol
            i_begin_row             = begrow
            i_end_col               = endcol
            i_end_row               = endrow
       TABLES
            intern                  = intern
       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.

END-OF-SELECTION.
* max. Feldlänge je Spalte ermitteln...
  LOOP AT intern.
    intern1 = intern.
    CLEAR intern1-row.
    APPEND intern1.
  ENDLOOP.
  SORT intern1 BY col.
  LOOP AT intern1.
    AT NEW col.
      t_col-col = intern1-col.
      APPEND t_col.
    ENDAT.
    zwlen = strlen( intern1-value ).
    READ TABLE t_col WITH KEY col = intern1-col.
    IF sy-subrc EQ 0.
      IF zwlen > t_col-size.
        t_col-size = zwlen.
        MODIFY t_col INDEX sy-tabix.
      ENDIF.
    ENDIF.
  ENDLOOP.
* max. Spaltenanzahl ermitteln.
  DESCRIBE TABLE t_col LINES zwlines.
* wenn die 1. Zeile die Spaltenüberschriften enthält...
  SORT intern BY row col.
  IF kzheader = 'X'.
    LOOP AT intern.
      fieldnames-title = intern-value.
      APPEND fieldnames.
      AT END OF row.                  " Ende der 1. Zeile
        EXIT.
      ENDAT.
    ENDLOOP.
  ELSE.
    DO zwlines TIMES.
      WRITE sy-index TO fieldnames-title.
      APPEND fieldnames.
    ENDDO.
  ENDIF.
* Data_tab füllen ...
  SORT intern BY row col.
  LOOP AT intern.
    IF kzheader = 'X'
    AND intern-row = 1.
      CONTINUE.
    ENDIF.
    tind = intern-col.
    CONCATENATE 'DATA_TAB-VALUE_' tind INTO zwfeld.
    ASSIGN (zwfeld) TO <fs1>.
    <fs1> = intern-value.
    AT END OF row.
      APPEND data_tab.
      CLEAR data_tab.
    ENDAT.
  ENDLOOP.

  CALL FUNCTION 'DISPLAY_BASIC_LIST'
       EXPORTING
            file_name     = filename
       TABLES
            data_tab      = data_tab
            fieldname_tab = fieldnames.

No Points Please, this is not my code, but it works.

Regards

Raju Chitale