Skip to main content

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

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.

 

 

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.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings