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: 

Names of the Sheets in Excel files

marcelom_bovo
Participant
0 Kudos

Hi people,

I´d like to know if ther is a function that read the name of the sheets in a excel file.

Because I have to do a validation with the name.

Tks

6 REPLIES 6

amit_khare
Active Contributor
0 Kudos

May be [this|; help you.

marcelom_bovo
Participant
0 Kudos

Sorry, but I didnt get any help with this

Former Member
0 Kudos

Chk this out...

&----


*& Report YMULTIXL *

*& *

&----


*& *

*& *

&----


REPORT YMULTIXL .

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 Member
0 Kudos

Hi,

Check the below Thread,

Regards,

Anki Reddy

marcelom_bovo
Participant
0 Kudos

Karan this program doesnt work, its fulled of bugs

I´m tring to use OLE Objects, but it isnt working yet.

Look what im trying:

INCLUDE ole2incl.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

teste(80),

ws TYPE ole2_object,

cells TYPE ole2_object.

CONSTANTS: row_max TYPE i VALUE 256.

DATA index TYPE i.

DATA: BEGIN OF itab1 OCCURS 0, first_name(10), END OF itab1.

DATA: BEGIN OF itab2 OCCURS 0, last_name(10), END OF itab2.

DATA: BEGIN OF itab3 OCCURS 0, formula(50), END OF itab3.

************************************************************************

START-OF-SELECTION.

CREATE OBJECT application 'excel.application'.

SET PROPERTY OF application 'visible' = 1.

CALL METHOD OF application 'Workbooks' = workbook.

CALL METHOD OF workbook 'Open'

EXPORTING #1 = 'c:EM_0488ContratosME2008 error2.xls'."'c:EM 0436.xls'. "filename

CALL METHOD OF application 'sheets' = sheet

EXPORTING #1 = 1.

GET PROPERTY OF sheet 'Name' = teste.

marcelom_bovo
Participant
0 Kudos

Hi people,

I got it, I used OLE objects, here is what I did:

INCLUDE ole2incl.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

worksheet TYPE ole2_object,

gs_activesheet TYPE ole2_object.

  • Makro für Fehlerbehandlung der Methods

DEFINE m_message.

case sy-subrc.

when 0.

when 1.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

when others. raise upload_ole.

endcase.

END-OF-DEFINITION.

  • check parameters

  • open file in Excel

CREATE OBJECT application 'Excel.Application'.

m_message.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open' EXPORTING #1 = 'c:EM 0436.xls'.

m_message.

CALL METHOD OF application 'WorkSheets' = gs_activesheet

EXPORTING

#1 = 5.

CALL METHOD OF gs_activesheet 'Activate'.

DATA: TESTE(250).

" Verifica si la hoja deseada existe

IF sy-subrc NE 0.

CALL METHOD OF application 'QUIT'.

m_message.

RAISE not_found.

ENDIF.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

GET PROPERTY OF worksheet 'NAME' = TESTE.

CALL METHOD OF application 'QUIT'.

WRITE teste.

FREE OBJECT worksheet. m_message.

FREE OBJECT workbook. m_message.

FREE OBJECT application. m_message.

THANKS