Open XML SDK + Office Services: Better Together

I am probably starting to sound like a broken record, but I am really excited about the different types of solutions that are possible when combining the Open XML SDK with Office Services. In the past, I showed you how to leverage the Open XML SDK to accomplish three main scenarios:

1. Push data into Open XML files
2. Pull data out of Open XML files
3. Manipulate Open XML files

In a previous post, I showed you how to use Word Automation Services to update fields, such as a table of contents, within a document. Today, I am going to show you another functionality of Word Automation Services that will allow you to perform file format conversions on the server. For example, you can leverage the service to convert Word documents into PDF. In addition to Word Automation Services, I am going to show you how to leverage Excel Services to recalculate charts and formulas within a workbook.

In today's post, I am going to show you how to leverage all three components, the SDK and the two Office services, to create a very cool mash up document assembly solution.

If you want to jump straight into the code, feel free to download the solution here.

Scenario

Imagine a scenario where I am working for a company that deals with mortgages. One of the services our company provides is creating custom mortgage reports for customers wishing to take out a loan. These reports include rich information and charts that are specific to the customer's mortgage specifics, such as mortgage amount, number of years of the mortgage, and interest rate of the loan. My company uses Excel workbooks to calculate the different financial and mortgage calculation models. In fact, for the sake of this blog post we will use the same workbook mentioned in this post on the Excel team blog. My company has asked me to create a server-side solution that is able to leverage the Excel workbook that contains the mortgage calculation model and generate a customer ready PDF report, which contains rich content, such as charts and data pulled in from the workbook.

Solution

The scenario described above is very similar to the scenario described by Brian Jones in his post that first talked about combining Open XML and Office Services. To accomplish this scenario we will need to take the following actions:

1. Create an Excel workbook that represents the company's mortgage calculation model
2. Create a Word document that represents the look and feel of the final report. This document will contain content controls to demarcate regions where content and data will be merged
3. Create a web part for SharePoint that includes three text fields, which will allow users to input a loan amount, number of years of the loan, and interest rate, and a button, which will allow users to generate the final mortgage report in PDF format
4. Using the Open XML SDK, open the Excel workbook, which contains the mortgage calculation model, and inject the values specified within the three text fields
5. Leverage Excel Services to recalculate the Excel workbook
6. Use the Open XML SDK to pull data and content from the recalculated Excel workbook and push the content into the Word template document
7. Leverage Word Automation Services to convert the Word document into a PDF

Step 1 & 2 – Creating the Right Excel and Word Templates

The right template makes all the difference when creating Office document solutions. The Excel workbook that contains the mortgage calculation model requires three values to perform calculations:

1. Mortgage amount
2. Term (years)
3. Fixed interest rate

In order to make it easy to insert these three values into the workbook, I am going to leverage Defined Names. Defined Names provides a mechanism to demarcate one or more cells in a workbook, which makes it really easy to find content and regions within workbooks. Here is a screenshot of my Excel workbook template:

Notice that this workbook contains pretty rich formulas in order to calculate monthly payments and rich charts.

The Word template document that represents the look and feel of the final report looks pretty similar to other templates I've shown you in the past that leverage content controls. Here is a screenshot of my Word document template:

You can find these two template files here.

Step 3 – Create a Web Part with a Button Command and Text Fields

Our end goal is to create a Web Part that contains three textboxes and a button command that will look like the following:

The easiest way to accomplish this task is to create a Web Part within Visual Studio 2010. We can create a user control ascx page that represents our web part with the following code:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %> <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %> <%@ Import Namespace="Microsoft.SharePoint" %> <%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1UserControl.ascx.cs" Inherits="WordExcelServicesDemo.VisualWebPart1.VisualWebPart1UserControl" %> <style type="text/css"> .style1 { width: 235px; } </style>   <table class="style1"> <tr> <td> Amount:</td> <td> <asp:TextBox ID="AmountTextBox" runat="server"></asp:TextBox> </td> </tr> <tr> <td> Term (Years):</td> <td> <asp:TextBox ID="YearsTextBox" runat="server"></asp:TextBox> </td> </tr> <tr> <td> Interest:</td> <td> <asp:TextBox ID="InterestTextBox" runat="server"></asp:TextBox> </td> </tr> </table> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Generate Report" />

