Book excerpt -- Chapter 20: Excel Services Workflows

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

This article is an excerpt from Professional Excel Services by Shahar Prish, and property of John Wiley & Sons, Incorporated (ISBN 978-0-470-10486-6) copyright April 2007, all rights reserved. No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

With SharePoint 2007, Microsoft introduced workflow into SharePoint Services. The workflow is based on the Windows Workflow Foundation (WWF) included in .NET 3.0, adding some of its own capabilities to it.

Workflow allows SharePoint managers to cause various operations to run when certain events happen in the system. Events are either items changing or being added to lists and a manual invocation of a workflow. There are also many operations (or activities) defined in the system that can be used when the event occurs. This can be as simple as adding a task to the site or running custom .NET code.

The solution introduced in this chapter contains two workflows. One for calculating workbooks saved to a document library so that the author does not need to calculate them on his machine. The second workflow saves a series of generated snapshots of a document library for consumption by users.

Overview

The overview shows how to use both workflows described in this solution. Since workflow is a new feature in SharePoint, this section will show the whole sequence that needs to be done step by step.

The first example will show how to create a document library that automatically recalculates the workbooks that are stored within it. The second example will again dip into the PremiumModel workbook to show how to create a document library that generates workbook snapshots into another library.

Use

The first example will require a document library to which authors will save their calculation-heavy workbooks. For this example, the library will be called “AutomaticRecalcLibrary.” From that library, the SharePoint contributor can go into the “Document Library Settings” menu item under the “Settings” dropdown, as shown in Figure 20-1.

Figure 20-1

Document library settings

Once inside the document library settings, under the “Permissions and Management” section, the contributor can find the “Workflow settings” options, which will allow him or her to add a workflow process (see Figure 20-2) to the document library. (If there is already workflow present on the document library, this option allows the contributor to either edit it or add a new one.)

Figure 20-2

Workflow settings - add a process

First, the user makes a selection inside the workflow template (in this case the choice was “Workbook Background Calculation”). The workflow then needs to be named (in this case, “AutoCalc”).

Figure 20-3 shows the bottom of the form where other options are available:

Figure 20-3

Other workflow settings options

The Task List option allows users to set where tasks that are derived from running the workflow are written. The History List allows users to choose an alternative list where the history of all that transpired in the workflow goes. Finally, checking “Start this workflow when a new item is created” and the “Start this workflow when an item is changed” will cause the newly created workflow to be issued whenever anything changes inside the document library.

Clicking OK will apply the workflow to the document library. Any additions or changes will now kick off the workflow process that was defined.

Once this is set up, the next step is to make use of the document library. In this case, a complex workbook that takes a long time to calculate gets saved to the library. Figure 20-4 shows the document library and the fact that the workflow is in progress.

Figure 20-4

Workflow progress indicator in document library

Looking at the newly created AutoCalc column in the document library will show the status of the currently active workflow.

When the workflow finishes doing its job, it creates a new workbook in the document library that has the _Calculate postfix appended (as shown in Figure 20-5).

Figure 20-5

Automatic workflow creation

The newly created file has as its Modified By property set to System Account — those are the credentials under which the workflow runs. If the user goes to the properties of the newly created file, he sees a property called __IgnoreCalc set to true — this allows the workflow to ignore this workbook when it’s saved to the document library (without this property, the process would continue ad infinitum — each newly created workbook would spawn a new workflow process, which would in turn cause another one to be created, etc.).

Notifying Users about Problems

In the previous section, when the document library was prepared for use with the workflow process, there was a “Task List” choice. The workflow is aware of this choice and can use it for adding tasks that have to do with the process.

Figure 20-6 shows a new workbook is uploaded to the SharePoint site — this one containing a query table. When finished, the workflow for this workbook will display a generic error string.

Figure 20-6

Workbook with error

The user who saved the workbook can then go to the SharePoint task list — it will contain, among other things, errors arriving from the workflow. Figure 20-7 shows the task that was produced in this instance.

Figure 20-7

Task for resolving workbook error

The task contains complete information about the problem — including the workbook URL and the verbose error that has occurred. Similar tasks get created from other errors as well. Workbooks that fail to calculate, that are corrupt, or that returned a generic error will add a task to the list.

Generated Report Library

The second type of workflow this solution shows is one that is capable of generating multiple workbooks from a single “template” workbook, saving them to a dedicated document library.

Going back to the solution described in Chapter 17, one of the examples shown was a link that automatically generates a workbook that can then be opened inside Excel or Excel Services. The link contained named ranges that were set to values and would then use the GetWorkbook() functionality to get a binary representation that was then streamed to the user.

This workflow employs a similar solution, only it does it automatically and creates ready-made workbook snapshots for immediate consumption. In cases where the process of calculating the workbook is a lengthy operation, this can greatly reduce processing time on the server.

There are two prerequisites for this solution. The first is in the document library, which must contain a special column called “ReportLibraryTarget.” This column is used during the workflow operation to figure out where the workbooks need to be saved.

The second is the information used to generate the multiple workbooks. The data could, conceptually, come from any source — XML files, databases, and so on.

In this example, the data is actually embedded inside Excel, allowing the workbook to be “self-contained” and not require extra files or information. The workbook needs to contain a named range called __ReportData (that’s double underscore and the string ReportData), which will be used to generate all the workbooks.

The workbook this example (shown in Figure 20-8) uses is a modified version of the PremiumModel.xlsx workbook that has been used throughout the book. This one has had a sheet added to it, containing the table of required reports:

Figure 20-8

Example workbook with required reports sheet

The named range __ReportData is a two-dimensional table where the first column represents the postfix string that will be added to the workbook file name when saved to the document library. All the other columns contain names of ranges in Excel that will be populated using the data inside the table.

In this example, the first row in the table will cause the creation of a file called PremiumModel_GadiBitton.xlsx. Before being saved, each of the named ranges ClientName, CarValue, Accident10Years, and Age will be set to “Gadi Bitton”, 20,000, 2, and 40 accordingly.

Once the prerequisites are in place, the next step is to set up the workflow on the document library. This is done in the same way as the previous example. This time the name of the workflow template selected in the list box is “Workbook report-set generator.”

The document library in this example has been set up so that the ReportLibraryTarget column is mandatory. This causes Excel to prompt for that field when saving the workbook to the library (as shown at the top of Figure 20-9).

Figure 20-9

Prompting for mandatory field

Once the user types in the target document library and the file is saved, the workflow will open the workbook, get the range containing the report generation data, and go over each row, creating the appropriate workbook for each row. The resulting list can be seen in Figure 20-10.

As the solution is set up, each of the generated workbooks is the “Published Items” snapshot of the workbook — making sure that these are purely static workbooks for potential user consumption.

Coding the Solution

The solution is based on a Workflow template inside Visual Studio that has been modified to work with SharePoint workflows.

Pes.ExcelWorkflows Project

Since this is not a trivial library project, this section will go through the tasks needed to create it.

The first stage involves using the .NET 3.0 Workflow SDK to create a Workflow library in C#. Figure 20-11 shows the New Project dialog box with the Sequential Workflow Library template selected.

Figure 20-11

Programming a new workflow library

The next stage is to set up Visual Studio so that it can use the SharePoint workflow extensions. For this, the following assemblies need to be added to the list of references assemblies in the project:

  • Microsoft.Office.Workflow.Tasks

  • Microsoft.SharePoint

  • Microsoft.SharePoint.Library

  • Microsoft.SharePoint.WorkflowActions

  • ExcelServicesLibrary (not needed for workflow, but will be used to access Excel Services)

Once it is added to the list of references, the toolbox needs to be updated. To do that, right-click the toolbox and select the Choose Items option. Select Microsoft.SharePoint.WorkflowActions, and click the OK button — the toolbox should now have a list of items that the assembly exports.

The next few sections explain how to code the class responsible for the workflow shown at the beginning of the chapter (the calculation workflow). That class makes use of a few support classes (Helper, TaskException, and DontFailException), which will be shown immediately after. After that, the chapter will show the second workflow (the report library workflow).

CalculationWorkflow Class

By default, the project template adds a workflow called Workflow1.cs to the project. Rename this file to CalculationWorkflow.cs (allowing the environment to rename the class as well). This class contains the sequence of activities that happen when a workflow is activated. The UI allows the developer to graphically design what the workflow looks like. In both workflows shown here, the workflow will be very simplistic, comprising a single activity — the one executed when the workflow starts up.

Double-clicking on the CalculationWorkflow.cs file will bring up the workflow designer. To the middle of the sequence, the toolbox item called OnWorkflowActivated needs to be added, as shown in Figure 20-12.

Adding toolbox item to sequence

The activity should be renamed to onWorkflowActivated (removing the “1” from it). Once all that is done, the next step is to set up the activity to invoke code that needs to execute.

First, the class needs to contain a reference to an instance that will include the information describing what the workflow is about. This information is managed by the SPWorkflowActivationProperties class. Adding that field to the workflow class will make it look like this:

namespace Pes.ExcelWorkflows
{
     public sealed partial class CalculationWorkflow: SequentialWorkflowActivity
     {
          // ...
          public SPWorkflowActivationProperties m_workflowProperties = 
               new SPWorkflowActivationProperties();
          // ...
     }
}

Once this is done, the properties of the activity that was added to the workflow sequence can be edited. The following table shows the values that need to be set inside each of the properties.

Note

Some of the properties have their own properties. The property grid of Visual Studio will display a “+” sign for some properties that represent instances of objects that themselves have properties, allowing to further edit the items.

Property Value Meaning

CorrelationToken

excelCalculationWorkflow

Used to identify the workflow.

CorrelationToken.OwnerActivityName

CalculationWorkflow

The name of the class representing the workflow.

Invoked

OnWorkflowStarted

The event that will cause this work­flow to execute.

WorkflowProperties

Activity=CalculationWorkflow, Path=m_workflowProperties

This allows the workflow to bind the workflow properties that are supplied by the infrastructure to the member that was defined in the code. Setting this property is done by clicking the “…” near the property value, and choosing the field (m_workflowProperties in this case) from the UI.

