Collecting AOT object Properties Details in to Excel

One of the user posted in DUG that he required the list of all Classes and its properties from AOT to extract into Excel..  So, here is my solution…..

static void Export_Classes_propertied(Args _args)
{
    SysExcelApplication xlsApplication;
    SysExcelWorkBooks xlsWorkBookCollection;
    SysExcelWorkBook xlsWorkBook;
    SysExcelWorkSheets xlsWorkSheetCollection;
    SysExcelWorkSheet xlsWorkSheet;
    SysExcelRange xlsRange;
    int row = 1;
    str fileName;
    TreeNode classesNode = TreeNode::findNode(@'\Classes');
    TreeNodeIterator iterator = ClassesNode.AOTiterator();
    TreeNode classNode = iterator.next();
    ClassName   className;
    UtilElements utilElements;
    System.DateTime CreatedSysDate, ModifiedDate;
    utcdatetime UtcCreatedDate, UtcModifiedDate;
    str StrCreatedDate, strModifiedDate;
    ;
    //Filename
    fileName = "C:\\Users\\saadullah\\Desktop\\Class.xlsx";  // Define your Excel Path


    //Initialize Excel instance
    xlsApplication = SysExcelApplication::construct();
    //Open Excel document
    //xlsApplication.visible(true);
    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection = xlsApplication.workbooks();
    xlsWorkBook = xlsWorkBookCollection.add();
    xlsWorkSheetCollection = xlsWorkBook.worksheets();
    xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);

    //Excel columns captions
    xlsWorkSheet.cells().item(row,1).value("ID");
    xlsWorkSheet.cells().item(row,2).value("Name");
    xlsWorkSheet.cells().item(row,3).value("Extends");
    xlsWorkSheet.cells().item(row,4).value("RunOn");
    xlsWorkSheet.cells().item(row,5).value("CreatedBy");
    xlsWorkSheet.cells().item(row,6).value("CreatedDateTime");
    xlsWorkSheet.cells().item(row,7).value("ChangedBy");
    xlsWorkSheet.cells().item(row,8).value("ChangedDateTime");
   
    row++;

    while (classNode)
    {
        className = classNode.treeNodeName();
        classNode.AOTgetProperties(true);
        select utilElements
            where utilElements.RecordType == UtilElementType::Class
            && utilElements.Name == className;
        UtcCreatedDate = utilElements.createdDateTime;
        CreatedSysDate = Global::utcDateTime2SystemDateTime(UtcCreatedDate);
        UtcCreatedDate = Global::CLRSystemDateTime2UtcDateTime(CreatedSysDate);
        StrCreatedDate = DateTimeUtil::toStr(UtcCreatedDate);
        UtcModifiedDate = utilElements.modifiedDateTime;
        ModifiedDate = Global::utcDateTime2SystemDateTime(UtcModifiedDate);
        UtcModifiedDate = Global::CLRSystemDateTime2UtcDateTime(ModifiedDate);
        strModifiedDate = DateTimeUtil::toStr(UtcCreatedDate);

        xlsWorkSheet.cells().item(row,1).value(classNode.applObjectId());
        xlsWorkSheet.cells().item(row,2).value(classNode.AOTgetProperty('Name'));
        xlsWorkSheet.cells().item(row,3).value(classNode.AOTgetProperty('Extends'));
        xlsWorkSheet.cells().item(row,4).value(classNode.AOTgetProperty('RunOn'));
        xlsWorkSheet.cells().item(row,5).value(utilElements.createdBy);
        xlsWorkSheet.cells().item(row,6).value(StrCreatedDate);
        xlsWorkSheet.cells().item(row,7).value(utilElements.modifiedBy);
        xlsWorkSheet.cells().item(row,8).value(strModifiedDate);
        classNode = iterator.next();
        
        row++;
    }

    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);

    //Save Excel document
    xlsWorkbook.saveAs(fileName);
    xlsWorkbook.comObject().save();
    //Open Excel document
    xlsApplication.visible(true);

    //Close Excel
    //xlsApplication.quit();
    //xlsApplication.finalize();
}
Advertisements

Import from Excel file using X++ in AX 2009

//Apply this method in any Button Override Method.
// This method is used to Import the Excel Data to AX Table
//
//
// Excel File Contains 3 Columns which is EquipmentId, Cost, EffectiveDate
// Same way here we have created Code For the particular Three field....
public void ImportData()
{
 SysExcelApplication application;
 SysExcelWorkbooks workbooks;
 SysExcelWorkbook workbook;
 SysExcelWorksheets worksheets;
 SysExcelWorksheet worksheet;
 SysExcelCells cells;
 COMVariantType type;
 System.DateTime ShlefDate;
 FilenameOpen filename;
 dialogField dialogFilename;
 Dialog dialog;
 #AviFiles
 // Progress Bar
 SysOperationProgress progress = new SysOperationProgress(1, NoYes::Yes);
 TestXls _TestXls; // Table name and decleration
 str equipId;
 str cost;
 str effectiveDate;
 int datevalue, row = 0;
 real costr;
 Date dater;
 #Excel
 str COMVariant2Str(COMVariant _cv,
 int _decimals = 1,
 int _characters = 0,
 int _separator1 = 0,
 int _separator2 = 0)
 {
 switch(_cv.variantType())
 {
 case (COMVariantType::VT_BSTR):
 return _cv.bStr();
 case (COMVariantType::VT_R4):
 return num2str(_cv.float(),
 _characters,
 _decimals,
 _separator1,
 _separator2);
 case (COMVariantType::VT_R8):
 return num2str(_cv.double(),
 _characters,
 _decimals,
 _separator1,
 _separator2);
 case (COMVariantType::VT_DECIMAL):
 return num2str(_cv.decimal(),
 _characters,
 _decimals,
 _separator1,
 _separator2);
 case (COMVariantType::VT_DATE):
 return date2str(_cv.date(),
 123,
 2,
 1,
 2,
 1,
 4);
 case (COMVariantType::VT_EMPTY):
 return "";
 default:
 throw error(strfmt("@SYS26908",
 _cv.variantType()));
 }
 return "";
 }
 ;
 dialog = new Dialog("ExcelUpload");
 dialogFilename = dialog.addField(typeId(FilenameOpen));
 dialog.filenameLookupFilter(["@SYS28576",#XLS, "@SYS28576",#XLSX]);
 dialog.filenameLookupTitle("Upload from Excel");
 dialog.caption("Excel Upload");
 dialogFilename.value(filename);
 if(!dialog.run()) Continue reading