One Excel , Multiple Worksheets

[Former Member]

Hello,

I have a requirement to read the excelsheet having multiple worksheets.

The excel will have sheet1, sheet2, sheet3 etc with data and I have to read all of them.

I searched a lot on SDN and fould the below code which is reading only the Active Sheet. Not all the sheets.

Any idea how this could be achieved?

I am pasting the code for your reference whici reads teh Active sheet.

=======================

&----

---

*& Report ZSCD_TEST16

*&

&----

---

*&

*&

&----

---

REPORT ZSCD_TEST16.

• Define Screen Container

DATA: obj_container TYPE REF TO cl_gui_custom_container.

DATA: o_error TYPE REF TO i_oi_error,

o_control TYPE REF TO i_oi_container_control,

o_document TYPE REF TO i_oi_document_proxy,

o_spreadsheet TYPE REF TO i_oi_spreadsheet,

o_sheetname TYPE SOI_SHEETS_TABLE.

• Data declarations.

DATA: t_files TYPE filetable,

s_files TYPE file_table,

v_doc_name TYPE char256,

v_changed TYPE int4,

v_rcode TYPE int4,

t_ranges TYPE soi_range_list,

s_ranges TYPE soi_range_item,

t_data TYPE soi_generic_table,

s_data TYPE soi_generic_item,

v_action TYPE int4.

• Initialization event.

INITIALIZATION.

CLASS c_oi_errors DEFINITION LOAD.

• Create Instance control for container

CALL METHOD c_oi_container_control_creator=>get_container_control

IMPORTING control = o_control

error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Create generic container linked to container in screen 100

CREATE OBJECT obj_container

EXPORTING

container_name = 'CONTAINER'

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 e208(00) WITH 'Error creating container'.

ENDIF.

• Establish connection to GUI Control

CALL METHOD o_control->init_control

EXPORTING r3_application_name = 'Excel Document Container'

inplace_enabled = 'X'

parent = obj_container

IMPORTING error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Create Document Proxy

CALL METHOD o_control->get_document_proxy

EXPORTING document_type = soi_doctype_excel_sheet

IMPORTING document_proxy = o_document

error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Start-of-selection.

START-OF-SELECTION.

• Call dialog to navigate to file

CALL METHOD cl_gui_frontend_services=>file_open_dialog

EXPORTING

default_extension = '.xls'

file_filter = '*.xls'

initial_directory = 'C:\'

CHANGING

file_table = t_files

rc = v_rcode

user_action = v_action

EXCEPTIONS

file_open_dialog_failed = 1

cntl_error = 2

error_no_gui = 3

OTHERS = 4.

IF sy-subrc <> 0.

MESSAGE e208(00) WITH 'FILE_OPEN_DIALOG'.

ENDIF.

• Only continue if User hasn't cancelled

CHECK: v_action = 0.

• Determine filename to open Excel document

READ TABLE t_files INDEX 1 INTO s_files.

IF sy-subrc = 0.

CONCATENATE 'FILE://' s_files-filename INTO v_doc_name.

ELSE.

MESSAGE e208(00).

ENDIF. "sy-subrc = 0

• Open Spreadsheet in SAPWORKDIR

CALL METHOD o_document->open_document

EXPORTING open_inplace = 'X'

document_title = 'Excel'

document_url = v_doc_name

no_flush = ''

IMPORTING error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Open Spreadsheet interface

CALL METHOD o_document->get_spreadsheet_interface

EXPORTING no_flush = 'X'

IMPORTING sheet_interface = o_spreadsheet

error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Set selection for 1000 rows

CALL METHOD o_spreadsheet->set_selection

EXPORTING top = 1

left = 1

rows = '10'

columns = '10'

UPDATING = '-1'.

• Define Range in spreadsheet

CALL METHOD o_spreadsheet->insert_range

EXPORTING name = 'Sheet2'

rows = '10'

columns = '20'

no_flush = ''

IMPORTING error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Define Range in spreadsheet

CALL METHOD o_spreadsheet->GET_SHEETS

EXPORTING no_flush = ''

UPDATING = '-1'

IMPORTING SHEETS = o_sheetname.

s_ranges-name = 'Sheet2'.

s_ranges-rows = '10'.

s_ranges-columns = '20'.

APPEND s_ranges TO t_ranges.

• Get data

CALL METHOD o_spreadsheet->get_ranges_data

EXPORTING all = '-1'

no_flush = ' '

IMPORTING contents = t_data

error = o_error

CHANGING ranges = t_ranges.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Close document

• Close the document

CALL METHOD o_document->close_document

EXPORTING do_save = ''

no_flush = ''

IMPORTING has_changed = v_changed

error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Clear Document Resources

CALL METHOD o_document->release_document

EXPORTING no_flush = ''

IMPORTING error = o_error.

IF o_error->has_failed = 'X'.

CALL METHOD o_error->raise_message

EXPORTING type = 'E'.

ENDIF.

• Clear table of file names

FREE: t_files,

o_control.

• Display the data

LOOP AT t_data INTO s_data.

AT NEW row.

SKIP.

ENDAT.

WRITE:(10) s_data-value.

ENDLOOP.

========================

Thanks in advance.

reagrds,

[former_member585060]

Try this Links

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/snippets/abap-UploaddatafromExceltoSapusing+OO

[Former Member]

Hello,

Thanks for the links.

The program that I have created is from one of the link youhave provided.

And other examples are related to exporting data to excel sheet.

My requirement is to read data from excel sheet.

Anybody any idea?

Thanks in advance.

Pranu

[Former Member]

Hi Pranu,

         Could you please let me know how you were able to solve this issue? My issue with the class-method (i_oi_spreadsheet=>set_selection & insert_range) requires for us to manually enter the start & the end row. But what if we do NOT know how many rows or columns the excel could contain?

Any ideas on this?

Thanks & Regards,

Divaker