Once these properties are set (Figure 20-13), the method pointed to from the EventName property needs to be created. This can be added either manually or by selecting the property and choosing the Generate Handlers link at the bottom of the property page.

Figure 20-13

Setting workflow properties

The class is now ready for coding. The first step will be to add the code that will execute when the workflow is activated. The class contains the following extra fields:

public sealed partial class CalculationWorkflow: SequentialWorkflowActivity
{
     private const string IgnoreCalcField = "__IgnoreCalc";

     private  Helper m_helper;
     public SPWorkflowActivationProperties m_workflowProperties = 
          new SPWorkflowActivationProperties();

     public CalculationWorkflow()
     {
          InitializeComponent();
}

The m_helper class (accessible through the Helper property) contains a reference to an instance of the Helper class that will be described later in this chapter. It wraps the SPWorkflowActivationProperties instance and adds some utility functionality on top of it. The Helper class also provides logging support.

The m_workbookData and m_session (which is accessible through the Session property) fields will hold transient instances used by some of the methods in the class.

Note

Some of the strings in this solution reside inside a resource file called Strings. Using the type-safe resource management capabilities of Visual Studio 2005, the system uses the generated Strings class to get access to the strings. The names of the strings are self-explanatory.

When the workflow starts, the OnWorkflowStart() method gets executed by the infrastructure. It takes care to log some information and execute the OperateOnWorkbook() method, which does the actual work of calculating the workbook and saving it.

private void OnWorkflowStarted(object sender, ExternalDataEventArgs e)
{
     m_helper = new Helper(m_workflowProperties);
     Helper.WriteToLog("Starting calculation workflow operation.");
     try
     {
          OperateOnWorkbook();
     }
     catch (TaskException ex)
     {
          Helper.AddTask(ex);
          throw;
     }
     catch (DontFailException ex)
     {
          Helper.WriteToLog(Strings.LogNonFatalException, ex);
     }
     catch (Exception ex)
     {
          Helper.WriteToLog(Strings.LogOuterException, ex);
          throw;
     }
     Helper.WriteToLog("Completed calculation workflow operation.");
}

The first stage of the method makes sure that the m_helper instance is properly initialized. The method wraps the call to OperateOnWorkbook() with a try/catchblock. Depending on the type of error that occurs, the system will do one of three things. If a TaskException (defined later in the chapter) is caught, a task is added to the SharePoint site using the Helper class AddTask() method. If the exception that was caught was the DontFailException (also defined later in this chapter), the workflow returns normally with no error code. This catch clause is useful when the file that was uploaded to the document library is not an Excel workbook that can be loaded by Excel Services. In those cases, the workflow should just complete quietly and do nothing about the file. As a last resort, the generic Exception class is also caught and the information logged to the log file.

The OperateOnWorkbook() method first makes sure that the document library that contains the document is properly configured. It then checks to make sure that the file that was changed or added needs to be calculated by calling the CheckIfUpdateNeeded() method. Once it was determined that the workbook needs to be calculated, the method opens up a new session against Excel Services, recalculates the workbook and saves the result back into the document library:

private void OperateOnWorkbook()
{
     ValidateListSettings();

     Helper.WriteToLog("Opening workbook '{0}' on server '{1}'", 
          Helper.Workbook, 
          Helper.ExcelServices);

     if (!CheckIfUpdateNeeded())
     {
          Helper.WriteToLog("Workbook does not need to be loaded");
          return;
     }

     using (Session session = Helper.CreateWorkbookSession())
     {
          RecalculateWorkbook(session);
          WorkbookData data = GetWorkbookData(session);
          byte[] workbookData = data.RawData;
          SaveToDocumentLibrary(workbookData);
     }
}

The next few pages will go through each of the methods called here and explain how they work.

The Helper.CreateWorkbookSession() call in the middle of OperateOnWorkbook tries to open the workbook on the server and checks any failures that occur to decide what path to take with the workbook. It will be explained in length in the next section.

ValidateListSettings() makes sure that the document library that contains the files is properly configured and has the appropriate fields. In this case, this method adds the “__IgnoreCalc” field to the list if it does not exist there.

Note

Since the workflow operation will save a new file into the document library, it is important to know which files need to be recalculated and which do not. In this case, when the workflow will save the newly calculated workbook to the library, it will make sure it sets the __IgnoreCalc field to “true” so that the newly (and already calculated workbook) does not get recalculated again.

private void ValidateListSettings()
{
     try
     {
          SPList list = Helper.Properties.List;
          if (!list.Fields.ContainsField(IgnoreCalcField))
          {
               string name = list.Fields.Add(
                    IgnoreCalcField, 
                    SPFieldType.Boolean, 
                    false);
               SPField field = list.Fields[name];
               field.Hidden = true;
               field.Update();
          }
     }
     catch
     {
          Helper.WriteToLog(Strings.LogFieldAdditionFailed, IgnoreCalcField);
          throw;
     }
}

First, the method takes the fields off the SPList instance and checks if one by the name of “__IgnoreCalc” (which is the string contained in the IgnoreCalcField constant) is one of them. If it does not exist, a call is made to the Add() method on the SPFieldCollection class to try and add the field. The Hidden property of the field is also turned on to make sure that users won’t be able to see or access it.

The CheckIfUpdateNeeded() method uses the item instance to check and see if the file needs to be recalculated. It does this by checking the “__IgnoreCalc” field and seeing if it’s false:

private bool CheckIfUpdateNeeded()
{
     SPListItem item = Helper.Item;
     object ignoreObject = item[IgnoreCalcField];
     return ignoreObject == null || 
          !((bool)ignoreObject);
}

The RecalculateWorkbook() method uses the created session to call the CalculateWorkbook() method. On any sort of SoapException failure, the method will add a task that explains to the author that the calculation failed. GetWorkbookData() is similar — it tries to get the binary workbook representation from Excel Services and returns it.

private void RecalculateWorkbook()
{
     try
     {
          Session.CalculateWorkbook();
     }
     catch (SoapException ex)
     {
          Helper.WriteToLog(
               "Was unable to calculate workbook. Error was:{0}", ex);
          string message = String.Format(
               Strings.CalculationFailedDescription,
               Helper.Workboook,
               ex.Message);
          throw new TaskException(
               Strings.CalculationFailedTitle,
               message,
               ex);
     }
}
private WorkbookData GetWorkbookData()
{
     WorkbookData data = null;
     Helper.WriteToLog("Getting workbook binary data.");
     try
     {
          data = Session.GetWorkbook(WorkbookType.FullWorkbook);
     }
     catch (SoapException ex)
     {
          Helper.WriteToLog("Unable to get the workbook. Error was: {0}", ex);
          string message = String.Format(Strings.GetWorkbookFailedDescription,
               Helper.Workboook,
               ex.Message);
          throw new TaskException(
               Strings.GetWorkbookFailedTitle,
               message,
               ex);
     }
     return data;
}

Finally, the method that saves the workbook back to SharePoint; SaveToDocumentLibrary() is called. It generates a new file name (postfixed with the _Calculated string) and creates the file in the document library, writing the data retrieved from the session into it. It also sets the “__IgnoreCalc” field to true so that the file will not be processed by the workflow again.

private void SaveToDocumentLibrary()
{
     SPListItem item = Helper.Item;
     try
     {
          SPFile calculatedFile =
               Helper.FindOrAddFile(
                    Helper.GetPostfixedWorkbookName("_Calculated"));
          SPListItem calcItem = calculatedFile.Item;
          calcItem[IgnoreCalcField] = true;
          calcItem.Update();

          Helper.WriteToLog("Trying to save...");
          calculatedFile.SaveBinary(m_workbookData);
     }
     catch (Exception ex)
     {
          Helper.WriteToLog(
               "Tried saving back to library. Failed with error: {0}", 
               ex);
          throw;
     }
}

The Helper.FindOrAddFile() method makes sure that a new file is created if it does not exist. The SPFile.SaveBinary() method is used to persist the file into the SharePoint document library.

Helper Class

The Helper class supplies utility functionality and helper methods to the project. It wraps the SPWorkflowActivationProperties class.

[Serializable]
public class Helper
{
     private SPWorkflowActivationProperties  m_properties;

     public Helper(SPWorkflowActivationProperties properties)
     {
          m_properties = properties;
     }
     // ...
}

The first method in the Helper class opens the workbook that is the subject of the workflow inside Excel Services. The try/catch block checks for some of the possible errors that an Excel Services server can return. Depending on the error that occurred, the system may throw different types of exceptions. If the WorkbookUnsupported error occurs, the call creates a new TaskException instance and fills it with information that will help the author understand what the problem was. (The Message property of a SoapException thrown because of unsupported features inside the workbook contains a text message that explains what the unsupported feature is.) If the file is considered corrupt, the call checks to see what the extension is. If it’s not one of the two supported by Excel Services, the method will gracefully ignore the workbook, throwing a DontFailException. On all the other errors that may come back from Excel Services, the workflow will add a task that contains a generic error message.

internal Session CreateWorkbookSession()
{
     ExcelServices service = new ExcelServices(
          ExcelServicesType.Soap,
          ExcelServices);
     Session session = null;
     try
     {
          session = service.Open(Workboook);
     }
     catch (SoapException soapEx)
     {
          if (soapEx.SubCode.Code.Name == "WorkbookNotSupported")
          {
               string message =
                    String.Format(Strings.UnsupportedWorkbookDescription,
                    Workboook,
                    soapEx.Message);
               throw new TaskException(
                    Strings.UnsupportedWorkbookTitle,
                    message,
                    soapEx);

          }
          else if (soapEx.SubCode.Code.Name == "FileCorrupt")
          {
               string ext = Path.GetExtension(Workboook);
               if (ext.Equals(".xlsx", StringComparison.OrdinalIgnoreCase) ||
                    ext.Equals(".xlsb", StringComparison.OrdinalIgnoreCase))
               {
                    string message =
                         String.Format(Strings.CorruptWorkbookDescription,
                         Workboook);
                    throw new TaskException(
                         Strings.CorruptWorkbookTitle,
                         message,
                         soapEx);
               }
               else
               {
                    throw new DontFailException();
               }
          }
          else
          {
               string message = String.Format(
                    Strings.UnknownOpenErrorDescription,
                    Workboook,
                    soapEx.Message);
               throw new TaskException(
                    Strings.UnknownOpenErrorTitle,
                    message,
                    soapEx);

          }
     }
     return session;
}

The TaskException instances that are thrown will cause the OnWorkflowStarted() method to add tasks to the task list and assign them to the user who authored the workbook.

Another method provided by the Helper class is AddTask(), which takes a TaskException instance and creates a SharePoint task out of it:

public void AddTask(TaskException ex)
{
     WriteToLog("Adding task to {0}.", Properties.TaskList);
     SPListItem task = Properties.TaskList.Items.Add();
     task["Title"] = ex.Title;
     task["Assigned To"] = Properties.OriginatorUser;
     task["Description"] = ex.Message;
     task.Update();
}

The method adds a new SPListItem instance to the task list object (the TaskList property of the SPWorkflowActivationProperties instance is determined by the configuration form shown at the beginning of the chapter).

The method that returns a workbook that is postfixed with another string uses some string manipulations to figure out the name of the file and its extension, which it then puts back together with the postfix:

public string GetPostfixedWorkbookName(string postfix)
{
     string url = Item.File.Url;
     string noExt = url.Substring(0, url.LastIndexOf('.'));
     string ext = Path.GetExtension(url);
     string workbook = String.Format(
          "{0}{1}{2}", 
          noExt,
          postfix,
          ext);
     return workbook;
}

The following two properties also do some string manipulations — the first to return the full URL of the item that the SPWorkflowActivationProperties holds on to, and the second to return the URL for Excel Web Services:

public string Workboook
{
     get
     {
          string workbook = Properties.Web.Url + "/" + Properties.Item.Url;
          return workbook;
     }
}

public string ExcelServices
{
     get
     {
          string server = Properties.Web.Url + "/_vti_bin/ExcelService.asmx";
          return server;
     }
}

The method that does logging uses the File.AppendAllText() static method to append text to a file inside the temporary Windows directory:

public static void WriteToLog(string format, params object[] args)
{
     string fileName = Path.GetTempPath();
     fileName = Path.Combine(fileName, "WFLog.txt");
     string text = String.Format(format, args);
     string final = String.Format("{0}:{1}\r\n", DateTime.Now, text);
     File.AppendAllText(fileName, final);
}

The method also prepends the date and time to each line in the log.

The last method in the Helper class is the one that returns an SPFile instance by either finding it inside SharePoint or by creating it if it does not exist:

internal SPFile FindOrAddFile(string url)
{
     SPWeb web = Properties.Web;
     SPFile file = web.GetFile(url);
     if (file == null)
     {
          web.Files.Add(url, new byte[] { 4 }, true);
     }
     return file;
}

DontFailException Class

When parts of the workflow want to bail out without showing an error to the user, they will throw a DontFailException instance. See the CalculationWorkflow.OnWorkflowStarted() method for the way to use it.

Since there’s no information that’s really needed when this exception is thrown, it has no settable properties in its constructor.

namespace Pes.ExcelWorkflows
{

     [global::System.Serializable]
     public class DontFailException : Exception
     {
          public DontFailException() { }
          protected DontFailException(
            System.Runtime.Serialization.SerializationInfo info,
            System.Runtime.Serialization.StreamingContext context)
               : base(info, context) { }
     }
}

TaskException Class

When a method needs to fail and add a task for the user who changed or added the file, it throws this exception. The exception is initialized with the information needed to create the task:

namespace Pes.ExcelWorkflows
{

     [global::System.Serializable]
     public class TaskException : Exception
     {
          private string m_title;

          public TaskException() { }

          public TaskException(string title, string message) : 
               this(title, message, null) { }

          public TaskException(
               string title, 
               string message, 
               Exception inner) : 
                    base(message, inner) 
          {
               m_title = title;
          }

          protected TaskException(
            System.Runtime.Serialization.SerializationInfo info,
            System.Runtime.Serialization.StreamingContext context)
               : base(info, context) { }

          public string Title
          {
               get { return m_title; }
          }

     }
}

The Message property of the task will be copied to the Description field in the task.Helper.AddTask() is the method used to take this class and transform it into a task in the task list.

ReportGeneratorWorkflow Class

This workflow is somewhat similar to the one described at the beginning of this section. Instead of saving a workbook to the same document library, it uses a range inside the workbook to generate multiple workbooks into a different report library. To create the class, the developer should follow the same steps outlined in the creation of the CalculationWorkflow class.

namespace Pes.ExcelWorkflows
{
     public sealed partial class ReportGeneratorWorkflow: 
SequentialWorkflowActivity
     {
          private const string ReportLibraryTarget = "ReportLibraryTarget";
          private const string ReportMergeRangeName = "__ReportData";
          public SPWorkflowActivationProperties m_workflowProperties = 
               new SPWorkflowActivationProperties();
          private Helper m_helper;
          private string m_targetLibraryName;

          public ReportGeneratorWorkflow()
          {
               InitializeComponent();
          }
          // ...
}

The class defines a constant called ReportLibraryTarget, which is the name of the field that will be used to figure out where the generated reports need to be saved. It also defines ReportMergeRangeName, which is the range that contains the table that contains the various values that need to be set into the workbook before it is saved to the generated report library. m_targetLibraryName will store the contents of the field that is used to determine where the generated reports need to go to.

The OnWorkflowStarted() method, which is executed when the workflow starts, is virtually identical to the one in the CalculationWorkflow class. It also calls a method called OperateOnWorkbook(). This method does the heavy lifting of generating all the workbooks:

private void OperateOnWorkbook()
{
     ExtractTargetLibrary();

     Helper.WriteToLog(
          "Opening workbook '{0}' on server '{1}'", 
          Helper.Workbook, 
          Helper.ExcelServices);

     using (Session session = Helper.CreateWorkbookSession())
     {
          RangeResult result = ExtractReportMergeRange(session);

          List<string> columns = ExtractColumnNames(result);

          GenerateWorkbooksFromRange(session, result, columns);
     }
}

The first step extracts the target library from the document library. Then, the method creates a session and tries to extract the range that contains the information needed to generate the workbooks. Once the method has that range, it extracts the various columns from it and passes all that information into the GenerateWorkbooksFromRanges() method. At the end, the method attempts to dispose of the instance stored inside the session local variable.

ExtractTargetLibrary() figures out what the target library is. It first tries to find the appropriate field in the list (ReportLibraryTarget). If it is not found, a task is created explaining the problem to the user and the workflow fails. If the field is found, the workflow takes its contents and sets it inside the m_targetLibraryName member.

private void ExtractTargetLibrary()
{
     object targetLibraryObject = Helper.Item[ReportLibraryTarget];
     if (targetLibraryObject == null || !(targetLibraryObject is string))
     {
          Helper.WriteToLog("Could not figure out target library from item.");
          string message = String.Format(
               Strings.NoTargetLibraryDescription,
               Helper.Workbook);
          TaskException taskEx = new TaskException(
               Strings.NoTargetLibraryTitle,
               message);
          throw taskEx;
     }

     m_targetLibraryName = ((string)targetLibraryObject).Trim();
     if (!m_targetLibraryName.EndsWith("/"))
     {
          m_targetLibraryName += "/";
     }
     Helper.WriteToLog("Target library is: {0}", m_targetLibraryName);
}

ExtractReportMergeRange() calls into the opened session, asking for the __ReportData named range. If the range does not exist, the workflow fails and a task is added, explaining that that field is needed for reports to be properly generated.

private RangeResult ExtractReportMergeRange(Session session)
{
     try
     {
          RangeResult result = session.GetRangeA1(ReportMergeRangeName);
          return result;
     }
     catch (SoapException soapEx)
     {
          string message = String.Format(
               Strings.ReportMergeRangeNotFoundDescription,
               Helper.Workbook,
               soapEx.Message);
          TaskException taskEx = new TaskException(
               Strings.ReportMergeRangeNotFoundTitle,
               message,
               soapEx);
          throw taskEx;
     }
}

ExtractColumnNames() is the method responsible for getting the header of the range retrieved by ExtractReportMergeRange() and turning it into a list that can then be used by the workflow to determine what ranges need to be set in the workbook:

private List<string> ExtractColumnNames(RangeResult rangeResult)
{
     List<string> columns = new List<string>();
     Row row = rangeResult.RowCollection[0];
     foreach (object col in row.CellCollection)
     {
          if (!(col is string))
          {
               string message = String.Format(
                    Strings.ReportMergeRangeInvalidDescription,
                    Helper.Workbook,
                    "Column header was not a string");
               TaskException taskEx = new TaskException(
                    Strings.ReportMergeRangeInvalidTitle,
                    message);
               throw taskEx;
          }
          columns.Add((string)col);
     }
     return columns;
}

The GenerateWorkbookFromRange() is responsible for setting the ranges into the workbook and calling the SaveWorkbook() method (described later in this section) to place the generated report back in a document library. The method goes over each of the rows in the range returned from the ExtractReportMergeRange() and sets their contents (the cells) back into the workbook into the appropriate named range.

Because this workflow can be a potentially long operation, the workflow knows how to recover from failures that may occur when calling into Excel Services. When such an error occurs, the workflow will retry up to five times to recreate the session. Once all five retries fail, the workflow will error out, adding a task to the user explaining what happened. While not all error cases are covered, the most common ones are.

private Session GenerateWorkbooksFromRange(Session session, RangeResult result, 
List<string> columns)
{
     const int MaxRetries = 5;

     for (int rowIndex = 1; rowIndex < result.RowCollection.Count; rowIndex++)
     {
          int retryCount = 0;
          bool success = false;
          while (!success)
          {
               try
               {
                    if (session == null)
                    {
                         try
                         {
                              session = Helper.CreateWorkbookSession();
                         }
                         catch (Exception ex)
                         {
                              throw new RetryException(ex);
                         }
                    }

                    Row row = result.RowCollection[rowIndex];
                    string postfix = (string)row[0];
                    Helper.WriteToLog(
                         "Preparing workbook for postfix '{0}'", 
                         postfix);
                    for (int colIndex = 1; 
                         colIndex < columns.Count; 
                         colIndex++)
                    {
                         object value = row[colIndex];
                         string namedRange = columns[colIndex];
                         Helper.WriteToLog(
                              "Setting '{0}' to '{1}'.", 
                              columns[colIndex], 
                              value);
                         SetCellInWorkbook(session, namedRange, value);
                    }
                    SaveWorkbok(postfix, session);
                    success = true;
               }
               catch (RetryException ex)
               {
                    Helper.WriteToLog(
                         "Got RetryException - retry #{0}. Error was:{1}",
                         retryCount,
                         ex);
                    if (retryCount == MaxRetries - 1)
                    {
                         string message = String.Format(
                              Strings.MaxRetriesDescription,
                              Helper.Workbook,
                              ex.InnerException);
                         TaskException taskEx = new TaskException(
                              Strings.MaxRetriesTitle,
                              message,
                              ex.InnerException);
                         throw;
                    }
                    session = null;
                    Thread.Sleep(20000);
                    retryCount++;
               }
          }
     }
     return session;
}

The method iterates over all the rows in the returned range. Each row is then iterated and all the values are used with the appropriate column title to call into the SetCellA1() method. For example, if the column header of the second column is “CarValue,” and in the currently iterated row the second cell contains the value 10,000, the workflow will set the value 10,000 into the named range CarValue.

The retry mechanism kicks in only inside the loop — if an error is encountered, the workflow goes to sleep for 20 seconds and sets the session to null (this will cause the session to be recreated at the top of the loop, starting from a fresh one).

The last important method in this class is the one responsible for saving the workbook. It is similar to the one that saves workbooks in the calculation workflow:

private void SaveWorkbok(string postfix, Session session)
{
     try
     {
          string workbook = Helper.GetPostfixedWorkbookName(postfix);
          workbook = Path.GetFileName(workbook);
          workbook = m_targetLibraryName + workbook;

          SPFile file = Helper.FindOrAddFile(workbook);
          Helper.WriteToLog("Getting workbook snapshot.");
          WorkbookData data = session.GetWorkbook(
               WorkbookType.PublishedItemsSnapshot);
          Helper.WriteToLog(
               "Saving binary to Sharepoint. Length = {0}", 
               data.RawData.Length);
          file.SaveBinary(data.RawData);
     }
     catch (WebException webEx)
     {
          throw new RetryException(webEx);
     }
     catch (SoapException soapEx)
     {
          Helper.WriteToLog("Unable to get the workbook. Error was: {0}", soapEx);
          throw new RetryException(soapEx);
     }
}

The method uses the postfix passed into it (the postfix is determined by the leftmost column in the range used for generating the workbooks). It then uses the Helper.FindOrAddFile() method to get the SPFile of the generated report and saves the data retrieved from the workbook into it.

Deploying the Solution

This section explains the steps that are needed to successfully deploy the solution to the server.

There are three things that need to be done for the workflow to be used by SharePoint:

  1. The assembly needs to be added to the GAC. For that, it needs to be strongly named using a key and dragged to the GAC on the SharePoint server machine.

    Note

    It is also possible to use utilities such as the GacUtil.exe provided with Visual Studio to do this or to add it to the setup process. The batch file that will be shown in this section will include an automatic way of GACing the assembly if GacUtil.exe is installed.

  2. SharePoint needs to be notified about the new feature being installed — this is done by creating a “feature” and making SharePoint aware of it by using the stsadm.exe utility. Features allow developers to package a fully functional definition of a feature and deploy it to SharePoint. The file will be named Feature.xml.

  3. The feature.xml file contains a reference to a workflow.xml file, which contains the definition of the workflow. When the feature file is processed by the SharePoint administration utility, the workflow file will be picked up, processed, and installed on the system.

Note

The names feature.xml and workflow.xml are not set in stone. Their name can be anything as long as they are XML files following the correct schema.

In this example, step one can either be done manually on the SharePoint server or automatically (if there’s Visual Studio 2005 or .NET 2.0 SDK installed). Steps 2 and 3 will be done automatically by a batch file, which will be shown.

Feature.xml File

A feature file can contain multiple elements that need to be deployed to the server. In this case, it will contain a single element — the workflow.

<?xml version="1.0" encoding="utf-8"?>
<Feature  Id="{064585F8-3949-4d5f-91F8-AB66D3BE036A}"
          Title="Professional Excel Services Workflows"
          Description="Allows for various activities to take place when an Excel 
2007 file is changed or added to a document library. Professional Excel Services 
book."
          Version="12.0.0.0"
          Scope="Site"
          ReceiverAssembly="Microsoft.Office.Workflow.Feature, Version=12.0.0.0, 
Culture=neutral, PublicKeyToken=71e9bce111e9429c"
          ReceiverClass="Microsoft.Office.Workflow.Feature.WorkflowFeatureReceiver"
          xmlns="https://schemas.microsoft.com/sharepoint/">
  <ElementManifests>
    <ElementManifest Location="workflow.xml" />
  </ElementManifests>
  <Properties>
    <Property Key="GloballyAvailable" Value="true" />
  </Properties>
</Feature>

The following tags and attributes are worth taking note of:

Tag/Attribute Meaning

Feature tag/Id attribute

A GUID that will uniquely identify the feature.

Title tag

Description tag

Human-readable information about the feature.

ReceiverAssembly tag

The assembly that contains the class that knows how to handle the files defined in the ElementManifest tags.

ReceiverClass tag

The class that handles the files defined in the ElementManifest tags.

ElementManifests tag

A collection of elements that will be installed to the server.

ElementManifest tag

A file containing the definition of a single element inside a feature.

In this case, the Feature.xml file is only pointing to a single file containing workflow definitions — the file called Workflow.xml.

Workflow.xml File

When the Feature.xml file described in this chapter is processed by the SharePoint administration utility, it will find that it needs to process the Workflow.xml file. The contents of that file tell SharePoint exactly what workflows will be available:

<?xml version="1.0" encoding="utf-8" ?>
<Elements xmlns="https://schemas.microsoft.com/sharepoint/">
  <Workflow
       Name="Workbook Background Calculation"
       Description="Calculates workbooks that are saved to SharePoint and resaves 
them calculated."
       Id="{C5507572-7459-4999-88D1-71F4DD39CB1C}"
       CodeBesideClass="Pes.ExcelWorkflows.CalculationWorkflow" 
       CodeBesideAssembly="Pes.ExcelWorkflows, Version=1.0.0.0, Culture=neutral, 
PublicKeyToken=70f77e400132c3ed">

