Excel File to Internal Table

[Former Member]

Hi,

I need to export the content of a excel file to an internal table.

I looked for it and I found some samples. But they opened the excel file.

My excel file comes from upload and I have it in a xstring variable.

The functions for excel expect for a file name, but I have the xstring only.

Are there functions (to export the content to an internal table) that expect a xstring as a parameter?

Thanks a lot.

Best Regards,

Jorge Luiz

[former_member262988 · Accepted solution]

Hi,

Try this way....

Get the value in xstring....

--> * Creating the main iXML factory
       lref_ixml = cl_ixml=>create( ).
     * Creating a stream factory
      lref_streamfactory = lref_ixml->create_stream_factory( ).
    * Wrap the table containing the file into a stream
      lref_istream = lref_streamfactory->create_istream_xstring( string =
       *iv_data_xstring* ).(xstring WHICH YOU GOT FROM UPLOAD FILE)
    * Creating a document
       lref_document = lref_ixml->create_document( ).
    * Create a Parser
      lref_parser = lref_ixml->create_parser( stream_factory = lref_streamfactory
                                          istream        = lref_istream
                                          document       = lref_document ).

   *   Parse the stream
      IF lref_parser->parse( ) NE 0.
      IF lref_parser->num_errors( ) NE 0.
      lv_count = lref_parser->num_errors( ).
      lv_index = 0.
      WHILE lv_index < lv_count.
        parseerror = lref_parser->get_error( index = lv_index ).
        lv_parse = parseerror->get_line( ).
        lv_parse = parseerror->get_column( ).
        lv_str = parseerror->get_reason( ).
        lv_index = lv_index + 1.
      ENDWHILE.                    " WHILE LV_INDEX < LV_COUNT
    ENDIF.                         " IF LREF_PARSER->NUM_ERRORS...
  ENDIF.                           " IF LREF_PARSER->PARSE( ) NE 0
  CLEAR: lv_index, lv_count.
* Assign Node
  lref_node ?= lref_document.
* Check on Node
  CHECK NOT lref_node IS INITIAL.
* If Node is initial - EXIT
  IF lref_node IS INITIAL.
    EXIT.
  ENDIF.                           " IF LREF_NODE IS INITIAL
* Create a node iterator
  lref_iterator  = lref_node->create_iterator( ).
* Get current node
  lref_node = lref_iterator->get_next( ).
* Loop over all nodes  ---->Excel
  WHILE NOT lref_node IS INITIAL.
    lv_indent = lref_node->get_height( ) * 2.
    lv_indent = lv_indent + 20.
*   Based on Type - identifying - ROW - CELL - DATA
    CASE lref_node->get_type( ).
      WHEN if_ixml_node=>co_node_element.
        lv_name      = lref_node->get_name( ).
        lref_nodemap = lref_node->get_attributes( ).
        lv_value     = lref_node->get_value( ).
*       Reading the ROW
        IF lv_name = 'Row'.
          lv_row_count = lv_row_count + 1.
          ENDIF.
          lv_col_count = 0.
          CLEAR  lv_name.
        ENDIF.     

*       Reducing the loop
        IF lv_name = 'DataValidation'.
          EXIT.
        ENDIF.                     " IF LV_NAME = 'DataValidation'
*       Reading Cell
        IF lv_name     = 'Cell'.
          lv_col_count = lv_col_count + 1.
          CLEAR lv_name.
        ENDIF.                     " IF LV_NAME = 'Cell'
      WHEN  if_ixml_node=>co_node_text            OR
            if_ixml_node=>co_node_cdata_section.
*       Capturing Cell Value
        CLEAR lv_value.
        lv_value  = lref_node->get_value( ).
*       if first row
        IF lv_row_count = 1 AND lv_name = 'Data'.
          CASE lv_col_count.
*here in case get the column values of the excel uploaded* 
  WHEN '1'.
**Move the value field** 
**like   lv_comp = 'MATNR' (field of uy internal table)*when '2'.*
**like wise move values to workarea and append in to internal table*when '3'.*
    ENDCASE.
 Advance to next node
    lref_node = lref_iterator->get_next( ).
  ENDWHILE.

Thanks,

Shailaja Ainala.

[Former Member · Accepted solution]

Hi Shailaja Ainala ,

it didnt work either. I worked fine for a xml file, but for my xls, it didn't work.

Saurav Mago ,

I've tested your sample too, but it didn't work for my application.

Thanks for helping.

[former_member262988 · Accepted solution]

Hi,

I had the same requirment ......

i followed the same as i mentioned previously...

--> get the data from the uplaoded file from webdynpro as xstring format...

-->convert uploaded file into xml

--> in that check for 'cell' increment the count ....

--> when WHEN if_ixml_node=>co_node_text OR

if_ixml_node=>co_node_cdata_section.

if this when satisfies check for value

lv_name = 'Data'.

lv_value = lref_node->get_value( ).

impelement the case ...

