Download the Excel file in SAP CRM WebUI in .xlsx format.

[yashoratna]

Introduction: We have provision and services available in SAP to download the files in .txt and .csv formats. I received one requirement where we were required to download the same file in .xlsx format for further processing. 

Solution: To achieve this functionality, we need to make some changes at the SAP service level along with calling the same service from the WebUI page (in the BSP Component) in stepped fashion as below.

Step 1: Create one SICF service e.g. ZCRM_GT_EXL_DL on path /default_host/sap/crm/ as per screenshot below.

Step 2: Create one handler class e.g. ZCL_CRM_XLS_UI_SERVICES with Interface IF_HTTP_EXTENSION and couple of variables (gv_variable, gt_file_content_x) to store file name and contents respectively.

Step 3: Create one static method SET_FILE_CONTENT with two importing parameters IV_FILE_CONTENT (type XSTRING) and IV_FILENAME (type STRING).

 METHOD set_file_content.
    gt_file_content_x = iv_file_content.
    gv_filename = iv_filename.
 ENDMETHOD.

Step 4: Now redefine method IF_HTTP_EXTENSION~HANDLE_REQUEST with below set of codes to set the server response as per below code snippet.

Mimetype for xlsx file should be 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

METHOD if_http_extension~handle_request.
    DATA: lv_content_type        TYPE string,
          lv_content_disposition TYPE string VALUE 'attachment',
          lv_extension           TYPE char04,
          lv_mimetype            TYPE w3conttype.

    FIND FIRST OCCURRENCE OF REGEX '\.[^\.]+$' IN gv_filename MATCH OFFSET DATA(lv_dot_offset).
    IF sy-subrc EQ 0.       "File extension found
      ADD 1 TO lv_dot_offset.
      lv_extension = gv_filename+lv_dot_offset.
      "Get MIME type from file extension
      CALL FUNCTION 'SDOK_MIMETYPE_GET'
        EXPORTING
          extension = lv_extension
        IMPORTING
          mimetype  = lv_mimetype.     "'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

      lv_content_type = lv_mimetype.
    ELSE."File extension not found, set joker
      lv_content_type = 'application/octet-stream'.
    ENDIF.

    DATA(lv_value) = lv_content_disposition && '; filename="' && gv_filename && '"' .
    DATA(lv_content_length) = CONV string( xstrlen( gt_file_content_x ) ).

*     -> create response
    server->response->set_header_field(
      name  = 'Content-Length'                              
      value = lv_content_length ).
    server->response->set_header_field( name  = 'Content-Disposition' 
                                        value = lv_value ).
    server->response->set_header_field( name  = 'Content-Type' 
                                        value = lv_content_type ).
    server->response->set_header_field( name  = 'expires'  
                                        value = '0' ).
    server->response->append_data( data = gt_file_content_x ).
  ENDMETHOD.

Step 5: Now it's time to call SICF service. So, I'd take relevant event handler of the BSP component.  gv_url is declared as an instance variable in corresponding IMPL class.

DATA: lv_url TYPE string,
      lv_cmd TYPE string VALUE '?application=PGDTL'.         

    CALL METHOD cl_crm_web_utility=>create_url
      EXPORTING
        iv_path            = '/sap/crm/crm_gt_download'   
        iv_in_same_session = 'X'                        
        iv_absolute        = 'X'                       
        iv_no_cookie       = 'X'                       
      RECEIVING
        ev_url             = lv_url.
    CONCATENATE lv_url lv_cmd INTO gv_url.

Step 6: And set the excel file name and the content (in XSTRING format) in method ZCL_CRM_XLS_UI_SERVICES=>SET_FILE_CONTENT from step 3.

Step 7: In .htm file (view layout of the BSP component), set gv_url in window.location from step 5.

<%
  data : lv_url type string.
%>
<%@page language="abap" %>
<%@extension name="thtmlb" prefix="thtmlb" %>
<%@extension name="chtmlb" prefix="chtmlb" %>
<%@extension name="bsp" prefix="bsp" %>
<%
* Conversion Cnode SelectionMode to Tag
  data: lv_cellerator_selectionmode type string,
  lv_cellerator_editmode  type string,
  lv_cellerator_selectioncolumn type string.
  cl_thtmlb_util=>translate_selection_mode(
  exporting
  iv_selection_mode    = BDGTHeader->SELECTION_MODE
  iv_all_rows_editable = space
  importing
  ev_selection_mode   = lv_cellerator_selectionmode
  ev_edit_mode        = lv_cellerator_editmode
  ev_selection_column = lv_cellerator_selectioncolumn ).
%>
<script language="JavaScript">
<%
  lv_url = controller->gv_url.
%>
 window.location = '<%= lv_url %>';
</script>
<%-- <input type="text" id="dummy" name="dummy" value="aaa"> --%>
<chtmlb:configCellerator downloadToExcel       = "TRUE"
                         editMode              = "<%= lv_cellerator_editmode %>"
                         id                    = "ConfCellTable"
                         onRowSelection        = "select"
                         personalizable        = "TRUE"
                         selectedRowIndex      = "<%= BDGTHeader->SELECTED_INDEX %>"
                         selectedRowIndexTable = "<%= BDGTHeader->SELECTION_TAB %>"
                         selectionColumn       = "<%= lv_cellerator_selectioncolumn %>"
                         selectionMode         = "<%= lv_cellerator_selectionmode %>"
                         table                 = "//BDGTHeader/Table"
                         usage                 = "EDITLIST"
                         visibleFirstRow       = "<%= BDGTHeader->VISIBLE_FIRST_ROW_INDEX %>"
                         actions               = "<%= controller->gt_buttons %>"
                         visibleRowCount       = "6"
                         width                 = "100%"
                         xml                   = "<%= controller->configuration_descr->get_config_data( ) %>" />

Step 8: Execute the download functionality on WebUI screen, it will download the file in the .xlsx format. 

This brings the functionality to the end. I appreciate you taking the time to read this. I hope it is helpful for you. Please feel free to post your suggestions.