Skip to Content

Hi,

I had a requirement where in user wanted to upload excel in CRM .

I can easily develop an se38 report where we provide the functionality of uploading excel and subsequent activity.

But this is an approach we have actually implemented many times and since CRM is all about web UI, I wanted to accommodate the requirement from Web UI.

Here for fulfilling I did some research and came across following link :

http://wiki.scn.sap.com/wiki/display/CRM/CRM+Web+UI+-+Uploading+data+from+Excel+files

I must appreciate author for this link.

But here whole code is done using Java script and some more concepts.

I personally did not wanted to follow the approach and hence after exploring I came up with new solution.

Please follow below specified procedure and hope your requirement gets covered.

For accommodating requirement I developed Z component.

/wp-content/uploads/2013/12/1_353020.jpg

Step 1: Create Z component ZEXCEL_UPLOAD, with View 1 for upload bar.

For Upload bar we have standard BSP component available GS_FILE_UPLOAD which we can use same.(By component usage functionality) in your Z component.

Or you can create a new view and just copy the .htm , EH_ONUPLOAD code from View GS_FILE_UPLOAD/VFileUpload .

I actually copied the code in Z view so that I can put my text as per requirement without enhancing standard component. (You can follow any approach)

If you are copying the view don’t forget to accommodate the code for Upload button

So now your View 1 is ready.

Step 2:  Create view 2 for displaying the data from excel in table view

Here a beautiful approach is followed . Dynamically we are actually generating the attributes in context node of your result list. Because we wanted to make a generic component.

Here suppose you have 5 columns in your excel automatically 5 columns with the same label of your excel would be available as result.

Hence you can use same component at multiple places

Since we want an list of attributes dynamically we won’t add attributes in context node.

But without any attribute in context note your system won’t allow you to create context node and also will throw exception when you will run the component as no config would be available.

Hence just add one attribute which is available by default like sr. no. later on by code we will replace same by our list of attributes.

Step 3: Reading excel data into an internal table.

I personally prefer using Class-method approach for my development.

Hence I developed a Z class ZCL_MK_GLOBAL and a static method with public visibility EXCEL_FILE_UPLOAD with importing

parameters filename and content.

In EH_ONUPLOAD method of view 1 (Copied code from GS_FILE_UPLOAD component )

We have two variable LV_FILE_NAME and LV_FILE_CONTENT_XSTRING.

At end of the code of EH_ONUPLOAD method, Call your method EXCEL_FILE_UPLOAD of class ZCL_MK_GLOBAL and

pass LV_FILE_NAME and LV_FILE_CONTENT_XSTRING in it.

/wp-content/uploads/2013/12/1_353020.jpg

Declare attribute in ZCL_MK_GLOBAL class.(As shown in below image)

/wp-content/uploads/2013/12/1_353020.jpg

Now we will code in EXCEL_FILE_UPLOAD method.

/wp-content/uploads/2013/12/1_353020.jpg

/wp-content/uploads/2013/12/1_353020.jpg


   METHOD EXCEL_FILE_UPLOAD.
  DATA: LO_DOC_SPREADSHEET TYPE REF TO CL_FDT_XL_SPREADSHEET,
        LS_MESSAGE         TYPE        IF_FDT_TYPES=>S_MESSAGE,
        LV_WS_NAME         TYPE                   STRING,
        LT_MESSAGE         TYPE        IF_FDT_TYPES=>T_MESSAGE,
        LT_WS_NAME         TYPE STANDARD TABLE OF STRING,
        ER_DATA            TYPE REF TO DATA.
  TRY .
      CREATE OBJECT LO_DOC_SPREADSHEET
        EXPORTING
          DOCUMENT_NAME = IV_FILENAME
          XDOCUMENT     = IV_CONTENT.
    CATCH CX_FDT_EXCEL_CORE.
      MESSAGE E004(FDT_EXCEL_API) INTO LS_MESSAGE-TEXT.
      LS_MESSAGE-MSGTY = 'E'.
      "'Unable to parse the excel content.'.
      APPEND LS_MESSAGE TO LT_MESSAGE.
      RAISE EXCEPTION TYPE CX_FDT_EXCEL
        EXPORTING
          MT_MESSAGE = LT_MESSAGE.
  ENDTRY.
