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.