01-28-2009 7:07 PM
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
01-28-2009 7:11 PM
01-29-2009 10:41 AM
01-29-2009 10:48 AM
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..
01-29-2009 11:20 AM
01-29-2009 3:24 PM
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.
02-27-2009 5:57 PM
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