Excel Sheet processing

Former Member — Wed, 11 Jun 2008 11:11:01 GMT

Dear Friends,

I am looking for a Function module that can process multiple worksheets in a Excel Sheet. Can any one help me in this.

Eg: I have a excel order form with several work sheets. first two work sheets describes the description of the order form. And the remaining worksheets contains the order details. I want to get each order details in to internal table.

Re: Excel Sheet processing

Former Member — Wed, 11 Jun 2008 11:19:01 GMT

Uploading multiple multitab Excel sheets or Ranges from Front end to SAP

Please refer to

http://www.sap-img.com/abap/abap-object-oriented-spreadsheet-with-unlimited-power.htm

Visit

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

and

http://help.sap.com/saphelp_47x200/helpdata/en/e9/0be775408e11d1893b0000e8323c4f/frameset.htm

You need some basic idea of range object in excel.

You need to create XLS with named ranges or create ranges dynamically.

This could be a neat way to upload XLS the OO way!

The function Module zjnc_get_range reads 1 range into any Internal

table.

DATA: BEGIN OF it_test OCCURS 0,

vpd LIKE mseg-menge,

vas LIKE mkpf-budat,

vkm LIKE mseg-matnr,

END OF it_test.

CALL FUNCTION 'ZJNC_GET_RANGE'

EXPORTING

rangename = 'test'

itabname = 'IT_TEST[]'

irecname = 'it_test'

spreadsheetintf = spreadsheetintf.

=Work!$A$14:$C$16 is range "test"

Numbers & Character data are no problem BUT dates are.

In Excel default date is mm/dd/yyyy but is dependent on PC's

international setting which is normally default

To Avoid any 5-March 3-May type mix-up, I have designed the FM so that you need to

enter dates as 'dd.Mon.yyyy i.e. in Characters in "Internet Date Format"

FUNCTION zjnc_get_range.

*"----

""Local interface:

*" IMPORTING

*" REFERENCE(RANGENAME) TYPE C

*" REFERENCE(ITABNAME) TYPE C

*" REFERENCE(IRECNAME) TYPE C

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

*" REFERENCE(SPREADSHEETINTF) TYPE REF TO I_OI_SPREADSHEET

*"----

DATA:

stru_ref TYPE REF TO cl_abap_structdescr,

comp_tab TYPE abap_compdescr_tab,

one_comp TYPE abap_compdescr,

one_name TYPE string,

type_ref TYPE REF TO cl_abap_typedescr,

is_ddic TYPE abap_bool,

lt_ddic TYPE dd_x031l_table,

wa_ddic TYPE x031l.

DATA: zjncranges TYPE soi_range_list,

zjnccontents TYPE soi_generic_table,

zjnconerange TYPE soi_range_item,

zjnconeitem TYPE soi_generic_item,

prevrow(4) TYPE n,

nrow(4) TYPE n,

ncolumn(4) TYPE n,

mystring TYPE string,

mydate LIKE sy-datum.

FIELD-SYMBOLS: <fs_type> TYPE ANY,

<fs_table> TYPE STANDARD TABLE,

<fs_line> TYPE ANY.

CONCATENATE '(' sy-cprog ')' itabname INTO mystring.

ASSIGN (mystring) TO <fs_table>.

CONCATENATE '(' sy-cprog ')' irecname INTO mystring.

ASSIGN (mystring) TO <fs_line>.

stru_ref ?= cl_abap_structdescr=>describe_by_data( <fs_line> ).

comp_tab = stru_ref->components.

REFRESH zjncranges.

MOVE rangename TO zjnconerange-name.

APPEND zjnconerange TO zjncranges.

CALL METHOD spreadsheetintf->get_ranges_data

IMPORTING

contents = zjnccontents

error = zjncerror

retcode = zjncretcode

CHANGING

ranges = zjncranges.

MOVE 0 TO prevrow.

LOOP AT zjnccontents INTO zjnconeitem.

MOVE zjnconeitem-row TO nrow.

IF nrow <> prevrow.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

CLEAR <fs_line>.

MOVE nrow TO prevrow.

ENDIF.

MOVE zjnconeitem-column TO ncolumn.

READ TABLE comp_tab INDEX ncolumn INTO one_comp.

CONCATENATE '(' sy-cprog ')' irecname '-' one_comp-name INTO one_name.

ASSIGN (one_name) TO <fs_type>.

IF one_comp-type_kind <> 'D'.

MOVE zjnconeitem-value TO <fs_type>.

ELSE.

TRANSLATE zjnconeitem-value TO UPPER CASE.

CALL FUNCTION 'CONVERSION_EXIT_SDATE_INPUT'

EXPORTING

input = zjnconeitem-value

IMPORTING

output = mydate.

MOVE mydate TO <fs_type>.

ENDIF.

ENDLOOP.

IF prevrow <> 0.

APPEND <fs_line> TO <fs_table>.

ENDIF.

ENDFUNCTION.

Regards,

Jagadish

Re: Excel Sheet processing

Former Member — Wed, 11 Jun 2008 11:20:57 GMT

hi Kannan,

Please go through the below given link,

[http://www.sap-img.com/abap/uploading-multiple-multitab-excel-sheets-or-ranges.htm]

hope it will help you,

*Reward points if usefull,*

Thanks,

kalyan.

topic Excel Sheet processing in Application Development and Automation Discussions

Excel Sheet processing

Re: Excel Sheet processing

Re: Excel Sheet processing