On my CRM wiki, I added a page that describes how you can use the Open Source ClosedXML project to run Excel formulas from CRM workflows.
Another way to use ClosedXML in a CRM plug-in is for importing data directly from an Excel file.
I implemented this recently for a client who has several employees who need to import data on a daily basis. They wanted to build out the data in Excel (multiple entities, picklists, custom matching logic, etc.) and quickly get the data into CRM, without the need to export the data first before importing.
One possible design for this is to register a plug-in on the Create message for an Annotation (note) record. The user submits the Excel file as an attachment. The plug-in code gets the message body (Excel contents) from the attachment and passes the data as a Stream to ClosedXML, which loads the Excel contents into an easy-to-query object model. From there, it's simply a matter of getting the cell values and performing your CRUD operations in CRM. For the plug-in, you'll need to use ILMERGE to combine your plug-in assembly with ClosedXML.DLL and Microsoft's Open XML library and register that combined/merged assembly to CRM.
Note that this solution works in CRM Online as well as on-prem. Some .NET assemblies can't run in the Sandbox because of platform constraints in CRM Online but ClosedXML works fine.
(Note: After I wrote the above info, I found an interesting CRM forum post relating to importing data from Excel into CRM. The structure of the data the person presented in the question is not in rows and columns, but that's not a problem because the solution I outlined above can be used to import this type of "unstructured" data without too much effort. Once the Excel content is in the ClosedXML object model, you can reference any cell, range of cells, calculation, etc., so obtaining the data no matter how it's formatted is certainly possible.)
Hi Tim. Great post. I am using ClosedXML in a plugin fro CRM 2015 online. I've merged the files and registered the plugin but the code throws an exception on this line
ReplyDeleteXLWorkbook workbook = new XLWorkbook(stream);
The exeception is System.Security.SecurityException which is the classic problem of executing code in a plugin running in the sandbox. Did you find the same issue with CRM Online?
Hi Charles, In the plugins where I've used ClosedXML, I first stored the data that is intended for an Excel worksheet into a System.Data.DataTable object. I then created a XLWorkbook object and then used the Worksheets.Add method to add the DataTable to the workbook. Here's an example (where dt is the DataTable object):
ReplyDeletevar wb = new XLWorkbook();
var ws = wb.Worksheets.Add(dt);
In my uses of ClosedXML, I haven't tried to create/load a workbook from a stream. I don't see why using a MemoryStream wouldn't work.
If you want to zip up and send your plugin code to me I'll see if I can spot anything that might help. tdutch at gmail.com.
Cheers, Tim