cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Excel Services SetRange method is not working

Jump to solution

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>

    <mSmiley FrustratedetRange>

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

    </mSmiley FrustratedetRange>

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

0 Kudos
Reply
13 Replies
Automation Master
Automation Master

Re: Excel Services SetRange method is not working

Jump to solution

Hi Sivakumar,

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

Reply
mlauer
Nintex Newbie

Re: Excel Services SetRange method is not working

Jump to solution

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>

    <ns1Smiley FrustratedetRange 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>

    </ns1Smiley FrustratedetRange>

  </s12:Body>

</s12:Envelope>

Reply
Not applicable

Re: Excel Services SetRange method is not working

Jump to solution

Please find the error in Nintex Workflow.

Error.png

0 Kudos
Reply
Not applicable

Re: Excel Services SetRange method is not working

Jump to solution

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.

0 Kudos
Reply
Nintex Employee
Nintex Employee

Re: Excel Services SetRange method is not working

Jump to solution

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

Reply
mlauer
Nintex Newbie

Re: Excel Services SetRange method is not working

Jump to solution

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

Reply
Not applicable

Re: Excel Services SetRange method is not working

Jump to solution

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>

    <mSmiley FrustratedetRange>

      <m:sessionId>{WorkflowVariableSmiley FrustratedessionID}</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>

    </mSmiley FrustratedetRange>

  </soap:Body>

</soap:Envelope>

What could be the issue?

0 Kudos
Reply
mlauer
Nintex Newbie

Re: Excel Services SetRange method is not working

Jump to solution

any error messages?

Reply
mlauer
Nintex Newbie

Re: Excel Services SetRange method is not working

Jump to 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>{WorkflowVariableSmiley FrustratedessionID}</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>

Reply