Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

One Excel , Multiple Worksheets

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,

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question