Saturday, March 1st, 2008
Office Open XML is an XML-based file format specification for electronic documents such as memos, reports, books, spreadsheets, charts, presentations and word processing documents. The specification has been developed by Microsoft as a successor of its binary office file formats and was published by Ecma International as the Ecma 376 standard in December 2006. The format specification is available for free at Ecma International.
Let me introduce you the technique I have used to generate the Excel 2007 sheet from CRM data. I have downloaded an Excel package. ExcelPackage provides server-side generation of Excel 2007 spreadsheets. See http://www.codeplex.com/ExcelPackage for details.
Specifying Output Directory and Template Directory
Output directory is where you place the generated excel file and Template directory is where you place the template file. Template file is simply a .xlsx file without data. Use following code to specify both directories.
DirectoryInfo outputDir = new DirectoryInfo(@”\GenerateExcel\output”);
DirectoryInfo templateDir = new DirectoryInfo(@”\GenerateExcel\templates”);
FileInfo newFile = new FileInfo(outputDir.FullName + @”\File-” + strRecordID + “.xlsx”);
FileInfo template = new FileInfo(templateDir.FullName + @”\TempReport.xlsx”);
if (!template.Exists)
throw new Exception(”Template file does not exist! i.e. template.xlsx”);
Write data to Excel File
ExcelPackage contains library functions for accessing worksheet, cells etc. For details please have a look into the Excel package code. Uses following code to write data to individual cell in excel worksheet.
using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
{
try
{
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[”Sheet1″];
if (worksheet != null)
{
worksheet.Cell(2, 9).Value = ClearText(strData);
xlPackage.Save();
}
}
catch (Exception ex)
{ }
}
Adding Rows to Sheet
worksheet.InsertRow(iRow);
Return Excel File for Download
Simply redirect the response object in ASP.NET to the excel file generated.
Response.Redirect(”http://” + Request.Url.Host + “:” + Request.Url.Port + “/” + “GenerateExcel/output/” + newFile.Name);
All cell styles, formates and pictures objects that you set once in Template will remain the same in the generated file.
Please find below my sample quote geneation application. I have placed a button at Quotes toolbar named Generate Quote. It will generate quote report in excel 2007 with the Quote data from crm currently selected.
My program fetches data from MSCRM through web services and populated my Excel sheet. It then returns my generated excel sheet with options to open or download.
Posted in CRM Integrations, Dynamic CRM 3.0, Dynamic CRM 4.0 | No Comments »
Saturday, March 1st, 2008
Proximity Search allows you to find businesses located around a reference point you provide. For example, hotels or restaurants can provide the nearest location of all automatic teller machines (ATMs). Or a manufacturing company can list all gas stations within a short drive from a supply center. Or a retail outlet can allow website visitors to search for stores that are open early or stores that handle return items. Or you can locate your potential customers nearby an existing customer.
Proximity Searches can be utilized in MSCRM to get maximum benefit of customer data. For example, you can draw your potential customers nearby an existing customer so that you can analyze and refer your potential leads and opportunities to existing customer to get maximum business benefits. In below section, I will demonstrate the idea by showing code snippets and pictures.The FindServiceSoap.FindNearby method is a powerful feature of the MapPoint Web Service SOAP API. Using the FindNearby method, you can add the power of proximity searching to your Web site or solution. You can perform proximity searches on regularly-updated commercial data, such as Yellow Pages listings, which are included with a MapPoint Web Service subscription. Additionally, you can upload custom data and use FindNearby in custom store-locator and brand-finder applications.For further information on how to use this method, please refer MSDN article at:
http://msdn2.microsoft.com/en-us/library/ms980179.aspx.Here are the possible steps to follow in order to draw the above example. 
Following code snippets will help you in understanding different steps involved in calling FindNearBy method.Get Longitude and Latitude for Existing Customer using Find Service
//Find Address Longitude and latitude
FindServiceSoap FindService = new FindServiceSoap ();
FindService.Credentials = new NetworkCredential(”", “”);
FindService.PreAuthenticate = true;�
FindAddressSpecification spec = new FindAddressSpecification();
spec.InputAddress = new Address();
spec.InputAddress.AddressLine = mAddress.Line1;
spec.InputAddress.CountryRegion = mAddress.Country;
spec.InputAddress.Subdivision = mAddress.StateProvince;
spec.InputAddress.PrimaryCity = mAddress.City;
spec.InputAddress.PostalCode = mAddress.Postalcode;�
spec.DataSourceName = “MapPoint.NA”;
FindResults results = FindService.FindAddress(spec);
�
Specifying Search Distance and Longitude and Latitude of Origin point and custom Database from where to search for nearby pointsFindNearbySpecification myFindSpec = new FindNearbySpecification();
//Specify the data source.
myFindSpec.DataSourceName = “Mappoint.InstallerAccounts”;
//Specify the latitude and longitude.
myLatLong.Latitude = 47.63873;
myLatLong.Longitude = -122.131;
myFindSpec.LatLong = myLatLong;
//Specify the distance.
myFindSpec.Distance = 10;
Limit the number of records returned by specifying the Range property//Set the Range property.
myRange.StartIndex = 0;
myRange.Count = 10;
myFindOptions.Range = myRange;�
myFindSpec.Options = myFindOptions;Return only the Properties you are interested in otherwise it will return all the properties from your custom database uploaded in MapPoint//Return only the properties that we’re interested in.
myPropertyNames[0] = “Name”;
myPropertyNames[1] = “AddressLine”;
myPropertyNames[2] = “PrimaryCity”;
myPropertyNames[3] = “RelationshipType”;
myPropertyNames[4] = “longitude/latitude”;�
myFindFilter.PropertyNames = myPropertyNames;�
myFindSpec.Filter = myFindFilter; Specify Filter Expressions to return desired data depending on the entity properties//Specify the text of the Expression.
string myText = “(RlationshipType = {0} AND ” +“Rating > {1} “;
myFilterExpression.Text = myText;�
//Set the paramater values to match the placeholders
//in the Text string.
object[] myParameters = new object[2];
string myFirstParameter = “Installers”;
myParameters[0] = myFirstParameter;
int mySecondParameter = 25;
myParameters[1] = mySecondParameter;
myFilterExpression.Parameters = myParameters;
myFindFilter.Expression = myFilterExpression;
myFindSpec.Filter = myFindFilter;
Perform Proximity Search using FindNearby Method
myFindResults = myFindService.FindNearby(myFindSpec); Find result contains potential customers who reside nearby the existing customer. This also specifies their longitude and latitude data. Now you can simple follow my previous post regarding MSCRM integration with MapPoint to draw these potential customers and existing customer to Map.http://ayazahmad.wordpress.com/2007/06/12/mscrm-and-mappoint-integration-a-picture-is-worth-a-thousand-words/ Possible MapPoint Integrations with Microsoft Dynamics CRM1. Store locators
2. Branch Finders
3. Drawing Customers to analyze Customers
4. Lead/Opportunity Analysis per Region
5. Driving Directions
6. Order Tracking/shipment Tracking
7. Finding Points of Interests in a specific route �
Posted in Dynamic CRM 3.0 | No Comments »
