BSP and Excel

Former Member · ‎07-31-2004

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

former_member181879 · ‎09-27-2004

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!)

Thilo · ‎09-27-2004

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

RieSe · ‎09-24-2004

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>

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

' 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

RieSe · ‎09-21-2004

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

RieSe · ‎09-20-2004

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

Former Member · ‎08-02-2004

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

Former Member · ‎07-31-2004

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

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.

BSP and Excel

Accepted Solutions (0)

Answers (7)

Answers (7)

Re: advance Data action to link two dimensions

Re: How to configure 2 Factor Authentication in C4...

How to configure 2 Factor Authentication in C4C us...

CAP Extensibility: Exended Properties displayed in...

Re: SAP CAP Trial - Error deploying in US - Kerber...