04-16-2008 9:18 PM
hi i am in the process of learning abap and i wanted to know how data from an excel sheet be displayed in a report. can anybody please help me with this.
04-16-2008 9:22 PM
04-16-2008 9:22 PM
04-17-2008 6:11 AM
Hi ,
Please some more points on your question .
There several function module ,by using that u can get excel data into innternal table and you can display or map to satndard transaction(bdc) etc.
The function modules used to read local files:
Text_convert_xls_to_sap for Xls files
Alsm_excel_to_internal_table
The function modules to create local files :
Sap_convert_to_xls_format
reward if useful
Thanks
Jagadeesh.G
04-17-2008 7:15 AM
but the excel sheet first row has to a definition of the coloum.if not it will leave the first record.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' " to upload from excel
EXPORTING
I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = zfilenam
TABLES
i_tab_converted_data = it_datatab[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
reward me if useful .
don't forget.
regards
vinoth.v
04-17-2008 7:18 AM
but the excel sheet first row has to a definition of the coloum.if not it will leave the first record.
use this code u will get it in the internal table.
TYPE-POOLS: truxs.
DATA: it_raw TYPE truxs_t_text_data.
DATA: IT_ERROR LIKE BDCMSGCOLL OCCURS 0 WITH HEADER LINE.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP' " to upload from excel
EXPORTING
I_FIELD_SEPERATOR =
i_line_header = 'X'
i_tab_raw_data = it_raw " WORK TABLE
i_filename = zfilenam
TABLES
i_tab_converted_data = it_datatab[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
reward me if useful .
don't forget.
regards
vinoth.v
04-17-2008 7:27 AM
Hi,
TEXT_CONVERT_XLS_TO_SAP
Using this Function modulw we convert the Excel data into a internal table, This is a normal Excel data upload Function module..
See the below thread for a Example program...
http://www.sapdevelopment.co.uk/file/file_upexcel.htm
Alsm_excel_to_internal_table
This function module uploads data from excel to internal table,
check this sample code
TYPE-POOLS truxs.
TABLES:zmatnr.
DATA : itab LIKE alsmex_tabline OCCURS 0 WITH HEADER LINE.
DATA row LIKE alsmex_tabline-row.
data : g_matnr like mara-matnr.
data : count type i.
data : itab_count type i.
data : gi_final like zmatnr occurs 0 with header line.
*data : begin of gi_final occurs 0,
mat_old like mara-matnr,
mat_new like mara-matnr,
end of gi_final.
***********************Selection Screen*************************
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
PARAMETER : pfname LIKE rlgrap-filename OBLIGATORY.
select-options : records for count.
SELECTION-SCREEN END OF BLOCK b1.
*******************************************************************
*********************At Selection Screen*************************
AT SELECTION-SCREEN ON VALUE-REQUEST FOR pfname.
PERFORM search.
*******************************************************************
START-OF-SELECTION.
perform process.
form process.
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = pfname
i_begin_col = 1
i_begin_row = 2
i_end_col = 12
i_end_row = 65000
TABLES
intern = itab
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.
describe table itab lines itab_count.
row = 1.
loop at itab.
if itab-row row.
append gi_final.
clear gi_final.
endif.
case itab-col.
when '1'.
CLEAR G_MATNR.
gi_final-OLD_MATNR = itab-value.
CONCATENATE 'NEW' gi_final-old_matnr INTO itab-value.
gi_final-new_MATNR = itab-value.
endcase.
row = itab-row.
endloop.
append gi_final.
clear gi_final.
CALL FUNCTION 'PROGRESS_INDICATOR'
EXPORTING
I_TEXT = 'File Has Been Successfully Uploaded from Workstation ' .
if not gi_final[] is initial.
if not records-low is initial .
if not records-high is initial.
records-high = records-high + 1.
DESCRIBE TABLE gi_final LINES count.
IF records-high < count.
DELETE gi_final FROM records-high TO count.
ENDIF.
IF records-low <> 1.
IF records-low 0.
DELETE gi_final FROM 1 TO records-low.
ENDIF.
ENDIF.
endif.
endif.
endif.
IF NOT GI_FINAL[] IS INITIAL.
CALL FUNCTION 'PROGRESS_INDICATOR'
EXPORTING
I_TEXT = 'Processing zmatnr table'
I_OUTPUT_IMMEDIATELY = 'X'.
if itab_count count.
*
message i000 with 'records are not matching'.
*
exit.
*
else.
modify zmatnr from table gi_final.
message i000 with 'data base table modified successfully'.
endif.
endif.
endform.
&----
*& Form search
&----
text
-
--> p1 text
<-- p2 text
-
FORM search .
CALL FUNCTION 'KD_GET_FILENAME_ON_F4'
EXPORTING
static = 'X'
CHANGING
file_name = pfname.
ENDFORM. " search
Sap_convert_to_xls_format
Download internal table data to Excel sheet.
call function 'SAP_CONVERT_TO_XLS_FORMAT'
exporting
i_field_seperator = '#' "Field seprator in internal table
i_line_header = 'X'
i_filename = 'C:\kk.xls'
I_APPL_KEEP = ' '
tables
i_tab_sap_data = gt_itab "Internal table data
CHANGING
I_TAB_CONVERTED_DATA =
exceptions
conversion_failed = 1
others = 2
.
Regards,
Raj.
04-17-2008 8:03 AM
Hi Savitha,
We can use the Function module TEXT_CONVERT_XLS_TO_SAP to read the Excel file into the internal table. From this internal table you can fill the target internal table.
I hope that it helps u .
Regards,
Venkat.O
report zvenkat-upload-xl no standard page heading.
"----------------------------------------------------------------------
"Declarations.
"----------------------------------------------------------------------
"types
types:
begin of t_bank_det,
pernr(8) type c,
bnksa(4) type c,
zlsch(1) type c,
bkplz(10) type c,
bkort(25) type c,
bankn(18) type c,
end of t_bank_det.
"work areas
data:
w_bank_det type t_bank_det.
"internal tables
data:
i_bank_det type table of t_bank_det.
"---------------------------------------------------------------------
" selection-screen
"----------------------------------------------------------------------
selection-screen begin of block b1 with frame title text_001.
parameters p_file type localfile.
selection-screen end of block b1.
*---------------------------------------------------------------------
"At selection-screen on value-request for p_file.
*---------------------------------------------------------------------
at selection-screen on value-request for p_file.
perform f4_help.
*---------------------------------------------------------------------
"Start-of-selection.
*---------------------------------------------------------------------
start-of-selection.
perform upload_data.
*---------------------------------------------------------------------
"End-of-selection.
*---------------------------------------------------------------------
end-of-selection.
perform display_data.
*&---------------------------------------------------------------------*
"Form f4_help
*&---------------------------------------------------------------------*
form f4_help .
data:
l_file_name like ibipparms-path .
call function 'F4_FILENAME'
exporting
program_name = syst-cprog
dynpro_number = syst-dynnr
field_name = 'P_FILE'
importing
file_name = l_file_name.
p_file = l_file_name.
endform. " f4_help
*---------------------------------------------------------------------*
"Form upload_data
*---------------------------------------------------------------------*
form upload_data .
type-pools:truxs.
data:li_tab_raw_data type truxs_t_text_data.
data:l_filename like rlgrap-filename.
l_filename = p_file.
call function 'TEXT_CONVERT_XLS_TO_SAP'
exporting
i_tab_raw_data = li_tab_raw_data
i_filename = l_filename
tables
i_tab_converted_data = i_bank_det
exceptions
conversion_failed = 1
others = 2.
if sy-subrc <> 0.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
endif.
endform. " upload_data
*---------------------------------------------------------------------*
" Form display_data
*---------------------------------------------------------------------*
form display_data .
data: char100 type char100.
loop at i_bank_det into w_bank_det .
if sy-tabix = 1.
write w_bank_det.
write / '------------------------------------------------------------'.
else.
write / w_bank_det.
endif.
endloop.
endform. " display_data
04-18-2008 5:17 AM
Hi!!
Try this code...
it works..n let me know if u face any difficulty!
This code uploads excel file from presentation layer(ur local PC)
and u can see the output in the form of report.
TYPES:BEGIN OF ty_itab ,
objecttype TYPE string,
objectname TYPE string,
END OF ty_itab.
DATA:
count TYPE i,
file TYPE ibipparms-path,
iexcel TYPE TABLE OF alsmex_tabline ,
wa_itab TYPE ty_itab,
itab type standard table of ty_itab,
wa_iexcel TYPE alsmex_tabline.
field-symbols : <fs> type string.
*Calling function module to get file path from PC
CALL FUNCTION 'F4_FILENAME'
EXPORTING
program_name = syst-cprog
dynpro_number = syst-dynnr
IMPORTING
file_name = file.
*Calling function module to upload excel sheet
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = file
i_begin_col = 1
i_begin_row = 1
i_end_col = 2
i_end_row = 65000
TABLES
intern = iexcel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
WRITE :/15 'FILE NOT UPLOADED. INVALID FILE NAME OR PATH.' COLOR 6 .
EXIT.
ENDIF.
LOOP AT iexcel INTO wa_iexcel.
count = count + 1.
ASSIGN COMPONENT count OF STRUCTURE wa_itab TO <fs>.
<fs> = wa_iexcel-value.
AT END OF row.
CLEAR count.
APPEND wa_itab TO itab.
ENDAT.
CLEAR wa_iexcel.
ENDLOOP.
loop at itab into wa_itab.
Write : / wa_itab-objectname ,wa_itab-objecttype.
endloop.
Rewards points..if useful.