buy microsoft office Standard 2007 Handling Workbo

[chaohong63]

Today's author, Sanjay Kulkarni, a Program Manager on the Excel Services team. 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. 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. 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

Payments workbook has information about the clients,office 2007 Professional Plus activation, 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,genuine office Home And Student 2010,334.29 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. the margin rate and some initialization (args.Length > 0)
Console.WriteLine("Margin rate is = "+ args[0] + "%"); the Web service and create a status array object.
ExcelService es = new ExcelService();
Status[] outStatus; = ""; credentials for requests
es.Credentials = System.Net.CredentialCache.DefaultCredentials; 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,microsoft windows 7 x86, "", "", out outStatus); input overhead margin rate if specified omcommand line.
if (args.Length > 0)
es.SetCellA1(sessionId, "RatesSheet", "Margin", args[0]);
data and recalculate the workbook.
es.Refresh(sessionId, "");
es.CalculateWorkbook(sessionId, CalculateType.CalculateFull); 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. the calculated rates from the workbook
double rate1Year, rate3Year, rate5Year, rate30Year;
valObj = es.GetCellA1(sessionId, "RatesSheet", "Rate1Year", false, out outStatus); Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, "RatesSheet", "Rate3Year", false, out outStatus); Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, "RatesSheet", "Rate5Year", false, out outStatus); Convert.ToDouble(valObj);
valObj = es.GetCellA1(sessionId, "RatesSheet", "Rate30Year", false, out outStatus); Convert.ToDouble(valObj); the session for Rates workbook.
es.CloseWorkbook(sessionId); = 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["rate3Year"] = Convert.ToString(rate3Year);
tab["rate5Year"]= Convert.ToString(rate5Year);
tab["rate30Year"]= Convert.ToString(rate30Year); SPFile object so that the properties are stores in the database.
file.Update();
site.Close(); 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. the payment workbook, update the rates from the document properties.
sessionId = es.OpenWorkbook(paymentsWorkbook, "en-US", "en-US",out outStatus); rates from the document properties.
SPSite site1 = new SPSite(paymentsWorkbook);
SPWeb web1 = site.OpenWeb();
SPFile file1 = web.GetFile(paymentsWorkbook);
Hashtable tab1 = file1.Properties; Convert.ToDouble(tab1["rate1Year"]);
rate3Year = Convert.ToDouble(tab1["rate3Year"]);
rate5Year = Convert.ToDouble(tab1["rate5Year"]);
rate30Year = Convert.ToDouble(tab1["rate30Year"]);
site.Close(); rates in the Payment workbook.
es.SetCellA1(sessionId, "Payments", "Rate1Year", rate1Year);
es.SetCellA1(sessionId, "Payments", "Rate3Year", rate3Year);
es.SetCellA1(sessionId, "Payments",office Standard 2007 key, "Rate5Year", rate5Year);
es.SetCellA1(sessionId, "Payments", "Rate30Year", rate30Year); 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!). the workbook to get new payment infomration.
es.CalculateWorkbook(sessionId,buy microsoft office Standard 2007, CalculateType.CalculateFull); 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); = Convert.ToDouble(valObj);
Console.WriteLine("Jaudy's Payment is= 0:C", monthlyPayment); the session for Payments workbook.
es.CloseWorkbook(sessionId); 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.