Import From Excel in Batch Job - Dynamics AX

First i will quickly go through all the different approaches that i tried unsuccessfully and then i will discuss my final solution that worked.  i had three unsuccesfull attempts:
1. Standard procedure to import data from using AX's system classes for Excel(SysExcelApplication, SysExcelWorkbooks .. etc) . All these base classes are configured to run only on client (verify the RunOn property). So my program was running fine on client but failed to run on Batch Job on Server.  FYI:  i changed the RunOn property to "Called from" but that didn't help.
My next approach was to do all the excel stuff in C#.net and then consume the .net assembly in AX.

2. Using .Net's System.Data.Oledb :  i chose this namespace instead of Microsoft.Office.InterOp.Excel because this doesn't need the Office Excel installed on the Server. I faced the same issue as in approach 1,  my AX program was able to create an instance for .Net class in AX client, but was failing to create the instance in Batch Job.
3. Using .Net's Microsoft.Office.InterOp.Excel: i was left with this choice and i thought this would work without any issue but the same result. AX client is able to consume my dll but Batch Job was failing.
Final solution:
Thanks to my friend Dusan Chalic for recommending me to use Excel Reader from codeplex, it worked perfectly, here is the C# solution:
a) add reference to Excel.dll (download it from above link in codeplex) in your Visual Studio Project
b) create a method to read the Excel contents into a DataSet instance
c) create a method that will take a row number and return the corresponding row (AX will call this method)
d) Here is C# class that will read data from Excel file :

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Excel;

using System.IO;

using System.Data;

namespace ExcelReader

{

    public class ReadDataFromXL

    {

        DataTable xlDataTable;

        public string RetrieveFromXL(string fileName)

        {

            //pass the filename with path

            IExcelDataReader xlReader = null;

            FileStream xlStream = null;

            DataSet xlDataSet = null;

            string empId, fullName, accPriority, routNum, accNum, accType;

            xlDataTable = new DataTable();

            xlDataTable.Columns.Add("EmpId", typeof(string));

            xlDataTable.Columns.Add("FullName", typeof(string));

            xlDataTable.Columns.Add("AccPriority", typeof(string));

            xlDataTable.Columns.Add("RoutNumber", typeof(string));

            xlDataTable.Columns.Add("AccNum", typeof(string));

            xlDataTable.Columns.Add("AccType", typeof(string));

            try

            {

                xlStream = File.Open(fileName, FileMode.Open, FileAccess.Read);

                //1. Reading from a binary Excel file ('97-2003 format; *.xls)

                xlReader = ExcelReaderFactory.CreateBinaryReader(xlStream);

                //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)

                //xlReader = ExcelReaderFactory.CreateOpenXmlReader(xlStream);

                xlReader.IsFirstRowAsColumnNames = false;

                xlDataSet = xlReader.AsDataSet();

                int rowNumber = 0;

                while (xlReader.Read())

                {

                    rowNumber++;

                    if (rowNumber < 5)

                        continue;

                    

                    empId = SanTryParse(xlReader.GetString(0));

                    fullName = SanTryParse(xlReader.GetString(1));

                    accPriority = SanTryParse(xlReader.GetString(2));

                    routNum = SanTryParse(xlReader.GetString(3));

                    accNum = SanTryParse(xlReader.GetString(4));

                    accType = SanTryParse(xlReader.GetString(5));

                    if (empId == "" && fullName == "" && accPriority == "")

                        break;

                    //Console.WriteLine(string.Format("{0} {1} {2} {3} {4} {5}", empId, fullName, accPriority, routNum, accNum, accType));                    

                    // fill the datatable

                    xlDataTable.Rows.Add(empId, fullName, accPriority, routNum, accNum, accType);

                }

                //Console.WriteLine("Row Count: " + xlDataTable.Rows.Count);

                xlReader.Close();               

            }

            catch (Exception ex)

            {

                if(xlReader != null)

                     xlReader.Close();

            }           

            return "Done";

        }

        public int GetRowCount()

        {

            return xlDataTable.Rows.Count;

        }        

        public string GetRow(int index)

        {

            string empId, fullName, accPriority, routNum, accNum, accType;

            DataRow currRow = xlDataTable.Rows[index];

            int endCol = 5;

            string result;

            empId = SanTryParse(currRow[0]);

            fullName = SanTryParse(currRow[1]);

            accPriority = SanTryParse(currRow[2]);

            routNum = SanTryParse(currRow[3]);

            accNum = SanTryParse(currRow[4]);

            accType = SanTryParse(currRow[5]);

            result = empId + "!" + fullName + "!" + accPriority + "!" + routNum + "!" + accNum + "!" + accType;

            return result;

        }

