Read Excel 2007 multi sheets file

[Former Member]

Hi guys,

I'm trying the code found here

http://wiki.sdn.sap.com/wiki/display/Snippets/ReadmultiplesheetsofanExcelfileintoSAPthroughABAP

Mi aim is to upload an excel 2007 document with multi sheets.

The code doesn't work properly at the command

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

I'm developing in sap netweaver 7.0

Any suggetion???

Many thanks.

Edited by: Salvatore Bruno on Apr 21, 2011 12:38 PM

[Former Member]

Hello Salvatore,

I have tried to do this once. I think it is not possible unless you know the names of the sheets.

I used the FM ALSM_EXCEL_TO_INTERNAL_TABLE and modified it slightly:

FUNCTION Z_ALSM_EXCEL_TO_INTERNAL_TABLE .

*"----

-

---

""Lokale Schnittstelle:

*" IMPORTING

*" VALUE(FILENAME) LIKE RLGRAP-FILENAME

*" VALUE(I_BEGIN_COL) TYPE I

*" VALUE(I_BEGIN_ROW) TYPE I

*" VALUE(I_END_COL) TYPE I

*" VALUE(I_END_ROW) TYPE I

*" REFERENCE(P_SHNAME) TYPE C

*" TABLES

*" INTERN STRUCTURE ALSMEX_TABLINE

*" EXCEPTIONS

*" INCONSISTENT_PARAMETERS

*" UPLOAD_OLE

*"----

-

---

DATA: excel_tab TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object,

sheet type ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA:

ld_rc TYPE i.

• Rückgabewert der Methode "clipboard_export "

• 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

IF i_begin_row > i_end_row. RAISE inconsistent_parameters. ENDIF.

IF i_begin_col > i_end_col. RAISE inconsistent_parameters. ENDIF.

• Get TAB-sign for separation of fields

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

• open file in Excel

IF application-header = space OR application-handle = -1.

CREATE OBJECT application 'Excel.Application'.

m_message.

ENDIF.

CALL METHOD OF application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open' EXPORTING #1 = filename.

m_message.

CALL METHOD OF application 'WORKSHEETS' = sheet

EXPORTING

#1 = p_shname.

CALL METHOD OF sheet 'ACTIVATE'.

FREE OBJECT sheet.

• set property of application 'Visible' = 1.

• m_message.

GET PROPERTY OF application 'ACTIVESHEET' = worksheet.

m_message.

• mark whole spread sheet

CALL METHOD OF worksheet 'Cells' = h_cell

EXPORTING #1 = i_begin_row #2 = i_begin_col.

m_message.

CALL METHOD OF worksheet 'Cells' = h_cell1

EXPORTING #1 = i_end_row #2 = i_end_col.

m_message.

CALL METHOD OF worksheet 'RANGE' = range

EXPORTING #1 = h_cell #2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

• copy marked area (whole spread sheet) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

• read clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

IF sy-subrc <> 0.

MESSAGE a037(alsmex).

ENDIF.

PERFORM separated_to_intern_convert TABLES excel_tab intern

USING ld_separator.

• clear clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

• ERROR_NO_GUI = 2

• NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

.

• quit Excel and free ABAP Object - unfortunately, this does not kill

• the Excel process

CALL METHOD OF application 'QUIT'.

m_message.

• >>>>> Begin of change note 575877

• to kill the Excel process it's necessary to free all used objects

FREE OBJECT h_cell. m_message.

FREE OBJECT h_cell1. m_message.

FREE OBJECT range. m_message.

FREE OBJECT worksheet. m_message.

FREE OBJECT workbook. m_message.

FREE OBJECT application. m_message.

• <<<<< End of change note 575877

ENDFUNCTION.

Then I call and pass the name of the sheet.

call function 'Z_ALSM_EXCEL_TO_INTERNAL_TABLE'

exporting

FILENAME = P_FILE

I_BEGIN_COL = BEG_COL1

I_BEGIN_ROW = 4

I_END_COL = BEG_COL1

I_END_ROW = END_ROW

P_SHNAME = SHNAME

tables

INTERN = ITAB

exceptions

INCONSISTENT_PARAMETERS = 1

UPLOAD_OLE = 2

others = 3.

Best Regards

Diana Abu Gosh

[Sandra_Rossi]

Hi Salvatore Bruno,

The code doesn't work properly at the command

What doesn't work properly??

Thanks.

Sandra

[Former Member]

Hi,

so the problem is the excel file... it has some formulas inside in all sheets and I think the problem is that.

Anyway thanks to all for all answers.

Edited by: Salvatore Bruno on Apr 22, 2011 10:05 AM

[Sandra_Rossi]

I still don't understand what kind of trouble you have, it works perfectly, even with formulas (the method gets the calculated values)... If you need a working example, run program SAPRDEMO_SPREADSHEET_INTERFACE, you can interact between ABAP and Excel (and especially test GET_RANGES_DATA), you'll set that you can read the ranges from any sheet

BR

Sandra