Generate sample excel template in ax 2012

 In the business process, its a very regular activity that imports data from an excel file to AX. for that business needs to maintain a proper and fixed template, in this scenario we have created one class to generate the excel file with required field. So user can easily generate the sample file every time if he required.

Here is the code

static void fileSampleGeneration()

{

    SysExcelWorksheetHelper             worksheetHelper;

    SysExcelHelper                      sysExcelHelper;

    SysExcelWorksheet                   worksheet;

    #File

    str                                 worksheetName;

    int                                 i = 1;

    ;

    worksheetName = "@PRU11";

    sysExcelHelper = SysExcelHelper::construct();

    sysExcelHelper.initialize();

    worksheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

    try

    {

        worksheetHelper.addColumn(i, "@SYS27207", Types::Real);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@WAX1240", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS11990", Types::Real);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS14614", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS14719", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS14462", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@WAX1614", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS78255", Types::Date);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@GLS112581", Types::Date);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS5755", Types::Real);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS83318", Types::Real);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SYS9913", Types::Real);

        worksheetHelper.autoFitColumns();

        i ++;

        worksheetHelper.addColumn(i, "@SBC1788", Types::String);

        worksheetHelper.autoFitColumns();

        i ++;

          worksheetHelper.addColumn(i, "@PRU311", Types::String);

            worksheetHelper.autoFitColumns();

            

        }

        sysExcelHelper.launchExcel();

        sysExcelHelper.save("@PRU11");

    }

    catch (Exception::Error)

    {

        exceptionTextFallThrough();

    }

}

Thank you!!

Export purchase order lines to Excel using X++ in AX 2012

 Here is the sample code to export all the purchase order lines details to an excel file, we can use the following method.

Public int getPurchaseLine()

{

    PurchLine               purchLine;

    InventDim               inventDim;

    #File

    CommaTextIo             commaTextIo;

    FileIOPermission        permission;

    int                     lineCount;

    PurchTable              purhTable;

    PurchDeliverySchedule   purchDeliverySchedule;

    ;

    permission = new FileIOPermission(fileName,#io_write);

    permission.assert();

    commaTextIo = new CommaTextIo(fileName,#io_write);

    commaTextIo.outFieldDelimiter(';');

    commaTextIo.write("@SYS15812","@sys312849","@SYS12836","@SYS5398","@RET3637","@SYS73726","@SYS103211","@SYS6437","@SYS14578",

                    "@SYS190813","@SYS16056","@SYS4006394");

    While Select * from purchLine

        Where purchLine.PurchStatus             == PurchStatus::Backorder   &&

              purchLine.ItemId                  != ''                       &&

              purchLine.PurchQty                !=  0                       &&

              purchLine.LineDeliveryType        != LineDeliveryType::OrderLineWithMultipleDeliveries

    join purhTable

        Where purhTable.PurchId                 == purchLine.PurchId

    join * from inventDim

        Where inventDim.inventDimId == purchLine.InventDimId

    notExists Join purchDeliverySchedule

       where purchDeliverySchedule.OrderLine   == purchLine.InventTransId

    {

        if ( purhTable.DocumentState           == VersioningDocumentState::Draft       ||

             purhTable.DocumentState           == VersioningDocumentState::Confirmed   ||

             purhTable.DocumentState           == VersioningDocumentState::Approved )

        {

            commaTextIo.write(purchLine.PurchId,purchLine.LineNumber,purchLine.ItemId,purchLine.itemName(),purchLine.VariantId,inventDim.InventColorId

                        ,inventDim.InventSiteId,inventDim.InventLocationId,purchLine.PurchQty,purchLine.PurchUnit,purchLine.DeliveryDate,purchLine.ConfirmedDlv);

            lineCount++;

        }

    }

    CodeAccessPermission::revertAssert();

    return lineCount;

}