BDC table control with excel sheet upload

[Former Member]

hi all,

can anybody help regarding BDC TABLE CONTROL WITH

EXCEL SHEET UPLOAD

thanks

[Former Member]

Hi,

The sample code is as given below:

REPORT upload_supply_area.

*include for dispaying icons in error log

INCLUDE <icon>.

*Declaration of structure.

TYPES:BEGIN OF x_struct,

werks TYPE v_pvbe-werks, "Plant

prvbe TYPE v_pvbe-prvbe, "Supply Area

pvbtx TYPE v_pvbe-pvbtx, "Production supply area description

lgort TYPE v_pvbe-lgort, "Storage Location

rgver TYPE v_pvbe-rgver, "Person responsible for one or more supply areas

END OF x_struct.

TYPES:BEGIN OF x_messages,

msgtyp(1) type c,

werks TYPE v_pvbe-werks, "Plant

prvbe TYPE v_pvbe-prvbe, "Supply Area

message(120) type c,

END OF x_messages.

DATA: it_messages TYPE STANDARD TABLE OF x_messages .

DATA: wa_messages TYPE x_messages.

DATA:it_msgtab TYPE STANDARD TABLE OF bdcmsgcoll,

wa_msgtab TYPE bdcmsgcoll.

*internal table for BDC

DATA: it_bdcdata TYPE STANDARD TABLE OF bdcdata.

DATA: wa_bdcdata TYPE bdcdata.

DATA:it_file TYPE STANDARD TABLE OF x_struct. "internal table which has same structure as file

DATA:wa_file TYPE x_struct. "work area which has same structure as file

DATA: it_excel TYPE STANDARD TABLE OF alsmex_tabline,

wa_excel TYPE alsmex_tabline.

DATA: x_ctuprms TYPE ctu_params.

DATA:nodata TYPE c VALUE '/'.

data:con(50) type c.

data:con1(50) type c.

*selection screen

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

*Enter file name on presentation server

PARAMETERS: p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

*Function which enables the user to browse the files on hard disk

CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

EXPORTING

program_name = syst-repid

static = 'X'

CHANGING

file_name = p_file

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.

*Subroutine to upload excel file and read it

PERFORM upload.

*Subroutine to upload supply area data

PERFORM fill.

&----

---

*& Form bdc_dynpro

&----

---

Fill the BDC table

-

---

FORM bdc_dynpro USING program dynpro. "#EC *

CLEAR wa_bdcdata.

wa_bdcdata-program = program.

wa_bdcdata-dynpro = dynpro.

wa_bdcdata-dynbegin = 'X'.

APPEND wa_bdcdata TO it_bdcdata.

ENDFORM. "BDC_DYNPRO

&----

---

*& Form bdc_field

&----

---

Fill the BDC table

-

---

FORM bdc_field USING fnam fval. "#EC *

IF fval nodata.

CLEAR wa_bdcdata.

wa_bdcdata-fnam = fnam.

wa_bdcdata-fval = fval.

APPEND wa_bdcdata TO it_bdcdata. "#EC

ENDIF.

ENDFORM. "BDC_FIELD

&----

---

*& Form collect_messages

&----

---

Collect the messages from transaction

-

---

FORM collect_messages . "#EC *

DATA: w_msg(100).

LOOP AT it_msgtab INTO wa_msgtab.

CALL FUNCTION 'FORMAT_MESSAGE'

EXPORTING

id = wa_msgtab-msgid

lang = wa_msgtab-msgspra

no = wa_msgtab-msgnr

v1 = wa_msgtab-msgv1

v2 = wa_msgtab-msgv2

IMPORTING

msg = w_msg

EXCEPTIONS

not_found = 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.

CONDENSE w_msg.

CLEAR wa_messages.

wa_messages-msgtyp = wa_msgtab-msgtyp.

wa_messages-message = w_msg.

wa_messages-werks = wa_file-werks.

wa_messages-prvbe = wa_file-prvbe.

if wa_messages-message eq 'Formatting error in the field V_PVBE-RGVER; see next message'.

wa_messages-message = 'Invalid name of the person responsible'.

endif.

if wa_messages-message eq 'Formatting error in the field V_PVBE-LGORT; see next message'.

wa_messages-message = 'Enter the storage location'.

endif.

APPEND wa_messages TO it_messages .

ENDLOOP.

REFRESH it_msgtab.

ENDFORM. "collect_messages

&----

---

*& Form write_messages

&----

---

Display the messages

-

---

FORM write_messages .

DELETE ADJACENT DUPLICATES FROM it_messages COMPARING werks prvbe.

LOOP AT it_messages INTO wa_messages .

WRITE:/1 sy-vline.

IF wa_messages-msgtyp = 'S'.

