Upload - Excel Sheet from different tabs

[former_member253880]

Hi ABAP Experts,

we like to upload an excel sheet to ABAP internal table. The problem is that the excel sheet contains serveral tabs.

How we can upload (Presentation Server) an excel sheet with serveral tabs.

We checked already the standard FM, like:

TEXT_CONVERT_XLS_TO_SAP

SAP_CONVERT_TO_CSV_FORMAT

KCD_EXCEL_OLE_TO_INT_CONVERT

........

Is there a special framework to handle OLE objects?

Thank you very much in advance for your answers.

Kind regards

Axel

[Former Member]

Hi Alex,

Please Check the below Code.

data:

t_alsmex_tabline like

standard table

of alsmex_tabline

with header line.

data:

begin of test occurs 1,

char1(20) type c,

char2(20) type c,

char3(20) type c,

end of test.

data:

w_row type i,

w_line(50) type c,

w_values type i,

w_type type c,

w_times type i.

describe field test type w_type components w_values.

call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

filename = 'c:\temp\test.xls'

i_begin_col = 1

i_begin_row = 1

i_end_col = w_values

i_end_row = 10

tables

intern = t_alsmex_tabline

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.

describe table t_alsmex_tabline.

w_times = sy-tfill div w_values.

w_row = 0.

do w_times times.

w_row = w_row + 1.

loop at t_alsmex_tabline where row = w_row .

concatenate w_line t_alsmex_tabline-value

into w_line separated by space.

endloop.

shift w_line left.

split w_line at space into test-char1

test-char2

test-char3 .

append test.

write:

/ test-char1,

test-char2,

test-char3.

clear: test,

w_line.

enddo.

Thanks

Kalyan B

[Former Member]

Chk this out...

REPORT YKEXL .

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.

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.

PARAMETERS:

p_file TYPE localfile OBLIGATORY,

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

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

INITIALIZATION.

CALL METHOD c_oi_container_control_creator=>get_container_control

IMPORTING

control = iref_control

error = iref_error

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'E'.

ENDIF.

CREATE OBJECT oref_container

EXPORTING

• parent =

container_name = 'CONT'

• style =

• lifetime = lifetime_default

• repid =

• dynnr =

• no_autodef_progid_dynnr =

EXCEPTIONS

cntl_error = 1

cntl_system_error = 2

create_error = 3

lifetime_error = 4

lifetime_dynpro_dynpro_link = 5

OTHERS = 6.

IF sy-subrc <> 0.

MESSAGE e001(00) WITH 'Error while creating container'.

ENDIF.

CALL METHOD iref_control->init_control

EXPORTING

• dynpro_nr = SY-DYNNR

• gui_container = ' '

inplace_enabled = 'X'

• inplace_mode = 0

• inplace_resize_documents = ' '

• inplace_scroll_documents = ' '

• inplace_show_toolbars = 'X'

• no_flush = ' '

• parent_id = cl_gui_cfw=>dynpro_0

r3_application_name = 'EXCEL CONTAINER'

• register_on_close_event = ' '

• register_on_custom_event = ' '

• rep_id = SY-REPID

• shell_style = 1384185856

parent = oref_container

• name =

• autoalign = 'x'

IMPORTING

error = iref_error

• retcode =

EXCEPTIONS

javabeannotsupported = 1

OTHERS = 2

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'E'.

ENDIF.

CALL METHOD iref_control->get_document_proxy

EXPORTING

• document_format = 'NATIVE'

document_type = soi_doctype_excel_sheet

• no_flush = ' '

• register_container = ' '

IMPORTING

document_proxy = iref_document

error = iref_error

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'E'.

ENDIF.

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

• To provide F4 help for the 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

• no_flush = ' '

open_inplace = 'X'

• open_readonly = ' '

• protect_document = ' '

• onsave_macro = ' '

• startup_macro = ''

• user_info =

IMPORTING

error = iref_error

• retcode =

.

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

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

CALL METHOD iref_spreadsheet->get_sheets

EXPORTING

no_flush = ' '

• updating = -1

IMPORTING

sheets = i_sheets

error = iref_error

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

LOOP AT i_sheets INTO wa_sheets.

CALL METHOD iref_spreadsheet->select_sheet

EXPORTING

name = wa_sheets-sheet_name

• no_flush = ' '

IMPORTING

error = iref_error

• retcode =

.

IF iref_error->has_failed = 'X'.

EXIT.

• call method iref_error->raise_message

• exporting

• type = 'E'.

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.

• call method iref_error->raise_message

• exporting

• type = 'E'.

ENDIF.

REFRESH i_data.

CALL METHOD iref_spreadsheet->get_ranges_data

EXPORTING

• no_flush = ' '

all = 'X'

• updating = -1

• rangesdef =

IMPORTING

contents = i_data

error = iref_error

• retcode =

CHANGING

ranges = i_ranges

.

DELETE i_data WHERE value IS INITIAL OR value = space.

ULINE.

WRITE:/1 wa_sheets-sheet_name COLOR 3.

ULINE.

LOOP AT i_data INTO wa_data.

WRITE:(50) wa_data-value.

AT END OF row.

NEW-LINE.

ENDAT.

ENDLOOP.

ENDLOOP.

CALL METHOD iref_document->close_document

• EXPORTING

• do_save = ' '

• no_flush = ' '

IMPORTING

error = iref_error

• has_changed =

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

CALL METHOD iref_document->release_document

• EXPORTING

• no_flush = ' '

IMPORTING

error = iref_error

• retcode =

.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

&----

---

*& 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.

READ TABLE l_i_files INDEX 1 INTO l_wa_files.

IF sy-subrc = 0.

p_file = l_wa_files-filename.

ELSE.

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

ENDIF.

ENDFORM. " SUB_FILE_F4

Hope this helps..

[former_member253880]

Solved, receiving XML instead of XLS