Archive for June, 2011

Extract InfoPath Forms Data To Excel Using The .NET Client Object Model

Well, this sounded like a good way to go. The client needed to have multiple InfoPath forms being written to a single Excel file on a variable schedule. The Excel file has a very specific format that is required, so CSV wouldn’t cut it. Timer jobs could not be used as the environment had to work like Office 365 (sandboxed code only).

The alternatives considered were:

1. Promote fields to list properties and export to Excel from the list. Discarded due to constraints with promoting repeating table fields.

2. Implement InfoPath rules to write the form information out to a SharePoint list on submit, then automate the list extract to Excel. Discarded as the form had a repeating table, would require code in the InfoPath form and would still require additional code to automate the extraction.

3. Use SharePoint workflow to extract the forms to Excel. Not really possible due without coding and potential Excel file concurrency issues.

4. Use InfoPath rules or workflow to add the records to Excel file on submit. Discarded due to concurrency risks on the Excel file.

 

So what we ended up considering was to use the new Client Object Model, run from a remote workstation in the client’s network, extracting the forms into an Excel file using OpenXML and updating the status of the form after it was extracted.

Sadly, while this ended up being a good choice, there is very little documentation/blogs on accessing InfoPath forms using the client object model. Fortunately, that didn’t stop me and while I had to figure out some stuff on my own, it turns out to be a relatively good way to meet the client requirement.

So, how does one go about this?

I decided to implement the extractor as a console application so it could be easily run in the windows scheduled tasks sub-system. This allows the extract to be scheduled as often as the client likes and because it simply extracts InfoPath forms in a particular status, then updates the status to indicate they have been extracted, it can be run as many times as one would like with no negative impacts.

Pre-Requisites

In order to work with OpenXML, you should install the OpenXML SDK 2.0 on your development machine:

OpenXML SDK 2.0

You will also need to Client Object Model DLLs. These can be found in the SharePoint 14 hive or use the Client Object Model redistributable:

Client Object Model Redistributable

 

Implementation

I implemented the extractor in it’s own class (in case we want to re-use from an alternate client type), and invoke the class from the main in the console exe as follows:

            OrderExtractor extractor = new OrderExtractor();
            extractor.ExtractOrders();

In the class, we need to reference stuff from client object model, SharePoint and OpenXML:

using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Utilities;
using SP = Microsoft.SharePoint.Client;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using OX = DocumentFormat.OpenXml;

I used a couple of class level variables so I wouldn’t have to pass them around all the time:

        protected ClientContext _ctx;
        protected UInt32 _CurrentRow = 2;

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The extractor needs to extract InfoPath forms that represent orders which contain the main order details and a repeating table for the products ordered.

In the main method, I open the client context to the site, open the form library to extract from, select the orders to extract based on a status (using CAML query to minimise the size of the result set). If there are no orders to extract we are done, but if there are, we copy the Excel template to a new file to extract to and loop through each order and extract it to Excel, then upload the Excel file to a document library on the site for easy access.

I used the InfoPath form’s schema (XSD) to generate a class that I can deserialise the InfoPath form into for easy access. There are a number of references on the internet about how to do this so I have not included it here.

Please note a couple of interesting things that have to happen after that:

1. Need to reset the worksheet dimension to cover the new rows added.

2. Save he updated workbook.

        // Extract Orders To Excel
        public void ExtractOrders()
        {
            try
            {

                // Get client context and setup credentials and authentication method
                _ctx = new ClientContext(Properties.Settings.Default.ExtractWebURL);
                SetupContext();

                // Open the form library to extract from
                SP.List formsLib = _ctx.Web.Lists.GetByTitle(Properties.Settings.Default.ExtractFormLibraryName);

                // Select all "Submitted" orders
                CamlQuery query = new CamlQuery();
                query.ViewXml = @"<View><Query><Where><Contains><FieldRef Name='OrderStatus' /><Value Type='Text'>Submitted</Value></Contains></Where></Query><RowLimit>2147483647</RowLimit></View>";
                SP.ListItemCollection items = formsLib.GetItems(query);
                _ctx.Load(items);
                _ctx.ExecuteQuery();

                // Only create the file if there are orders to extract
                if (items.Count > 0)
                {

                    // Create Excel file Orders-ddmmyy-hhmmss.xlsx from template
                    string outFilePath = CreateExtractFile();

                    // Open the extract file using OpenXML
                    using (SpreadsheetDocument extract = SpreadsheetDocument.Open(outFilePath, true))
                    {
                        // Track the row in the spreadsheet to use next, start at 2 as template has header row in row 1.
                        SheetData currentSheetData = extract.WorkbookPart.WorksheetParts.First().Worksheet.GetFirstChild<SheetData>();
                        _CurrentRow = 2;

                        // Loop through each order - extract fields to Excel
                        foreach (SP.ListItem item in items)
                        {
                            ExtractOrderDetails(item, currentSheetData);
                        }

                        // Update the worksheet dimension
                        string lastDimension = "Q" + Convert.ToString((_CurrentRow - 1));
                        extract.WorkbookPart.WorksheetParts.First().Worksheet.SheetDimension.Reference = "A1:" + lastDimension;

                        // Save Excel extract file with extract records
                        extract.WorkbookPart.Workbook.Save();
                        extract.Dispose();
                    }

                    // Upload excel file to destination library
                    string relURL = Path.Combine(Properties.Settings.Default.ExtractsLibraryRelativeURL, Path.GetFileName(outFilePath));
                    using (FileStream upStream = new FileStream(outFilePath, FileMode.Open))
                    {
                        SP.File.SaveBinaryDirect(_ctx, relURL, upStream, true);
                    }
                }
                // Dispose of client context
                _ctx.Dispose(); 
            }
            catch (Exception ex)
            {
                TextFileLogger.LogError("Exception extracting Orders", "Client.Intranet.OrderExtractor.ExtractOrders", ex);
            }
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

There is a bit of interesting stuff going on in SetupContext. This method sets the credentials to use for the connection and also provides a configurable mechanism to handle SharePoint 2010 sites using claims authentication with a call back method:

        private void SetupContext()
        {
            try
            {
                if (Properties.Settings.Default.UseClaimsAuthentication == "True")
                {
                    //Configure the handler that will add the header.
                    _ctx.ExecutingWebRequest += new EventHandler<WebRequestEventArgs>(SetHeader_MixedAuthRequest);
                }
                
                //Set the Windows credentials.
                _ctx.AuthenticationMode = ClientAuthenticationMode.Default;
                System.Net.NetworkCredential credentials = new NetworkCredential(Properties.Settings.Default.CredentialsUser, Properties.Settings.Default.CredentialsPassword, Properties.Settings.Default.CredentialsDomain);
                _ctx.Credentials = credentials;
            }
            catch (Exception ex)
            {
                TextFileLogger.LogError("Exception setting up header and credentials", ex);
            }
        }


        void SetHeader_MixedAuthRequest(object sender, WebRequestEventArgs e)
        {
            try
            {
                //Add the header that tells SharePoint to use Windows authentication.
                e.WebRequestExecutor.RequestHeaders.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");
            }
            catch (Exception ex)
            {
                TextFileLogger.LogError("Exception setting authentication header", ex);
            }
        }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

The next step is to add rows to the Excel file for the order header and additional records for each product on the order:

private void ExtractOrderDetails(SP.ListItem listItem, SheetData sData) { try { // Get the InfoPath XML file string fPath = (string)listItem["FileRef"]; FileInformation fInfo = SP.File.OpenBinaryDirect(_ctx, fPath); // Load file into XML reader and deserialise into a class to work with XmlTextReader reader = new XmlTextReader(fInfo.Stream); XmlSerializer serializer = new XmlSerializer(typeof(Diageo.Intranet.HouseOrderExtractor.Order)); Order order = (Order)serializer.Deserialize(reader); // Determine calculated fields // Requested Delivery Date - depends on home state DateTime reqDelivery; // … // Write out the header record to Excel at current row Row r = new Row(); string rowID = Convert.ToString(_CurrentRow); r.RowIndex = _CurrentRow++; r.Spans = new ListValue<StringValue>() { InnerText = "1:17" }; r.DyDescent = 0.25D; // Add fields to header row r.Append(CreateTextCell(("A" + rowID), "H")); // Order row type - H for order header // r.Append for each cell to add to the row

// Example with deserialised class access to InfoPath form data

r.Append(CreateTextCell(("F" + rowID), order.OrderID)); // Purchase order number

// Add row to Excel
sData.Append(r);

// Write out a row for each order item
foreach (OrderItems orderItem in order.OrderItems)
{
CreateItemRow(orderItem, sData);
}

// Update order status to Processed

// Note: This only works when the list field is a read-write promoted field
listItem["OrderStatus"] = "Processed";
listItem.Update();
// Execute to update item statuses
_ctx.ExecuteQuery();

}
catch (Exception ex)
{
TextFileLogger.LogError("Exception extracting order details to Excel file", ex);
}
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

I used SQLMetal to generate the Order class from the XSD in the InfoPath template. Note that the class name must match exactly with the Main container name in the InfoPath fields view or the deserialise will not work.

Also note how we updated the order status using a promoted property on the form list. Please be aware that the promoted property must be Read-Write for this to work (found that out the hard way Smile). You can make a promoted property read/write only when you publish the InfoPath form (not through advanced form options dialog) by checking the text box “Allow users to edit data in this field by using a datasheet or properties page”, which then make the property writeable on the list column.

For each product in the order, we need to add a row to the Excel file with that products ordering details. The code below demonstrates this as well as showing how I create text cells (I only used text cells but you can use other cell types, just search the internet for some examples, there’s lots around).

private void CreateItemRow(OrderItems orderItem, SheetData sData) { // Create next row Row r = new Row(); string rowID = Convert.ToString(_CurrentRow); r.RowIndex = _CurrentRow++; r.Spans = new ListValue<StringValue>() { InnerText = "1:17" }; r.DyDescent = 0.25D; // Add order item to row r.Append(CreateTextCell(("A" + rowID), "D")); // Order row type - D for order items // r.Append for each item in the row – example using deserialised InfoPtah order object

r.Append(CreateTextCell(("M" + rowID), orderItem.ItemCode)); // Material Number - product code - not used for H records

// Add the row to the file
sData.Append(r);
}

private Cell CreateTextCell(string cellRef, string cellValue)
{
Cell newCell = new Cell();
newCell.CellReference = cellRef;
newCell.DataType = CellValues.InlineString;
newCell.InlineString = new InlineString { Text = new Text { Text = cellValue } };
return newCell;
}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

We then simple setup the console .exe application to run from windows scheduled task and the client has a nice Excel extract they can push into the order management system, which can be retrieved from the file system on the machine where this runs, or easily retrieve manually or programmatically from the SharePoint document library.

It’s an interesting approach and something that will also work on an Office 365 environment when run remotely from a networked client machine in the client’s internal network.

Happy Client Object Model and OpenXML coding!

 

…Derek