Back
Excel

Handling Workbook Dependencies using Excel Services

Today’s author, Sanjay Kulkarni, a Program Manager on the Excel Services team. 

We have heard from several customers about scenarios where they have workbook dependencies. So the output of one workbook is used in other workbooks for calculations. The dependencies could be a result of the business model meaning different people in the organization being responsible for the workbooks or it could simply be a way of isolating related logic in different workbooks. In this blog post we will look at a sample way of how these workbook dependencies can be handled by writing a little code using Excel Services Web Services API and SharePoint object model. Note that you will need MOSS 2007 with Excel Services configured to use what is described here.

To simplify things let us look at a scenario with two workbooks. At a high level workbook one (Rates.xlsx) has mortgage rates that are refreshed from the database. This workbook also adds the overhead and calculates the retail rate that can be offered to customers. The second workbook (Payments.xlsx) has a list of potential customers and information about them. The idea is to calculate the first workbook to get the retail rates and then use these rates to calculate the mortgage payment for the customers in the second workbook. We will use document properties to pass values from the Rates workbook to the Payment workbook.

The Rates workbook has a Pivot Table that has interest rates and a cell that stores the margin. To make the sample work independently we have removed the connection to the external database and instead we get the data from another sheet in the workbook however that can be easily changed. Here is what the Rates workbook looks like.

       

0.75

Margin above the wholesale rates

   
       
       
 

Values

   

Row Labels

Interest Rates

Retail Rates

 

1 Year

4.5

5.25

 

3 Year

5

5.75

 

30 Year

5.875

6.625

 

5 Year

5.25

6

 

7 Year

5.5

6.25

 

  

  

   

The Payments workbook has information about the clients, their mortgage as well as cells where the retail mortgage rates can be updated.

             

Mortgage Type

Interest Rate

         

1 Year ARM

5

         

3 Year ARM

5.5

         

5 Year ARM

5.75

         

7 Year ARM

6

         

30 Year

6.375

         
             
             

Client Name

Email Id

Loan Type

Interest Rate

Loan Term in Years

Principal

Monthly Payment

Jack

jack@contoso.com

3 Year ARM

5.5

30

$350,000

$1,987.26

Judy

judy@contoso.com

5 Year ARM

5.75

30

$400,000

$2,334.29

The following sections describe the steps involved with some code snippets. The complete code and the workbooks can be downloaded at the bottom of this post (see attachments). This is a console application and accepts the margin rate added for overhead as a parameter. We use a fixed constant in the workbook if no margin is specified. To simplify things I have omitted the error handling for the most part. You will definitely need to add robust error handling if you are planning to deploy it for a practical application.

First print the margin rate and some initialization

if (args.Length > 0)
   Console.WriteLine(“Margin rate is = “+ args[0] + “%”);

// Instantiate the Web service and create a status array object.
ExcelService es = new ExcelService();
Status[] outStatus;

string ratesWorkbook = “http://server-name/Site/doclib//Rates.xlsx”;

// Set credentials for requests
es.Credentials = System.Net.CredentialCache.DefaultCredentials;

Now we open the workbook and set the margin rate in the Rates workbook, refresh data and calculate the retail rates.

// Open the Rates workbook, get new rates by refreshing the external data and // calculate. string sessionId = es.OpenWorkbook(ratesWorkbook, “”, “”, out outStatus);

//Set the input overhead margin rate if specified omcommand line.
if (args.Length > 0)
{
  
es.SetCellA1(sessionId, “RatesSheet”, “Margin”, args[0]);
}

// Refresh data and recalculate the workbook.
es.Refresh(sessionId, “”);
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);

Now that the retail rates are calculated; we get the retail rates and set them as document properties on the Payments workbook. The advantages of using the document properties for storing values are firstly the properties are associated with the document so if you update the workbook they will still be preserved. Secondly since they are stored by SharePoint the process of calculating rates and payments can be done at different times if needed by the business process.

