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:
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.
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.
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:
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:
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.
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:
The code behind this ascx page will contain functionality behind the Generate Report button command:
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".
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:
Here is the code necessary to push data into specific regions of an Excel workbook:
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:
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:
Here is the necessary code to accomplish this task:
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.
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:
Only five lines of code!
Putting everything together and running the code we end up with a PDF that contains all the content from our Word and Excel documents: