CRM 4 Sending Data to Excel and Calculating formulas using the SOAP/REST services

  • Article

In CRM 4, I had made this application for one of my customers, that would let you help you send data to an Excel sheet, calculate data and send it back to CRM. This sheet would be hosted on SharepointP 2010. Pretty simple if you go through the steps below. 

If you are aware, SP 2010 allows you to have Excel sheets and also provides you with a SOAP API, and a REST API to communicate to the sheet. It has a set of methods that you can use to add data into the excel sheet cells, calculate forumals automatically and so on.

Scenario:

In my scenario here, we have this legacy excel sheet that has a bulky set of complex formulas and needs data in the sheet to "calculate" those formulas. This generally happens a lot - we have many offices and businesses that rely on Excel a lot due to the formulas and macros that it can have and easy calculations with the engine. In my case, all the data required for calculating the formulas resides in CRM 4 and i do not want to copy each of that to my Excel sheet and then calculate the formula.

There needs to be a functionality that allows the user to see all the CRM data, send the data to the Excel sheet and make it calculate the formula on the excel sheet itself and bring the results to CRM to display or store. The whole purpose of this scenario is to NOT bring those formulas into CRM, but to use that sheet to do the formulas and calculate it using the Excel engine and bring the data back to CRM. Otherwise you can imagine the pain of gettng those formulas being done in CRM, and the amount of legacy excel sheets and formulas that exist in the world!

What you need to do to implement something of this sort:

1. For that particular entity that stores the required data in CRM 4, create a button on the toolbar using the isv.config. Name it something like "Calculate". This button needs to invoke some js functions that would perform the actual implementation of sending the data to the excel services "in the memory of the excel server" and calculate the formulas and send it back.  

2. The Excel sheet must be available on SP 2010 and should also have the cell names parameters as placeholders so that the API can use them

3. Whenever the user clicks on calculate, the code (jquery, jscript) needs to communicate to the Excel REST or SOAP services. Methods from the API should be used to get the formulas and calculate it on the Excel sheet using the excel engine.

4. Once the result is calculated the data should be brought back to CRM

The steps are simple but the catch here is getting all the cell names as a placeholders with parameter names. It's a tough job, so you could write a small application using the Excel API to do that for you.

Apart from the using the RESt or SOAP API for the Excel services is pretty simple using jscript or jquery.

