Skip to Content
Author's profile photo Former Member

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.

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

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harish Kumar
      Harish Kumar

      Thanks for sharing 🙂 ..

      definitely will help in the future.

      Regards,

      Harish Kumar

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      🙂 ur welcome Harish !

      Author's profile photo Sanchez Wang
      Sanchez Wang

      How u configure UI for a dynamic table?

      Author's profile photo Dhruvin Mehta
      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.

      Author's profile photo Dhruvin Mehta
      Dhruvin Mehta

      Well job done, will help others as well.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanx Dhruvin 🙂

      Author's profile photo Sumeet Gehlot
      Sumeet Gehlot

      Thanks for sharing ...helpful doc 🙂 ...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      🙂

      Author's profile photo siddarth kabde
      siddarth kabde

      Thanks for a good document. learnt a new approach.

      Author's profile photo srinivas karri
      srinivas karri

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

      Author's profile photo j singh
      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

      Author's profile photo Dhruvin Mehta
      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.

      Author's profile photo Former Member
      Former Member
      Blog 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 🙂

      Author's profile photo Former Member
      Former Member

      hmmm I did all according to what is listed but after I click on the button "upload" nothing happens...

      Any ideas?

      Author's profile photo Dhruvin Mehta
      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Dhruvin Mehta
      Dhruvin Mehta

      Hello Tom,

      I think u should check get table line sample method...

      Author's profile photo RAHUL MATHUR
      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

      Author's profile photo Former Member
      Former Member

      What exception is it throwing?

      Author's profile photo Dhruvin Mehta
      Dhruvin Mehta

      just check the field catalog table before executing