Skip to Content

Introduction
There have been recent discussion in the BSP forum on BSP and Excel. I would like to take this one step further and discuss how to do this same download, but with the data in Unicode.

The example code in this thread works great until you hit one situation. If you have a Unicode or MDMP SAP system and you are downloading data from more than one code page or from a different code page than the default codepage of the OS that the client is running, you will have a problem. The data stream that is sent to Excel will be encoded with the default codepage for the logged on language and it will then corrupt any characters from another code page.

Example
To give you a concrete example I will tell you about our system. We have English and Polish both installed in our system (using MDMP we have two code pages Latin-1 for English and Latin-2 for Polish). My Windows XP client is Unicode capable as is Microsoft Office. However your pc also has to work in a single non-Unicode code page when dealing with non-Unicode data. This is normally set by the country version of the OS you are running (In Windows XP you can switch this value in the Region Settings). My PC for instance is US based and therefore runs English (Latin-1) as its default codepage. I then run a BSP page logged on to the WebAS in Polish. I need to download a report with Material Descriptions that are in Polish. However the download from SAP by default will be in Polish (latin-2) and my PC and Excel won’t understand it since they are expecting English (Latin-1). As you can see from this screen shot, what I end up with is quite a mess. The BSP output is on the top and is formatted correctly. However the Excel display on the bottom has many incorrect and corrupted characters:
image

Looking at MS Excel
I would like to give you some code samples that will expand on those in the given Forum posting. I want to stress that these samples will work even in a non-Unicode SAP system. I have tested them on WebAS 620. The first thing we have to understand is what kind of Unicode file does MS Excel want. After doing a little research, I found out that Excel expects a UTF-16 encoded file (evidently all the Microsoft Office tools use UTF-16 when saving files in their primary format) with a byte order mark of Little Endian. If UTF-16 and Byte Order Mark (BOM) are unfamiliar, I suggest some light reading on the Unicode Home Page.

I wanted to verify this information I had found on the web. I opened Excel and typed in some Polish national characters. I then saved my spreadsheet as Unicode Text. This creates a tab-delimited text file in Unicode format. I then opened the file in a Hex Editor. Sure enough, there was my UTF-16le (Little Endian) Byte Order Mark – FF FE – right at the beginning of the of the file.

image

Possible BOM Values
For reference the following are the possible values for the Byte Order Mark:

Bytes Encoding Form
00 00 FE FF UTF-32, big-endian
FF FE 00 00 UTF-32, little-endian
FE FF UTF-16, big-endian
FF FE UTF-16, little-endian
EF BB BF UTF-8

ABAP Code Changes
Now we are ready to start making changes to the ABAP code from our BSP examples. The first thing we need to change is the application type string.
Old Code

APP_TYPE = 'APPLICATION/MSEXCEL'.

New Code

app_type = 'APPLICATION/MSEXCEL; charset=utf-16le'.

With this change we have now set our output character set to UTF-16 Little Endian. The call to SCMS_STRING_TO_XSTRING will now use UTF-16le as the destination format as it converts our source string to our output binary string:

call function 'SCMS_STRING_TO_XSTRING' 
       exporting text = l_string mimetype = app_type importing buffer = l_xstring.

For Reference the SAP internal Code page numbers are 4102 for utf-16be, 4103 for utf-16le, and 4110 for utf-8. These can be found in table TCP00A or by calling function module: SCP_CODEPAGE_BY_EXTERNAL_NAME.

That was actually quite easy. Now we have our output formatted as Unicode Text Tab Delimited. We just need to add the Byte Order Mark to the beginning of the binary string:

concatenate cl_abap_char_utilities=>byte_order_mark_little 
   l_xstring into l_xstring in byte mode.

Here we used the wonderful little class CL_ABAP_CHAR_UTILITIES. It already has the Byte Order Marks for UTF-8, UTF-16be, and UTF-16le defined for us.

Closing
As you can see in this final screen shot, out output in Excel now matches what we saw on the screen in BSP:
image

Full Code
Here is the complete code sample taken from the original forum posting and adjusted with the changes I have suggest here. Just in case the original forumn posting ever becomes unreachable. Special thanks to Suresh Babu for originally posting this code to the BSP forumn.

***ITAB contains my data so.. 
LOOP AT ITAB INTO WA.
     CONCATENATE L_STRING WA-PARTNER WA-ADR_KIND WA-ADDRNUMBER
     CL_ABAP_CHAR_UTILITIES=>CR_LF
     INTO L_STRING SEPARATED BY SPACE.