//Get the calculated rates from the workbook
double rate1Year, rate3Year, rate5Year, rate30Year;
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate1Year”, false, out outStatus);

rate1Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate3Year”, false, out outStatus);

rate3Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate5Year”, false, out outStatus);

rate5Year = Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate30Year”, false, out outStatus);

rate30Year = Convert.ToDouble(valObj);

// Close the session for Rates workbook.
es.CloseWorkbook(sessionId);

string paymentsWorkbook = http://server-name/Site/doclib/Payments.xlsx;

//Add the different rates as document properties for the payment workbook.
SPSite site = new SPSite(paymentsWorkbook);
SPWeb web = site.OpenWeb();
SPFile file = web.GetFile(paymentsWorkbook);
Hashtable tab = file.Properties;

tab["rate1Year"]= Convert.ToString(rate1Year);
tab["rate3Year"] = Convert.ToString(rate3Year);
tab["rate5Year"]= Convert.ToString(rate5Year);
tab["rate30Year"]= Convert.ToString(rate30Year);

//Update the SPFile object so that the properties are stores in the database.
file.Update();
site.Close();

The next step is to open the Payments workbook, get the retail rates from the document properties and update the Payments workbook with the new rates. Note that since the document properties are stored by SharePoint we can do the following steps at a different time than the updates to the rates that we did above. That could be critical is some business processes since the two could be owned by different people or business units in the organization.

//Open the payment workbook, update the rates from the document properties.
sessionId = es.OpenWorkbook(paymentsWorkbook, “en-US”, “en-US”,out outStatus);

//Read the rates from the document properties.
SPSite site1 = new SPSite(paymentsWorkbook);
SPWeb web1 = site.OpenWeb();
SPFile file1 = web.GetFile(paymentsWorkbook);
Hashtable tab1 = file1.Properties;

rate1Year = Convert.ToDouble(tab1["rate1Year"]);
rate3Year = Convert.ToDouble(tab1["rate3Year"]);
rate5Year = Convert.ToDouble(tab1["rate5Year"]);
rate30Year = Convert.ToDouble(tab1["rate30Year"]);
site.Close();

//Set the rates in the Payment workbook.
es.SetCellA1(sessionId, “Payments”, “Rate1Year”, rate1Year);
es.SetCellA1(sessionId, “Payments”, “Rate3Year”, rate3Year);
es.SetCellA1(sessionId, “Payments”, “Rate5Year”, rate5Year);
es.SetCellA1(sessionId, “Payments”, “Rate30Year”, rate30Year);

Finally we calculate the Payments workbook and print out the monthly payments. To simplify the sample we just print the monthly payments here but this can be easily updated to perform different tasks like sending email to the clients with the information. Or better yet we could compare their existing monthly payment to the new calculated payment and send an email if the new payment is less (It is time to refinance!).

// Recalculate the workbook to get new payment infomration.
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);

// Get the payments and print them. In real life you could add logic here
// to save the workbook or to iterate through the table and send an emai
// using the email id in the workbook.
valObj = es.GetCellA1(sessionId, “Payments”, “JackPayment”, false, out outStatus);
double monthlyPayment = Convert.ToDouble(valObj);
Console.WriteLine(“Jack’s Payment is= {0:C}”, monthlyPayment);
valObj = es.GetCellA1(sessionId, “Payments”, “JudyPayment”, false, out outStatus);  

monthlyPayment = Convert.ToDouble(valObj);
Console.WriteLine(“Jaudy’s Payment is= {0:C}”, monthlyPayment);

// Close the session for Payments workbook.
es.CloseWorkbook(sessionId);

This logic can be extended to scenarios where the output of one workbook serves as the input for several workbooks with some modifications. In the example above we are using specific properties on the workbook. Note that if there are dependencies between multiple workbooks then properties written while updating one workbook may get overwritten by another. Also one may want to use a specific namespace for the properties by prefixing them so that they are easily identifiable.