How to Import/ Export data from AX 2012 into Excel through code

How to write data in Excel from AX through X++ code

Hi..........

here are examples of how to deal import/ export data through excel in AX 2012......

Import data from Excel into AX 2012........

Example-1: Here is an example of importing SubProgram financial dimension from excel file into AX 2012...

static void importFinancialDimension(Args _args)

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Name                    name;

    FileName                filename;

    DimensionFinancialTag   dimFinTag, dimFinTagOrig;

    int row;

    

    application = SysExcelApplication::construct();

    workbooks   = application.workbooks();

    

    //specify the file path that you want to read

    filename = "C:\\Users\\v-vimsin\\Documents\\Vimal\\DIXF\\SubProgram.xlsx";

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1); //Here 1 is the worksheet Number which you can see like Sheet1

    cells       = worksheet.cells();

    

    do

    {

        row++;

        dimFinTag.Value                 = cells.item(row, 3).value().bStr();

        dimFinTag.Description           = cells.item(row, 1).value().toString();

        dimFinTag.FinancialTagCategory  = any2int64(cells.item(row, 2).value().bStr());

        

        select dimFinTagOrig 

            where dimFinTagOrig.FinancialTagCategory == dimFinTag.FinancialTagCategory

                && dimFinTagOrig.Value == dimFinTag.Value;

        if (!dimFinTagOrig)

            dimFinTag.insert();

        type = cells.item(row+1, 1).value().variantType();

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

}

Example-2: In order to import data into AX from Excel through X++..
Excel file import using X++ code:



static void Salestargetdataimport(Args _args) { #AviFiles SysOperationProgress progress = new SysOperationProgress(); SysExcelApplication application; SysExcelWorkbooks workbooks; SysExcelWorkbook workbook; SysExcelWorksheets worksheets; SysExcelWorksheet worksheet; SysExcelWorkSheet workSheetInventTableModule; SysExcelCells cellsInventTableModule; SysExcelCells cells; COMVariantType type; COMVariantType typeModule; DataEntryForm DataEntryForm1; int row = 0; FileIoPermission perm; dipl_monthsofyear months; year1 year1; Country Country2; #define.Filename('D:\Manish_Sales_to_Ax.xlsx') #define.FileMode('R') ;
perm.assert();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(#Filename);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);//.itemFromName("Sales target");
cells = worksheet.cells();
progress.setCaption("Sales target data import...");
progress.setAnimation(#AviTransfer);
try
{
ttsbegin;
do
{
row++;
if (row > 1)
{
select firstonly DataEntryForm1
where DataEntryForm1.SalesPersonName == any2str(cells.item(row, 1).value().bStr())
&& DataEntryForm1.Year1 == str2enum(Year1, any2str(cells.item(row, 4).value().bStr()))
&& DataEntryForm1.MonthsOfYear == str2enum(Months, any2str(cells.item(row, 3).value().bStr()));
if (!DataEntryForm1)
{
DataEntryForm1.SalesPersonName = any2str(cells.item(row, 1).value().bStr());
DataEntryForm1.Country = str2enum(Country2, any2str(cells.item(row, 2).value().bStr()));
DataEntryForm1.MonthsOfYear = str2enum(Months, any2str(cells.item(row, 3).value().bStr()));
DataEntryForm1.Year1 = str2enum(Year1, any2str(cells.item(row, 4).value().bStr()));
if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::April || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::May
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::June)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q1;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::July || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::August
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::September)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q2;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::October || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::November
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::December)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q3;
}
else if (DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::January || DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::February
|| DataEntryForm1.MonthsOfYear == Dipl_MonthsOfYear::March)
{
DataEntryForm1.DIPL_QuarterOfYear = DIPL_QuarterOfYear::Q4;
}
DataEntryForm1.OrderBookClosuresPlanned = cells.item(row, 5).value().double();
DataEntryForm1.OrderBookClosuresActual = cells.item(row, 6).value().double();
if (DataEntryForm1.OrderBookClosuresPlanned || DataEntryForm1.OrderBookClosuresActual)
{
DataEntryForm1.AcheivedPercentOrderBookClosure = (DataEntryForm1.OrderBookClosuresActual/DataEntryForm1.OrderBookClosuresPlanned) * 100;
}
DataEntryForm1.SalesPipeLinePlanned = cells.item(row, 7).value().double();
DataEntryForm1.SalesPipeLineActual = cells.item(row, 8).value().double();
if (DataEntryForm1.SalesPipeLinePlanned || DataEntryForm1.SalesPipeLineActual)
{
DataEntryForm1.AcheivedPercentSalesPipeline = (DataEntryForm1.SalesPipeLineActual/DataEntryForm1.SalesPipeLinePlanned) * 100;
}
DataEntryForm1.NumOfProposalsPlanned = cells.item(row, 9).value().double();
DataEntryForm1.NumOfProposalSubmitted = cells.item(row, 10).value().double();
if (DataEntryForm1.NumOfProposalsPlanned || DataEntryForm1.NumOfProposalSubmitted)
{
DataEntryForm1.AcheivedPercentNoOfProposals = (DataEntryForm1.NumOfProposalSubmitted/DataEntryForm1.NumOfProposalsPlanned) * 100;
}
DataEntryForm1.ValueOfProposalsPlanned = cells.item(row, 11).value().double();
DataEntryForm1.ValueOfProposalsSubmitted = cells.item(row, 12).value().double();
if (DataEntryForm1.ValueOfProposalsPlanned || DataEntryForm1.ValueOfProposalsSubmitted)
{
DataEntryForm1.AcheivedPercentValuOfProposals = (DataEntryForm1.ValueOfProposalsSubmitted/DataEntryForm1.ValueOfProposalsPlanned) * 100;
}
DataEntryForm1.insert();
}
}
type = cells.item(row+1, 1).value().variantType();
} while (type != COMVariantType::VT_EMPTY);
ttscommit;
}
catch (Exception::Error)
{
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
ttsabort;
}
workbooks.close();
CodeAccessPermission::revertAssert();
application.quit();
}

perm = new FileIOPermission(#FileName, #FileMode);

Export data from AX 2012 into Excel file.......

Here is an example of exporting sales data and delivery address from AX 2012 into excel file...

static void SalesTableExportIntoExcel(Args _args)
{
    SalesTable              salesTable;
    LogisticsPostalAddress  postalAddress;
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            font;
    int                     row;
    str                     city, state, country, zipCode;
    SysDictEnum             dictEnumStatus, dictEnumSalesType;
 
    dictEnumStatus    = new SysDictEnum(Enumnum(SalesStatus));  
    dictEnumSalesType = new SysDictEnum(Enumnum(SalesType));

    application     = SysExcelApplication::construct();
    workbooks       = application.workbooks();
    workbook        = workbooks.add();
    worksheets      = workbook.worksheets();
    worksheet       = worksheets.itemFromNum(1);
    cells           = worksheet.cells();
    cells.range('A:A').numberFormat('@');
 
    // Setting Header values
    cell = cells.item(1, 1);
    cell.value("Sales Order");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 2);
    cell.value("Supplemental part");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 3);
    cell.value("Customer account");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 4);
    cell.value("Name");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 5);
    cell.value("Created date and time");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 6);
    cell.value("Customer requisition");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 7);
    cell.value("ZIP/Postal Code");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 8);
    cell.value("Status");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 9);
    cell.value("Order type");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 10);
    cell.value("Country/region");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 11);
    cell.value("Reason for export");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 12);
    cell.value("State");
    font = cell.font();
    font.bold(true);
 
    cell = cells.item(1, 13);
    cell.value("City");
    font = cell.font();
    font.bold(true);
    row = 1;
 
    while select salesTable
    {
        postalAddress   = salesTable.deliveryAddress();
        city            = postalAddress.City;
        state           = postalAddress.State;
        //country         = postalAddress.CountryRegionId;
        country         = LogisticsAddressCountryRegion::find(postalAddress.CountryRegionId).displayName();
        zipCode         = postalAddress.ZipCode;
     
        row++;
        cell = cells.item(row, 1);
        cell.value(salesTable.SalesId);
        cell = cells.item(row, 2);
        cell.value(" ");
        cell = cells.item(row, 3);
        cell.value(salesTable.InvoiceAccount);
        cell = cells.item(row, 4);
        cell.value(salesTable.invoiceName());//CustTable::find(salesTable.InvoiceAccount).name());
        cell = cells.item(row, 5);
        cell.value(datetime2str(salesTable.createdDateTime));
        cell = cells.item(row, 6);
        cell.value(salesTable.PurchOrderFormNum);
        cell = cells.item(row, 7);
        cell.value(zipCode);
        cell = cells.item(row, 8);
        cell.value(dictEnumStatus.value2Label(salesTable.SalesStatus));
        cell = cells.item(row, 9);
        cell.value(dictEnumSalesType.value2Label(salesTable.SalesType));
        cell = cells.item(row, 10);
        cell.value(country);
        cell = cells.item(row, 11);
        cell.value(salesTable.ExportReason);
        cell = cells.item(row, 12);
        cell.value(state);
        cell = cells.item(row, 13);
        cell.value(city);
    }
 
    application.visible(true);
}

Happy DAXing

How to filter records in a form by code in AX 2012

How to set range in a form by code in AX 2012

The standard filter functionality in Ax forms is a neat and powerful feature.
Using this filter functionality in your code is something you'll definitely use at some point in time as a programmer.

Although it's possible to do it in a single line of code, I prefer a 3 step solution. That way it's more flexible.
Let me show you by example. We'll filter the customers records in form CustTable, only showing customers with currency USD.

Step 1: Declare a class variable
In the ClassDeclaration method of the form, define a range.

QueryBuildRange CurrencyQBR;

Step 2: Instantiate the new range.
In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()
{
super();

CurrencyQBR = this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency));
}
Step 3: In the last step, you assign a value to the range.
This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue('USD'));

