OfficeControl UI Element filling with Excel im XML format

[Former Member]

Hi folks!

I have a Web Dynpro ABAP Application where I want to use the OfficeControl UI Element to transfer Result List Table content to an Excel File using MS Office XML Format.

.. I generate an XML String in the coding with data from context

.. convert it to xstring

.. bind it to a XSTRING context node which is the DataSource for

the OfficeControl UI Element

In the debugger everything is fine and Excel is opened for both

versions of the XML file: 1. string and 2. xstring.

Also when I copy&paste the String and put it in an XML File, opening

with EXCEL works perfectly.

But the UI Element does not open the document.It just displays a white

s´quare with "SAP IOS" in it's heart.

The SAP ACF Trace throws the following (very self-explanatory) things:

...

ERRO|20110210150118|WD0516_5976|CIOS_GeneralDocumentContainer_Acf::|Load|HRESULT=-2147287038()

ERRO|20110210150118|WD0516_5976|CIOS_GeneralDocumentContainer_Acf::|loadOffice2007xml|HRESULT=-2147287038()

...

Here is the relevant coding snippet:

DATA lv_excel_string TYPE string.
  DATA lv_excel_xstring TYPE xstring.
  DATA lo_conv_out   TYPE REF TO cl_abap_conv_out_ce.
  DATA lo_office_ctrl TYPE REF TO cl_wd_office_control.
*1) Tabelle XML String konvertieren
  concatenate lv_excel_string
              '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
              '<?mso-application progid="Excel.Sheet"?>'
              '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
              '<ss:Worksheet ss:Name="Download">'
              '<ss:Table>'
              into lv_excel_string.
  LOOP AT lt_et_result_items_tot INTO ls_et_result_items_tot.
    CONCATENATE lv_excel_string
                '<ss:Row>'
                '<ss:Cell>'
                '<ss:Data ss:Type="String">'
                ls_et_result_items_tot-index_id
                '</ss:Data>'
                '</ss:Cell>'

                '<ss:Cell>'
                '<ss:Data ss:Type="String">'
                ls_et_result_items_tot-object_id
                '</ss:Data>'
                '</ss:Cell>'

                '<ss:Cell>'
                '<ss:Data ss:Type="String">'
                ls_et_result_items_tot-external_key
                '</ss:Data>'
                '</ss:Cell>'

                '<ss:Cell>'
                '<ss:Data ss:Type="String">'
                ls_et_result_items_tot-name_komplett
                '</ss:Data>'
                '</ss:Cell>'
                '</ss:Row>'
                INTO lv_excel_string.
  ENDLOOP.
  concatenate lv_excel_string
              '</ss:Table>'
              '</ss:Worksheet>'
              '</ss:Workbook>'
              into lv_excel_string.
*2) String in XSTRING konvertieren
  lo_conv_out = cl_abap_conv_out_ce=>create( encoding =  'UTF-8' ).
  lo_conv_out->convert(
   EXPORTING
    data = lv_excel_string
   IMPORTING
   buffer = lv_excel_xstring ).

And here is the generated XML file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Excel.Sheet"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
	<ss:Worksheet ss:Name="Download">
		<ss:Table>
			<ss:Row>
				<ss:Cell>
					<ss:Data ss:Type="String">ZPERSONAL</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00004715</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00004715</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Herr Volkerlein Kolberg</ss:Data>
				</ss:Cell>
			</ss:Row>
			<ss:Row>
				<ss:Cell>
					<ss:Data ss:Type="String">ZPERSONAL</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00002839</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00002839</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Herr Volkerlein Kolberg</ss:Data>
				</ss:Cell>
			</ss:Row>
			<ss:Row>
				<ss:Cell>
					<ss:Data ss:Type="String">ZPERSONAL</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00003131</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">00003131</ss:Data>
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Herr Volkerlein Kolberg-Zwei</ss:Data>
				</ss:Cell>
			</ss:Row>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

We are working with SAP NetWeaver 7.01 with SAPKA70107 and SAPKB70107

Please help!!! Thanx very much in advance!

Regards,

Volker Kolberg

[gill367]

HI

You cannot just convert string to xstring and use it to like an XML resource.

you first have to create one XML file in the ICM cache and then read it from there as an XSTRING

and then load this XSTRING to data source of the office control.

here is the code for the same..

try this

  data l_content type xstring.
data mr type ref to if_mr_api.
mr = cl_mime_repository_api=>get_api( ).
    DATA lv_str type string.
LV_str = '       '   .      " this string valiable should contain the XML file content


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

 data: l_app_type type string.

      cached_response->set_cdata( lv_str ).
      l_app_type = 'text/xml'.

 cached_response->set_header_field( name  = if_http_header_fields=>content_type
                                     value = l_app_type ).

  cached_response->set_status( code = 200 reason = 'OK' ).
  cached_response->server_cache_expire_rel( expires_rel = 60 ).
  data: guid type guid_32.
  call function 'GUID_CREATE'
    importing
      ev_guid_32 = guid.


  DATA lv_ulr type string.
   concatenate '/sap/public' '/' guid '.' 'xml' into lv_ulr.



  cl_http_server=>server_cache_upload( url      = lv_ulr
                                       response = cached_response ).



