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: 

Read Excel 2007 multi sheets file

Former Member
0 Kudos

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

4 REPLIES 4

0 Kudos

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
Active Contributor
0 Kudos

Hi Salvatore Bruno,

The code doesn't work properly at the command

What doesn't work properly??

Thanks.

Sandra

0 Kudos

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

0 Kudos

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