topic excel sheet in Application Development and Automation Discussions

excel sheet

Former Member — Wed, 16 Apr 2008 20:18:55 GMT

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.

Re: excel sheet

Former Member — Wed, 16 Apr 2008 20:22:59 GMT

Hi,

Please go though the following link where you will get the answer.

[;

Reward points if helpful.

Thanks and Regards

Re: excel sheet

Former Member — Thu, 17 Apr 2008 05:11:29 GMT

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

Re: excel sheet

Former Member — Thu, 17 Apr 2008 06:15:20 GMT

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

Re: excel sheet

Former Member — Thu, 17 Apr 2008 06:18:08 GMT

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

Re: excel sheet

Former Member — Thu, 17 Apr 2008 06:27:43 GMT

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.

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.

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.

Re: excel sheet

venkat_o — Thu, 17 Apr 2008 07:03:51 GMT

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.


  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

I hope that it helps u . Regards, Venkat.O

Re: excel sheet

Former Member — Fri, 18 Apr 2008 04:17:26 GMT

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.