27 Replies Latest reply: Jun 18, 2008 6:29 AM by Sonia Agarwal RSS

BSP and Excel

Suresh Babu
Currently Being Moderated

Hi All,

I have a specific requirement ....

 

I want to display data that is in an internal table on to thye Browser using BSP. I have done it using PDF format using Smartforms, but Excel..I am not sure..

 

Can anyone help....

 

thanks,

Suresh

  • Re: BSP and Excel
    Craig Cmehil
    Currently Being Moderated

    Hello Suresh,

     

    I've not tried anything with excel and BSP's before but I do remember reading about here in fact quite often. You should try a search in this forum for "Excel" here is what it returned for me, perhaps these will help you get started enough that you might be able to post a specific problem you are having later...

     

    Export to excel

     

    EXCEL reading through BSP application

     

    As for displaying a table in a BSP whether it is internal or not defiently check out the tableView.bsp example in se80 (BSP Application for HTMLB) then check out Brian McKellar's WebLog <a href="/people/brian.mckellar/blog/2003/10/31/bsp-programming-htmlb-tableview-iterator">BSP Programming: HTMLB TableView Iterator</a>

     

    Once you've started and come across a snag or otherwise strange problem give a shout back and I'm sure you'll be able to catch a few people online who are happy to give a hand.

  • Re: BSP and Excel
    KUMAR RAMASAMY
    Currently Being Moderated

    Hi Suresh,

         you can upload the data from the Excel into BSP application in the following ways.

     

      1. In the application use file upload to upload the file.

     

      2. caputure the content and length

     

        DATA: data TYPE REF TO CL_HTMLB_FILEUPLOAD.

        data ?= CL_HTMLB_MANAGER=>GET_DATA(

    request      = runtime->server->request

    name         = 'fileUpload'

    id           = 'xx').

     

          name         = data->file_name.

          content      = data->file_content.

          length       = data->file_length.

          content_type = data->file_content_type.

     

    3. Then read the data using the class

     

      DATA: conv TYPE REF TO CL_ABAP_CONV_IN_CE.

      conv = CL_ABAP_CONV_IN_CE=>CREATE( input = CONTENT ).

      conv->READ( importing data = Variable len = length ).

     

     

    Hope this solves your purpose.

     

    Cheers,

    Kumar

    • Re: BSP and Excel
      Suresh Babu
      Currently Being Moderated

      Hi Kumar, My task was the other way. I wanted to bring the data in an internal table to the ecel sheet through BSP. I have achieved this using 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'.

       

       

      call function 'SCMS_STRING_TO_XSTRING'

        exporting

          text           = l_string

         MIMETYPE        = APP_TYPE 

      IMPORTING

         BUFFER          = l_xstring

      • EXCEPTIONS

      •   FAILED         = 1

      •   OTHERS         = 2

       

      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( ).

       

       

      this worked for me. If you want to format the cells also you have to use horizontal lines in the CHAR Utilities class.

       

      Regards,

      Suresh

      • Re: BSP and Excel
        Brian McKellar
        Currently Being Moderated

        Hallo Suresh ,

         

        Can i use this example in <a href="/people/brian.mckellar/blog/2003/10/23/bsp-trouble-shooting-frequently-asked-short-questions">FAQ</a>? It is now a question that has been asked a number of times, and is definitely required in FAQ. I hope that this carries your approval!

        thanks, brian

        • Re: BSP and Excel
          Suresh Babu
          Currently Being Moderated

          Hi Brian,

           

          Anything for you go ahead and add it. In fact part of the information taken from your great weblogs. I am greatly honoured.

          Thanks,

          Suresh

        • Re: BSP and Excel
          Janice Ishee
          Currently Being Moderated

          Brian and Suresh,

           

          I have implemented your code to download to Excel in my BSP application and I agree with Brian -- it works great!  Quite straightforward.  I am experiencing something that perhaps you can help with.  When the internal table is coming up in Excel, I get the same IE message twice entitled "File Download" and asking me if I want to open the file or save it.  Have you or anybody experienced this?  Do you know why that might be?  I am running my BSP in Internet Explorer.

           

          Thanks

          Janice

      • Re: BSP and Excel
        Trond Stroemme
        Currently Being Moderated

        Hi,

         

        this doesn't seem to work; I'm getting compiler errors saying "The field RESPONSE is unknown, but there is a field with the similar name MRESPONSE".

         

        Any idea what I need to do here?

         

        Trond

      • Re: BSP and Excel
        ANTHONY TRIPP
        Currently Being Moderated

        Hi Suresh,

        Can you tell me what class definition I need to load in order to use "Response".  I keep getting an error saying field "response" is unknown and I've been adding different definitinos for a while now and none of them seem to work.

         

        Thanks,

        Anthony

      • Re: BSP and Excel
        Sonia Agarwal
        Currently Being Moderated

        Hi Suresh,

         

        I tried executing your code to transfer an internal table to Excel, But i am getting some runtime Error as:

         

        Following error text processed in system:

        An exception with the type CX_SY_REF_IS_INITIAL occurred, but was neither handled locally, nor declared in a RAISING clause

         

        Please help me how to correct this error.

         

        Thanks ...

  • Re: BSP and Excel
    Stefan Riedel-Seifert
    Currently Being Moderated

    Hi,

     

    in my opinion it is much easier to call excel as a control. You have only to supply the data via cs format.

     

    This snippet should work:

     

    <HTML>
      <HEAD>
        <TITLE>Web-Based Excel Data</TITLE>
    <SCRIPT language=JavaScript type=text/javascript>
    <!--
    
      
    
    // ***************************************************** EXCEL RUNTIME
    //   before submitting the form save the user input
    //   from sheet to the form (hidden) fields
    
      // class InputRange - constructor
      function InputRange(layout_id, sheet,top,left,bottom,right,isText) {
      //  set local variables
          this.name    = layout_id;
          this.top     = top;
          this.left    = left;
          this.bottom  = bottom;
          this.right   = right;
          this.sheet   = sheet;
          this.isText  = isText;
          this.store   = inputRange_store; // methods
      }
      function inputRange_store() {
        var theForm = document.forms("bspform");
        with (this) {
          var fname = name + "-" + top + "-" + left + "-" + bottom + "-" + right;
          if (isText) {
            // we must resolve non formated values. The only posibility is via loop:
            var tsv = ""; // tab separated values
            for (var row = top; row <= bottom; row++)
              for (var col = left; col <= right; col++)
                tsv += sheet.Cells(row,col) + "t";
            theForm.elements(fname).value = tsv;
          }
          else {
            // work throwgh clipboard (Microsoft doesn't provide other method to read range)
            sheet.Range(sheet.Cells(top,left),sheet.Cells(bottom,right)).Copy();
            theForm.elements(fname).value = clipboardData.getData("Text");
          }
        }
      }
      // endclass InputRange
    
      // ARRAY that holds inputRanges descriptions
      var inputCollection = new Array();
    
      // loops over the inputCollection (if it contains input any entry)
      function processLayoutInput() {
        if (inputCollection.length) {
          // remember the text from clipboard
          var clipBoardStorage = clipboardData.getData("Text");
          for (var i = 0; i < inputCollection.length; i++) {
            inputCollection<i>.store();
          }
          clipboardData.setData("Text",clipBoardStorage);
        }
      }
    
    // *********************************************** end of excel runtime
    //-->
    </SCRIPT>
    </HEAD>
    <BODY >
    
      <OBJECT id=Layout_1 
              style="WIDTH: 1200px; HEIGHT: 700px" 
              classid=CLSID:0002E510-0000-0000-C000-000000000046><PARAM NAME="DisplayTitleBar" VALUE="false"><PARAM NAME="HTMLData" VALUE=" 
    
    "></OBJECT></TD></TR></TBODY></TABLE><!-- non visual code for layout Layout_1 --><INPUT 
                            type=hidden name=Layout_1-detail> <!-- detail selection --><INPUT type=hidden 
                            name=Layout_1-detail_size> <INPUT type=hidden 
                            name=Layout_1-5-2-8-8> <INPUT type=hidden 
                            name=Layout_1-10-2-17-8> <INPUT type=hidden 
                            name=Layout_1-19-2-26-8> <INPUT type=hidden 
                            name=Layout_1-29-2-34-8> <INPUT type=hidden 
                            name=Layout_1-38-2-38-8> <INPUT type=hidden 
                            name=Layout_1-40-2-50-8>
                            <SCRIPT language=JavaScript type=text/javascript>
    <!--
    
      layout_sheet = document.all( "Layout_1" );
      layout_sheet.ActiveSheet.Protection.Enabled = false;
    
    // ****************************************************** remember input ranges
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,5 ,2 ,8 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,10 ,2 ,17 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,19 ,2 ,26 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,29 ,2 ,34 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,38 ,2 ,38 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
        inputRange = new InputRange("Layout_1",layout_sheet,40 ,2 ,50 ,8 ,false);
        inputCollection = inputCollection.concat(inputRange);
     //
    
    // ******************************************************* set content
     //
    
        range = layout_sheet.Range(layout_sheet.Cells(3 ,1 ),layout_sheet.Cells(3 , 8 ));
        content_csv = "';'column 1;'column 2;'column 3;'column 4;'column 5;'column 6;'column 7" ;
        range.ParseText(content_csv, ";");
     //
    
        range = layout_sheet.Range(layout_sheet.Cells(5 ,1 ),layout_sheet.Cells(10 , 8 ));
        content_csv = "'row 1;10;20;20;40;80;160;320n'row 2;180,000;0;0;0;0;0;0n'row 3;0;0;0;0;0;0;0n'row 
    
    4;0;0;0;0;0;0;0n'Sum;180,000;0;0;0;0;0;0n'" ;
        range.ParseText(content_csv, ";");
     //
    
    // ******************************************************* set format
     //
    
        range = layout_sheet.Range(layout_sheet.Cells(5 ,1 ), layout_sheet.Cells(10 , 1 ));  //
          range.NumberFormat  = "@";  //
    
        range = layout_sheet.Range(layout_sheet.Cells(5 ,2 ), layout_sheet.Cells(10 , 8 ));  //
          range.NumberFormat  = "#,##0.";  //
    
        range = layout_sheet.Range(layout_sheet.Cells(3 ,1 ), layout_sheet.Cells(3 , 1 ));   //
          range.Font.Color =  4276545 ;  //
          range.Font.Size  =  12 ;  //
          range.Font.Bold  =  1 ;  //
          range.Interior.Color = 16051415 ;  //
    
        range = layout_sheet.Range(layout_sheet.Cells(3 ,2 ), layout_sheet.Cells(3 , 8 ));   //
          range.Font.Color =  4276545 ;  //
          range.Font.Size  =  12 ;  //
          range.Font.Bold  =  1 ;  //
          range.Interior.Color = 16051415 ;  //
    
        range = layout_sheet.Range(layout_sheet.Cells(5 ,1 ), layout_sheet.Cells(10 , 1 ));   //
          range.Font.Color =  4276545 ;  //
          range.Font.Size  =  9 ;  //
          range.Interior.Color = 16051415 ;  //
    
        range = layout_sheet.Range(layout_sheet.Cells(5 ,2 ), layout_sheet.Cells(8 , 8 ));   //
          range.Font.Color =  4276545 ;  //
          range.Font.Size  =  9 ;  //
          range.Interior.Color = 16777215 ;  //
          range.Locked         = 0 ;  //
    
        range = layout_sheet.Range(layout_sheet.Cells(9 ,2 ), layout_sheet.Cells(9 , 8 ));   //
          range.Font.Color =  4276545 ;  //
          range.Font.Size  =  9 ;  //
          range.Interior.Color = 16051415 ;  //
    
        
    
    
    
        layout_sheet.Cells.AutoFitColumns();  //
      layout_sheet.ActiveSheet.Protection.Enabled = true;
    
    // -->
    </SCRIPT>
    
                            
                             
          </BODY></HTML>

     

    Hope this helps also :-).

     

    Best regards,

    Stefan

    • Re: BSP and Excel
      Brian McKellar
      Currently Being Moderated

      Hallo Stefan,

       

      Tonight I invested an hour into taking your example code apart, and understanding it. Really brilliant!

       

      Until now all approaches into loading Excel has taken the road of building the data into some format that Excel understands, usually CSV. The data is then send in a HTTP response to the browser, which triggers Excel to be loaded. This prevents any interaction with Excel.

       

      Your approach is totally different. Knowing that Excel has a very good COM model that can be programmed against using JavaScript, plus knowing that this JavaScript can run in the browser, you took a very refreshing approach. You instantiate the Excel inplace in the browser and then use JavaScript from the browser to directly program/fill the workbook of Excel. The very nice benefit is that now suddenly you can do things such as enter formulas, or setting formatting (colours, fonts, etc) for the cells individually. All of this JavaScript code can be dynamically generated to give you exactly the spreadsheet required for this problem.

       

      I suspect now it will also be very easy, is to extract entered/changed data from the spreadsheet, and sending it to the server on the next round-trip.

       

      I did not see this in your example, but see many traces that you are doing this (processLayoutInput function and hidden input fields). Just for you info, the example you appended has a closing table sequence directly after the </object> sequence for a table that was never started. This just looks like a cut and paste error from the original example.

       

      Although slightly complex initially, this is a very ellegant way to go. And if ever you should feel like it, definitely worth a weblog or two! I think many people will benefit from this knowhow that have. This coding shows the meister at work

       

      regards, brian

      • Re: BSP and Excel
        Ted Hoffman
        Currently Being Moderated

        Just wanted to second the idea of a weblog.

         

        Of course, it would be GREAT to see a snippet of code that extracts the data before then

        • Re: BSP and Excel
          Ted Hoffman
          Currently Being Moderated

          Just a note, I was able to get the sample to return data.

           

          I had to change the following statement in the processLayoutInput() function:

           

                     inputCollection.store();
          
          Changed to:
          
                     inputCollection(i).store();

           

          (Note:  Change the above to use square brackets [].  Forum interpreted the -square-i-square as italics)

           

          I also had to change the name of the hidden fields to use '_' instead of '-'; Page Attributes didn't like the '-'.

           

          Just added processLayoutInput as the "onSubmit" for the Form, and got the data back.

  • Re: BSP and Excel
    Stefan Riedel-Seifert
    Currently Being Moderated

    Hello Brian,

     

    yes, there is a cut&paste error. I have this copied from my bsp-application. The original is some larger and longer and i want only to show the way. Interested guys can get the whole documentation from the microsoft developer network.

     

    Like this way you can also deal with word. Powerpoint is much more difficult and not really interesting.

     

    My first approach was to load the data via vba into excel standalone, but that's more work than this.

     

    If i have some time, i create a weblog. But before, i want to capsulate this in a own extension. I have also some other funny things.

     

    Best regards,

    Stefan

  • Re: BSP and Excel
    Stefan Riedel-Seifert
    Currently Being Moderated

    hello togehter,

     

    i wil post a complete example within a weblog at the next time.

     

    Three remarks:

     

    1) You can find the documentation on

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffdev/html/vsofficedev.asp

     

    2) You can also automate word, if you want.

     

    3) You can also automate excel in a separate window.

    An impressive example:

     

    <HTML>

    <BODY>

     

    <INPUT id=button1 name=button1 type=button value=Button>

     

    <SCRIPT LANGUAGE="VBScript">

     

           sub button1_onclick()

     

              ' Launch Excel

              dim app

              set app = createobject("Excel.Application")

         

              ' Make it visible

              app.Visible = true

         

              ' Add a new workbook

              dim wb

              set wb = app.workbooks.add

         

              ' Fill array of values first...

              dim arr(19,9) 

              for i = 1 to 10

                 for j = 1 to 10

                    arr(i-1,j-1) = i*j

                 next

              next

         

              ' Declare a range object to hold our data

              dim rng

              set rng = wb.Activesheet.Range("A1").Resize(20,10)

         

              ' Now assign them all in one shot...

              rng.value = arr

         

               

     

              ' Give the user control of Excel

              app.UserControl = true

     

           end sub

    </SCRIPT>

     

    </BODY>

    </HTML>

     

    Best regards,

    Stefan

  • Re: BSP and Excel
    Thilo Klopfer
    Currently Being Moderated

    Hello to all,

     

    sorry for the long message, but I think there is maybe someone out there who might bee interestet...

     

    Excel (from version 2000) supports HTML as a native file format. Here you find a reference: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffxml/html/ofxml2k.asp

     

    Using this file fomat it’s easy to let a View build an Excel-File for you:

     

    Set the views mime type to: application/vnd.ms-excel

     

    Layout (not a running example, just some snippets):

    <%@page language="abap"%>
    
    <%
       DATA: dataref   TYPE REF TO DATA,
             wa_field  LIKE LINE OF it_field,
             f_type(1) TYPE C,
             wa_ref    TYPE REF TO DATA.
       FIELD-SYMBOLS: <fs>          TYPE ANY,
                      <fs_wa_data>  TYPE ANY,
                      <fs_data_tab> TYPE STANDARD TABLE.
       ASSIGN (data_tab) TO <fs_data_tab>.
       CREATE DATA wa_ref LIKE LINE OF <fs_data_tab>.
       ASSIGN wa_ref->* TO <fs_wa_data>.
    
       " Erstellungsdatum ermitteln
       DATA: date_created TYPE STRING,
             s_tst        TYPE timestamp,
             c_tst(15)    TYPE C.
       GET TIME STAMP FIELD s_tst.
       c_tst = s_tst.
       CONCATENATE c_tst(4) '-' c_tst+4(2) '-' c_tst+6(2) 'T' c_tst+8(2) ':' c_tst+10(2) ':' c_tst+12(2) 'Z' INTO date_created.
    
    
       DATA: wa LIKE LINE OF model->detail_list_all.
       FIELD-SYMBOLS <wa> LIKE LINE OF model->detail_list_all.
    %>
    
    <html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">
    
    <head>
    <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
    <meta name=ProgId content=Excel.Sheet>
    <meta name=Generator content="Microsoft Excel 9">
    
    <title><%=title%></title>
    <xml>
     <o:DocumentProperties>
      <o:Title><%=title%></o:Title>
      <o:Subject><%=subject%></o:Subject>
      <o:Author><%=SY-UNAME%></o:Author>
      <o:LastAuthor>Thilo Klopfer</o:LastAuthor>
      <o:Created><%=date_created%></o:Created>
      <o:LastSaved>2004-05-03T11:07:09Z</o:LastSaved>
      <o:Company>Excel via View</o:Company>
      <o:Version>9.7616</o:Version>
     </o:DocumentProperties>
    </xml>
    
    <style>
    table
         {mso-displayed-decimal-separator:",";
         mso-displayed-thousand-separator:".";}
    @page
         {margin:.98in .79in .98in .79in;
         mso-header-margin:.49in;
         mso-footer-margin:.49in;}
    tr
         {mso-height-source:auto;}
    col
         {mso-width-source:auto;}
    br
         {mso-data-placement:same-cell;}
    .style0
         {mso-number-format:General;
         text-align:general;
         vertical-align:bottom;
         white-space:nowrap;
         mso-rotate:0;
         mso-background-source:auto;
         mso-pattern:auto;
         color:windowtext;
         font-size:9.0pt;
         font-weight:400;
         font-style:normal;
         text-decoration:none;
         font-family:Arial;
         mso-generic-font-family:auto;
         mso-font-charset:0;
         border:none;
         mso-protection:locked visible;
         mso-style-name:Standard;
         mso-style-id:0;}
    td
         {mso-style-parent:style0;
         padding-top:1px;
         padding-right:1px;
         padding-left:1px;
         mso-ignore:padding;
         color:windowtext;
         font-weight:400;
         font-style:normal;
         text-decoration:none;
         font-family:Arial;
         mso-generic-font-family:auto;
         mso-font-charset:0;
         mso-number-format:General;
         text-align:general;
         vertical-align:bottom;
         border:none;
         mso-background-source:auto;
         mso-pattern:auto;
         mso-protection:locked visible;
         white-space:nowrap;
         mso-rotate:0;}
    .xl24
         {mso-style-parent:style0;
         font-weight:700;
         font-family:Arial, sans-serif;
         mso-font-charset:0;}
    </style>
    
    <xml>
     <x:ExcelWorkbook>
      <x:ExcelWorksheets>
       <x:ExcelWorksheet>
         <x:Name><%=worksheet%></x:Name>
         <x:WorksheetOptions>
           <x:DefaultColWidth>10</x:DefaultColWidth>
           <x:Selected></x:Selected>
           <x:ProtectContents>False</x:ProtectContents>
           <x:ProtectObjects>False</x:ProtectObjects>
           <x:ProtectScenarios>False</x:ProtectScenarios>
         </x:WorksheetOptions>
       </x:ExcelWorksheet>
      </x:ExcelWorksheets>
    
      <x:ActiveSheet>0</x:ActiveSheet>
      <x:ProtectStructure>False</x:ProtectStructure>
      <x:ProtectWindows>False</x:ProtectWindows>
     </x:ExcelWorkbook>
    </xml>
    
    </head>
    
    <body link=blue vlink=purple>
    
    <table x:str border=0 cellpadding=0 cellspacing=0 width=400 style='border-collapse:collapse;table-layout:fixed;width:300pt'>
    
     <%-- Spaltenbreiten einstellen --%>
     <col style='mso-width-source:userset;mso-width-alt:800'>
     <% LOOP AT it_field INTO wa_field. %>
            <col style='mso-width-source:userset;mso-width-alt:<%=wa_field-width%>'>
     <% ENDLOOP. %>
    
     <%-- Spaltenüberschriften --%>
     <tr height=17 style='height:12.75pt'>
         <td></td>
         <% LOOP AT it_field INTO wa_field.
             IF wa_field-header IS INITIAL.
                 " Header über DDIC ermitteln
                 CONCATENATE '<fs_wa_data>-' wa_field-name INTO wa_field-name.
                 ASSIGN (wa_field-name) TO <fs>.
                 GET REFERENCE OF <fs> INTO dataref. %>
                 <td class=Hd<%=lmr%>><%=runtime->ddic_utils->get_field_label( data_object_ref = dataref )%></td>
             <% ELSE.     " Header wurde in der Tabelle übergeben    %>
                 <td class=Hd<%=lmr%>><%=wa_field-header%></td>
             <% ENDIF.
         ENDLOOP. %>
    </tr>
    
     <%-- Datentabelle verarbeiten --%>
     <% LOOP AT <fs_data_tab> ASSIGNING <fs_wa_data>. %>
         <tr height=17 style='height:12.75pt'>
             <td></td>
    <%       LOOP AT it_field INTO wa_field.
                 CONCATENATE '<fs_wa_data>-' wa_field-name INTO wa_field-name.
                 ASSIGN (wa_field-name) TO <fs>.
                 DESCRIBE FIELD <fs> TYPE f_type.
                 IF f_type = 'P' OR f_type = 'I' OR f_type = 'N' OR f_type = 'b'. %>
                     <td class="Dat" x:num style='<%=wa_field-style%>'><%=application->excel_to_string( <fs> )%></td>
    <%           ELSEIF f_type = 'D'. %>
                     <td class="Dat" x:num style='mso-number-format:"Short Date";<%=wa_field-style%>'><%=application->excel_to_string( <fs> )%></td>
    <%           ELSEIF f_type = 'T'. %>
                     <td class="Dat" x:num style='mso-number-format:"hh:mm:ss";<%=wa_field-style%>'><%=application->excel_to_string( <fs> )%></td>
    <%           ELSE. %>
                     <td class="Dat" style='<%=wa_field-style%>'><%=<fs>%></td>
    <%           ENDIF.
             ENDLOOP. %>
         </tr>
     <% ENDLOOP. %>
    
    </table>
    
    </body>
    
    </html>

     

    In ON_HANDLE_EVENT I call a method which is calling the view, generates the Excel-File an stores the output in ICM-Cache:

    METHOD EXCELDOWNLOAD .
    
      DATA: view            TYPE REF TO if_bsp_page.
      DATA: cached_response TYPE REF TO if_http_response.
      DATA: guid            TYPE guid_32.
      DATA: lt_fields TYPE /sie/ad_z0mxls_field_t,
            l_field   TYPE /sie/ad_z0mxls_field_s,
            l_esn     TYPE /sie/ad_z0mesns_s,
            l_file    TYPE string,
            l_string  TYPE string,
            l_out     TYPE REF TO if_bsp_writer.
    
    
      view = create_view( view_name = 'xls_download.xls' ).
    * Die bisher aufgebaute Seite löschen, sie wird ersetzt
      l_out = view->get_out( ).
      l_out->clear( ).
    
      view->set_attribute( name = 'model' value  = model ).
      view->set_attribute( name = 'worksheet' value  = 'Worksheet Name' ).
      view->set_attribute( name = 'title'   value  = 'Excel Title' ).
      view->set_attribute( name = 'subject' value  = 'Subject' ).
      view->set_attribute( name = 'data_tab' value = 'MODEL->DATA_LIST' ).
    
      " Felder, die exportiert werden sollen
      l_field-name = 'Z0MEPNR'.            l_field-width = '3291'. l_field-header = ''.  APPEND l_field TO lt_fields.
      l_field-name = 'Z0MEPMATFLD'.        l_field-width = '5376'. l_field-header = ''.  APPEND l_field TO lt_fields.
      l_field-name = 'Z0MCOBEZ'.           l_field-width = '7314'. l_field-header = 'Mat-Grp'. APPEND l_field TO 
      l_field-name = 'Z0MEPRESP'.          l_field-width = '3840'. l_field-header = ''.  APPEND l_field TO lt_fields.
    
      view->set_attribute( name = 'it_field' value = lt_fields ).
      call_view( view ).
    
    * Ausgabe der Exceltabelle aus dem ICM-Cache
      l_out = view->get_out( ).
      l_string = l_out->get_content( ). "Seiteninhalt übernehmen
    
      CREATE OBJECT cached_response TYPE cl_http_response EXPORTING add_c_msg = 1.
    
      cached_response->set_cdata( l_string ).
      cached_response->set_header_field( name  = if_http_header_fields=>content_type
                                         value = 'application/vnd.ms-excel' ).
      cached_response->set_status( code = 200 reason = 'OK' ).
      cached_response->server_cache_expire_rel( expires_rel = 180 ).
    
      CALL FUNCTION 'GUID_CREATE'
        IMPORTING
          ev_guid_32 = guid.
      CONCATENATE runtime->application_url '/' guid INTO display_url.
    
      cl_http_server=>server_cache_upload( url      = display_url
                                           response = cached_response ).
    
      " Der Excel-Output von oben muss gelöscht werden
      l_out->clear( ).
    
    ENDMETHOD.

     

    In the Excel-View above, you can do almost everything (Formulars, Layout, Conditional Formatting, …)Excel can do. And you don't need ActiveX in the clients browser. If you don't want to read the reference from Micorsoft, try this:

    - Build your table in Excel.

    - Save as HTML

    - look what Excel wrote in the .htm file.

     

    Have Fun (reading the german remarks)

    Thilo

     

  • Re: BSP and Excel
    Brian McKellar
    Currently Being Moderated

    All of this is becoming very interesting. Looking into my crystal ball, I can see that we have now two very good ideas for extended weblogging. What is nice about a weblog with a well polished text, is that it is a self contained, which can include text, source, images, links. Any takers? (Unfortunately SDN is not giving out points anymore, but we still have open slots on our wall of fame!)

    • Re: BSP and Excel
      Subramanian Venkateswaran
      Currently Being Moderated

      Hi all,

       

      Apart from the BSP FAQ, where Brian McKellar(talking about Brian, where is he ??) had quoted Suresh's download in Excel format, I could not understand the other code excerpts. Reason being, not good at Javascript and VBScript.

       

      Anyway, thanks to Suresh and Thomas(for the Unicode), for the code snippet. Can anyone direct me to a website, where I can understand what that piece of code does ..for eg.. the methods delete_header_field, set_header_field and set_cdata, I would be very grateful.

       

      Its a little late to respond to this forum post, but never too late to say "thanks to all" for this forum post.

       

      Regards,

      Subramanian V.

    • Re: BSP and Excel
      Paul Moyer
      Currently Being Moderated

      Hello Brian, I currently have a small problem with embedding an Excel spreadsheet object into a BSP page. This has been working fine for some time, but recently I have found that the CLASSID is different for the Excel spreadsheet object in the different versions of MS Office, and we are now starting to use a newer version.

       

      Would it be possible for you to provide a method for me to determine what the correct value for the CLASSID is for the individual client based upon what is in the registry? Do you have any other suggestions for getting around this problem with creating this object?

       

      Any assistance would be greatly appreciated.

       

      Regards, Paul

      • Re: BSP and Excel
        Durairaj Athavan Raja
        Currently Being Moderated

        you can use method

         

        CL_GUI_FRONTEND_SERVICES=>REGISTRY_GET_VALUE to get the correct class ID per client.

         

        Regards

        Raja

         

        Note: if you had opened this as a new topic, the response would have been better.

        • Re: BSP and Excel
          Currently Being Moderated

          Hi Raja,

          a question from a beginner in the area of embedding activeX controls. What parameters does the method

          REGISTRY_GET_VALUE expect?

           

          CALL METHOD cl_gui_frontend_services=>registry_get_value

            EXPORTING

              root                 = ?

              key                  = ?

              VALUE                = ?

           

          Thanx in advance for your help!

           

          Best Regards

          Peter Kück

           

          • Re: BSP and Excel
            Durairaj Athavan Raja
            Currently Being Moderated

            Hi Welcome to SDN.

             

            Please use a new thread for better response.

             

            Here is the answer for your question.

             

             

            CALL METHOD cl_gui_frontend_services=>registry_get_value
              EXPORTING
                root                 = '0'
                key                  = 'Excel.ApplicationClsid'
            *    VALUE                =
              IMPORTING
                REG_VALUE            = 'here you will get the class id
            *  EXCEPTIONS
            *    GET_REGVALUE_FAILED  = 1
            *    CNTL_ERROR           = 2
            *    ERROR_NO_GUI         = 3
            *    NOT_SUPPORTED_BY_GUI = 4
            *    others               = 5
                    .
            IF sy-subrc <> 0.
            * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
            *            WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
            ENDIF.

             

            Regards

            Raja

            • Re: BSP and Excel
              Anubhav Jain
              Currently Being Moderated

              Hi All,

              I am using the code from the weblog for downloading data from internal table to excel through BSP application (MVC):

              Suppose ITAB is my internal table:

               

               

              data: output TYPE string ,

              app_type TYPE string ,

              l_xstring TYPE xstring.

               

              CONSTANTS: crlf TYPE string VALUE cl_abap_char_utilities=>cr_lf,

              tab TYPE string VALUE

              cl_abap_char_utilities=>horizontal_tab.

              if itab is NOT INITIAL .

              loop at itab_final INTO wa .

              concatenate output wa-field1

              wa-field2

              wa_field3

              wa_field4

              CL_ABAP_CHAR_UTILITIES=>CR_LF

              INTO output SEPARATED BY TAB .

              endloop.

               

              .

               

              *end of addition.

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

               

              CALL FUNCTION 'SCMS_STRING_TO_XSTRING'

              EXPORTING

              text = output

              mimetype = 'APPLICATION/MSEXCEL;charset=utf-16le'

              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.

               

              CALL METHOD cl_bsp_utility=>download

              EXPORTING

              object_s = l_xstring

              content_type = app_type

              content_disposition = 'attachment;filename=webforms.xls'

              response = response

              navigation = navigation.

               

              endif .

               

              Now when the excel sheet opens ,  the first column is coming blank.

              I mean the first field of internal table ITAB is coming from column B of excel sheet.

              Has anyone come across same problem?

               

              Thanks.

Actions