ENDLOOP.

APP_TYPE = 'APPLICATION/MSEXCEL; charset=utf-16le'.

call function 'SCMS_STRING_TO_XSTRING'
     exporting text = l_string
                     MIMETYPE = APP_TYPE
     IMPORTING BUFFER = l_xstring.
* Add the Byte Order Mark - UTF-16 Little Endian
concatenate cl_abap_char_utilities=>byte_order_mark_little l_xstring
              into l_xstring in byte mode.

response->set_header_field( name = 'content-type' value = APP_TYPE ).
* some Browsers have caching problems when loading Excel Format
response->delete_header_field( name = if_http_header_fields=>cache_control ).
response->delete_header_field( name = if_http_header_fields=>expires ).
response->delete_header_field( name = if_http_header_fields=>pragma ).
* start Excel viewer either in the Browser or as a separate window
response->set_header_field( name = 'content-disposition' value = 'attachment; filename=webforms.xls' ).
* finally display Excel format in Browser
l_len = xstrlen( l_xstring ).
response->set_data( data = l_xstring length = l_len ).
navigation->response_complete( ).
To report this post you need to login first.

39 Comments

You must be Logged on to comment or reply to a post.

  1. Massimo Boccali
    Hi, thanks for the code: it works but I have a little problem.

    I have a BSP page with a button that I called “Download to Excel”. When I press the button, the event is captured by the OnInputProcessing of the Event Handler and the code for downloading an Excel file is executed in the right way. A window call “File Download” will open with 3 choises: OPEN – SAVE – CANCEL.
    And now the problem: whatever choise I do, in the Status Bar of the starting page (the page with the button) remains in “Waiting for server response…”. The problem is that I can’t use any other buttons in the page, nor the same “Download to Excel” button!

    Another little thing: I put the code in the OnInputProcessing and when I tried to activate the page, an error occurred: I had to change the word “response” with “_m_response”  
    and “navigation” with “_m_navigation”. I don’t know what exactly they mean, but only in this way the page can be activated. These words were suggested me by the compiler.

    Please, help me. Thanks in advance.
    Massimo

    (0) 
    1. Thomas Jung
      I’m a little confused by the situation you describe.  You said that you were in the event handler OnInputProcessing, but you don’t have a response or navigation object.  In my system when I go into the OnInputProcessing Event Handler and turn on Signature, I see the following parameters:

      @78QImporting@     PAGE_CONTEXT     TYPE REF TO IF_BSP_PAGE_CONTEXT
      @78QImporting@     PAGE     TYPE REF TO IF_BSP_PAGE
      @78QImporting@     REQUEST     TYPE REF TO IF_HTTP_REQUEST
      @78QImporting@     NAVIGATION     TYPE REF TO IF_BSP_NAVIGATION
      @78QImporting@     EVENT_ID     TYPE STRING

      As to why you are getting Waiting for server response, I suspect that the navigation->response_complete isn’t getting properly set.  I suggest however if you are wanting to implement this solution, you have a look at another weblog: Creating a BSP Extension for Downloading a Table .  This weblog implements this functionality as a custom BSP extension.  I also implement the delivery of the content differently.  I place the content into the ICM cache and then insert the URL to the cached object into an IFrame.  I like this way of delivery, because it doesn’t disrupt the flow of the hosting page.

      (0) 
      1. R Hemachandran

        Hi Thomas,

        I am facing one problem, while downloading excel from BSP.

        We are download some date to excel by using the class

           CALL METHOD cl_abap_conv_out_ce=>create
                    EXPORTING
                      encoding = ‘UTF-8’
                    RECEIVING
                      conv     = lo_utf8.

                    CALL METHOD lo_utf8->write
                    EXPORTING
                      data = lv_string.

        Every thing is fine but for some special character its not displaying properly. please find the below example

        text = Chief Operating Officer – CIS

        after down load to excel the special character is changed as mentioned below.

        Chief Operating Officer – CIS

        Is any way is there to solve this problem its very urgent Thanks in advance.

        Thanks & Regards,

        Hema

        (0) 
        1. Thomas Jung Post author

          You are using UTF-8 for your encoding.  In this blog I disucess how to use UTF-16LE for the format Excel expects.  You don’t seem to be using the concept in this blog, so I’m not sure how to assist you.

          (0) 
  2. Sumith Rajagopalan
    During the migration process problems have been reported for this blog. The blog content may look corrupt due to not supported HTML code on this platform. Please adjust the blog content manually before moving it to an official community.
    (0) 
  3. Sumith Rajagopalan
    Hi,
    I tried to implement this logic and it is working partly.  The problem i’m facing is, when I test this code.. a IE window opens and another popup comes with options for Save,Open & Cancel.  The same window displays the file name, type & application.  But what is displayed is not the xls filename i’m sending, but my bsp page name.  And it is also not recognising the file type.

    I have tried the enhancements that Mr.Thomas Jung has suggested.  But the response is the same.

    How can i make the browser understand the type of the file sent and the name?

    Regards,
    Sumith

    (0) 
    1. Thomas Jung

      I would suspect that there is a problem with the redirection of the response to the Excel File Content.  Where did you implement the logic to send the excel content back to the response object?  (MVC or Page Event – What event?).  Also you might share you code here.  That might help determine what is wrong.  Like I have said before, I really prefer the method of using an IFrame to download the content.  I have never any problems with that approach.  Have you had a look at the later weblog with the custom BSP element that does this.  It actually has more functionality than just an Excel file.  The following is a little code sample of this:<br/><br/>***The user has selected to download the data to MS Excel<br/>  if event_id cs ‘DOWNLOAD’.<br/>    data: l_content type string.<br/><br/>**Build the output table as Text Tab Delimited<br/>    l_content = model1->download_output_table2( ).<br/>    data: s_length type string.<br/>    s_length = strlen( l_content ).<br/><br/>**Build the Cached Response Object<br/>    data: cached_response type ref to if_http_response.<br/>    create object cached_response type cl_http_response exporting add_c_msg = 1.<br/><br/>**Set the data and the header parameters into the cached response object<br/>    cached_response->set_cdata( l_content ).<br/>    cached_response->set_header_field( name  = if_http_header_fields=>content_type<br/>                                       value = ‘application/msexcel’ ).<br/>    cached_response->set_header_field( name  = ‘Content-Disposition'<br/>                                value = ‘attachment; filename=GCS_Changes_by_Date.xls’ ).<br/>    cached_response->set_status( code = 200 reason = ‘OK’ ).<br/>    cached_response->server_cache_expire_rel( expires_rel = 60 ).<br/><br/>**Build a Unique URL<br/>    data: guid type guid_32.<br/>    call function ‘GUID_CREATE'<br/>      importing<br/>        ev_guid_32 = guid.<br/>    concatenate runtime->application_url ‘/’ guid into appl->display_url.<br/><br/>**Set the Unique URL into the Cache<br/>    cl_http_server=>server_cache_upload( url      = appl->display_url<br/>                                         response = cached_response ).<br/>    return.<br/><br/>  endif.<br/><br/><br/>***This creates the content and puts it into the ICM cache.  Next in our Page we include the IView if we have a link to the ICM cache object.<br/><br/> <%<br/>  IF application->display_url IS NOT INITIAL.<br/>    %><br/>    <iframe src=”<%= application->display_url%>” width=”100%” height=”1px”><br/>          </iframe><br/>    <%<br/>  clear application->display_url.<br/>  ENDIF.<br/>    %><br/>

      (0) 
      1. Sumith Rajagopalan
        Well Thomas, I had seen ur and Suresh’s articles on download and wanted to test them.  So i created a BSP application with flow logic and copied the code that that suresh had given in the OnInitialisation Event.  So there is nothing in the layout or in other Events.  Since I faced this problem,I modified the code according to your suggestions, but that too didn’t help.  Here is the code that I am using.

        data: flights type flighttab,
              flight  like line of flights,
              output  type string,
              app_type type string ,
              l_xstring type xstring ,
              l_len type i .

          constants: crlf type string value cl_abap_char_utilities=>cr_lf,
                     tab  type string value
                         cl_abap_char_utilities=>horizontal_tab.

          select * from sflight into table flights up to 20 rows.

          loop at flights into flight.
            data: seatsmax type string.
            seatsmax = flight-seatsmax.
            condense seatsmax.
            data: seatsocc type string.
            seatsocc = flight-seatsocc.
            condense seatsocc.

            concatenate output
                        flight-carrid     tab
                        flight-connid     tab
                        flight-fldate     tab
                        flight-planetype  tab
                        seatsmax          tab
                        seatsocc          tab
                        crlf
                   into output.
          endloop.

        app_type = ‘APPLICATION/MSEXCEL;charset=utf-16le’.

        call function ‘SCMS_STRING_TO_XSTRING’
            exporting
              text = output
              mimetype = ‘APPLICATION/MSEXCEL’
            importing
              buffer = l_xstring.

        * Add the Byte Order Mark – UTF-16 Little Endian
          concatenate  cl_abap_char_utilities=>byte_order_mark_little
                       l_xstring
                       into l_xstring in byte mode.

        response->set_header_field(
          name = ‘content-type’
          value = ‘APPLICATION/MSEXCEL; charset=utf-16le’ ).

        * some Browsers have caching problems when loading Excel format
          response->delete_header_field( name =
                  if_http_header_fields=>cache_control ).
          response->delete_header_field( name =
                if_http_header_fields=>expires ).
          response->delete_header_field( name = if_http_header_fields=>pragma ).

        * start Excel viewer either in the Browser or as a separate window
        *  response->set_header_field(
        *    name  = if_http_header_fields=>content_type
        *    value = ‘application/msexcel’ ).
          response->set_header_field(
            name  = ‘content_disposition’
            value = ‘attachment;filename=webforms.xls’ ).

        ** finally display Excel format in Browser
        *  response->set_cdata( data = output ).

        * finally display Excel format in Browser
          l_len = xstrlen( l_xstring ).
          response->set_data( data = l_xstring
                              length = l_len ).
        * do not process Layout, response has been
        * rendered
          navigation->response_complete( ).

        Another thing that I noticed is if I give the app_type (ie. with the charset defined) in the String to XString conversion, the function fails.

        I hope the above code explains what I’m doing.

        Regards,
        Sumith

        (0) 
        1. Thomas Jung
          It appears to be a simple typo in your code.

          You had:
          response->set_header_field(
          name = ‘content_disposition’
          value = ‘attachment;filename=webforms.xls’ ).

          The correct code is:
            response->set_header_field(
                name = ‘content-disposition’
                value = ‘attachment;
                filename=webforms.xls’ ).

          Please note that is is a dash not an underscore in the content-disposition header field name.

          (0) 
          1. Sumith Rajagopalan
            Hey thanks Thomas.  It did solve the problem, even though my output had some corrupt data. 
            I wanted to know, where can I find the information regarding the various header fields and their probable values for html.  Like the ‘content’disposition’, ‘cache control’, etc.

            Anyway thanks a lot.

            (0) 
          2. venkat yelagandula
            Thomas,
            My am getting a corrupted excel out,

            If we check out the following code

            call function ‘SCMS_STRING_TO_XSTRING’
            exporting
            text = output
            mimetype = ‘APPLICATION/MSEXCEL’
            importing
            buffer = l_xstring.

            where importing parameter mimetype is type of type C and we are passing a string.Is this the reason why my file is corrupted.

            Regards,
            Venkat

            (0) 
            1. Thomas Jung Post author
              What kind of corruption are you seeing?  Have you looked at the binary string in the debugger after the translation?  This is a function that I use quite often and I have never seen it “corrupt” data.  I really need to know more details about what you are seeing.
              (0) 
  4. Hi,

    I’m trying to implement this, but my compiler doesn’t like the “response” methods. Basically, it says “The field “RESPONSE” is unknown, but there’s a similar field “_M_RESPONSE”. Is this due to my WAS (6.20, SP 44) or have I (or you) forgot to include something in the code…?

    Trond

    (0) 
    1. Thomas Jung
      I have had this comment before from someone that said they didn’t have the response field.  Are you doing this event handling from within a Controller or a Page.  If you are in a controller you should inherit RESPONSE through your Class -> CL_BSP_CONTROLLER2 -> CL_BSP_CONTROLLER.

      Now in my system in a regular Page, in  OnInputProcessing I can look at the signature and I see the following:
      @78\QImporting@     RUNTIME     TYPE REF TO IF_BSP_RUNTIME
      @78\QImporting@     APPLICATION     TYPE REF TO ZCL_EM_ECO_GLOBAL_INFO
      @78\QImporting@     PAGE_CONTEXT     TYPE REF TO IF_BSP_PAGE_CONTEXT
      @78\QImporting@     PAGE     TYPE REF TO IF_BSP_PAGE
      @78\QImporting@     REQUEST     TYPE REF TO IF_HTTP_REQUEST
      @78\QImporting@     NAVIGATION     TYPE REF TO IF_BSP_NAVIGATION
      @78\QImporting@     EVENT_ID     TYPE STRING

      (0) 
      1. Thomas Jung
        Sorry didn’t get to finish my last post…

        So I suspect you are doing this in Page and not in a controller.  It is true that the original sample event code posted on the forum was written for a controller.  I don’t do much with pages (only MVC) so I would have to play with it to see if _m_reponse is the same in the page, but I suspect that it is. 

        On the other hand you might look at the following weblog:
        Creating a BSP Extension for Downloading a Table

        I use a cached response and pass the URL for this cached response back to my calling program.  I then display the cached response in an IFrame.  I have had good luck with this approach. 

        (0) 
        1. Mohammed Atwan
          Well … I’m replying a couple of months late for this one; I’m sure people have found the answer somewhere else. But for people who just stumbled upon this blog and if you are using pages without controllers (like myself) then you need to put this code in the “OnInitialization” handler, not the “OnInputProcessing”. The response object is available in “OnInitialization”.

          What I do is set a variable like lv_download_requested = ‘X’ in “OnInputProcessing” and then test against it in “OnInitialization”.

          Everything worked great for me and I use pages only.

          Regards,
          Mohammed

          (0) 
          1. Thomas Jung Post author
            That seems like a perfectly fine solution.  You might also be able to access the response object from the runtime->server reference.  Although I’m not sure what state it is in at that point.

            As a matter of personal preference, I really like the cached response approach however. I just feel like it provides a cleaner flow to the processing.

            (0) 
  5. Samson Samuel
    I bought the book “Advanced BSP Programming” and I tried the download to  xl code, the exact way as it is given in the book. But When I execute the code, I’m neither getting the xl to open nor am I seeing the file on my pc. All I’m getting is an error message saying that the page can not be displayed with an HTTP error number 500.
    Cany anybody help..?.. Thanks …Sam
    (Here’s the code)
    ***ITAB contains my data so..
    LOOP AT ITAB INTO WA.
       CONCATENATE L_STRING WA-PARTNER
       WA-ADR_KIND
       WA-ADDRNUMBER
       CL_ABAP_CHAR_UTILITIES=>CR_LF INTO L_STRING SEPARATED BY SPACE.
    ENDLOOP.

    APP_TYPE = ‘APPLICATION/MSEXCEL; charset=utf-16le’.

    call function ‘SCMS_STRING_TO_XSTRING’
        exporting
          text = l_string
          MIMETYPE = APP_TYPE
        IMPORTING
          BUFFER = l_xstring.

    * Add the Byte Order Mark – UTF-16 Little Endian
      concatenate  cl_abap_char_utilities=>byte_order_mark_little
                   l_xstring
                   into l_xstring in byte mode.

    response->set_header_field( name = ‘content-type’ value = APP_TYPE ).

    * some Browsers have caching problems when loading Excel Format
      response->delete_header_field( name = if_http_header_fields=>cache_control ).

      response->delete_header_field( name = if_http_header_fields=>expires ).

      response->delete_header_field( name = if_http_header_fields=>pragma ).

    * start Excel viewer either in the Browser or as a separate window

      response->set_header_field( name = ‘content-disposition’
                                  value = ‘attachment;
                                  filename=webforms.xls’ ).
    * finally display Excel format in Browser
      l_len = xstrlen( l_xstring ).
      response->set_data( data = l_xstring
                          length = l_len ).
      navigation->response_complete( ).

    (0) 
    1. Thomas Jung Post author
      First of all, thanks for buying the book.  My daughters’ college education fund especially appreciates it. 🙂  Hope you enjoy.

      First thing you want to do is turn off “Friendly HTTP Error Messages” so that you can get more details about any BSP Page error than just HTTP 500.  The steps are:
      In IE: Tools->Internet Options->Advanced Tab->Uncheck “Show Friendly HTTP Error Messages”

      Right off the bat, I don’t see anything that stands out as wrong in your coding.  We will probably need more detailed error information. In addition to turning off the “friendly” HTTP errors, you might also check transaction ST22 and see if the page produced an ABAP Dump.  That would also have lots more details about the problem.

      (0) 
  6. Thomas Schröder
    Hi. Great and easy to use WL.
    I got only one little problem:
    There is always a “Wait..your request is processed” DIV.
    I have many other events handled for this site and everything works fine. Always start at the top of controller hierarchy and than to the child, etc.
    But the event to download an excel file seems to finished incorrectly. I tried to outcomment the “navigation->response_complete( ).” But makes no difference.
    I’ve no ideas anymore, may be you got one.

    regards Thomas Schröder

    (0) 
    1. Thomas Jung Post author
      That is a problem with sending the content back as the complete response.  After the download is finished, it doesn’t refresh the original page. 

      That is why I like the cached response approach to downloading much better. You take your content and you cache a separate response object into the ICM. Then you load this cached response via a 1x1px iFrame in your page. The content popup for save/open looks the same to the user, but you haven’t disrupted the request/response cycle of the main page.  For an example of this, check out this weblog:
      Creating a BSP Extension for Downloading a Table

      (0) 
      1. Thomas Schröder
        Thx a lot.
        i had the iframe with document-flowlogic and request the application and get the controller class i need. In the method do_handle_event i build a html table and put this to a global variable (again in the iframe) i just start a new response. Works fine.
        (0) 
      2. Ankit Gupta
        Thanks for such a wonderful Blog.

        We have 2 issues.

        1. While downloading the table into excel. Whole table gets properly downloaded but additional junk line is added in the last. I dont know why it is giving like that. I am using SAP ECC 6.0 and excel 2003.

        2. We have written and tried all these things but still the never ending popup is coming. Just check the code below :

        * Set the application Type – character set for conversion from string to xstring
        data: lapp_type type char30.
        lapp_type = ‘text/unicode; charset=utf-16le’.

        * Convert the string to Binary string
        CALL FUNCTION ‘SCMS_STRING_TO_XSTRING’
        exporting
        text = l_string
        mimetype = lapp_type
        importing
        buffer = l_xstring.

        * Add the UTF-16 Little Endian Byte Order Mark to the begining of the file
        concatenate cl_abap_char_utilities=>byte_order_mark_little l_xstring
        into l_xstring in byte mode.

        * set parameters
        runtime->server->response->set_header_field( name = ‘Content-Type’ value = ‘APPLICATION/MSEXCEL; charset=utf-16le’ ).
        runtime->server->response->delete_header_field( name = if_http_header_fields=>cache_control ).
        runtime->server->response->delete_header_field( name = if_http_header_fields=>expires ).
        runtime->server->response->delete_header_field( name = if_http_header_fields=>pragma ).
        runtime->server->response->set_header_field( name = ‘Content-Disposition’ value = ‘attachment; filename=xloutput.xls’ ).
        content_length = xstrlen( l_xstring ).
        runtime->server->response->set_data( data = l_xstring length = content_length ).
        navigation->response_complete( ).
        * return.

        ***********************************************************************************
        data: cached_response type ref to if_http_response.
        create object cached_response type cl_http_response exporting add_c_msg = 1.

        * ****Create a unique URL for the object

        ****set the data and the headers
        cached_response->set_data( l_xstring ).
        cached_response->set_header_field( name = if_http_header_fields=>content_type
        value = ‘APPLICATION/MSEXCEL; charset=utf-16le’ ).

        ****Set the filename into the response header
        cached_response->set_header_field( name = ‘Content-Disposition’
        value = ‘attachment;filename=xloutput.xls’ ).

        ****Set the Response Status
        cached_response->set_status( code = 200 reason = ‘OK’ ).

        ****Set the Cache Timeout – 60 seconds – we only need this in the cache
        ****long enough to build the page and allow the IFrame on the Client to request it.
        cached_response->server_cache_expire_rel( expires_rel = 60 ).

        ***Create a unique URL for the object
        data: guid type guid_32.
        data : url type string.
        call function ‘GUID_CREATE’
        importing
        ev_guid_32 = guid.
        concatenate runtime->application_url ‘/’ guid into url.

        ****Cache the URL
        cl_http_server=>server_cache_upload( url = url
        response = cached_response ).

        call function ‘GUID_CREATE’
        importing
        ev_guid_32 = guid.
        concatenate runtime->application_url ‘/’ guid into url.

        ****Get Link URL if it exists
        DATA html TYPE string.
        .
        DATA: prev_out TYPE REF TO cl_bsp_writer.

        CREATE OBJECT prev_out
        EXPORTING
        previous_writer = prev_out.

        * DATA: url_string TYPE string.

        * IF display_url IS NOT INITIAL.
        * MOVE display_url TO url_string.
        CONCATENATE html
        ``
        INTO html.
        * prev_out = me->get_previous_writer( ).

        prev_out->get_previous_writer( ).
        prev_out->print_string( html ).
        CLEAR html.
        CLEAR url.
        * ENDIF.

        (0) 
        1. Thomas Jung Post author
          1.  Difficult to say where that is coming from without debugging. I would look at the string before placing it in the ICM cache and see if there is anything at the end of it.

          2.  My guess, you aren’t clearing the variable being passed into display_url.

          (0) 
  7. Hello
    I used your method to download data into excel format. but some of the columns data are coming in different format. I have to manually change the cell format in the excel to see the data. Is there any way to pass the cell format information during download to excel process??

    Regards
    Saurabh Garg

    (0) 
    1. Thomas Jung Post author
      Not with Text Tab Delimited.  There simply isn’t anything built into that format to support it.  On the other hand it is extremely easy to work with.  If you want more formatting options you would have to switch to something like the Excel XML format. That is what is used by SAP in the Web Dynpro ALV exporter.  It is more complex, but can be done from ABAP.  Check for documentation on Microsoft’s website or just take a speadsheet and do a save as XML and view the results.  The only problem is that with the XML format you would only be able to support Office 2003 and higher.
      (0) 
  8. Martin Ward
    I have used this excellent weblog to embed an Excel version of my data in a BSP page.

    When I click on the button, I get prompted to open/save the Excel File. However, if I open the file, there are no excel toolbars, the only way that I can ‘manipulate’ the data is to Save the file first.

    I have used the following code to create the page content after creating the required XML string.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    response->set_header_field(
    name = if_http_header_fields=>content_type
    value = ‘APPLICATION/XLS; charset=utf-16le’ ).

    * some browsers have caching problems when loading excel format
    response->delete_header_field( name = if_http_header_fields=>cache_control ).

    response->delete_header_field( name = if_http_header_fields=>expires ).

    response->delete_header_field( name = if_http_header_fields=>pragma ).

    * start excel viewer either in the browser or as a separate window

    response->set_header_field( name = ‘content-disposition’
                                value = ‘content; filename=CASAPReport.xls’ ).

    * finally display excel format in browser
    nlen = xstrlen( xexcel ).

    response->set_data( data = xexcel
                        length = nlen ).
    navigation->response_complete( ).

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Is there a way of adding hte toolbars?

    Martin

    (0) 
    1. Thomas Jung Post author
      There is nothing you are doing on the BSP side that is controlling Excel directly.  You are just pushing content through the browser and the file extension on the content-disposition is what your PC uses to determine the application that should be opened in response.

      I have to think that this is a setting or setup issue on your PC.  I’ve never seen Excel open off of such a download wihtout the toolbars and I wouldn’t know how you could even make it do that with this approach.

      (0) 
  9. Katrin Regener

    Hi,* SAP –


    ><br/>  response->delete_header_field( name = if_http_header_fields=>cache_control ).<br/>  response->delete_header_field( name = if_http_header_fields=>expires ).<br/>  response->delete_header_field( name = if_http_header_fields=>pragma ).<br/>

    (0) 
    1. Thomas Jung Post author
      At the point hwere you do the STRING to XSTRING conversion are you running in a session that is logged on in Bulgarian?  You would have to be in the proper code page at the time you do the conversion. In my example, the user my BSP application is logged on in Polish.

      In another blog I show how you can mix codepages using a similar technique. In this case I did my conversions via RFC and setup RFC destinations to my system that are in the logon language of the data I am reading.  This way if I am logged on in English I can RFC to the system in Bulgarian or Polish in my case, lookup the Polish texts, and convert them to UTF-16 (Unicode).  I then just pass the Unicode byte string back to my English session, thereby preserving the original characters.

      In your source code you provide, you seem to be doing the conversion in line. For this to work you would have to be logged on in Bulgarian.

      (0) 
      1. Katrin Regener
        Hi Thomas,

        first of all, thank you very much for your fast reply!

        While looking through my code, I found another error. (I didn’t write this myself, so I missed it before…)
        In the part, where I convert the header to xml, add the BOM and then add the code to the response, it is not the XML String, that is added, but the Character String. So basically the BOM is missing. I changed this and voila, Excel shows me bulgarian letters. Only now, Excel no longer recognizes the semicolon, that I used to separate cells as a separater and instead writes the whole line in one cell. Do you know if there is some issue with the BOM, that confuses CSV? How can I tell Excel to recognize the semicolon as a separator?

        Also, the session I am running is indeed German. Do you still think this might be an issue, even though Excel showed bulgarian letters?

        I appreciate your help!

        Regards
        Katrin

        (0) 
        1. Thomas Jung Post author
          I never use semicolon delimited files as that means that you need to escape characters within your string as well. I also use Tab-delimited and have never had a problem with it.
          (0) 
  10. suresh T
    Hi Thomas,

    Your Blogs are excellent and they helped me in resolving many issues for sending email (Email with more than 255 char in attachment and using cl_bcs class).
    But my problem is I am sending an Excel attachment with content in different languages.
    For ex a user name can be in Japanese,Chinese,Dutch,German or israel.
    Also same with SO item text, it can be in any of the above mentioned languages.
    Please help me how I can send Excel attachment with different languages.

    I have written code like this:

    * To send email with more than 255 char length convert the data
    LOOP AT p_it_attachment INTO is_attachment.
    * Convert from string to hex
    CALL FUNCTION ‘SCMS_STRING_TO_XSTRING’
    EXPORTING
    text = is_attachment-line
    mimetype = ‘APPLICATION/MSEXCEL;charset=utf-16le’
    IMPORTING
    buffer = l_data
    EXCEPTIONS
    failed = 1
    OTHERS = 2.
    IF sy-subrc <> 0.
    MESSAGE i999 WITH text-054.
    ENDIF.

    concatenate cl_abap_char_utilities=>byte_order_mark_little
    l_data
    into l_data in byte mode.

    *Convert from hex to binary
    CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’
    EXPORTING
    buffer = l_data
    TABLES
    binary_tab = it_bin_tab.
    APPEND LINES OF it_bin_tab TO it_hex_tab.
    CLEAR: l_data.
    REFRESH it_bin_tab.
    ENDLOOP.

    Example text is G“�•”•iŠÇ—�#65860 ’†”nŒõ‹`—l.
    In Excel attachment I am getting entire data in chinese or japanese text.

    (0) 
    1. Thomas Jung Post author
      Is your system Unicode or Non-Unicode?  You will only be able to mix languages from different code pages if your system is Unicode.  I suspect based upon what you describe that your ABAP system is not Unicode.
      (0) 
      1. suresh T
        Hi Thomas,
        Thanks for the quick reply. I am working on Unicode enabled system only.I tried using UTF-8,UTF-16le AND UTF-16be but nothing works.
        Also if I use BOM entire data in Excel is displayed as long chain of Chinese or Japanese characters.
        If possible could you please provide some code which supports multiple languages in the Excel attachment.

        (0) 
      2. suresh T
        Hi Thomas,

        Finally I found the problem. Problem is in my code itself especially with the loop statement.
        I tried to convert each line in the attachment tab which I should not do.I need to concatenate all lines in the attachment to one line and then convert from string to hex and hex to binary.
        Now the problem is resolved and thanks for all your support.

        bye..
        Suresh.

        (0) 
  11. Anita Barrer

    Hi Thomas,

    My case is little diferent, isn’t a bsp, but I need to generate a excel to attach a ticket of CRM. For this, first i separet by # the cells:

    CONCATENATE lv_string xls_tabvalue

          cl_abap_char_utilities=>horizontal_tab “cl_abap_char_utilities=>cr_lf

          INTO lv_string SEPARATED BY space.

    Then, at end of row, separete the rows:

    CONCATENATE cl_abap_char_utilities=>NEWLINE

                       lv_string INTO lv_string.

    And then, I convert from string to xstring with UTF-16le (because there are diferents language “chinese, english, cirylic, etc..) like you.

    CALL FUNCTION ‘SCMS_STRING_TO_XSTRING’

             EXPORTING

               text     = lv_string

               mimetype = ‘APPLICATION/MSEXCEL; charset=utf-16le’

             IMPORTING

               buffer   = v_xstring.

    CONCATENATE cl_abap_char_utilities=>BYTE_ORDER_MARK_LITTLE

                       v_xstring  INTO v_xstring IN BYTE MODE.


    And finally i convert in binary code, for excel:

    CALL FUNCTION ‘SCMS_XSTRING_TO_BINARY’

             EXPORTING

               buffer          = v_xstring

               append_to_table = ‘X’          

             TABLES

               binary_tab      = binary_tab.

    But there are 2 issues:

    1. exist a symbol (‘?’) to final each row, until last row. I don’t know why…. This symbol is an interrogant.

    2. Exist one row empty initially.

    Could you help please? I’m lost…

    +

    Thanks

    (0) 

Leave a Reply