CALL METHOD  mr->get(
 EXPORTING
 I_URL = lv_ulr
 IMPORTING
  E_CONTENT =   l_content
    ).



 DATA lo_conv_out   TYPE REF TO cl_abap_conv_out_ce.

lo_conv_out = cl_abap_conv_out_ce=>create( encoding =  'UTF-8' ).
  lo_conv_out->convert(
   EXPORTING
    data = lv_str
   IMPORTING
   buffer = l_content ).
  DATA lo_nd_doc TYPE REF TO if_wd_context_node.
  DATA lo_el_doc TYPE REF TO if_wd_context_element.
  DATA ls_doc TYPE wd_this->element_doc.
  DATA lv_source LIKE ls_doc-source.
* navigate from <CONTEXT> to <DOC> via lead selection
  lo_nd_doc = wd_context->get_child_node( name = wd_this->wdctx_doc ).

* get element via lead selection
  lo_el_doc = lo_nd_doc->get_element(  ).

* get single attribute
  lo_el_doc->set_attribute(
    EXPORTING
      name =  `SOURCE`

      value = l_content ).

Thanks

sarbjeet singh

[Former Member]

Hi Sarbjeet!

Thanx for the coding, but why should I do that? In fact I do the same: Creating an XML document, convert it to XSTRING and then bind it to Data source via Context Node.

And in the meantime I even tried it hardcoded with a sample XML Excel File I got from Wikipedia. And with this file I get the same results: Opening with Excel works perfect. Even directly in the debugger when using the VAR XML view Excel opens correctly with String and with Xstring variable.

There must be something special the OfficeControl UI element expects otherwise it should be able to open the document as Excel itself can.

Regards,

Volker

[gill367]

yeah it behaves strangley,

I guess may be you are right storing the file explicitly is not required.

Do one thing check out the XML file format used in the example present in the SIOS package.

may be we need to specify the XML in that particular format only.

You can find that file in the mime repository at the location.

'/SAP/BC/WebDynpro/SAP/PUblic/BC/ssr/uuielibs/office_integration/testxmlexportsimple.xml'

also yeah one more important thing is .

just binding the XSTRING of the xml file to the data source of the office control wont produce the desired result.

you need to store one empty XLS file int the mime convert it into XSTRING and then pass this to the data source.

then using the method ActivateXMLstring( ) of the interface IF_IOS_SPREADSHEET, you pass the XSTRING of the xml file.

thanks

sarbjeet singh

[Former Member]

Hi folks!

Just to inform you. I have an answer from SAP that OfficeControl in my NetWeaver version 7.01 does not support direct Excel XML XStrings but only binary Excel files. I have now solved it with cl_wd_runtime_services=>attach_file_to_response giving this method my Xstring and that works! Excel opens correctly with the correct data and formatting.

SAP said with 7.02 there will be new features in OfficeControl UI Element.

I close this thread now although unsolved.

Thank you all so far for your hints!

Regards,

Volker

[former_member210296]

Hi All,

The above code provided to use cache, didn't work for me. I also checked the standard WD Component IOS_TEST_SPREADSHEET_MS which is somewhat doing the same thing.This component also gives the same error for XML file.

I am using 7.4 system of SAP. Still getting the same error for displaying XML transform output in Office Control.

Does anyone know to may it work. I see that SAP has provided component IOS_TEST_SPREADSHEET_MS with some XML stuff which does not work.

[Former Member]

Hi Volker,

Can you confirm that the SAP standard wda applications in SIOS package are working correctly ?

[Former Member]

Unfortunately I cannot do this, because I am not an employee of SAP ...

But this does not really help me to solve my issue ... ;-(

Regards,

volker

[Former Member]

>

> Unfortunately I cannot do this, because I am not an employee of SAP ... 
> But this does not really help me to solve my issue ... ;-(
> Regards,
> volker

hi volker,

i am sorry if there is any misunderstanding caused.

SIOS package is standard package available in all the Netweaver systems.

i am not a SAP employee and i can see this package in my system and i can run MSWORD,EXCEL applications which are delivered on for DEMO purpose.

The reason why i ask you to check this was that sometimes it is possible that the environment is not configured correctly or any other pre requisite things to run ACF is not met. You can easily ruled out that the problem is not in your component.

[Former Member]

Ah okay. Sorry. Now I understand. SIOS of course is present and it works. But the problem ist that the demo applications seem to load the xls file from Mime Repository. That works fine.

In my case I (have to) create this XLS file in my coding - because its content (columns and arrangement) is highly dynamic, thus can be completely different from one time to the other - depending on what the user makes with table personalization.

Regards,

Volker

[Former Member]

Hi,

Now at least we know that it is something to do with dynamic generation of xls to xstring. I shall look into your code and let you know if i find something different.

[Former Member]

Okay. Thank you!

[Former Member]

Hi Volker,

Please see the [|]

Thomas Jung reply.

[Former Member]

Hi!

Unfortunately this is not my case. We work with Office 2007. And like already mentioned: Opening the file directly with Excel works perfect. Only that f... OfficeControl does not want to work.

Regards,

Volker