        private string SanTryParse(object input)

        {

            if (input == null)

                return "";

            return Convert.ToString(input);

        }

    }

}

e) Sign the above VS project, compile and deploy the assembly to GAC.

f) Now the AX part - open AOT -> References node -> right click and Add reference to the above assembly in GAC

g) create a a new batch job Class in AX (Extending RunBaseBatch)

h) create a method , here is the code to call the methods in C# class and read the data from Excel:

void importDataFromXlReader()
{
    Set   permissionSet;
    System.Exception e;
    str result,  currRowStr;
    int totalRows, i, j;
    List values;
    ListIterator iter;
    str valuesArr[6];
    SanTempTable buffer;  // Temporary table to hold the data from Excel
  ;
    try
    {


        permissionSet =  new Set(Types::Class);
        permissionSet.add(new InteropPermission(InteropKind::ClrInterop));
        permissionSet.add(new InteropPermission(InteropKind::ComInterop));
        permissionSet.add(new InteropPermission(InteropKind::DllInterop));
        CodeAccessPermission::assertMultiple(permissionSet);


        xlReader = new ExcelReader.ReadDataFromXL();
        result = xlReader.RetrieveFromXL(fileName);
        //info(result);


        if(result == "Done")
        {
          totalRows = xlReader.GetRowCount();
          if(totalRows <= 0)
             {
               errMessage = "Zero Rows read from XL, there is an issue";
               throw error(errMessage);
             }


          lastRow = totalRows; //lastRow is class vraiable used for ProgressBar
          info(strFmt("Total Rows:  %1", totalRows));


           for(i=0; i<totalRows ; i++)
           {
             currRowStr = xlReader.GetRow(i);
             //info(strFmt("Current Row: %1", currRowStr));
             values = Global::strSplit(currRowStr, '!');
             iter = new ListIterator(values);
             j = 0;
             while(iter.more())
             {
               j++;
               //info(iter.value());
               if(j<=6)
                 valuesArr[j] = iter.value();
               iter.next();
             }
             //info(strFmt("Individual Values: %1 %2 %3 %4 %5 %6 ", valuesArr[1], valuesArr[2], valuesArr[3], valuesArr[4], valuesArr[5], valuesArr[6] ));
             // fill the buffer
             buffer.EmplId = valuesArr[1];
             buffer.EmpName = valuesArr[2];             
             buffer.AccountPriority = str2Int(valuesArr[3]);             
             buffer.RoutingNumber = valuesArr[4];
             buffer.AccountNumber = valuesArr[5];
             buffer.AccountType = valuesArr[6];
             buffer.insert();
           }// end for
        }// end if


        CodeAccessPermission::revertAssert();
    }
    catch(Exception::CLRError)
    {
        info(CLRInterop::getLastException().ToString());
        e = CLRInterop::getLastException();
        errMessage = e.get_Message() + "\n";
        while( e )
        {
           info( e.get_Message() );
           e = e.get_InnerException();
           errMessage = errMessage +  "\n" + e.get_Message();
        }
        throw error(errMessage);
    }
}


with these 2 code snippets we should be able to import data from Excel in AX 2009 Batch Jobs.

Happy Daxing and Cyber Monday shopping
San.

Send Email to multiple recipients using .Net in Dynamics AX

Here is the way to Send Email to multiple recipients  using .Net in Dynamics AX:

    System.Net.Mail.MailMessage             mailMessage;
    System.Net.Mail.SmtpClient              smtpClient;
    str                                     SmtpServer;
    str sub, body, to, fromAddr; 
    
     ;
     
    sub =  "Test Email" ;
    body = "Testing Email, Plz delete this";
    fromAddr = "donotreply@AX-Prod.com";
    to = "user1@test.com,user2@test.com";
    mailMessage = new System.Net.Mail.MailMessage(fromAddr, to);
    SmtpServer = "smtpServer";
    mailmessage.set_Subject(sub);
    mailmessage.set_Body(body);
    smtpClient = new System.Net.Mail.SmtpClient(SMTPServer);
    smtpClient.Send(mailmessage);

Thanks,

San