    <Categories/>
  </Workflow>
</Elements>

The tags and attributes to consider in this XML are:

Tag/Attribute Meaning

Workflow tag

Defines a single workflow process that will be deployed to the server.

Workflow tag/Name attribute

The name of the workflow — this is the string that appears in the list box when setting up workflows on a document library.

Workflow tag/Description attribute

The description of the workflow, as it appears near the list box, when the workflow is selected.

Workflow tag/Id attribute

Unique GUID identifying the workflow.

Workflow tag/CodeBesideAssembly

The assembly containing the code that implements the workflow.

Workflow tag/CodeBesideClass

The class inside the CodeBesideAssembly that implements the workflow.

Install.bat File

The install.bat file, when executed on the server, will run everything needed to install the workflow. This will probably not be an acceptable solution for deployment on production servers, but for development ones, it should be good enough.

The highlighted parts can be modified to make the batch file work with any feature.

echo Copying the feature...
Set FeatureName=PesExcelWorkflow
Set ServerUrl=http://oss
Set BinaryName=Pes.ExcelWorkflows

Set FeatureLocation=%CommonProgramFiles%\Microsoft Shared\web server 
extensions\12\TEMPLATE\FEATURES\%FeatureName%
Set GacUtilExe=%programfiles%\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe

:: Step 1: Recreate the folder for the feature
rd /s /q "%FeatureLocation%"
mkdir "%FeatureLocation%"

:: Step 2: Copy the appropriate files to the feature directory.
copy /Y feature.xml  "%FeatureLocation%\"
copy /Y workflow.xml "%FeatureLocation%\"

:: Step 3: Try installing the assemblies to the GAC.
echo Adding assemblies to the GAC...

"%GacUtilExe%" -uf %BinaryName%
"%GacUtilExe%" -if bin\Debug\%BinaryName%.dll

:: Step 4: Deactivate and Reactivate the feature on SharePoint
echo Activating the feature...

pushd %CommonProgramFiles%\microsoft shared\web server extensions\12\bin

stsadm -o deactivatefeature -filename %FeatureName%\feature.xml -url %ServerUrl%
stsadm -o uninstallfeature -filename %FeatureName%\feature.xml

stsadm -o installfeature -filename %FeatureName%\feature.xml -force
stsadm -o activatefeature -filename %FeatureName%\feature.xml -url %ServerUrl%

echo Doing an iisreset...

popd
iisreset
pause

The first step makes sure the feature directory exist (the features must be located in the well known folder shown in the batch file). The second step copies the appropriate files into the feature directory. Next, the assembly is installed to the GAC (this will only work if the .NET 2.0 or Visual Studio 2005 is installed). The fourth step uses the stsadm utility to install and activate the feature on the server. Finally, the batch file resets IIS so that the new features are accepted.

Summary

These two classes show how workflow can be used to take time consuming operations and move their execution to the server in a manner that is tightly coupled with the lifetime of the documents themselves.

These examples didn’t even scratch the surface of the functionality of workflow — each of these workflows could have been componentized into separate activities, which could then be used in other workflows to create even more complex templates. On top of that, tools such as the SharePoint designer allow users who do not know how to code to generate their own workflow templates in a completely graphical manner. This chapter exists mainly to show developers how to start using a workflow in SharePoint and to show some of the really nice things that can be done with it.