CRM Web UI - Uploading data from Excel files

 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

Labels

upload upload Delete
excel excel Delete
webui webui Delete
javascript javascript Delete
xls xls Delete
upload_excel_in_crm upload_excel_in_crm Delete
upload_excel upload_excel Delete
upload_ppt upload_ppt Delete
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.