Vadim Tabakman

Nintex Workflow - Updating an Excel Spreadsheet UDA

SharePoint 2013 (Enterprise edition) comes with Excel Services.  One of the lesser known pieces of functionality, is the Excel Services web service.  More information on it can be found here : http://msdn.microsoft.com/en-us/library/office/ms494732(v=office.14).aspx 

Since this is an ASMX web service, it can be called from Nintex Workflow using the Call Web Service action.  This web service behaves a little differently to others you may be used to.  You can't just call a method to update a cell in your spreadsheet.  The way to use this web service, is to first obtain a Session ID.  This is then used through future calls to the web service, until you finally close your session.

Getting a Session ID

The url to the Excel Service web service is http://[siteurl]/_vti_bin/ExcelService.asmx .  In order to update a spreadsheet, firstly, open up a session to the spreadsheet so that it can be edited.  The web method to call is OpenWorkflowForEditing.  A bunch of XML will be returned, and the Session ID needs to be extracted from the XML by clicking on the Select Element link in the action. 

Get Session ID

Notice that at the bottom of the action configuration is the Web Service Output.  The "Specify Elements" needs to be checked, and then it's possible to select the appropriate XML node.  This XML browser gives you an easy way to get to the data in the XML that is needed, without having to know XPath:

Select Session ID XML Node

The OpenWorkflowForEditingResult is the node that contains the Session ID that will be needed for any future calls to update this Excel file.

Updating a Cell

There is a trick to updating a cell using Nintex Workflow and the Excel service.  The SetCell web method is what is needed to update a single cell.

When selecting that web method in the Call Web Service action, behind the scenes, a SOAP packet is being built.  You can configure most of the parameters for this web method in the nice UI provided.  In order to complete it though, you need to go into SOAP Editor mode.  The cellValue field, for some reason is missing the namespace behind it.  For this web method call to work, put in a "m:" before the two cellValue parameters.

SetCell

That is all you need for the SetCell web method to work.

Closing an Excel Service Session

On completion of the update, the session needs to be closed.  This requires yet another Call Web Service call, this time calling the CloseWorkbook method to close the session we have open.  This will commit the update.

Close Workbook

Update Cell User Defined Action

The reason this was put into a UDA, is that I had requirements for a project where I needed to update a single cell in multiple places in my workflow.  This allowed me to reuse this functionality in different locations in my workflow, but also throughout other workflows.

The configuration of the UDA when being used in a workflow is a little messy.  The parameters are not in any particular order.  Wait for the next release of Nintex Workflow (after 11th March 2014) and you'll have the ability to reorder parameters and variables and then we can make this a little more visually appealing.  In the end, this works for what it needs to do.

UDA Design

Without going into too much of the detail, you can see from the screenshot above what the UDA design looks like.  We are opening a session, checking for errors, updating a cell, checking for errors and finally closing the session.  Although in the screenshot, I'm doing something naughty, in that I'm not labeling my actions, in the downloaded UDA file below, they have been labeled and should make it easier for you to understand what it is doing.

Requirements

As with most of my posts that use actions that require credentials, I use a Nintex Workflow Constant.  In this workflow/uda, I'm using one called FarmAdmin.  As long as you have a credential with that name, the workflow/uda should work fine.

If you have a different named constant, just update the UDA and republish it an the workflow.

Downloads

Nintex Workflow 2013

Download the Update Excel Cell UDA (.NWF) - Download

Download the Workflow - Download and import into the Workflow Designer Page

Posted by Vadim Tabakman Monday, March 10, 2014 8:59:00 AM Categories: Call Web Service Excel Nintex Workflow Nintex Workflow 2013 Run If Set a Condition
Copyright Vadim Tabakman
Rate this Content 0 Votes

Comments

Friday, March 28, 2014 2:38:40 PM
Mike
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hey Vadmin,

This is what I've been looking for. I was unable to download the UDA and import it but tried to recreat it and i'm getting this error on the second Call Web Service.

Failed to invoke web service. Error returned from server: <soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>soap:Client</faultcode><faultstring>Server was unable to read request. ---&gt; There is an error in XML document (1, 58

Thanks,

Mike

Friday, March 28, 2014 2:44:21 PM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Mike,

in order to import the UDA, go to Manage User Defined Actions, click on the New or Create button and that will open the designer.

Then click on Import.  That should let you import the file that is in the Downloads section on this post.

I'm not sure what that error is.  Something is wrong with the SOAP packet.

cheers,

Vadim

Thursday, July 31, 2014 9:54:52 AM
Florian
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi, i have the same problem like Mike.

Failed to open the Excel Spreadsheet - Vom Server gelieferte Fehlermeldung: <soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>soap:Server</faultcode><faultstring xml:lang="de-DE">Ein unbekanntes Problem ist aufgetreten.</faults

Can anyone help?

Thanks  a lot!

Cheers,

Florian

Thursday, July 31, 2014 9:56:19 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Florian,

I'm wondering if you have excel services configured in your SP environment?  I'm just guessing here, as I don't know what could be the issue here.

cheers,

Vadim

Thursday, April 9, 2015 11:56:58 PM
Alexey
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

thanks a lot for this great post! It helped me a lot to configure the workflow to work with Excel.

But I'm trying to use this in more efficient way and to update multiple cells in one call to service using method setRange. Unfortunately I haven't got any success here - constantly getting an error "The size of the provided array does not match the size and shape of the destination range", so seems like I couldn't set the array of values in SOAP correctly.

If it's convenient, could you please help with getting this worked. Especially - how to correctly set the <rangeValues> in SOAP for method "setRange"

THANKS A LOT in advance!

Friday, April 10, 2015 5:10:39 AM
Alexey
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

Did you try to use "setRange" method? what xml to be sent in rangeValues tag...?

Very appreciated if you could help...

Friday, April 10, 2015 8:43:55 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Alexey,

I'm afraid I haven't tried the SetRange to see how that works.

cheers,

Vadim

Monday, May 4, 2015 7:07:01 AM
Stu Thomason
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi, Vadim,

Are farm-level credentials required to make this work?  I ask because I'm able to use my own Site Owner account to select the Web method and get to OpenWorkbookForEditing. However, when I navigate to get the Web service output and add m:OpenWorkbookForEditingResult, the dropdown down does not return anything, meaning no SessionID option.

Ideas?

Tuesday, October 20, 2015 8:15:34 AM
Clifford Espinosa
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi! Vadim,

Do you by any chance have something for updating Headers of Excel Spreadsheet using Nintex workflow?

Tuesday, October 20, 2015 8:59:55 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

hi Clifford

I'm afraid not.

Vadim

Friday, November 13, 2015 8:10:36 AM
Thomas Olbrich
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Thank you Vadim. You are the Workflow master - again.

I had several tries until it worked for me.
These are my findings:

  • Excel file must be in a trusted location
  • version should be Office 2010 at least (xslx format) I got an error "file corrupted" when I tried an Excel 2003 file

Best Regards

Thomas

Tuesday, November 24, 2015 4:12:41 AM
Anshita
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

I have a requirement to read data from different tabs of excel and creating sharepoint list item for each excel row.

I am using 'Query Excel Service' to read excel data , my data can be n (1 to 5000) rows in excel . Using Query excel is very slow performance wise. Can you suggest something!!

 

Regards,

Anshita

Tuesday, November 24, 2015 8:23:09 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Anshita,

I'm afraid I don't. 

 

Vadim

Wednesday, December 9, 2015 2:56:24 PM
Brian
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

I am getting the faultstring "We're sorry. We couldn't lock this file for editing. Would you like to try again?" Any ideas what needs to be done to get past? 

Wednesday, February 10, 2016 12:42:06 PM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

How does this work for Office Live 365 instances of Sharepoint?  Non premise.

Friday, April 22, 2016 9:22:50 AM
Vitaly
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Thank you for the instructions. I'm trying to create a workflow that would create a folder, an Excel document from a template and a couple of other files and then populate the cells in the Excel document with various values from various sources (including web page on the intranet).

I get this error when configuring the Call Web Service:

Failed to invoke web service. Error returned from server: <soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>soap:Server</faultcode><faultstring xml:lang="en-US">Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException:

You mentioned that it might be related to the Excel Services configuration. I don't have the administration access to the site – but need to know what to ask the support team.

Wednesday, June 1, 2016 8:14:33 AM
Melanie
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Brand new to workflows. Is it possible to have a workflow open an Excel file and refresh pivot data? There is a setting in Excel that will automatically refresh if the file is opened, so all I need the workflow to do is open the file and then close it again.

 

Thanks!

 

Wednesday, June 1, 2016 8:37:18 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Melanie,

Although I haven't done much work with pivot tables, I would say that it's probably not do-able with out of the box Workflow.

You could try it, based on the logic in this post. 

If the pivot data refreshes when you open the file anyway, why do you need a workflow to do it, since it'll refresh the next time a user opens the file?

cheers,

Vadim

Wednesday, June 1, 2016 3:50:13 PM
Melanie
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Thanks for the reply, Vadim. I need a workflow because we want to display the pivot table in a webpart that the user sees. They won't manipulate the workbook.

 

We are using a Nintex form that the user data enters in the data.

That form feeds to a list.

The list is exported to Excel

The workbook is stored in a library.

The pivot table is pulling from the data (sheet).

Sooo.. the user sees the blank form and enters the data. That feeds everything else.

Other users can see the pivot table.

 

Does this make sense now?

Monday, June 6, 2016 5:12:44 AM
Markus Remlinger
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

the procedure is working fine with xlsx files but I do not get the sessionID from an xlsm file? (Nintex stops with an error)

What can I do to update such a file?

Thanks

Markus

Monday, June 6, 2016 11:29:02 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Melanie,

 

I get it now.  But I don't think you would be able to do this with the out of the box actions.  At least, I don't believe so.

It might require custom action or web service that uses the Excel API to open the document at let it recalculate and save it.

Vadim

Monday, June 6, 2016 11:31:28 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Markus,

I have no experience with XLSM files.  Don't know even know what they are, other some Excel workbook.

So without testing, I would say that those probably aren't supported.

Vadim

Tuesday, June 7, 2016 12:19:37 AM
Markus Remlinger
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

it is an Excel file with Macros - I just wanted to set a cell via workflow and use that cell as part of the Header. The set cell works but if you set the Header (and as far as I know it's only possible with a macro) you have to save the file as .xlsm - and then my Problem starts :-)

Markus

Thursday, June 9, 2016 1:00:32 AM
Wenche Stark
Gravatar

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,


Thanks for all your excellent posts! Perhaps you can help me. I have been struggling a long time to get exactly what you do with your UDA work with Nintex Workflow for Office 365 using the Web Request action. I am able to call the OpenWorkbookForEditing method successfully, and it returns a session ID as it is supposed to. The problem is that the next call (for testing I only do CloseWorkbook for now, but have tried others as well) fails with status 500 and says “Missing required soap:Body element”. I believe this must be because the sessionID is wrong.
In your UDA, I see you are using the whole string in <OpenWorkbookForEditingResult>. I have not tested this in the on-premises version, so not sure if it is the same that is returned there, but in the Office 365 version, the < OpenWorkbookForEditingResult> contains both a sessionID and a userSessionID. It looks something like this (dots to shorten it down): <OpenWorkbookForEditingResult>{"sessionId":"12.c92a4001aad81.A231....","userSessionID":"6323829d.....","wacCluster":"HK1"}</OpenWorkbookForEditingResult>


I assume the Session ID should be only the “sessionId” part of it? I have extracted that part, with and without the quotation marks, and have tried with the whole result string as well, but no luck. When putting that into the CloseWorkbook method, I am still getting the 500 status. Any idea what the problem might be here?

Wenche

Tuesday, March 14, 2017 4:17:50 AM

re: Nintex Workflow - Updating an Excel Spreadsheet UDA

Hi Vadim,

Thanks a lot for this post. I need to ask what is this close variable in 3rd web service call? I am not able to call it

You must sign in to this site to post comments.
Already Registered?
Sign In
Not Yet Registered?
Register





 

Statistics

  • Entries (246)
  • Comments (1725)

Categories