*gets the first worksheet and then getting the data in a internal table
  LO_DOC_SPREADSHEET->IF_FDT_DOC_SPREADSHEET~GET_WORKSHEET_NAMES( IMPORTING WORKSHEET_NAMES = LT_WS_NAME  ).
*  Get the first worksheet
  READ TABLE LT_WS_NAME INDEX 1 INTO LV_WS_NAME.
  IF LV_WS_NAME IS INITIAL.
    MESSAGE E005(FDT_EXCEL_API) INTO LS_MESSAGE-TEXT.
    LS_MESSAGE-MSGTY = 'E'.
    "'No worksheet exists in the excel content.'.
    APPEND LS_MESSAGE TO LT_MESSAGE.
    RAISE EXCEPTION TYPE CX_FDT_EXCEL
      EXPORTING
        MT_MESSAGE = LT_MESSAGE.
  ENDIF.
  ER_DATA = LO_DOC_SPREADSHEET->IF_FDT_DOC_SPREADSHEET~GET_ITAB_FROM_WORKSHEET( LV_WS_NAME ).
  GV_CTXT_DATA = ER_DATA.
ENDMETHOD.

Step 4: We will create attributes (by reading the header of the excel) for the result list in view 2.

Here we know that in GET_TABLE_LINE_SAMPLE  method, code for attributes of a context node (Table Columns) is done.

Hence we will code in this method.

/wp-content/uploads/2013/12/1_353020.jpg

If you have noticed in above steps we took the data in GV_CTXT_DATA.

/wp-content/uploads/2013/12/1_353020.jpg

(If above image is not clear please click on same you would able to see in full screen else please find same code below)


   METHOD GET_TABLE_LINE_SAMPLE.
  TYPES: BEGIN OF LINE.
**  TYPES NO TYPE CHAR2. "Added by wizard
  TYPES:
    SRNO TYPE CHAR2,
        END OF LINE.
  FIELD-SYMBOLS: <LS_TABLE_LINE> TYPE STANDARD TABLE.
  FIELD-SYMBOLS: <FS_IS_FINAL>  TYPE ANY."TABLE.
  DATA: LS_TABLE_HEADER TYPE REF TO DATA.
  FIELD-SYMBOLS: <GS_TABLE_LINE> TYPE STANDARD TABLE.
  FIELD-SYMBOLS: <LS_COMP> TYPE ANY.
  DATA: IT_FCAT TYPE LVC_T_FCAT.
  FIELD-SYMBOLS: <WA_FCAT> TYPE LVC_S_FCAT.
  DATA: LV_TABLINE TYPE REF TO DATA.
  ASSIGN ZCL_MK_GLOBAL=>GV_CTXT_DATA->* TO <LS_TABLE_LINE> .
  CREATE DATA RV_SAMPLE TYPE LINE. " standard
  CHECK <LS_TABLE_LINE> IS ASSIGNED.
  READ TABLE <LS_TABLE_LINE> ASSIGNING <FS_IS_FINAL> INDEX 1.
  CHECK <FS_IS_FINAL> IS ASSIGNED.
  CREATE DATA LV_TABLINE LIKE LINE OF <LS_TABLE_LINE>.
  DATA : IT_DETAIL TYPE ABAP_COMPDESCR_TAB,
         WA_COMP   TYPE ABAP_COMPDESCR,
         LV_COUNT TYPE CHAR20.
  DATA : LR_REF_DESCR TYPE REF TO CL_ABAP_STRUCTDESCR.
  DATA :GT_DYNA_TABLE  TYPE REF TO  DATA.
  DATA: LV_INDEX TYPE SY-INDEX.
  DATA : LV_SY_SUBRC TYPE SY-SUBRC VALUE 0.
  LR_REF_DESCR ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA_REF( LV_TABLINE ).
  IT_DETAIL     = LR_REF_DESCR->COMPONENTS .
  IF IT_DETAIL IS NOT INITIAL.
    DESCRIBE TABLE IT_DETAIL LINES LV_COUNT.
