cancel
Showing results for 
Search instead for 
Did you mean: 
sstonehouse
Nintex Newbie

Troubleshooting excel service setcell

Jump to solution

I was wondering if someone could please help me.

I would like to dynamically create a excel workbook and place some of the data from a SharePoint 2010 InfoPath form into a excel workbook.

I have managed to get Nintex to create the workbook by using the excel service method 'openworkbookForEditing' which opens an excel workbook already in SharePoint with session id 'sessionid'.  I then use the excel service method 'setcell' for the same sessionid, Sheet1. row 5, column 5, value "test". 

I then use 'saveworkbookcopy' and copy sessionid to the same location in SharePoint, but with a different name. 

I then use the 'closeWorkbook' for sessionid.  The newly created workbook appears, but there is not value in the required cell in either workbook.

Any advice would be gratefully received.

Thank you kindly

Sam

Tags (1)
Reply
2 Replies
stantond
Nintex Newbie

Re: Troubleshooting excel service setcell

Jump to solution

This question was asked quite awhile back but I was having the same issue and eventually figured it out.

 

It is not very clear from the documentation what Excel Services is expecting for the cell value. In the Excel Services documentation it gives the following for a SOAP 1.1 request.

POST /_vti_bin/excelservice.asmx HTTP/1.1
Host: cintas1.sharepoint.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://schemas.microsoft.com/office/excel/server/webservices/SetCell"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <SetCell xmlns="http://schemas.microsoft.com/office/excel/server/webservices">
      <sessionId>string</sessionId>
      <sheetName>string</sheetName>
      <row>int</row>
      <column>int</column>
      <cellValue />
    </SetCell>
  </soap:Body>
</soap:Envelope>

I tried a bunch of different things for <cellValue /> and the server kept coming back with HTTP Status Code 200 and no errors but the value was not showing up in my excel sheet.

 

What it actually wanted in place of <cell Value /> was <cellValue xsi:type="xsd:string">‎‏</cellValue> with whatever value or variable you want in the middle (Ex. <cellValue xsi:type="xsd:string">‎‏{Current Item:Created By}‌</cellValue>).

 

I also noticed that row and column start at 0 so that is something to check if your value is not showing up in the correct place.

 

 

View solution in original post

0 Kudos
Reply
Community Manager Community Manager
Community Manager

Re: Troubleshooting excel service setcell

Jump to solution

While you can do this with excel services, you could also use generate document within Nintex Workflow to create the excel file also from the SharePoint list item.

0 Kudos
Reply