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
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
{
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.
