08-01-2007 3:22 PM
how can i declare the second tab sheet (work sheet) in FM ALSM_EXCEL_TO_INTERNAL_TABLE???
08-01-2007 3:30 PM
Hi Antonis,
Pls check the below sample coding
*&---------------------------------------------------------------------*
*& Report UPLOAD_EXCEL *
*& *
*&---------------------------------------------------------------------*
*& *
*& Upload and excel file into an internal table using the following *
*& function module: ALSM_EXCEL_TO_INTERNAL_TABLE *
*&---------------------------------------------------------------------*
REPORT UPLOAD_EXCEL no standard page heading.
*Data Declaration
*----------------
data: itab like alsmex_tabline occurs 0 with header line.
* Has the following format:
* Row number | Colum Number | Value
* ---------------------------------------
* i.e. 1 1 Name1
* 2 1 Joe
TYPES: Begin of t_record,
name1 like itab-value,
name2 like itab-value,
age like itab-value,
End of t_record.
DATA: it_record type standard table of t_record initial size 0,
wa_record type t_record.
DATA: gd_currentrow type i.
*Selection Screen Declaration
*----------------------------
PARAMETER p_infile like rlgrap-filename.
************************************************************************
*START OF SELECTION
call function 'ALSM_EXCEL_TO_INTERNAL_TABLE'
exporting
filename = p_infile
i_begin_col = '1'
i_begin_row = '2' "Do not require headings
i_end_col = '14'
i_end_row = '31'
tables
intern = itab
exceptions
inconsistent_parameters = 1
upload_ole = 2
others = 3.
if sy-subrc <> 0.
message e010(zz) with text-001. "Problem uploading Excel Spreadsheet
endif.
* Sort table by rows and colums
sort itab by row col.
* Get first row retrieved
read table itab index 1.
* Set first row retrieved to current row
gd_currentrow = itab-row.
loop at itab.
* Reset values for next row
if itab-row ne gd_currentrow.
append wa_record to it_record.
clear wa_record.
gd_currentrow = itab-row.
endif.
case itab-col.
when '0001'. "First name
wa_record-name1 = itab-value.
when '0002'. "Surname
wa_record-name2 = itab-value.
when '0003'. "Age
wa_record-age = itab-value.
endcase.
endloop.
append wa_record to it_record.
*!! Excel data is now contained within the internal table IT_RECORD
* Display report data for illustration purposes
loop at it_record into wa_record.
write:/ sy-vline,
(10) wa_record-name1, sy-vline,
(10) wa_record-name2, sy-vline,
(10) wa_record-age, sy-vline.
endloop.
<b>Reward pts if found usefull :)</b>
regards
Sathish
08-01-2007 3:37 PM
Hi ,
you can read data from excel to internal table using FM: ALSM_EXCEL_TO_INTERNAL_TABLE
check this example:
REPORT ZSR_BDC_XL
NO STANDARD PAGE HEADING LINE-SIZE 255.
TABLES : LFA1,RF02K.
DATA : BEGIN OF ITAB OCCURS 0,
LIFNR LIKE RF02K-LIFNR,
KTOKK LIKE RF02K-KTOKK,
NAME1 LIKE LFA1-NAME1,
SORTL LIKE LFA1-SORTL,
LAND1 LIKE LFA1-LAND1,
SPRAS LIKE LFA1-SPRAS,
END OF ITAB.
DATA : ITAB1 LIKE ALSMEX_TABLINE OCCURS 0 WITH HEADER LINE.
DATA : B1 TYPE I VALUE 1,
C1 TYPE I VALUE 1,
B2 TYPE I VALUE 10,
C2 TYPE I VALUE 99.
START-OF-SELECTION.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = 'C:\Documents and Settings\sudhapa\Desktop\xl.XLS'
I_BEGIN_COL = B1
I_BEGIN_ROW = C1
I_END_COL = B2
I_END_ROW = C2
TABLES
INTERN = ITAB1
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
PERFORM ORGANIZE_UPLOADED_DATA.
FORM ORGANIZE_UPLOADED_DATA .
SORT ITAB1 BY ROW
COL.
LOOP AT ITAB1.
CASE ITAB1-COL.
....................................................
WHEN 1.
ITAB-LIFNR = ITAB1-VALUE.
WHEN 2.
ITAB-KTOKK = ITAB1-VALUE.
WHEN 3.
ITAB-NAME1 = ITAB1-VALUE.
WHEN 4.
ITAB-SORTL = ITAB1-VALUE.
WHEN 5.
ITAB-LAND1 = ITAB1-VALUE.
WHEN 6.
ITAB-SPRAS = ITAB1-VALUE.
....................................................
ENDCASE.
AT END OF ROW.
APPEND ITAB.
CLEAR ITAB.
ENDAT.
ENDLOOP.
ENDFORM. " ORGANIZE_UPLOADED_DATA
<b> Reward if found useful... </b>
08-01-2007 3:42 PM
Check the below program :
REPORT ZLWMI151_UPLOAD no standard page heading
line-size 100 line-count 60.
*tables : zbatch_cross_ref.
data : begin of t_text occurs 0,
werks(4) type c,
cmatnr(15) type c,
srlno(12) type n,
matnr(7) type n,
charg(10) type n,
end of t_text.
data: begin of t_zbatch occurs 0,
werks like zbatch_cross_ref-werks,
cmatnr like zbatch_cross_ref-cmatnr,
srlno like zbatch_cross_ref-srlno,
matnr like zbatch_cross_ref-matnr,
charg like zbatch_cross_ref-charg,
end of t_zbatch.
data : g_repid like sy-repid,
g_line like sy-index,
g_line1 like sy-index,
$v_start_col type i value '1',
$v_start_row type i value '2',
$v_end_col type i value '256',
$v_end_row type i value '65536',
gd_currentrow type i.
data: itab like alsmex_tabline occurs 0 with header line.
data : t_final like zbatch_cross_ref occurs 0 with header line.
selection-screen : begin of block blk with frame title text.
parameters : p_file like rlgrap-filename obligatory.
selection-screen : end of block blk.
initialization.
g_repid = sy-repid.
at selection-screen on value-request for p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
PROGRAM_NAME = g_repid
IMPORTING
FILE_NAME = p_file.
start-of-selection.
Uploading the data into Internal Table
perform upload_data.
perform modify_table.
top-of-page.
CALL FUNCTION 'Z_HEADER'
EXPORTING
FLEX_TEXT1 =
FLEX_TEXT2 =
FLEX_TEXT3 =
.
&----
*& Form upload_data
&----
text
----
FORM upload_data.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
FILENAME = p_file
I_BEGIN_COL = $v_start_col
I_BEGIN_ROW = $v_start_row
I_END_COL = $v_end_col
I_END_ROW = $v_end_row
TABLES
INTERN = itab
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
IF SY-SUBRC <> 0.
write:/10 'File '.
ENDIF.
if sy-subrc eq 0.
read table itab index 1.
gd_currentrow = itab-row.
loop at itab.
if itab-row ne gd_currentrow.
append t_text.
clear t_text.
gd_currentrow = itab-row.
endif.
case itab-col.
when '0001'.
t_text-werks = itab-value.
when '0002'.
t_text-cmatnr = itab-value.
when '0003'.
t_text-srlno = itab-value.
when '0004'.
t_text-matnr = itab-value.
when '0005'.
t_text-charg = itab-value.
endcase.
endloop.
endif.
append t_text.
ENDFORM. " upload_data
&----
*& Form modify_table
&----
Modify the table ZBATCH_CROSS_REF
----
FORM modify_table.
loop at t_text.
t_final-werks = t_text-werks.
t_final-cmatnr = t_text-cmatnr.
t_final-srlno = t_text-srlno.
t_final-matnr = t_text-matnr.
t_final-charg = t_text-charg.
t_final-erdat = sy-datum.
t_final-erzet = sy-uzeit.
t_final-ernam = sy-uname.
t_final-rstat = 'U'.
append t_final.
clear t_final.
endloop.
delete t_final where werks = ''.
describe table t_final lines g_line.
sort t_final by werks cmatnr srlno.
Deleting the Duplicate Records
perform select_data.
describe table t_final lines g_line1.
modify zbatch_cross_ref from table t_final.
if sy-subrc ne 0.
write:/ 'Updation failed'.
else.
Skip 1.
Write:/12 'Updation has been Completed Sucessfully'.
skip 1.
Write:/12 'Records in file ',42 g_line .
write:/12 'Updated records in Table',42 g_line1.
endif.
delete from zbatch_cross_ref where werks = ''.
ENDFORM. " modify_table
&----
*& Form select_data
&----
Deleting the duplicate records
----
FORM select_data.
select werks
cmatnr
srlno from zbatch_cross_ref
into table t_zbatch for all entries in t_final
where werks = t_final-werks
and cmatnr = t_final-cmatnr
and srlno = t_final-srlno.
sort t_zbatch by werks cmatnr srlno.
loop at t_zbatch.
read table t_final with key werks = t_zbatch-werks
cmatnr = t_zbatch-cmatnr
srlno = t_zbatch-srlno.
if sy-subrc eq 0.
delete table t_final .
endif.
clear: t_zbatch,
t_final.
endloop.
ENDFORM. " select_data
Thanks
Seshu
08-01-2007 3:42 PM
08-01-2007 3:53 PM
it is not possible if you use ALSM_EXCEL_TO_INTERNAL_TABLE
Check with below links :
http://diocio.wordpress.com/2007/02/12/sap-multiple-sheet-excel-document/
Thanks
Seshu