Update Query Range at Runtime in Dynamics AX

     In Dynamics AX we can update the Query Range at run-time in code,  This is similar to "Filter by Grid" feature except that we are handling this in code. For example my user asked me to provide a Button on Sales LineItems which would toggle between displaying "Current SalesLineItems" VS "All SalesLineItems". By Current SalesLineItems i mean displaying LineItems with more than zero quantity, other addendum to original requirement is to preserve the existing user filters on the form.

In this image you can see a SalesOrder with 3 LineItems (notice that one LineItem has zero quantity), blue arrow pointing to new button("Show Only Current LineItems"):

If the User clicks this new button then :
a) LineItems with zero quantity will be filtered out, in the image below you can see only 2 LineItems
b) the label of the button will be updated to "Show All LineItems" , see the following image:

      
      
Let's start DAXing now - All i need to update for this requirement is SalesTable form:
1.  Add a new boolean variable to ClassDeclaration method
     boolean showOnlyCurrentLineItems;

2. If the user wants to display only current LineItems by default when the form loads, then initialize our boolean variable to true in the Form->init()
    showOnlyCurrentLineItems = true;

3. Now, let's add our query range just before the query is exceuted by overriding the SalesLine DataSource's executeQuery() method (SalesTable -> DataSources->SalesLine->Methods->executeQuery() ) .
Before the super()  method is called we need to add our range to the SalesLine query, AX form maintains two instances of the query objects at run-time (reference) :
 a)  formDataSource.query()  - original datasource query added at the design time to the form's datasource node
 b)  formDataSource.queryRun().query()   - the query originated from a) +  filters applied by the User
So it is better to update both the queries to cover all the scenarios. Here is the code to write within the executeQuery():


public void executeQuery()
{
  // instances for query type b)
 Query  queryForSalesLine;
 QueryBuildDataSource qbdsForSalesLine;
 QueryBuildRange qbrForSalesLine; 

 // instances for Query type a)
  Query  origQueryForsalesLine;
  QueryBuildDataSource origQbdsForSalesLine;
  QueryBuildRange origQbrForSalesLine;
   
  boolean updateOrigQuery;
 
  // check if query type b) exists, if so use it otherwise use Query type a)
  if(salesLine_DS !=null && salesLine_DS.queryRun() != null && salesLine_DS.queryRun().query() != null)
   {
        queryForSalesLine = salesLine_DS.queryRun().query();
        updateOrigQuery = true;
   }
   else
        queryForSalesLine = salesLine_DS.query();
         
   qbdsForSalesLine = queryForSalesLine.dataSourceName("SalesLine");
   qbdsForSalesLine.clearRange(fieldnum(SalesLine, SalesQty));
   qbrForSalesLine = qbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
 
   // update both the queries in both the cases
    if(showOnlyCurrentLineItems)  // this is initialized to true and we will update this variable variable thru a button
    {
        qbrForSalesLine.value(">0");
        if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value(">0");
         }
    }
    else
     {
       qbrForSalesLine.value("*");
       if(updateOrigQuery)
         {
           origQbdsForSalesLine =  salesLine_ds.query().dataSourceName("SalesLine");
           origQbdsForSalesLine.clearRange(fieldnum(SalesLine, salesQty));
           origQbrForSalesLine = origQbdsForSalesLine.addRange(fieldnum(SalesLine,SalesQty));
           origQbrForSalesLine.value("*");
         }
     }
 
    super();
} 
 
4. Add a new Button to the SalesLine section which will allow the User to toggle the Query's range
a)  Add a new Button named "UpdateQueryButton" at SalesTable Form->Designs->[Group:Line] -> [ButtonGroup: ButtonLine]
b) Set its Text property to "Show All Line Items"
c) Override the button's clicked event method with this code:


void clicked()
{
    super();
    if(showOnlyCurrentLineItems)
    {
        showOnlyCurrentLineItems = false;
        this.text("Show Only Current LineItems");  // Update button's text
    }
    else
    {
        showOnlyCurrentLineItems = true;
        this.text("Show All LineItems"); // Update button's text
    }
    
    salesLine_DS.research(true); // this will call executeQuery method that we overrode earlier
}


That is it, you can verify that it preserves the User added Filters/Ranges on the form.
Happy DAXing
-San

Strip Out HTML from Exported CRM data to Excel

    In CRM,  the exported data can have html tags in them for eg: Description field in Email Activities when exported to Excel will have whole lot of HTML tags that it would be almost impossible to clean them manually and extract the actual data. To solve this issue i have written an Add-In for Excel that would do this job.
