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: 

READING DATA FORM MULTIPLE WORKSHEET OF A EXCEL FILE.

Former Member
0 Kudos
4,083

HI ALL,

i have a excel file . i have data into worksheets of that excel file .

how i read the data from 2nd worksheet third worksheet and so on.

please help me.

thank you.

2 REPLIES 2

Former Member
0 Kudos
243

hI ALL PLZ HELP ME IN THIS QUERY .

I WANT TO PUT THE DATA INTO DIFF INTERNAL TABLES .

ALSM_EXCEL_TO_INTERNAL_TABLE. WILL NOT WORK BCOZ IT ONLY READ ONLY ONE WORKSHEET

former_member189059
Active Contributor
0 Kudos
243

Hi

use this code

you will have to enter the range to be copied in each sheet

&----


*& Report ZKRIS_EXCELUPLOAD_2SHEETS

*&

&----


*&

*&

&----


REPORT zkris_excelupload_2sheets.

  • value of excel-cell

TYPES: ty_d_itabvalue TYPE alsmex_tabline-value,

  • internal table containing the excel data

ty_t_itab TYPE alsmex_tabline OCCURS 0,

  • line type of sender table

BEGIN OF ty_s_senderline,

line(4096) TYPE c,

END OF ty_s_senderline,

  • sender table

ty_t_sender TYPE ty_s_senderline OCCURS 0.

*

CONSTANTS: gc_esc VALUE '"'.

INCLUDE lalsmexf01.

TYPE-POOLS ole2.

START-OF-SELECTION.

PARAMETERS: filename LIKE rlgrap-filename.

PARAMETERS: st_rw_s1 TYPE i.

PARAMETERS: st_cl_s1 TYPE i.

PARAMETERS: st_rw_s2 TYPE i.

PARAMETERS: st_cl_s2 TYPE i.

PARAMETERS: ed_rw_s1 TYPE i.

PARAMETERS: ed_cl_s1 TYPE i.

PARAMETERS: ed_rw_s2 TYPE i.

PARAMETERS: ed_cl_s2 TYPE i.

DATA: it_data1 TYPE ty_t_itab.

DATA: it_data2 TYPE ty_t_itab.

DATA: it_data_wa LIKE LINE OF it_data1.

  • DATA DECLARATION

DATA: excel_tab TYPE ty_t_sender,

excel_tab1 TYPE ty_t_sender.

DATA: ld_separator TYPE c.

DATA: application TYPE ole2_object,

workbook TYPE ole2_object,

sheet TYPE ole2_object,

range TYPE ole2_object,

worksheet TYPE ole2_object.

DATA: h_cell TYPE ole2_object,

h_cell1 TYPE ole2_object.

DATA: ld_rc TYPE i.

  • MESSAGE DEFINATION

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.

  • PARAMETER CHECK

IF st_rw_s1 > ed_rw_s1.

RAISE inconsistent_parameters.

ENDIF.

IF st_cl_s1 > ed_cl_s1.

RAISE inconsistent_parameters.

ENDIF.

IF st_rw_s2 > ed_rw_s2.

RAISE inconsistent_parameters.

ENDIF.

IF st_cl_s2 > ed_cl_s2.

RAISE inconsistent_parameters.

ENDIF.

CLASS cl_abap_char_utilities DEFINITION LOAD.

ld_separator = cl_abap_char_utilities=>horizontal_tab.

  • OPENING EXCEL FILE

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 application 'Workbooks' = workbook.

m_message.

CALL METHOD OF workbook 'Open'

EXPORTING

#1 = filename.

m_message.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 1.

m_message.

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 1.

m_message.

CALL METHOD OF sheet 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

  • MARKING OF WHOLE SPREADSHEET

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING

#1 = st_rw_s1

#2 = st_cl_s1.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING

#1 = ed_rw_s1

#2 = ed_cl_s1.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING

#1 = h_cell

#2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • Copy marked area (SHEET1) 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 it_data1

USING ld_separator.

  • Clear the 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

.

  • Working in Second Excel Work Sheet

CALL METHOD OF application 'Worksheets' = sheet

EXPORTING

#1 = 2.

m_message.

CALL METHOD OF sheet 'Activate'.

m_message.

GET PROPERTY OF application 'ACTIVESHEET' = sheet.

m_message.

  • Mark Sheet2

CALL METHOD OF sheet 'Cells' = h_cell

EXPORTING

#1 = st_rw_s2

#2 = st_cl_s2.

m_message.

CALL METHOD OF sheet 'Cells' = h_cell1

EXPORTING

#1 = ed_rw_s2

#2 = ed_cl_s2.

m_message.

CALL METHOD OF sheet 'RANGE' = range

EXPORTING

#1 = h_cell

#2 = h_cell1.

m_message.

CALL METHOD OF range 'SELECT'.

m_message.

  • Copy Marked Area (Sheet2) into Clippboard

CALL METHOD OF range 'COPY'.

m_message.

  • Read Clipboard into ABAP

CALL METHOD cl_gui_frontend_services=>clipboard_import

IMPORTING

data = excel_tab1

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_tab1 it_data2

USING ld_separator.

  • Clear Clipboard

REFRESH excel_tab.

CALL METHOD cl_gui_frontend_services=>clipboard_export

IMPORTING

data = excel_tab1

CHANGING

rc = ld_rc

EXCEPTIONS

cntl_error = 1

  • ERROR_NO_GUI = 2

  • NOT_SUPPORTED_BY_GUI = 3

OTHERS = 4

  • Leaving Application

.

CALL METHOD OF application 'QUIT'.

m_message.

FREE OBJECT application.

m_message.

LOOP AT it_data1 INTO it_data_wa.

WRITE:/ it_data_wa.

ENDLOOP.

SKIP 3.

LOOP AT it_data2 INTO it_data_wa.

WRITE:/ it_data_wa.

ENDLOOP.