Here's a snippet of a code I wrote that calculates a formula in Excel using 3 parameters sent from CRM 4. Enjoy!

 

 function GetData()
 {
 var princ = crmForm.all.new_principal;
 var rate = crmForm.all.new_rate;
 var year = crmForm.all.new_year;
 
 var path='https://swgu20117011/ExcelServicesLib/CalcSI.xlsx';
 
 var xmlhttp2 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp2.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp2.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp2.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/OpenWorkbook');
 
 var soap= "<soap:Envelope"+ 
 " xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+ 
 " xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+ 
 " xmlns:xsd='https://www.w3.org/2001/XMLSchema'>"+ 
 "<soap:Body>"+ 
 "<OpenWorkbook xmlns='https://schemas.microsoft.com/office/excel/server/webservices'>"+
 "<workbookPath>"+path+"</workbookPath>"+
 "<uiCultureName>"+'en-Us'+"</uiCultureName>"+
 "<dataCultureName>"+'en-Us'+"</dataCultureName>"+
 "</OpenWorkbook>"+
 "</soap:Body>"+ 
 "</soap:Envelope>";
 
 xmlhttp2.send(soap);
 
 xmlDoc=new ActiveXObject('Microsoft.XMLDOM'); 
 xmlDoc.async=false;
 xmlDoc.loadXML(xmlhttp2.responseXML.xml);
 
 
 var result = xmlDoc.getElementsByTagName('OpenWorkbookResult')[0].childNodes[0].nodeValue;
 
 
 var xmlhttp3 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp3.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp3.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp3.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/SetCellA1');
 var soap2 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'>"+
 "<soap:Body>"+ 
 "<SetCellA1 xmlns='https://schemas.microsoft.com/office/excel/server/webservices'>"+
 "<sessionId>"+result+"</sessionId>"+
 "<sheetName>"+'Sheet1'+"</sheetName>"+
 "<rangeName>"+'Principal'+"</rangeName>"+
 "<cellValue type='s:string' >"+princ.value+"</cellValue>"+
 "</SetCellA1>"+
 "</soap:Body>"+
 "</soap:Envelope>"; 
 
 
 alert(soap2);
 xmlhttp3.send(soap2);
 var out = xmlhttp3.responseXML; 
 alert(out.xml);
 
 
 
 var xmlhttp7 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp7.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp7.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp7.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/GetCellA1');
 var soap6 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'>"+
 "<soap:Body>"+ 
 "<GetCellA1 xmlns='https://schemas.microsoft.com/office/excel/server/webservices'>"+
 "<sessionId>"+result+"</sessionId>"+
 "<sheetName>"+'Sheet1'+"</sheetName>"+
 "<rangeName>"+'Principal'+"</rangeName>"+
 "<formatted>true</formatted>"+
 "</GetCellA1 >"+
 "</soap:Body>"+
 "</soap:Envelope>"; 
 
 alert(soap6);
 xmlhttp7.send(soap6);
 var out = xmlhttp7.responseXML; 
 alert(out.xml);
 
 
 var xmlhttp4 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp4.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp4.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp4.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/SetCellA1');
 var soap3 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'<"
 "<soap:Body<"+ 
 "<SetCellA1 xmlns='https://schemas.microsoft.com/office/excel/server/webservices'<"+
 
 "<sessionId<"+result.value+"</sessionId<"+
 "<sheetName<"+'Sheet1'+"</sheetName<"+
 "<rangeName<"+'Rate'+"</rangeName<"+
 "<cellValue rate.value/<"+
 "</SetCellA1<"+
 "</soap:Body<"+
 "</soap:Envelope<"; 
 
 xmlhttp4.send(soap3);
 
 
 var xmlhttp5 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp5.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp5.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp5.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/SetCellA1');
 var soap4 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'"<
 "<soap:Body<"+ 
 "<SetCellA1 xmlns='https://schemas.microsoft.com/office/excel/server/webservices'<"+
 "<sessionId<"+result.value+"</sessionId<"+
 "<sheetName<"+'Sheet1'+"</sheetName<"+
 "<rangeName<"+'Time'+"</rangeName<"+
 "<cellValue year.value /<"+
 "</SetCellA1<"+
 "</soap:Body<"+
 "</soap:Envelope<"; 
 
 xmlhttp5.send(soap4);
 
 
 
 var xmlhttp6 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp6.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp6.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp6.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/CalculateWorkbook');
 var soap5 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'"<
 "<soap:Body<"+ 
 "<CalculateWorkbook xmlns='https://schemas.microsoft.com/office/excel/server/webservices'<"+
 "<sessionId<"+result.value+"</sessionId<"+
 "<calculateType<CalculateFull</calculateType<"+
 "</CalculateWorkbook <"+
 "</soap:Body<"+
 "</soap:Envelope<"; 
 
 xmlhttp6.send(soap5);
 
 var xmlhttp7 = new ActiveXObject('Msxml2.XMLHTTP');
 xmlhttp7.open('POST', 'https://swgu20117011/_vti_bin/ExcelService.asmx', false);
 xmlhttp7.setRequestHeader('Content-Type', 'text/xml; charset=utf-8');
 xmlhttp7.setRequestHeader('SOAPAction', 'https://schemas.microsoft.com/office/excel/server/webservices/GetCellA1');
 var soap6 = "<soap:Envelope"+
 "xmlns:soap='https://schemas.xmlsoap.org/soap/envelope/'"+
 "xmlns:xsi='https://www.w3.org/2001/XMLSchema-instance'"+
 "xmlns:xsd='https://www.w3.org/2001/XMLSchema'"<
 "<soap:Body<"+ 
 "<GetCellA1 xmlns='https://schemas.microsoft.com/office/excel/server/webservices'<"+
 "<sessionId<"+result+"</sessionId<"+
 "<sheetName<"+'Sheet1'+"</sheetName<"+
 "<rangeName<"+'SimpleInterest'+"</rangeName<"+
 "<formatted<true</formatted<"+
 "</GetCellA1 <"+
 "</soap:Body<"+
 "</soap:Envelope<"; 
 
 xmlhttp7.send(soap6);
 var out = xmlhttp7.responseXML; 
 
 
 
 
 }
 GetData();"