Let's understand the problem first : In the figure below, the data reflects the exported CRM data to excel. I have highlighted the HTML tags in description column.

Once you install this new Add-In, all you would do is select Description column header (or any column header that contains HTML in its cells), goto Add-Ins tab-> Click "Clean HTML" Button. Once the process is done the program would display a message box to the user.
Image here shows Description column selction and "Clean HTML" button clicking:

Image below shows Description Column without HTML tags:

This is the code that cleans out all the HTML in a string, i.e., it takes the string with HTML tags in it and returns clean text:

public class Html2Text
    {
       private string _htmlStr = "";
       private string _textContents = "";
  
       public string GetContents(string htmlStr)
       {
           this._htmlStr = htmlStr;
           Thread t = new Thread(runInThread);
           t.TrySetApartmentState(ApartmentState.STA);
           t.Start();
           t.Join();
           return _textContents;
       }
   
       private void runInThread()
       {
           WebBrowser wb = new WebBrowser();
           wb.DocumentText = "Cleaning HTML";
           HtmlDocument htmlDoc = wb.Document.OpenNew(true);
           htmlDoc.Write(_htmlStr);
    
           HtmlElementCollection htmlColl =  htmlDoc.All;
           List<string> indvidualTextColl = new List<string>();
   
           for (int i = 0; i < htmlColl.Count; i++)
           {
               HtmlElement currElement =  htmlColl[i];
               if(currElement.TagName.ToUpper() == "P" &&
                               !string.IsNullOrEmpty(currElement.InnerText))
               {
                   indvidualTextColl.Add(currElement.InnerText);
               }
           }
    
           this._textContents = string.Join("\n", indvidualTextColl.ToArray());
       }
    }


Now, let's create the Excel AddIn project and use the above class to strip off HTML:
1. Create a project using VS 2010's Excel 2010 Add-In template and name it ExcelAddIn
Note: we are not going to touch ThisAddIn.cs
2. Add a new CS file and add the above Html2Text class code
3. Add New Item -> Ribbon (Visual Designer)
4. Add a button to the Visual designer with the following properties:
Name:  button1 , Label: Clean HTML
5. Double click the button to goto Code behind file, add the following code to the click event:
Note: In CRM i have noticed that mostly the data is within <P> tag, so i am extracting the data from within <P> tag.

try
  {
     Excel.Worksheet activeWS = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

     int numOfRows = activeWS.UsedRange.EntireRow.Count;
     
     Excel.Range activeCellRange = Globals.ThisAddIn.Application.ActiveCell;
     int currRow = activeCellRange.Row;  // getting the number of rows for Description column
     int currColl = activeCellRange.Column;
     Html2Text obj = new Html2Text();  // instantiate Html2Text object 
     string htmlStr = "";
     string textContents = ""; 
     
     // looping thru all the rows of Description column and fixing the data
     for (int i = 1; i < numOfRows; i++)
     {
         Excel.Range currCell = (Excel.Range)activeWS.Cells[i + 1, currColl];
         //MessageBox.Show(currCell.Value.ToString());                
           if (currCell.Value != null && currCell.Value.ToString() != "")
            {
                  htmlStr = currCell.Value.ToString();
                  if (htmlStr.Contains("<htm") || htmlStr.Contains("<HTML") || htmlStr.Contains("<p>") ||          
                          htmlStr.Contains("<P>") || htmlStr.Contains("<!DOCTYPE") ||
                            htmlStr.Contains("<!doctype") || htmlStr.Contains("<FONT") || 
                             htmlStr.Contains("<font"))
                        { }
                   else
                        continue;
                }
            else
                 continue;
   
            textContents = obj.GetContents(htmlStr);
            currCell.Value2 = textContents;
       }
     
       MessageBox.Show("HTML cleaning is done.", "Office Extension");
   }

catch (Exception ex)
   {
        MessageBox.Show("Error: " + ex.Message);
    }

6. Now just build this project , VS will generate dll, pdb and a VSTO file. Just double click the VSTO file to install this new Office AddIn

7. Open your Excel 2010 or 2007 , verify the appearance of this new button within Ribbon's AddIn Tab and test its functionality

Note: User has to manually save the changes after running this AddIn, this is done intentionally so that User can verify the data and save.

Hope you found this article useful

-San.