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