Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

 Link to Content's target Space :

 http://wiki.sdn.sap.com/wiki/display/CRM/CRM+Web+Client+UI+Framework

 Applies to:

 CRM 6.0/7.0

 Summary

 Upload data from Excel files to CRM system using ActiveXObjects and Javascript.

 Author(s):  

   
 Company:  Accenture
 Created on:   May'13 2010
 Author(s) Bio
               Arun Kumar is a CRM developer working with Accenture.

 General description:

 Uploading of files from CRM web ui is done using thtmlb:fileUpload tag. The file content represented in xstring format can be processed using character set conversion utilities like class cl_abap_char_utilities.  However this conversion however is limited to text documents, CSV files, XML files and cannot be used for Excel files. So it was not possible to upload data from Excel file to CRM system.  

 This wiki explains how to upload data from Excel files and Word documents using JavaScript and ActiveXObjects.

 Implementation:

 I have created a tableview to display the uploaded data. This is the structure of the context node,


 The view layout is enhanced with  the below tags,  
  •      A file upload tag <thtmlb:fileUpload id="fileUpl" /> 
  •      A button to trigger the upload process. The button onClientClick event will trigger the JavaScript method upload_file( ).   

                        <thtmlb:button id           = "mybutton"
                                 text                           = "Submit"
                                 onClientClick           = "upload_file( );" />

  •    Add a text area with width, height and rows as "0". This is done in order to hide the textarea from the screen since textarea does not have separate visibility                            property to hide it.             
   <thtmlb:textArea id     = "Excel_Data"
                    text   = " "
                    width  = "0"
                    height = "0"
                     rows  = "0" /> 
  •     Add a tableview to display the uploaded data.The rows are rendered from context node 'CondUpload'.
  •     Add a bsp:htmlbEvent                 
      <bsp:htmlbEvent name = "fireMyEvent"
                      id   = "fireMyEvent"
                      p1   = "p1"
                      p2   = "p2" />

  

 When the user hits the upload button, the javascript function upload_file( ) will be triggered. In the JS function activexobjects are used to open the excel file and read the data. The data is then populated to the textarea which is created with height and width as '0'. Once the data is populated, the bsp:htmlbEvent 'fireMyEvent' will be triggered. The onServerEvent  in  the IMPL checks for the parameter p1 of event fireMyEvent. If the condition check is satisfied , data is read from the textarea and passed to the collection wrapper and the table view is updated.

 Function upload_file:

 Read the entered file name from fileupload tag 

fileName = document.getElementById("<%= controller->component_id %>_fileUpl").value; 

 Create a reference to automation object Excel application and open the specific file to read the data, 

Excel = new ActieXObject("Excel.Application");  
Book = Excel.Workbooks.Open( fileName );

 Assuming the Sheet1 is populated with records, read the number of active rows.

ActiveRows = Book.Worksheets("Sheet1").Range("A1,IV65536").CurrentRegion.Rows.Count;

 Loop into the rows and read the data from each cell and concatenate to string "sValue" separated by horizontal tab and line feed for new rows. 

for(iRow=1;iRow<=ActiveRows;iRow++)
{
     for(jCol=1;jCol<7;jCol++)
     {
          cData  = Book.Worksheets("Sheet1").Cells(iRow,jCol).Value;
          sValue = sValue.concat(cData,"\t");
     }
     sValue = sValue.concat("\n\r");
}

 Populate the data into the textarea and close the workbook. 

document.getElementById("<%= controller->component_id %>_Excel_Data").value = sValue;
Excel.Workbooks.Close;

 Return the bsp:htmlbEvent fireMyEvent assiging parameter p1 = 'GetContent'.