super();
}
You're done! When you open the form, your customer records are filtered, you only get the customers with currencycode USD set up.

Like I said in the intro of this post, this can be done in one line of code as well.
In the init method of the form datasource, after the super call, place this code:

this.query().dataSourceName('CustTable').addRange(fieldnum(CustTable,Currency)).value(queryvalue('USD'));
But this way, it's fixed. If you choose the 3 step method, you could for example use a variable in the range value. The way to go would be to place an input field on your form, get the value from it and supply it in the executeQuery method.
For example like this:
public void executeQuery()
{ ;

CurrencyQBR.value(queryvalue(MyInputField.text()));

super();
}
Just make sure the executeQuery method is executed, thus applying the desired filter (maybe be using a button on your form to activate it).
Of course it's possible to combine multiple querybuildranges.

Example: 

Let me show you by another example: We'll filter the Sales PackingSlip records in form MSASNShipmentExport (new developed), only showing PackingSlip records on the basis of Financial Dimension "Region" as per mentioned screen shots.

Step-1:

declare the class variables in the ClassDeclaration method of the form, define datasource and range.

    //<declared> by Vimal ...

    DimensionAttributevaluesetitem  DimensionAttributevaluesetitemLoc;    

    QueryBuildDataSource                 qbds, qbdsdimensionValue;

    QueryBuildRange                         qbrIsExport, qbrdimValue;

    //<end> by Vimal

Step 2: Instantiate the new range.

In the init method on the datasource of the form, you assign the range to a specific field (after the super call).

public void init()

{

    Query       query; //<declared> by Vimal

    super();

    //<start> declared by Vimal on April 23, 2015

    // to initialise range for filtering records as per Financial dimension "Region"

    query = CustPackingSlipTrans_ds.query();

    qbds = query.dataSourceTable(tablenum(CustPackingSlipTrans));

    qbds.clearRanges();

    qbrIsExport = qbds.addRange(fieldnum(CustPackingSlipTrans,MSASNExport)); //.value(IsExport.valueStr());

    qbdsdimensionValue = qbds.addDataSource(tableNum(DimensionAttributevaluesetitem));

    qbdsdimensionValue.addLink(fieldNum(DimensionAttributevaluesetitem, DimensionAttributeValueSet), fieldNum(CustPackingSlipTrans, DefaultDimension));

    qbdsdimensionValue.joinMode(JoinMode::ExistsJoin);

    qbrdimValue = qbdsdimensionValue.addRange(fieldNum(DimensionAttributevaluesetitem, DisplayValue));

    //<end> by Vimal

}

Step 3: In the last step, you assign a value to the range.

This is done in the executeQuery method on the same datasource of the form. Before the super call. Like this:

//<created> by Vimal .....

public void executeQuery()

{   

        

    

    qbrdimValue.value(FilterBy.valueStr()); //here, FilterBy is the StringEdit control name

    qbrIsExport.value(IsExport.valueStr()); //here, IsExport is the StringEdit control name

    //<end> by Vimal

    

    super();

.......................

}

You're done! When you open the form, your packingslip records are filtered, you only get the records with Financial Dimension "Region" set up.

Like this:

Happy DAXing

Default dimension storage in Ax 2012 : to fetch one dimension from defauly dimension group

 Hi,

In this post we will see, how to get one dimension value from default dimension master RecId.
Method:

DimensionValue getDimensionValue(RefRecID dimensionSetRecID, Name attributeName)

{

    DimensionAttributeValueSet      dimAttrValueSet;

    DimensionAttributeValueSetItem  dimAttrValueSetItem;

    DimensionAttributeValue         dimAttrValue;

    DimensionAttribute              dimAttribute; 

    dimAttrValueSet = DimensionAttributeValueSet::find(dimensionSetRecID); 

    select dimAttrValueSetItem

        where dimAttrValueSetItem.DimensionAttributeValueSet == dimAttrValueSet.RecId

    join dimAttrValue

        where dimAttrValue.RecId == dimAttrValueSetItem.DimensionAttributeValue

    join dimAttribute

        where dimAttribute.RecId == dimAttrValue.DimensionAttribute

        &&    dimAttribute.Name  == attributeName; 

    return dimAttrValue.getValue();

}

 

e.g.: Our task is to get "Importer"only through default dimension (here Importer is CustAccount) then we need to call this method like that:

 

importerDimension = CustTable::find(this.getDimensionValue(SalesTable.DefaultDimension, "Importer"));

    if (importerDimension)

    {

        msCommercialPackingSlipTmp.msImporterName = CustTable::find(importerDimension).name();

        msCommercialPackingSlipTmp.msImporterAddress = CustTable::find(importerDimension).address();

    }

    else

    {

        msCommercialPackingSlipTmp.msImporterName = msCommercialPackingSlipTmp.DeliveryName;

        msCommercialPackingSlipTmp.msImporterAddress = msCommercialPackingSlipTmp.DeliveryAddress;

    }

 

Happy DAXing....