Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Data from a particular excel sheet in to an internal table

former_member212002
Active Contributor
0 Kudos

Hello Experts,

I am opening an excel workbook from BDS which contains 12 Sheets. Of the 12 sheets i want to get the data of particular sheet(Say sheet 5) back to the intenal table. Please let me know which methods or classes should i use to acheive this requirement.

FYI : I have used methods like SAVE_DOCUMENT_TO_TABLE.. But am not getting the data as required.

Any kind of help will be appreciated.

Regards

Mishra

1 ACCEPTED SOLUTION

satyajit_mohapatra
Active Contributor
0 Kudos

Use method SELECT_SHEET(Interface I_OI_SPREADSHEET) to select the specific sheet from which want to read the data. Then use method GET_RANGES_DATA (Interface I_OI_SPREADSHEET) to read the data using the range defined in the excel sheet. If no range is created in template create a dyanamic range for the data you want to read and use the same in the method.

You can refer the following link to check the use of the method GET_RANGES_DATA.

http://wiki.sdn.sap.com/wiki/display/ABAP/HandlingDatainExcelIn-placeDisplayUsing+BDS

3 REPLIES 3

Former Member
0 Kudos

Hi,

Check the following program-->

&----


*& Report ZMSSTEST2

*&

&----


REPORT zmsstest2 NO STANDARD PAGE HEADING.

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.

FIELD-SYMBOLS: <fs1>.

DATA: BEGIN OF intern OCCURS 0.

INCLUDE STRUCTURE alsmex_tabline.

DATA: END OF intern.

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.

IF iref_error->has_failed = 'X'.

CALL METHOD iref_error->raise_message

EXPORTING

type = 'E'.

ENDIF.

CREATE OBJECT oref_container

EXPORTING

container_name = 'CONT'

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

inplace_enabled = 'X'

r3_application_name = 'EXCEL CONTAINER'

parent = oref_container

IMPORTING

error = iref_error

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_type = soi_doctype_excel_sheet

IMPORTING

document_proxy = iref_document

error = iref_error.

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

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.

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.

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.

CALL METHOD iref_spreadsheet->get_ranges_data

EXPORTING

all = 'X'

IMPORTING

contents = i_data

error = iref_error

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

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->release_document

IMPORTING

error = iref_error.

IF iref_error->has_failed = 'X'.

CALL METHOD

iref_error->raise_message

EXPORTING

type = 'I'.

LEAVE LIST-PROCESSING.

ENDIF.

&----


*& Form sub_file_f4

&----


  • text

----


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'

file_filter = '.xls'

initial_directory = l_desktop

CHANGING

file_table = l_i_files

rc = l_rcode

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

Regards,

Madhukar Shetty

satyajit_mohapatra
Active Contributor
0 Kudos

Use method SELECT_SHEET(Interface I_OI_SPREADSHEET) to select the specific sheet from which want to read the data. Then use method GET_RANGES_DATA (Interface I_OI_SPREADSHEET) to read the data using the range defined in the excel sheet. If no range is created in template create a dyanamic range for the data you want to read and use the same in the method.

You can refer the following link to check the use of the method GET_RANGES_DATA.

http://wiki.sdn.sap.com/wiki/display/ABAP/HandlingDatainExcelIn-placeDisplayUsing+BDS

0 Kudos

Hi, thanks for this example.

I have a question.

How can I get the dimensions of a sheet?

To avoid having to truncate the number of rows and columns, since how many records will come in each sheet.

I really appreciate your help.