return fireMyEvent('GetContent');

 EH_ONSERVEREVENT:

 Read data populated in the text area using method get_form_fields. Excel data available in ls_items-value is assigned to a string.

    CALL METHOD request->get_form_fields
      CHANGING
         fields = lt_items.
            
    LOOP AT lt_items INTO ls_items WHERE name CS 'excel_data'.
      lv_str = ls_items-value.
    ENDLOOP.

  Split the data by line feed and the records into an internal table. The data is then split by horizontal tab and assigned to seperate columns of an internal tab le of structure context node.

       SPLIT lv_str AT cl_abap_char_utilities=>cr_lf INTO TABLE  lt_str.
 
       LOOP AT lt_str INTO lv_str.
        SPLIT lv_str AT cl_abap_char_utilities=>horizontal_tab INTO ls_cond-documentid
                                                                  ls_cond-sold_to_party_id
                                                                  ls_cond-amount
                                                                  ls_cond-currency
                                                                  ls_cond-validfrom
                                                                  ls_cond-validto.
        APPEND ls_cond TO lt_cond.
       ENDLOOP.

  Create value node entities of type 'CondUpload' context node and populate the uploaded records. Add the entity to the collection wrapper. 

LOOP AT lt_cond INTO ls_cond. 
        CREATE DATA lr_forms.
        CREATE OBJECT lr_entity
                     EXPORTING
                     v_data_ref = lr_forms.
        lr_entity->set_properties( ls_cond ).
  
        typed_context->condupload->collection_wrapper->add( lr_entity ).
      ENDLOOP.

 Data in excel sheet

 Uploaded data displayed in tableview

:

Coding:

 Function upload_file:


<scirpt language="javascirpt">function upload_file( )
{
 var   iRow = 1,
       iCol = 1,
       ActiveRows = 0,
       fileName,
       sValue = "";
 fileName = document.getElementById("<%= controller->component_id %>_fileUpl").value;
 try
 {
  Excel = new ActieXObject("Excel.Application");
  Book = Excel.Workbooks.Open( fileName );
  ActiveRows = Book.Worksheets("Sheet1").Range("A1,IV65536").CurrentRegion.Rows.Count;
  for(iRow=1;iRow<=ActiveRows;iRow++)
  {
       for(jCol=1;jCol<7;jCol++)
       {
                cData = Book.Worksheets("Sheet1").Cells(iRow,jCol).Value;
                sValue = sValue.concat(cData,"\t");
       }
       sValue = sValue.concat("\n\r");
  }
  document.getElementById("<%= controller->component_id %>_Excel_Data").value = sValue;
  Excel.Workbooks.Close;
 }
return fireMyEvent('GetContent');
</scirpt>

METHOD eh_onserverevent:

* Types declaration
  TYPES: BEGIN OF line,
          documentid       TYPE string,
          sold_to_party_id TYPE string,
          amount           TYPE string,
          currency         TYPE string,
          validfrom        TYPE string,
          validto          TYPE string,
         END OF line.


  DATA: lr_event          TYPE REF TO cl_bsp_htmlb_event,
             lr_entity          TYPE REF TO cl_bsp_wd_value_node,
             lr_forms           TYPE REF TO line,
             ls_items           TYPE ihttpnvp,
             ls_cond            TYPE line,
             lt_items           TYPE tihttpnvp,
             lt_str               TYPE TABLE OF string,
             lt_cond            TYPE TABLE OF line,
             lv_str              TYPE string.

* Assign the htmlb_event_ex to htmlb_event reference
            lr_event ?= htmlb_event_ex.
* Clear the collection wrappr of context node CondUpload
           typed_context->condupload->collection_wrapper->clear_collection( ).
* Check the triggered event
          IF lr_event->p1 EQ 'GetContent'.
* Read the list of all form fields
             CALL METHOD request->get_form_fields
               CHANGING
                    fields = lt_items.
* Read the records populated in text area
            LOOP AT lt_items INTO ls_items WHERE name CS 'excel_data'.
                 lv_str = ls_items-value.
            ENDLOOP.
* Split the data into an internal table with line feed as delimiter
            SPLIT lv_str AT cl_abap_char_utilities=>cr_lf INTO TABLE lt_str.
* Delete the first record since the first record contains the header
            DELETE lt_str INDEX 1.
* Split the records with horizontal tab as delimiter and populate
* the internal table of type 'CondUpload' context node
          LOOP AT lt_str INTO lv_str.
            SPLIT lv_str AT cl_abap_char_utilities=>horizontal_tab INTO ls_cond-documentid
                                                                    ls_cond-sold_to_party_id
                                                                    ls_cond-amount
                                                                    ls_cond-currency
                                                                    ls_cond-validfrom
                                                                    ls_cond-validto.
            APPEND ls_cond TO lt_cond.
          ENDLOOP.
* Loop into the records and create value node entities
          LOOP AT lt_cond INTO ls_cond.
            CREATE DATA lr_forms.
            CREATE OBJECT lr_entity
               EXPORTING
                  iv_data_ref = lr_forms.
* Populate the entity
             lr_entity->set_properties( ls_cond ).
* Add the entity to colellection wrapper
            typed_context->condupload->collection_wrapper->add( lr_entity ).
           ENDLOOP.
  ENDIF.

  Thanks & Regards, 

   Arun Kumar

19 Comments

  1. Guest

    Hi,Kumar!

    Can you tell me where i must add javascript Upload_File?
    ServerEvent doesn't work when i press button "Submit".

    Regards,Marat.

  2. Hi,

    Javascript Upload_File must be added to the onClientClick of the "Submit" button.

    <thtmlb:button id           = "mybutton"
            text                           = "Submit"
           onClientClick           = "upload_file( );" />

    Regards,
    Arun

  3. Guest

    Hi, Kumar.

    Can you show whole code of UploadExcel.htm page?

    Regards,Marat.

  4. Guest

    I find one mistake:

    Instead:

    ActiveRows = Book.Worksheets("Sheet1").Range("A1,IV65536").CurrentRegion.Rows.Count;

    i write:

    ActiveRows = Book.Worksheets("Sheet1").Range("A1:IV65536").CurrentRegion.Rows.Count;

    Regards, Marat.

  5. Guest

    Hi,

    when i am clicking the Submit button is pressed error on page its showing and nothing is comming basically i understand that the Event is not being Triggered. please can you help me .

  6. Great! Very helpful, thanks.

  7. Hi Arun,

    are you aware of the abap2xlsx Project? You could try to adapt your solution to use this library. This would make the functionality independent of the client functionality and browser.

    Best regards
    Gregor

  8. Former Member

    Hi Kumar!
    I’ve written an article and want to add it into child page in CRM Web Client UI Framework. But I cannot add images with description of customizing activities. Couldn’t you provide how to do this.
    Thanks in advance!

    Kindly regards,
    Lenar Danilov.

  9. Former Member

    Hi Arun,

               Thanks for your wiki. I have followed the steps given by you. But  the event handler method was not being triggered.Will you please give a suggestion regarding it.

    Regards,

    Darwin

  10. Guest

    Hi Sir,

    May you share me the XML of your page? Thanks in advance...

    Best Regards

  11. Guest

    Hi Sir,

    May you share me the XML of your page? Thanks in advance...

    Best Regards

  12. Guest

    Hi guys.

    Can I use it for xsls or only for xls? 

    Best regards

  13. You can use the the SAP Code Exchange Project abap2xlsx to read XSLX which you then can use in CRM. But are you also aware that starting with CRM 7.0 EhP1 you can also copy & paste from Excel?

  14. I never tried with .xlsx file but it should work with it as well since its just the Javascript that handles the file.

    With CRM 7.0 EhP1 it is possible to copy & paste from Excel to a table view in Web UI, yet fast row creation must be handled seperately.

    Regards,

    Arun

  15. Guest

    Thanks, Gregor and Arun. Very helpful.

    Best regards.

  16. Former Member

    Hi Arun,

    I am new to using javascript in BSP page.

    Could you please share the complete code of the .htm file?

    Thanks & Regards,

    Sayan

  17. Former Member

    Very nice article, we started using this feature about a year ago.  But we are having an issue with IE11.  IE8, 9 & 10 seem tot work fine. 

    Curious if anyone experiencing the same as I don't think we can log an OSS message on this.

     

    1. Hello Glen,

      Kindly take a look at Note 1746385 which is our main browser support Note for limitations and pre-requisites.

      BR,

      Hasan

       
  18. Former Member

    Thanks for the response, we are 731 CRM 702 EhP2 and running in compatibility mode, but I don't think SAP will support if the excel upload is not working.

    As an FYI we do have an OSS message as the parameter SEARCH_MAX_ROW_PASTE  does not look to be compatible with IE11 in compatibility mode