Create Excel file using OpenXML in Dynamics 365 Plugin

Use OpenXml to create Excel file based on data in Customer (i.e. Account) and attach this file in email and send email to all contacts related to customer.

Let’s say you want to create excel file like this on the fly based on data in customer entity or its related entities.

Step 1- Create Plugin

Create a class library project in VS 2015, like this I done

Add reference of OpenXml like this

Now use ILMerge to merge OpenXml DLL with plugin’s DLL when we will build the project. You can do this easily by including these two packages into your project:

• ILMerge
• MSBuild.ILMerge.Task

For further details how I have done see following screenshots which explains step by step way to do this.

Now install MSBuild.ILMerge.Task

Now simply build the project, the final DLL size should be little bigger as you can see below.

Step 2- Create action

Create a new action by clicking new button

Final look of action should be like this

Note: You must activate action to see it later in plugin step in plugin registration tool. As you can see in “Step 3” below. As you will start typing name of action in Message box, it will show complete name in intellisence.

Step 3 – Register plugin on action

After registering plugin from plugin registration tool, add a new step like this

Step 4 – Call Action by JS

Call the created action by following JS code

function callSendExcelFileInEmailAction() {
    debugger;
    //Alert.showLoading();
    var entityId = Xrm.Page.data.entity.getId().replace(/\{|\}/gi, '');
    var entityName = 'account';
    var entityPluralName = 'accounts';
    var actionUniqueName = 'new_SendExcelFileInEmail';

    var inputParams = null;
    var result = callEntityAction(entityId, entityName, entityPluralName, actionUniqueName, inputParams);
    debugger;
    if (result.ResponseData != null && result.ResponseData != undefined) {
        var pluginTrace = result.ResponseData['PluginTrace'];
        var message = result.ResponseData['Message'];
        if (message == 'Success')
        {
            alert('Excel file created successfully and send to contacts in email.');
        }
        else {
            alert('There is something wrong with file creation or email sending.');
        }
    }
}

Step 5 – Call JS on Customer form

Let’s say we call it on OnLoad of form, simply register this on form like this

Step 6 – Create Email template

I have created following template which is used in plugin code to send email

You have to create an email template like this

Step 7 – Excel file creation code

I created a separate class named “CustomerExcel” to generate excel file. There is a method named “CreateExcelDoc” which is handling all this stuff.

Have a look at the code below

public byte[] CreateExcelDoc()
        {
            MemoryStream memoryStream = new MemoryStream();
            Trace.AppendLine("CE=>---2...Inside CreateExcelDoc()");
            //FileNameWithPath = @"C:\_Temp\UNI STYLE TAILORING BR_131596023494075050_Accounts.xlsx";
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
                //// add styles to sheet                
                Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
                wbsp.Stylesheet = stylesheet1;//CreateStylesheet();
                wbsp.Stylesheet.Save();

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Accounts" };

                sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                sheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                //sheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

                sheets.Append(sheet);

                workbookPart.Workbook.Save();

                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                Columns _cols = new Columns(); // Created to allow bespoke width columns
                CreateColumns(document, worksheetPart.Worksheet, _cols);
                AddHeader(worksheetPart.Worksheet, new List<string>() { CustomerObj.CompanyName, string.Empty, string.Empty, string.Empty });
                worksheetPart = MergeRowCells(worksheetPart);
                AddHeader(worksheetPart.Worksheet, new List<string>() { "Account Number", "Product Name", "Fax", "Main Phone" });

                // Inserting each employee
                UInt32 currentRowNumber = Convert.ToUInt32(sheetData.ChildElements.Count()) + 1;
                int accountNumber = 123456;
                int fax = 92321555;
                int phone = 92321666;

                for (int i = 0; i < 5; i++)
                {
                    //var localDate = DateTime.UtcNow.ToLocalTime();
                    //var localDateTime = DateTime.SpecifyKind(localDate, DateTimeKind.Unspecified);
                    //string fromDate = ConvertCrmDateToUserDate(localDateTime).ToString();
                    //string toDate = ConvertCrmDateToUserDate(localDateTime.AddDays(2)).ToString();

                    string productName = "Product "+ (i+1).ToString();
                    accountNumber++;
                    Row dataRow = CreateExcelRow(accountNumber.ToString(), productName, fax.ToString(), phone.ToString(), currentRowNumber, false);
                    sheetData.AppendChild(dataRow);
                    currentRowNumber++;
                }

                wbsp.Stylesheet = CreateStylesheet_New(); //CreateStylesheet();
                wbsp.Stylesheet.Save();

                int countRows = sheetData.Elements<Row>().Count();

                if (countRows >= 3)
                {
                    Row row_1 = sheetData.Elements<Row>().ElementAt<Row>(0);
                    int index0 = 0;
                    foreach (Cell c in row_1.Elements<Cell>())
                    {
                        c.StyleIndex = Convert.ToUInt32(index0);
                    }

                    Row row_2 = sheetData.Elements<Row>().ElementAt<Row>(1);
                    int index1 = 1;
                    foreach (Cell c in row_2.Elements<Cell>())
                    {
                        c.StyleIndex = Convert.ToUInt32(index1);
                    }

                    for (int i = 0; i < sheetData.Elements<Row>().Count(); i++)
                    {
                        Row dataRow = sheetData.Elements<Row>().ElementAt(i);
                        if (i > 1)
                        {
                            int index2 = 2;
                            foreach (Cell c in dataRow.Elements<Cell>())
                            {
                                c.StyleIndex = Convert.ToUInt32(index2);
                            }
                        }
                    }
                }

                worksheetPart.Worksheet.Save();
            }
            Trace.AppendLine("CE=>---3...Executed method CreateExcelDoc()");
            return memoryStream.ToArray();
        }