The code behind this ascx page will contain functionality behind the Generate Report button command:

public partial class VisualWebPart1UserControl : UserControl { protected void Page_Load(object sender, EventArgs e) { }   protected void Button1_Click(object sender, EventArgs e) { //Code to perform pushing/pulling/generating of data/content ... } }

So now we're ready to add the Open XML SDK and Office Services code. For the sake of this solution I am going to add my two template documents within a specific templates directory within my SharePoint library called "Excel Template".

Step 4 – Add Data to Excel Workbook

This step is all about inserting data into specific regions within our Excel workbook. I am going to leverage a couple of Open XML SDK code snippets to help out with this task. Specifically, I am going to reuse the following snippets:

• XLGetWorksheetPartByName
• InsertCellInWorksheet

Here is the code necessary to push data into specific regions of an Excel workbook:

protected void Button1_Click(object sender, EventArgs e) { SPWeb myWebSite = SPContext.Current.Web;   string outputExcelFilename = myWebSite.Url + @"/Shared%20Documents/output.xlsx"; string outputWordFilename = myWebSite.Url + @"/Shared%20Documents/output.docx";   string amountTxBx = AmountTextBox.Text; string interestTxBx = InterestTextBox.Text; string termTxBx = YearsTextBox.Text;   if (amountTxBx == "") amountTxBx = "200000"; if (interestTxBx == "") interestTxBx = ".09"; if (termTxBx == "") termTxBx = "30";   //Copy templates SPWeb myWebsite = SPContext.Current.Web;   SPFile templateExcelFile = myWebsite.GetFile(myWebSite.Url + @"/Excel%20Template/Mortgages.xlsx"); templateExcelFile.CopyTo(outputExcelFilename, true); SPFile templateWordFile = myWebsite.GetFile(myWebSite.Url + @"/Excel%20Template/Template.docx"); templateWordFile.CopyTo(outputWordFilename, true);   //Update Excel output file SPFile outputExcel = myWebsite.GetFile(outputExcelFilename); byte[] byteArray = outputExcel.OpenBinary();   using (MemoryStream mem = new MemoryStream()) { mem.Write(byteArray, 0, (int)byteArray.Length); using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(mem, true)) { WorksheetPart sheet1 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet1"); AddCellValues(sheet1, amountTxBx, interestTxBx, termTxBx); }   outputExcel.SaveBinary(mem); }   ... }   protected void AddCellValues(WorksheetPart sheetPart, string amount, string interest, string term) { /* I am going to cheat a bit here and not calculate the Column/Row index of the defined names (You can look https://blogs.msdn.com/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx for more information on how to accomplish this task   <definedName name="AMOUNT">Sheet1!$A$2</definedName> <definedName name="FIR">Sheet1!$A$8</definedName> <definedName name="TERM">Sheet1!$A$5</definedName> * */ Excel.Cell cell = InsertCellInWorksheet(sheetPart.Worksheet, "A2"); cell.CellValue.Text = amount; cell = InsertCellInWorksheet(sheetPart.Worksheet, "A5"); cell.CellValue.Text = term; cell = InsertCellInWorksheet(sheetPart.Worksheet, "A8"); cell.CellValue.Text = interest; }

Step 5 – Recalculate Workbook using Excel Services

Step 4 helped push data into the Excel workbook. Our next task is to recalculate the workbook. This task is pretty easy to do with Excel Services:

protected void Button1_Click(object sender, EventArgs e) { ...   //RECALC workbook ExcelService es = new ExcelService(); Status[] status; string sessionId = es.OpenWorkbookForEditing(outputExcelFilename, "en-US", "en-US", out status); es.CalculateWorkbook(sessionId, CalculateType.CalculateFull, out status); es.SaveWorkbook(sessionId, out status); es.CloseWorkbook(sessionId, out status);   ... }

Step 6 – merge Excel Content Data into Word Document

I've shown this step a number of times in previous blog posts. For this task I am going to leverage the following Open XML SDK code snippets:

• XLGetWorksheetPartByName
• WDGetContentControl
• XLGetCellValueRowCol

Here is the necessary code to accomplish this task:

protected void Button1_Click(object sender, EventArgs e) { ...   outputExcel = myWebsite.GetFile(outputExcelFilename); byte[] byteArrayExcel = outputExcel.OpenBinary();   using (MemoryStream memExcel = new MemoryStream()) { memExcel.Write(byteArrayExcel, 0, (int)byteArrayExcel.Length); using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(memExcel, true)) { WorkbookPart wbPart = spreadsheetDocument.WorkbookPart; WorksheetPart sheet1 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet1"); WorksheetPart sheet2 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet2");   SPFile outputWord = myWebsite.GetFile(outputWordFilename); byte[] byteArrayWord = outputWord.OpenBinary();   using (MemoryStream memWord = new MemoryStream()) { memWord.Write(byteArrayWord, 0, (int)byteArrayWord.Length);   using (WordprocessingDocument myDoc = WordprocessingDocument.Open(memWord, true)) { MainDocumentPart mainPart = myDoc.MainDocumentPart;   Word.SdtElement monthlyPaymentsChartSdt = WDGetContentControl(mainPart, "MonthlyPaymentsChart"); ImportChartFromSpreadsheet(mainPart, monthlyPaymentsChartSdt, sheet2, "rId2");   Word.SdtElement totalPaymentsChartSdt = WDGetContentControl(mainPart, "TotalPaymentsChart"); ImportChartFromSpreadsheet(mainPart, totalPaymentsChartSdt, sheet2, "rId1");   Word.SdtElement interestRateChartSdt = WDGetContentControl(mainPart, "PaymentsInterestRateChart"); ImportChartFromSpreadsheet(mainPart, interestRateChartSdt, sheet1, "rId1");   Word.SdtElement mortgageAmountSdt = WDGetContentControl(mainPart, "MortgageAmount"); AddTextToSdt(mainPart, mortgageAmountSdt, "$" + XLGetCellValueRowCol(wbPart, sheet1, "A", 2));   Word.SdtElement termSdt = WDGetContentControl(mainPart, "Term"); AddTextToSdt(mainPart, termSdt, XLGetCellValueRowCol(wbPart, sheet1, "A", 5));   Word.SdtElement interestRateSdt = WDGetContentControl(mainPart, "InterestRate"); AddTextToSdt(mainPart, interestRateSdt, XLGetCellValueRowCol(wbPart, sheet1, "A", 8));   Word.SdtElement monthlyPaymentSdt = WDGetContentControl(mainPart, "MonthlyPayment"); string amount = XLGetCellValueRowCol(wbPart, sheet1, "B", 11); decimal decAmount; Decimal.TryParse(amount, out decAmount); decAmount = Math.Round(decAmount, 2); AddTextToSdt(mainPart, monthlyPaymentSdt, "$" + decAmount);   Word.SdtElement paymentTableSdt = WDGetContentControl(mainPart, "PaymentTable"); ImportTableFromSpreadsheet(mainPart, paymentTableSdt, wbPart, sheet2, 3, 17, 10, 50); }   outputWord.SaveBinary(memWord); }   } }   ... }   protected void AddTextToSdt(MainDocumentPart mainPart, Word.SdtElement sdt, string text) { sdt.SdtProperties.RemoveAllChildren<Word.SdtPlaceholder>(); Word.Paragraph oldP = sdt.Descendants<Word.Paragraph>().First();   Word.Paragraph p = new Word.Paragraph( new Word.Run( new Word.Text(text)));   OpenXmlElement parent = oldP.Parent; parent.ReplaceChild<Word.Paragraph>(p, oldP); }

One method I am not going to show in this post is how to import a table from a spreadsheet into a Word document since I've already showed you how to accomplish this task in my previous post showing you how to merge Excel, PowerPoint, and Word content together.

Step 7 – Convert Word Document into PDF

Our last step is to convert our Word document into PDF. This task is really easy to accomplish with Word Automation Services. Here is the necessary code to accomplish this task:

protected void Button1_Click(object sender, EventArgs e) { ...   ConversionJob pdfJob = new ConversionJob("Word Automation Services"); pdfJob.Name = "Mortgage Report"; pdfJob.UserToken = myWebsite.CurrentUser.UserToken; pdfJob.AddFile(outputWordFilename, outputWordFilename.Replace(".docx", ".pdf")); pdfJob.Start();   ... }

Only five lines of code!

End Result

Putting everything together and running the code we end up with a PDF that contains all the content from our Word and Excel documents:

Zeyad