Uploading the excel sheet data with multiple worksheets, Please help me out

[Former Member]

Hi Gurus,

I got a requirement to uploading the data from Excel sheet with multiple worksheets, i followed the below given process and want to know how to move the data from i_data(contains row, col, and value) into the internal table, I have 4 sheets in my excel file, i am getting the data into i_data for every sheet, but the thing is for each and every sheet i will have roughly 50 to 60 fields, please suggest me in this regards.

After moving the data into the internal tables, i have to build BAPI structure which i have to pass to the BAPI BAPI_SALESORDER_CREATEFROMDAT2 PLease suggest me of how to build the internal to pass the data.

Code as follows :

In the code i have taken only few fields(6) in the structure .

REPORT zexcel_upload NO STANDARD PAGE HEADING.

TYPES : BEGIN OF s_data,

name(15) TYPE c,

type(25) TYPE c,

name1(25) TYPE c,

name2(25) TYPE c,

name3(25) TYPE c,

name4(25) TYPE c,

END OF s_data.

DATA:

oref_container TYPE REF TO cl_gui_custom_container,

iref_control TYPE REF TO i_oi_container_control,

iref_document TYPE REF TO i_oi_document_proxy,

iref_spreadsheet TYPE REF TO i_oi_spreadsheet,

iref_error TYPE REF TO i_oi_error,

l_lines TYPE i..

DATA:

v_document_url TYPE c LENGTH 256,

i_sheets TYPE soi_sheets_table,

wa_sheets TYPE soi_sheets,

i_data TYPE soi_generic_table,

wa_data TYPE soi_generic_item,

i_ranges TYPE soi_range_list.

DATA : it_data TYPE TABLE OF s_data WITH HEADER LINE,

wa_data1 LIKE LINE OF it_data,

it_data1 TYPE TABLE OF s_data WITH HEADER LINE,

wa_data2 LIKE LINE OF it_data1,

it_data2 TYPE TABLE OF s_data WITH HEADER LINE,

wa_data3 LIKE LINE OF it_data2..

PARAMETERS:

p_file TYPE localfile OBLIGATORY,

p_rows TYPE i DEFAULT 100 OBLIGATORY, "Rows (Maximum 65536)

p_cols TYPE i DEFAULT 60 OBLIGATORY. "Columns (Maximum 256)

INITIALIZATION.

CALL METHOD c_oi_container_control_creator=>get_container_control

IMPORTING

control = iref_control

error = iref_error.

CREATE OBJECT oref_container

EXPORTING

• parent =

container_name = 'CONT'

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

create_error = 3

lifetime_error = 4

lifetime_dynpro_dynpro_link = 5

OTHERS = 6.

CALL METHOD iref_control->init_control

EXPORTING

inplace_enabled = 'X'

r3_application_name = 'EXCEL CONTAINER'

parent = oref_container

IMPORTING

error = iref_error

EXCEPTIONS

javabeannotsupported = 1

OTHERS = 2.

CALL METHOD iref_control->get_document_proxy

EXPORTING

document_type = soi_doctype_excel_sheet

IMPORTING

document_proxy = iref_document

error = iref_error.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

PERFORM sub_file_f4.

START-OF-SELECTION.

CONCATENATE 'FILE://' p_file INTO v_document_url.

CALL METHOD iref_document->open_document

EXPORTING

document_title = 'Excel'

document_url = v_document_url

open_inplace = 'X'

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

CALL METHOD iref_document->get_spreadsheet_interface

EXPORTING

no_flush = ' '

IMPORTING

error = iref_error

sheet_interface = iref_spreadsheet.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

<<<<<< >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>This method gets the work sheets name.

CALL METHOD iref_spreadsheet->get_sheets

EXPORTING

no_flush = ' '

IMPORTING

sheets = i_sheets

error = iref_error.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

<<< >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>Looping through all the worksheets that are there in the excel file

LOOP AT i_sheets INTO wa_sheets.

CALL METHOD iref_spreadsheet->select_sheet

EXPORTING

name = wa_sheets-sheet_name

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

EXIT.

ENDIF.

CALL METHOD iref_spreadsheet->set_selection

EXPORTING

top = 1

left = 1

rows = p_rows

columns = p_cols.

CALL METHOD iref_spreadsheet->insert_range

EXPORTING

name = 'Test'

rows = p_rows

columns = p_cols

no_flush = ''

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

EXIT.

ENDIF.

REFRESH i_data.

<<<<<< >>>>>>>>>>>>>>>>>>>>>>>>>>>Method gets the data from the worksheets

CALL METHOD iref_spreadsheet->get_ranges_data

EXPORTING

all = 'X'

IMPORTING

contents = i_data

error = iref_error

CHANGING

ranges = i_ranges.

DATA: lv_row TYPE i VALUE '1'.

LOOP AT i_data INTO wa_data.

IF lv_row NE wa_data-row.

COLLECT wa_data1 INTO it_data.

MOVE wa_data-row TO lv_row.

CLEAR wa_data1.

ENDIF.

IF wa_sheets-sheet_name = 'Sheet1'.

IF wa_data-row GE 1 AND wa_data-column = 1.

wa_data1-name = wa_data-value.

ELSEIF wa_data-row GE 1 AND wa_data-column = 2.

wa_data1-name1 = wa_data-value.

ELSEIF wa_data-row GE 1 AND wa_data-column = 3.

wa_data1-name2 = wa_data-value.

ELSEIF wa_data-row GE 1 AND wa_data-column = 4.

wa_data1-type = wa_data-value.

ELSEIF wa_data-row GE 1 AND wa_data-column = 5.

wa_data1-name3 = wa_data-value.

ELSEIF wa_data-row GE 1 AND wa_data-column = 6.

wa_data1-name4 = wa_data-value.

ENDIF.

ENDIF.

CALL METHOD iref_document->close_document

• EXPORTING

• do_save = ' '

• no_flush = ' '

IMPORTING

error = iref_error

• has_changed =

• retcode =.

CALL METHOD iref_document->release_document

• EXPORTING

• no_flush = ' '

IMPORTING

error = iref_error

• retcode =

.

REFRESH : it_data,it_data1,it_data2.

&----

---

*& Form SUB_FILE_F4

&----

---

• F4 help for file path

----

---

FORM sub_file_f4 .

DATA:

l_desktop TYPE string,

l_i_files TYPE filetable,

l_wa_files TYPE file_table,

l_rcode TYPE int4.

• Finding desktop

CALL METHOD cl_gui_frontend_services=>get_desktop_directory

CHANGING

desktop_directory = l_desktop

EXCEPTIONS

cntl_error = 1

error_no_gui = 2

not_supported_by_gui = 3

OTHERS = 4.

IF sy-subrc <> 0.

MESSAGE e001(00) WITH

'Desktop not found'.

ENDIF.

• Update View

CALL METHOD cl_gui_cfw=>update_view

EXCEPTIONS

cntl_system_error = 1

cntl_error = 2

OTHERS = 3.

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

window_title = 'Select Excel file'

default_extension = '.xls'

• default_filename =

file_filter = '.xls'

• with_encoding =

initial_directory = l_desktop

• multiselection =

CHANGING

file_table = l_i_files

rc = l_rcode

• user_action =

• file_encoding =

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error = 2

error_no_gui = 3

not_supported_by_gui = 4

OTHERS = 5

.

IF sy-subrc <> 0.

MESSAGE e001(00) WITH 'Error while opening file'.

ENDIF.

Regards,

C.Bharath Kumar

[matt]

There is a 2500 character limit, or thereabouts, on posting. Please repost just the relevant portion of your code.

matt