Upload excel from CRM web UI and displaying result using Dynamic Table (valid only for xlsx. and xls. extention)

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.

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.

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

Now we will code in EXCEL_FILE_UPLOAD method.


   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.

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

(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.

20 Comments

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

Harish Kumar December 31, 2013 at 6:06 am

Thanks for sharing 🙂 ..

definitely will help in the future.

Regards,

Harish Kumar

like (0)
1. Preeti Singh Post authorDecember 31, 2013 at 7:19 am
  
  🙂 ur welcome Harish !
  
  like (0)
  1. Sanchez Wang December 15, 2014 at 3:11 am
    
    How u configure UI for a dynamic table?
    
    like (0)
    1. Dhruvin Mehta March 20, 2015 at 2:50 pm
      
      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.
      
      like (0)
Dhruvin Mehta January 2, 2014 at 6:20 am

Well job done, will help others as well.

like (0)
1. Preeti Singh Post authorJanuary 7, 2014 at 4:12 am
  
  Thanx Dhruvin 🙂
  
  like (0)
Sumeet Gehlot January 7, 2014 at 9:41 am

Thanks for sharing …helpful doc 🙂 …

like (0)
1. Preeti Singh Post authorJanuary 17, 2014 at 6:36 am
  
  🙂
  
  like (0)
siddarth kabde January 7, 2014 at 10:20 am

Thanks for a good document. learnt a new approach.

like (0)
srinivas karri December 15, 2014 at 7:08 am

nice document this is very informative and useful to others as well… Thanks for sharing… 🙂

like (0)
j singh March 20, 2015 at 6:47 am

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

like (0)
1. Dhruvin Mehta March 20, 2015 at 2:57 pm
  
  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.
  
  like (0)
2. Preeti Singh Post authorApril 9, 2015 at 8:56 am
  
  Dear JotSaroop,
  
  Sorry for the delay in reply… I was not so active on SDN. Hope Your query is resolved by Dhruvin’s reply 🙂
  
  like (0)
Tom Lismont May 26, 2015 at 2:51 pm

hmmm I did all according to what is listed but after I click on the button “upload” nothing happens…

Any ideas?

like (0)
1. Dhruvin Mehta May 28, 2015 at 9:48 am
  
  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.
  
  like (0)
  1. Tom Lismont May 28, 2015 at 10:30 am
    
    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.
    
    like (0)
    1. Dhruvin Mehta June 1, 2015 at 1:00 pm
      
      Hello Tom,
      
      I think u should check get table line sample method…
      
      like (0)
RAHUL MATHUR May 29, 2015 at 10:08 am

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

like (0)
1. Tom Lismont May 29, 2015 at 2:28 pm
  
  What exception is it throwing?
  
  like (0)
2. Dhruvin Mehta June 1, 2015 at 1:01 pm
  
  just check the field catalog table before executing
  
  like (0)