Dynamics AX7 / D3FO: Code for Excel importing
Now
Dynamics AX 365 is running on Web browser so import the data in AX using Excel,
CSV, text etc. has been changed. FileName, FilenameOpen extended data type is
no more supported to browse the excel file.
If
we compare Excel file import process between AX 2012 and Dynamics AX 365 then
in AX 2012 file was importing from Client (from local system) to AX database
directly but now the new AX is running on web server over the IIS so to import
file in AX mean first file need to import on server and need to store in File
server or SharePoint or Database. And then read the file from stored location
may be from File server, SharePoint or Database.
So
how we can import data in AX using Excel file? The simple way is via using Data
entities and if data entity does not exist then need to create new Data entity
for table and then we can import data via using the excel file or any other
supported file using that Data entity.
But
sometime required to import the data in AX using dialog in that case Data
entities will not help, so in this blog I will describe how we can import data
in Dynamics AX 365 using excel file.
In
ax 2012 we have used the below classes to import data in AX via using excel
file:-
SysExcelApplication
application;SysExcelWorkbooks workbooks;SysExcelWorkbook
workbook;SysExcelWorksheets worksheets;SysExcelWorksheet
worksheet;SysExcelCells cells;
But
now in Dynamics AX 365 the above classes does not exist anymore to read excel
file.
So
how we can import data in AX via Excel file? ,
So
first step to import the excel file in AX , create a new class
(TestExcelImport) and extend with RunBase and add a new button in dialog with
upload caption (Same as AX 2012)
Object
Dialog()
{
FormBuildButtonControl buttonControl;
DialogGroup dlgGroup;
FormBuildGroupControl buttonGroup;
dialog
=
super();
dlgGroup =
dialog.addGroup('');
buttonGroup =
dialog.formBuildDesign().control(dlgGroup.formBuildGroup().id());
buttonControl =
buttonGroup.addControl(FormControlType::Button, 'Upload');
buttonControl.text("Upload
file");
buttonControl.registerOverrideMethod(methodStr(FormButtonControl,
clicked),
methodStr(TestExcelImport,
uploadClickedEvent),
this);
return
dialog;
}
|
And
below is the upload button click event (click event already registered in above
dialog method)
private void
uploadClickedEvent(FormButtonControl _formButtonControl)
{
FileUploadTemporaryStorageResult
result = File::GetFileFromUser() as FileUploadTemporaryStorageResult;
If
(result && result.getUploadStatus())
{
result.getFileContentType();
//result.
fileUrl
= result.getDownloadUrl();
info(fileUrl);
}
}
|
FileUploadTemporaryStorageResult is the class has been introduced in AX and this class is
responsible to browse and upload the file on server (On File server, SharePoint
or Database). And from here store the file path (for me fileUrl) in a variable
to read the file later in run method.
Now
next step to read the data from uploaded excel file:-
Public
void run()
{
System.Byte[]
byteArray;
System.IO.Stream stream;
try
{
stream
= File::UseFileFromURL(fileUrl);
this.
readExcelData(stream);
//info("Done");
}
catch(Exception::Error)
{
info(strFmt("%1
%2",Exception::Error,fileUrl));
}
}
|
File
class has been used to read excel data from the url (file location from server)
and will return the data in stream. Now the task is to read excel Stream data
in AX.
So
to read stream data of excel file Microsoft have used ExcelPackage (EPPlus ),
we can manipulate the file using this package (Create excel,create new
worksheet , pivot table , design ,formatting etc.). Developer can access the
classes of EPPlus using OfficeOpenXml namespace.
the below classes is responsible to read the data from stream.
OfficeOpenXml.ExcelWorksheet;
OfficeOpenXml.ExcelPackage;
ExcelPackage
class is responsible to create excel package from stream (can be file
also in place of Stream), ExcelWorksheet class where the worksheet has been
copied with data. And then looping the data of rows from the excel cells.
conData
container variable declared in class declaration of class. So now enjoy
the excel import in AX.
Note:-Import
the data via using CSV or text file still easy and we can use CommaIo or
Comma7Io.
Reading your article is such a privilege. It does inspire me, I hope that you can share more positive thoughts. Visit my site too. The link is posted below.
ReplyDeletedynamics 365 for operations
Hi Marianna,
DeleteThanks a ton! Keep reading :)
Can you share example of periodic data import without selecting a file from dialog . Basically , my requirement is to develop periodic batch job to read files from folder and move it processing folder or error folder depending upon outcome . I have achieved this in AX 2012 . Need detailed example to perform same in D365 . I came across threads like recurring integration and logic apps but none of them mentioned detailed steps to implement this feature.
ReplyDeleteHi Vishal,
ReplyDeleteDo you have example code with a simple excel template that how to use OfficeOpenXml.ExcelWorksheet and OfficeOpenXml.ExcelPackage which mentioned after run()?
Thanks,
Raj
+ readExceldata() ?
Thanks Marry :) Keep reading.
ReplyDeleteHappy D365FO
Informative blog. Thank you for sharing with us.
ReplyDeleteMicrosoft Dynamics AX Online Training
Thank you Mahesh!
Delete
ReplyDeleteThank you for sharing the article. The data that you provided in the
blog is informative and effective.
Thank you Anirudh :) Keep reading!
DeleteThank you Yashnit!
ReplyDeleteThanks for sharing!
ReplyDeleteMicrosoft Dynamics AX Online Training
This is a nice post in an interesting line of content.Thanks for sharing this article, great way of bring this topic to discussion.
ReplyDeleteMySQL Online Training
MSBI Online Training
very useful information, the post shared was very nice.
ReplyDeleteD365 Finance and Operations Online Training
This post was quite awesome and interesting to read. Congrats for your work. Thanks a lot for providing this with us.thanks for shrain this
ReplyDeleteC and C++ Training Institute in chennai | C and C++ Training Institute in anna nagar | C and C++ Training Institute in omr | C and C++ Training Institute in porur | C and C++ Training Institute in tambaram | C and C++ Training Institute in velachery
Thank you for sharing the knowledge about excel.It was very impressive.
ReplyDeleteJava training in Chennai
Java training in Bangalore
Java training in Hyderabad
Java Training in Coimbatore
Java Online Training
I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
ReplyDeleteSalesforce Training in Chennai
Salesforce Online Training in Chennai
Salesforce Training in Bangalore
Salesforce Training in Hyderabad
Salesforce training in ameerpet
Salesforce Training in Pune
Salesforce Online Training
Salesforce Training
Best Power BI Training instittue in Hyderabad https://srimanit.com/
ReplyDeleteBest POWER BI & Tableau Training institute in Hyderabad https://www.kalyanithub.com/
ReplyDelete