WRITE: 10 icon_green_light.

ELSEIF wa_messages-msgtyp = 'E'.

WRITE: 10 icon_red_light.

ELSEIF wa_messages-msgtyp = 'W'.

WRITE: 10 icon_yellow_light.

ENDIF.

WRITE: 20 sy-vline.

WRITE : 30 'Plant-', wa_messages-werks . "#EC NOTEXT

WRITE: 48 sy-vline.

WRITE : 49 'Supply Area-', wa_messages-prvbe . "#EC NOTEXT

WRITE: 79 sy-vline.

WRITE : 80 wa_messages-message .

WRITE: 180 sy-vline.

WRITE:/1 sy-vline.

ULINE 1(180).

ENDLOOP.

ENDFORM. " write_m

&----

---

*& Form fill_params

&----

---

Processing mode for the transaction

-

---

FORM fill_params .

x_ctuprms-dismode = 'N'.

x_ctuprms-updmode = 'A'.

x_ctuprms-defsize = 'X'.

ENDFORM. "fill_params

&----

---

*& Form upload

&----

---

Upload the excel file and read the data

-

---

FORM upload .

*Function to upload excel file

CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'

EXPORTING

filename = p_file

i_begin_col = 1

i_begin_row = 2

i_end_col = 5

i_end_row = 9999

TABLES

intern = it_excel

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.

CLEAR wa_file.

*Read the file row-wise

LOOP AT it_excel INTO wa_excel.

CASE wa_excel-col .

*Read plant

WHEN '1'.

wa_file-werks = wa_excel-value.

*Read supply area

WHEN '2'.

wa_file-prvbe = wa_excel-value.

*Read decription

WHEN '3'.

wa_file-pvbtx = wa_excel-value.

*Read storage location

WHEN '4'.

wa_file-lgort = wa_excel-value.

*Read Person responsible

WHEN '5'.

IF STRLEN( wa_excel-value ) = 1.

CONCATENATE '00' wa_excel-value INTO con.

wa_file-rgver = con.

ELSEIF STRLEN( wa_excel-value ) = 2.

CONCATENATE '0' wa_excel-value INTO con1.

wa_file-rgver = con1.

ELSE.

wa_file-rgver = wa_excel-value.

ENDIF.

ENDCASE.

AT END OF row.

CONDENSE:wa_file-werks,wa_file-prvbe,wa_file-pvbtx,wa_file-lgort,wa_file-rgver.

APPEND wa_file TO it_file.

CLEAR wa_file.

ENDAT .

ENDLOOP.

ENDFORM. " upload

&----

---

*& Form fill

&----

---

Call the transaction 'PK05'

-

---

FORM fill .

*Upload the data through transaction 'PK05'

PERFORM fill_params.

LOOP AT it_file INTO wa_file.

PERFORM bdc_dynpro USING 'SAPLSVIX' '0100'.

PERFORM bdc_field USING 'BDC_CURSOR'

'D0100_FIELD_TAB-LOWER_LIMIT(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=OKAY'.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0020'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-PVBTX(01)'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=NEWL'.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0021'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-RGVER'.

PERFORM bdc_field USING 'BDC_OKCODE'

'/00'.

PERFORM bdc_field USING 'V_PVBE-WERKS'

wa_file-werks.

PERFORM bdc_field USING 'V_PVBE-PRVBE'

wa_file-prvbe.

PERFORM bdc_field USING 'V_PVBE-PVBTX'

wa_file-pvbtx.

PERFORM bdc_field USING 'V_PVBE-LGORT'

wa_file-lgort.

PERFORM bdc_field USING 'V_PVBE-RGVER'

wa_file-rgver.

PERFORM bdc_dynpro USING 'SAPL0PK1' '0021'.

PERFORM bdc_field USING 'BDC_CURSOR'

'V_PVBE-WERKS'.

PERFORM bdc_field USING 'BDC_OKCODE'

'=SAVE'.

PERFORM bdc_field USING 'V_PVBE-WERKS'

wa_file-werks.

PERFORM bdc_field USING 'V_PVBE-PRVBE'

wa_file-prvbe.

PERFORM bdc_field USING 'V_PVBE-PVBTX'

wa_file-pvbtx.

PERFORM bdc_field USING 'V_PVBE-LGORT'

wa_file-lgort.

PERFORM bdc_field USING 'V_PVBE-RGVER'

wa_file-rgver.

CALL TRANSACTION 'PK05'

USING it_bdcdata

OPTIONS FROM x_ctuprms

MESSAGES INTO it_msgtab.

REFRESH it_bdcdata.

PERFORM collect_messages.

CLEAR wa_file.

ENDLOOP.

PERFORM write_messages.

ENDFORM. " fill

Thanks,

Jaten