--> case (count = 1).

check for the coumn name and pass the value into relavant workarea field...

Thanks,

Shailaja Ainala.

[Former Member · Accepted solution]

Shailaja Ainala,

What function do you use to convert xstring to xml ?

Thanks a lot

[thomas_jung · Accepted solution]

You can't process the binary, native format of XLS within ABAP. That is a Microsoft propriatery format. The Excel Sheet has to be saved as Text Tab Delimited, Text Semi Colon delimited, XML or some other open format in order to process the file in ABAP . This is no converter within ABAP that will take the native XLS and turn it into XML.

[Former Member · Accepted solution]

Thanks.

Edited by: Jorge Castro on Apr 9, 2009 1:34 PM

[Former Member]

hi,

I am uploading excel file. Ensure that columns in your excel are exactly present in the internal table structure that you use.

I am using a TabDelimeted file as input for the below mention case .

Declare context node FILE

with Attributes:

Filename type String

MimeType type String

FILE type xstring.

Place 'Fileupload UI element' on you view , bind DATA property to Attribute u2018FILEu2019 created in the context node.

Create a Button lets call UPLOAD. on its click action put the following code.

method upload_file .

data: s_cont type string,

x_cont type xstring,

convt type ref to cl_abap_conv_in_ce,

item_file type xstringval.

data: input_string type string,

fields type string_table,

fields2 type string_table,

tbl_fields type string_table,

s_table type string_table,

ls_table like line of s_table,

ls like line of fields,

lv_num_cols type i.

data: lt_data type table of ZMYREC, "here

ls_data like line of lt_data,

lv_data type string.

field-symbols: <wa_table> like line of s_table.

wd_this->m_elem_file->get_attribute(

exporting

name = `FILE`

importing

value = item_file ).

convt = cl_abap_conv_in_ce=>create( input = item_file ).

convt->read( importing data = s_cont ).

"Column headers

split s_cont at cl_abap_char_utilities=>cr_lf into table s_table.

read table s_table into ls_table index 1.

"Delete column header

delete s_table index 1.

split ls_table at cl_abap_char_utilities=>horizontal_tab into table fields.

lv_num_cols = lines( fields ).

refresh fields.

loop at s_table assigning <wa_table>.

split <wa_table> at cl_abap_char_utilities=>horizontal_tab into :

ls_data-recordid

ls_data-bstkd

ls_data-kunnr

ls_data-matnr

ls_data-candno

ls_data-fname

ls_data-inits

ls_data-lname

ls_data-gesch.

append ls_data to lt_data.

clear ls_data.

endloop.

wd_this->m_node_data->bind_table(

exporting

new_items = lt_data

set_initial_elements = abap_true ).

endmethod.

I hope it helps.

Thanx.

[Former Member]

Saurav,

My excel file is not TabDelimeted, it is a normal excel file with 3 columns.

I did what you said, but it didn't work.

Thanks for helping.

[Former Member]

hi,

Check out this.

In this case i am having two columns in excel.

Double click on ONACTION_UPLOAD method. Write a code as given below.

METHOD onactionon_upload .

TYPES :

BEGIN OF str_itab,

name(10) TYPE c,

age(10) TYPE c,

END OF str_itab.

DATA : t_table1 TYPE STANDARD TABLE OF str_itab,

i_data TYPE STANDARD TABLE OF string,

lo_nd_sflight TYPE REF TO if_wd_context_node,

lo_el_sflight TYPE REF TO if_wd_context_element,

l_string TYPE string,

fs_table TYPE str_itab,

l_xstring TYPE xstring,

fields TYPE string_table,

lv_field TYPE string.

DATA : t_table TYPE if_main=>elements_data_tab,

data_table TYPE if_main=>elements_data_tab.

• get single attribute

wd_context->get_attribute( EXPORTING name = `DATASOURCE` IMPORTING value = l_xstring ).

CALL FUNCTION 'HR_KR_XSTRING_TO_STRING'

EXPORTING

in_xstring = l_xstring

IMPORTING

out_string = l_string. SPLIT l_string AT cl_abap_char_utilities=>newline INTO TABLE i_data.

• Bind With table Element.

LOOP AT i_data INTO l_string.

SPLIT l_string AT cl_abap_char_utilities=>horizontal_tab INTO TABLE fields.

READ TABLE fields INTO lv_field INDEX 1.

fs_table-name = lv_field. READ TABLE fields INTO lv_field INDEX 2.

fs_table-age = lv_field. APPEND fs_table TO t_table1.

ENDLOOP.

lo_nd_sflight = wd_context->get_child_node( 'DATA_TAB' ).

lo_nd_sflight->bind_table( t_table1 ).

ENDMETHOD.

[Former Member]

I am gonna try to explain better.

My web dynpro application has to do an upload of a excel file chosen by user.

Then, that WD4A application has to export the excel file that is in a xstring variable to an internal table.

How can I do that?

Thanks a lot,

Jorge Luiz