Creating and Publishing Excel 2010 Documents with Custom SharePoint 2010 Workflows

Article
10/20/2016

Summary: SharePoint Server 2010 provides a rich platform to create line of business applications that integrate with workflow processes and publish information to intranet, Internet, and extranet websites. Open XML file formats make it possible to generate Microsoft Office documents, spreadsheets, and presentations programmatically. By combining the two technologies, you can easily automate common business processes such as document generation and publishing. Learn to generate Excel 2010 spreadsheets inside SharePoint workflows and publish them to SharePoint sites.

Provided by: Todd Baginski, Aptillon, SharePoint Server MVP | About the Author

Contents

Overview
Creating Custom SharePoint Workflows with Visual Studio 2010
Adding Supporting Open XML 2.0 API Helper Methods to the Workflow
Deploying Custom Workflows to SharePoint 2010
Testing Custom Workflows
Conclusion
Additional Resources
About the Author

Download code: Creating and Publishing Excel 2010 Documents with Custom SharePoint 2010 Workflows

Overview

This article is part of a series of articles that describe how to integrate client applications with business processes and workflows:

Integrating Mobile Client Applications with SharePoint 2010 Workflows
Creating and Publishing Excel 2010 Documents with Custom SharePoint 2010 Workflows
Integrating Custom Silverlight Applications with SharePoint Server 2010

Open XML File Formats

Microsoft Office 2010 documents are based on the Open XML standard. The Open XML SDK 2.0 for Microsoft Office 2010 enables you to create and update Office 2010 documents programmatically.

SharePoint Server 2010 Workflows

Microsoft SharePoint Server 2010 workflows are based on the Windows Workflow Foundation. You can create SharePoint workflows within SharePoint sites by using a web browser, in Microsoft SharePoint Designer 2010, or with Microsoft Visual Studio 2010. Microsoft Visual Studio 2010 is required to create workflows which use the Open XML APIs. It is also possible to create custom workflow activities that use the Open XML APIs in Visual Studio, and then publish them to SharePoint servers so the activities may be used inside workflows created by using SharePoint Designer 2010. This article focuses on creating custom workflows with Visual Studio 2010.

SharePoint Foundation 2010

Microsoft SharePoint Foundation 2010 provides an extensible platform that integrates with SharePoint Workflows. This integration enables you to associate workflows with data that is stored in SharePoint lists and document libraries. The workflows can create, read, update and delete items and documents in SharePoint lists. This article describes how to create a SharePoint workflow that reads information from SharePoint list items participating in the workflow and use the list item information to generate an Excel document.

SharePoint 2010 document management capabilities let you securely store and manage documents. After the workflow generates the Excel document, it publishes the document to a SharePoint document library.

Sample Scenario: Generating Invoices

Consider a scenario where a sporting goods store, AdventureWorks Inc., tracks the orders it receives in a SharePoint list on the AdventureWorks intranet. After the orders are added, invoices are generated for the orders and sent to customers. Creating the invoices is a manual process that requires employees to open an Excel document to generate invoices, add the appropriate data to the document, and then save the invoice to a document library on a SharePoint site that can be accessed by customers.

To automate this process, AdventureWorks, Inc. creates a custom workflow to automatically generate invoices for orders after the orders are entered into the SharePoint list. The workflow also publishes the invoices to the SharePoint site that can be accessed by customers. The workflow significantly reduces the time it takes to generate invoices and improves the accuracy of the invoices by eliminating the opportunity for error associated with employees manually generating invoices.

Sample Environment

The scenario relies on the following elements outlined in this section. Before proceeding, set up the sample environment as described:

Orders list. The custom SharePoint Workflow created in this article is published to a SharePoint list named Orders. The Orders list is based on a custom list. The sample code for this article includes the list template necessary to re-create the Orders list. The Orders list contains columns as show in the following figure.

Figure 1. Columns in the Orders list
Intranet site. The Orders list resides in the AdventureWorks intranet (http://intranet/Lists/Orders). The intranet site collection is based on the Publishing Portal site template.
SalesInvoiceTemplate.xlsx file. The workflow uses the Excel document named SalesInvoiceTemplate.xlsx file to create sales invoices. The SalesInvoiceTemplate.xlsx file is stored in the Shared Documents library in the AdventureWorks intranet (http://intranet/Shared Documents/SalesInvoiceTemplate.xlsx).

Figure 2. SalesInvoiceTemplate.xlsx
Extranet site. The workflow publishes the sales invoices that it generates to the AdventureWorks extranet customer site and stores them in a document library named Invoices (http://extranet/Invoices). The extranet site collection is based on the Publishing Portal site template.

Creating Custom SharePoint Workflows with Visual Studio 2010

Microsoft Visual Studio 2010 provides two templates for creating custom SharePoint Workflows: the Sequential Workflow, and the State Machine Workflow. This article uses the Sequential Workflow project template.

Using Visual Studio Templates for Workflows

First, you must create custom SharePoint workflows to generate sales invoice Excel documents and publish them to the extranet site

To create custom SharePoint workflows

Open Visual Studio 2010.
Click File, select New and then click Project.
In the Installed Templates section, expand Visual C#, expand SharePoint, and then select 2010.
To the right side of the Installed Templates section select Sequential Workflow.
In the Name box, type the name that you want to use for the workflow. In this example the project is named SharePoint.Workflow.ExcelGenerator.
In the .NET Framework Version list, at the top of the New Project, dialog select .NET Framework 3.5.
Click OK. The SharePoint Customization Wizard opens.

Figure 3. Creating a Sequential SharePoint Workflow New Project Dialog
In the What site do you want to use for debugging? box, type the URL to the intranet site where the workflow is to be deployed. In this example the URL is http://intranet. Click Next.
In the box What is the name of the workflow?, type the name for the workflow. In this example the workflow is named Sales Invoice Generation.
Select List Workflow and click Next.
Check the Yes, associate this workflow with the following libraries and lists check box.
In the library or list to associate your workflow with list, select the Orders list. If you do not see the orders list in the list, see the "Sample Scenario: Generating Invoices" section to make sure that your environment is set up correctly and contains the Orders list. Click Next.
Check the A user manually starts the workflow check box.
Clear the other checkboxes and click Finish.

Visual Studio 2010 automatically adds the following code to the Worfkflow1.cs class. The following code example creates a Guid that uniquely identifies the workflow.

public Guid workflowId = default(System.Guid);

The following code example creates an instance of the SPWorkflowActivationProperties class. This class retrieves details related to context of the currently executing workflow instance. The workflowProperties class instance retrieves the URL for the site collection the workflow is executing in and details about the Orders list item the workflow is acting upon.

public SPWorkflowActivationProperties workflowProperties = new SPWorkflowActivationProperties();

Adding References to the Open XML 2.0 APIs

To work with the Open XML 2.0 API, add the following references and using statements to the project and the Workflow1.cs class.

To add a reference to the Open XML 2.0 APIs

In Solution Explorer, right-click the References node and select Add Reference.
Click Browse.
Browse to the following location C:\Windows\assembly\GAC_MSIL\DocumentFormat.OpenXml\2.0.5022.0__31bf3856ad364e35\DocumentFormat.OpenXml.dll
Select the DocumentFormat.OpenXml.dll and click OK.
In Solution Explorer, right-click the References node and select Add Reference.
Click .NET.
Select WindowsBase.
Click OK.
In Solution Explorer, right-click Workflow1.cs file and select View Code.

Add the following using statements to the Workflow1.cs class to add support for the Open XML 2.0 API.

   using DocumentFormat.OpenXml.Packaging;
   using DocumentFormat.OpenXml.Spreadsheet;
   using DocumentFormat.OpenXml;

Add the following using statement to support opening the SalesInvoiceTemplate.xlsx file by using a Stream.
```
   using System.IO;
```
Add the following using statement to support the Open XML 2.0 API helper methods that are used in the workflow.
```
   using System.Collections.Generic;
```

Defining Constants Used in the Workflow

Constants are defined in the workflow to indicate where the SalesInvoiceTemplate.xlsx file is stored and where you want to publish the generated sales invoice Excel documents.

To define the constants in the workflow add the following lines of code to the Workflow1.cs file

readonly string[] excelDocumentHeaderColumns = new[] { "A", "B", "C" };
const string sourceDocumentPath = "http://intranet/shared documents/SalesInvoiceTemplate.xlsx";
const string destinationSitePath = "http://extranet";
const string destinationDocumentLibraryName = "Destination Document Library";

The following code example creates a string array that contains the names of the columns in the SalesInvoiceTemplate.xlsx file. These column names insert values into the spreadsheet by using the Open XML 2.0 API.

readonly string[] excelDocumentHeaderColumns = new[] { "A", "B", "C" };

The following code example creates constants representing where you want to store the SalesInvoiceTemplate.xlsx file and the site and document library where you want to publish the generated sales invoice Excel documents.

const string sourceDocumentPath = "http://intranet/shared documents/SalesInvoiceTemplate.xlsx";
const string destinationSitePath = "http://extranet";
const string destinationDocumentLibraryName = "Destination Document Library";

Adding a Code Activity to the Workflow

A code activity is added to the workflow to execute the custom code that is required to generate the sales invoice Excel document and publish it to the extranet website.

To add a code activity

In Visual Studio 2010, click the Toolbox tab to open the Toolbox window
Drag a code activity from the Toolbox and drop it on the design surface for the Workflow1.cs file. Drop the code activity on the arrow below the onWorkflowActivated1 activity on the design surface.
On the design surface, select the codeActivity1 activity.
In the Properties window configure the Name property for the code activity to indicate the activity generates and publishes Excel documents. In this example the code activity is named createAndPublishExcelDocument.
In the Properties window click the Generate Handlers link. This generates the createAndPublishExcelDocument_ExecuteCode(object sender, EventArgs e) event handler in the Workflow1.cs file.

Creating and Publishing Excel Documents in SharePoint Workflows

To create the code activity that uses the SalesInvoiceTemplate.xlsx file to generate sales invoice templates and publish them to the extranet site, add the following code to the createAndPublishExcelDocument_ExecuteCode event handler in the Workflow1.cs class.

SPListItem listItem = workflowProperties.Item;
            
using (SPSite sourceSite = new SPSite(workflowProperties.SiteUrl))
{
   SPFile sourceFile = sourceSite.RootWeb.GetFile(sourceDocumentPath);

   using (SPSite destinationSite = new SPSite(destinationSitePath))
   {
      using (SPWeb web = destinationSite.OpenWeb())
      {
         if (sourceFile != null)
         {
            SPFolder destinationDocumentLibrary = 
   web.Folders[destinationDocumentLibraryName];

            using (Stream sourceFileStream = 
   sourceFile.OpenBinaryStream())
            {
               using (SpreadsheetDocument spreadSheet = 
                  SpreadsheetDocument.Open(sourceFileStream, true))
               {
                  WorksheetPart worksheetPart = 
   ReturnWorksheetPart(spreadSheet, "Sheet1");
                  if (worksheetPart != null)
                  {
                     Worksheet worksheet = worksheetPart.Worksheet;
                     InsertTextCellValue(worksheet, "A", 3, 
      listItem["Customer"].ToString());
                     InsertTextCellValue(worksheet, "A", 4, 
listItem["Address"].ToString());
                     InsertTextCellValue(worksheet, "B", 3, 
"Invoice: " + listItem.Title);
                     InsertTextCellValue(worksheet, "B", 4, "Invoice 
Date: " + DateTime.Now.ToShortDateString());
                     InsertTextCellValue(worksheet, "A", 7, 
listItem["Description"].ToString());
                     InsertNumberCellValue(worksheet, "B", 7, 
listItem["Amount"].ToString());
                     InsertNumberCellValue(worksheet, "B", 9, 
listItem["Amount"].ToString());
                     double tax = 
                        ((double)listItem["Amount"] * .07);
   InsertNumberCellValue(worksheet, "B", 10, 
tax.ToString());
                     double total = ((double)listItem["Amount"] * 
1.07);
                     InsertNumberCellValue(worksheet, "B", 11, 
total.ToString());
   destinationDocumentLibrary.Files.Add("Invoice "
                   + listItem.Title + ".xlsx", 
                        sourceFileStream, true);
                  }
               }
            }
         }
      }
   }
}

The Item property in the workflowProperties class provides access to the current item the workflow is working with. In this scenario the item represents a list item in the Orders list.

SPListItem listItem = workflowProperties.Item;

The SiteUrl property in the workflowProperties class provides access to the current site the workflow is executing within. The SiteUrl property constructs a SPSite object that corresponds to the site collection where the workflow is executing.

SPSite sourceSite = new SPSite(workflowProperties.SiteUrl)

The following code returns a SPFile object that corresponds to the SalesInvoiceTemplace.xlsx file. This file serves as the template for the sales invoice.

SPFile sourceFile = sourceSite.RootWeb.GetFile(sourceDocumentPath);

The following code creates a SPSite object that corresponds to the site collection where the generated sales invoice is to be published.

SPSite destinationSite = new SPSite(destinationSitePath)

The following code creates a SPFolder object that corresponds to the document library where the generated sales invoice is to be published.

SPFolder destinationDocumentLibrary = web.Folders[destinationDocumentLibraryName];

The following code creates a Stream object that corresponds to the sales invoice template file. The OpenBinaryStream method returns the SalesInvoiceTemplate.xlsx file as a Stream.

Stream sourceFileStream = sourceFile.OpenBinaryStream()

The following code creates a SpreadSheetDocument object that corresponds to the sales invoice template file. The Open method loads the SalesInvoiceTemplate.xlsx file. The SpreadSheetDocument class is part of the Open XML 2.0 API.

SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(sourceFileStream, true)

The following code creates a WorksheetPart object that corresponds to the worksheet named Sheet1 in the sales invoice template file.

WorksheetPart worksheetPart = ReturnWorksheetPart(spreadSheet, "Sheet1");

The following code creates a Worksheet object that corresponds to the worksheet named Sheet1 in the sales invoice template file. The Worksheet property uses the Open XML 2.0 API to return the WorkSheet object that then updates values in the worksheet.

Worksheet worksheet = worksheetPart.Worksheet;

The following code inserts values into the worksheet at the appropriate locations. The listitem["<Column Name>"] code retrieves the column values for the Orders list item the workflow is currently working with. The InsertTextCellValue method and InsertNumberCellValue method use the Open XML 2.0 API to insert the column values into the worksheet. These methods use the worksheet object, the column in the worksheet, and the row in the worksheet to locate the cells to update.

InsertTextCellValue(worksheet, "A", 3, 
listItem["Customer"].ToString());
InsertTextCellValue(worksheet, "A", 4, 
listItem["Address"].ToString());
InsertTextCellValue(worksheet, "B", 3, "Invoice: " + listItem.Title);
InsertTextCellValue(worksheet, "B", 4, "Invoice 
Date: " + DateTime.Now.ToShortDateString());
InsertTextCellValue(worksheet, "A", 7, 
listItem["Description"].ToString());
InsertNumberCellValue(worksheet, "B", 7, listItem["Amount"].ToString());
InsertNumberCellValue(worksheet, "B", 9, 
listItem["Amount"].ToString());
double tax = ((double)listItem["Amount"] * .07);
InsertNumberCellValue(worksheet, "B", 10, tax.ToString());
double total = ((double)listItem["Amount"] * 1.07);
InsertNumberCellValue(worksheet, "B", 11, total.ToString());

Note

In the code sample tax and total calculations are performed in the code that generates the Excel document programmatically. The calculations are performed in the workflow because the Open XML 2.0 API does not support executing cell formulas defined in the Excel document. In cases in which the calculations must be defined in cell formulas, you can publish the document to Excel Services. Excel Services supports performing calculations on Excel documents programmatically. Then the workflow could programmatically invoke the formulas defined in the Excel document to perform the calculations.

The following code uses the Add method on the Files collection associated with the document library in the extranet website where the sales invoice is to be published to add the generated sales invoice to the document library. The Title property of the listItem object names the generated sales invoice. The sourceFileStream representing the generated sales invoice specifies the document to save and the true Boolean parameter indicates the file should be overwritten if it already exists.

destinationDocumentLibrary.Files.Add("Invoice " + listItem.Title + ".xlsx", sourceFileStream, true);

This approach shows how the SalesInvoiceTemplate.xlsx file is loaded into a Stream, modified, and then saved directly to the destination document library. No intermediate saving of the document is necessary. After the document is saved to the destination document library, the Stream object associated with the generated document is automatically disposed of because the code was implemented with using statements.

Adding Supporting Open XML 2.0 API Helper Methods to the Workflow

To finish the workflow, add the following helper methods to the Workflow1.cs class.

The ReturnWorksheetPart method uses the Open XML 2.0 API to return a WorkSheetPart given a SpreadSheetDocument and the name of a worksheet.

private static WorksheetPart ReturnWorksheetPart(SpreadsheetDocument document, string sheetName)
{
IEnumerable<Sheet> sheets = 
   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
   Elements<Sheet>().Where(s => s.Name == sheetName);

   if (sheets.Count() == 0)
            {
                return null;
            }

            string id = sheets.First().Id.Value;
            WorksheetPart worksheetPart = 
   (WorksheetPart)document.WorkbookPart.GetPartById(id);

return worksheetPart;
}

The InsertTextCellValue method uses the Open XML 2.0 API to insert a text value into a cell in the worksheet. The worksheet parameter specifies the worksheet to insert the cell in, the column parameter and row parameter locate the cell to update in the worksheet. This method sets the DataType property on the Cell class to the CellValues.String enumeration value to format the cell as a string.

private void InsertTextCellValue(Worksheet worksheet, string column, uint row, string value)
{
Cell cell = ReturnCell(worksheet, column, row);
CellValue v = new CellValue();
v.Text = value;
cell.AppendChild(v);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
worksheet.Save();
}

The InsertNumberCellValue method uses the Open XML 2.0 API to insert a number value into a cell in the worksheet. The worksheet parameter specifies the worksheet to insert the cell in, the column parameter and row parameter locate the cell to update in the worksheet. This method sets the DataType property on the Cell class to the CellValues.Number enumeration value to format the cell as a number.

private void InsertNumberCellValue(Worksheet worksheet, string column, uint row, string value)
{
Cell cell = ReturnCell(worksheet, column, row);
CellValue v = new CellValue();
      v.Text = value;
      cell.AppendChild(v);
      cell.DataType = new EnumValue<CellValues>(CellValues.Number);
      worksheet.Save();
}

The ReturnCell method uses the Open XML 2.0 API to return a given cell in the worksheet. It is called by the InsertTextCellValue method and InsertNumberCellValue method. The worksheet parameter specifies the worksheet locate the cell in, and the columnname parameter and row parameter locate the cell to update in the worksheet.

private static Cell ReturnCell(Worksheet worksheet, string columnName, uint row)
{
Row targetRow = ReturnRow(worksheet, row);

if (targetRow == null)
return null;

return targetRow.Elements<Cell>().Where(c => 
   string.Compare(c.CellReference.Value, columnName + row, 
   true) == 0).First();
}

The ReturnRow method uses the Open XML 2.0 API to return a given row in the worksheet. It is called by the ReturnCell method. The worksheet parameter specifies the worksheet locate the row in, and row parameter locates the row.

private static Row ReturnRow(Worksheet worksheet, uint row)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == row).First();
}

Deploying Custom Workflows to SharePoint 2010

You can use Visual Studio 2010 to deploy the custom workflow to a SharePoint 2010 site for testing. To deploy the custom workflow from Visual Studio 2010, click Build and select Deploy Solution. To deploy the custom workflow to a production server, upload the WSP to the Solution Gallery in Central Administration. Then, enable the associated feature

Testing Custom Workflows

To test the custom workflow

Open the Order list on the intranet website and create an item in the list.

Figure 4. Sample item in the Orders list
Select the new item in the Orders list.
In the ribbon, click Workflows.
In the Start a New Workflow section, click the name of the workflow. In this example, the name is Sales Invoice Generation.

Figure 5. Selecting a workflow to start
After the workflow is complete, the list item in the Orders list indicates this is the case. The Sales Invoice Generation column in the Orders list shows a Completed status for the workflow.

Figure 6. Workflow completed
On the extranet website, open the Invoices document library. Notice the generated sales invoice Excel document is in the document library
Open the generated sales invoice Excel document. Notice the values from the list item in the Orders list are inserted into the appropriate locations in the Excel document.

Figure 7. Generates sales invoice Excel document

Conclusion

This article describes how SharePoint Server 2010 provides a rich platform to create line of business applications which include data in SharePoint lists. It has also shows how to use custom SharePoint workflows to generate Excel documents by using the Open XML 2.0 API and publish the documents to extranet websites.

Additional Resources

The following resources are related to the topics discussed in this article and provide more details about SharePoint workflows, Open XML, and SharePoint Foundation.

Integrating Mobile Client Applications with SharePoint 2010 Workflows
Creating and Publishing Excel 2010 Documents with Custom SharePoint 2010 Workflows
Integrating Custom Silverlight Applications with SharePoint Server 2010
http://www.ecma-international.org/publications/standards/Ecma-376.htm
https://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en
https://msdn.microsoft.com/en-us/netframework/aa663328.aspx
Workflow Technical Articles for SharePoint 2010

About the Author

MVP Contributor Todd Baginski, SharePoint Server MVP, has architected and implemented intranets, extranets, public facing Internet sites, and several custom applications with the Microsoft Windows SharePoint Services and SharePoint Server platforms; in 2003 he began his work during the product’s beta stages. He frequently speaks about SharePoint at many industry leading conferences including TechEd, SharePoint Connections, and the Microsoft SharePoint conference. A SharePoint Master Certification program content author, Todd is a presenter for the Business Data Catalog portion of the training.