*    LOOP AT IT_DETAIL INTO WA_COMP.
*      WRITE:/ WA_COMP-NAME .
*    ENDLOOP.
    DO LV_COUNT TIMES.
*    WHILE LV_SY_SUBRC = 0.
      ASSIGN COMPONENT  SY-INDEX OF STRUCTURE <FS_IS_FINAL> TO <LS_COMP>.
*      IF SY-SUBRC = 0.
      APPEND INITIAL LINE TO IT_FCAT ASSIGNING <WA_FCAT>.
      LV_INDEX = SY-INDEX.
      <WA_FCAT>-COL_POS   = LV_INDEX.
      <WA_FCAT>-FIELDNAME = <LS_COMP>.
      <WA_FCAT>-COLTEXT   = <LS_COMP>.
      <WA_FCAT>-DATATYPE  = 'C'.
      <WA_FCAT>-INTTYPE   = 'C'.
      <WA_FCAT>-INTLEN    = '30' .
*      LV_SY_SUBRC = 0.
*      ELSE.
*        LV_SY_SUBRC = 1.
*      ENDIF.
*    ENDWHILE.
    ENDDO.
    CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
      EXPORTING
        IT_FIELDCATALOG = IT_FCAT
      IMPORTING
        EP_TABLE        = GT_DYNA_TABLE.
    ASSIGN GT_DYNA_TABLE->* TO <LS_TABLE_LINE>.
  ENDIF.
  CHECK <LS_TABLE_LINE> IS ASSIGNED.
  CREATE DATA RV_SAMPLE LIKE LINE OF <LS_TABLE_LINE>.
ENDMETHOD.


And congratulation you are done with it Your data is uploaded from excel to grid…Now play with it in whatever way you want

Please take care of following points

·      1)   If your excel header line contain special character than this solution wont work

·      2)   If length of header is less than 3 character it will not work

·      3)   If length of header increases 15 character it wont work.

But being a developer we can definitely handle this by our coding skills or we can ask user to take care on specified points.

Note:   CL_FDT_XL_SPREADSHEET specified class is available in CRM 7.0 EHP1 onwards only.

To report this post you need to login first.

20 Comments

You must be Logged on to comment or reply to a post.

        1. Dhruvin Mehta

          You can not configure the same. As the columns are dynamically genrated if you’d know a table then you should not create dynamic table and then you can configure.

          (0) 
  1. j singh

    Hi Preeti,

    How would it display the data on Web UI?

    Here, you are just passing the structure to “rv_sample” which will help in creating the dynamic structure on web UI however, data will not come.

    Please explain, if my understanding is incorrect. Thanks!

    Regards,

    JotSaroop Singh

    (0) 
    1. Dhruvin Mehta

      Dear Jotsaroop , You are right RV_SAMPLE will only help to create dynamic table but data is coming from a Global attribute. In pictures she has described it with arrows you can do the same to get desired results.

      (0) 
    2. Preeti Singh Post author

      Dear JotSaroop,

      Sorry for the delay in reply… I was not so active on SDN. Hope Your query is resolved by Dhruvin’s reply 🙂

      (0) 
    1. Dhruvin Mehta

      Hi Tom , I have tried and tested it… can u check in debug where exactly u r facing issue?

      Then we can also check where the issue is.

      (0) 
      1. Tom Lismont

        Hello Dhruvin. The issue is that there is not data being displayed. I checked in debug and the global parameter  gv_ctxt_data is correctly filled so the upload actually worked. The only thing which is not working is getting my data displayed on the screen.

        (0) 
  2. RAHUL MATHUR

    Hi,

    I am new to SAP, I followed the above steps but not able to get the output..I debugged it and found that in GET_TABLE_LINE_SAMPLE ,

    CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE is not able to get executed and giving exception.

    Thnks

    (0) 

Leave a Reply