Excel Services SetRange method is not working

[#1]

Through Excel service Set Range method call I have tried to set Excel file cells as a range.

I have faced issues with the <anyType>xml</anyType> and there are less number of references to make it works.

This is the required xml to update.

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/office/excel/server/webservices">

  <soap:Header>

  </soap:Header>

  <soap:Body>

    <metRange>

      <m:sessionId>string</m:sessionId>

      <m:sheetName>string</m:sheetName>

      <m:rangeCoordinates>

        <m:Row>int</m:Row>

        <m:Column>int</m:Column>

        <m:Height>int</m:Height>

        <m:Width>int</m:Width>

      </m:rangeCoordinates>

      <m:rangeValues>

        <anyType>xml</anyType>

      </m:rangeValues>

    </metRange>

  </soap:Body>

</soap:Envelope>

I don't have direction what would be the value or what type of value would be required.

I've tried lots of variations but nothing works. For example passing xml type values, string values with comma separated, string values with double quoted, string values with single quoted, string values without quoted, string values with semi colon separated, collection type of values. No luck at all.

If anyone have solution please share it. Thanks in advance and Happy Nintexing.

[philipplucas]

Hi Sivakumar,

I know that Vadim Tabakman​ has done some cool stuff with Excel Services in Nintex. Maybe he can help us.

[mlauer]

look at WDSL: http://<server>/<customsite>/_vti_bin/excelservice.asmx?WSDL

There You will find description of how the service can be called, what parameters it expects, and what data structures it returns.

You can upload WDSL to  Online WSDL Viewer and Validator to view operations and parameter and request templates too:

<s12:Envelope xmlns:s12='http://www.w3.org/2003/05/soap-envelope'>

  <s12:Body>

    <ns1etRange xmlns:ns1='http://schemas.microsoft.com/office/excel/server/webservices'>

<!-- optional -->

      <ns1:sessionId>?XXX?</ns1:sessionId>

<!-- optional -->

      <ns1:sheetName>?XXX?</ns1:sheetName>

<!-- optional -->

      <ns1:rangeCoordinates>

        <ns1:Row>?999?</ns1:Row>

        <ns1:Column>?999?</ns1:Column>

        <ns1:Height>?999?</ns1:Height>

        <ns1:Width>?999?</ns1:Width>

      </ns1:rangeCoordinates>

<!-- optional -->

      <ns1:rangeValues>

<!-- from 0 to unbounded -->

<!-- This element may be left empty if xsi:nil='true' is set. -->

        <ns1:anyType />

      </ns1:rangeValues>

    </ns1etRange>

  </s12:Body>

</s12:Envelope>

[#4]

Please find the error in Nintex Workflow.

[#5]

As you suggested I have uploaded WSDL and get the response.

<s:element name="SetRange">

<s:complexType>

<s:sequence>

<s:element minOccurs="0" maxOccurs="1" name="sessionId" type="s:string"/>

<s:element minOccurs="0" maxOccurs="1" name="sheetName" type="s:string"/>

<s:element minOccurs="0" maxOccurs="1" name="rangeCoordinates" type="tns:RangeCoordinates"/>

<s:element minOccurs="0" maxOccurs="1" name="rangeValues" type="tns:ArrayOfAnyType"/>

</s:sequence>

</s:complexType>

</s:element>

But I don't have idea about the values for tns:ArrayOfAnyType.

[vadim_tabakman]

I'm afraid I've never used anyType node.  Have you tried posting on any of the MS or Excel boards that are out there, in case someone knows the structure of that node?

Vadim

[mlauer]

Format for rangeValues have to be:

<rangeValues>

     <anyType xsi:type="ArrayOfAnyType">

                   <anyType xsi:type="xsd:string">row_n col_x</anyType>

                   <anyType xsi:type="xsd:string">row_n col_x+1</anyType>

      </anyType>

      <anyType xsi:type="ArrayOfAnyType">

                   <anyType xsi:type="xsd:string">row_n+1 col_x</anyType>

                   <anyType xsi:type="xsd:string">row_n+1 col_x+1</anyType>

      </anyType>

</rangeValues>

For each row in range insert an ArrayOfAnyType and each nested element is a cell in that row.

cell type can be boolean, dateTime, decimal, double, float, int, short, string, or unsigned byte, See [MS-ESP]: SetRange

[#8]

Thanks for the reply Manfred. I have tried this option. Workflow ran and completed. But nothing updated in Excel file.

Find the xml part.

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/office/excel/server/webservices">

  <soap:Header>

  </soap:Header>

  <soap:Body>

    <metRange>

      <m:sessionId>{WorkflowVariableessionID}</m:sessionId>

      <m:sheetName>Sheet1</m:sheetName>

      <m:rangeCoordinates>

        <m:Row>5</m:Row>

        <m:Column>5</m:Column>

        <m:Height>2</m:Height>

        <m:Width>2</m:Width>

      </m:rangeCoordinates>

     <rangeValues>

     <anyType xsi:type="ArrayOfAnyType">

                   <anyType xsi:type="xsd:string">Test value</anyType>

                   <anyType xsi:type="xsd:string">Test value</anyType>

      </anyType>

      <anyType xsi:type="ArrayOfAnyType">

                   <anyType xsi:type="xsd:string">Test value</anyType>

                   <anyType xsi:type="xsd:string">Test value</anyType>

      </anyType>

</rangeValues>

    </metRange>

  </soap:Body>

</soap:Envelope>

What could be the issue?

[mlauer]

any error messages?

[mlauer · Accepted solution]

this is working for me (tested in Chrome, Wizdler extension):

<?xml version="1.0" encoding="UTF-8"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns="http://schemas.microsoft.com/office/excel/server/webservices" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <soap:Body>

        <SetRange>

            <sessionId>{WorkflowVariableessionID}</sessionId>

            <sheetName>Sheet1</sheetName>

            <!-- Optional -->

            <rangeCoordinates>

                <Row>0</Row>

                <Column>5</Column>

                <Height>2</Height>

                <Width>2</Width>

            </rangeCoordinates>

            <!-- Optional -->

            <rangeValues>

                   <anyType xsi:type="ArrayOfAnyType">

                      <anyType xsi:type="xsd:string">Test value</anyType>

                      <anyType xsi:type="xsd:string">Test value</anyType>

                   </anyType>

                   <anyType xsi:type="ArrayOfAnyType">

                      <anyType xsi:type="xsd:string">Test value</anyType>

                      <anyType xsi:type="xsd:string">Test value</anyType>

                   </anyType>

            </rangeValues>

        </SetRange>

    </soap:Body>

</soap:Envelope>