Import and Export Data to a Text file, Excel through X++ Code in ax 2012,

Import CSV file

static void ReadCsvFile(Args _args)
{
    #File
    IO  iO;
    CustAccount custAccount;
    CustName custname;
    FilenameOpen        filename = "d:\\Rajendra.csv"; // --file path
    Container           record;
    Table                  testTable;
    boolean first = true;
    ;
    iO = new CommaTextIo(filename,#IO_Read);
    if (! iO || iO.status() != IO_Status::Ok)
    {
        throw error("@SYS19358");
    }
    while (iO.status() == IO_Status::Ok)
    {
        record = iO.read();// To read file
        if (record)
        {
            if (first)  //To skip header
            {
                first = false;
            }
            else
            {
           
                custAccount = conpeek(record, 1);//To peek record
                custGroup = conpeek(record, 2);
                testTable.CustAccount = custAccount;
                testTable.custGroup     = custGroup ;
                testTable.insert();
                //info(strfmt('%1--%2',custAccount,custname));
            }
        }
    }
}

static void UpdItemChannelCode(Args _args)

{

    #File

    str                         initPath                = @"C:\TEMP\";

    //import

    IO                          fileImport;

    FilenameOpen                fileNameImport;

    FileNameFilter              filter                  = ["CSV file", "*.csv"];

    str                         importInfieldDelimiter  = ';';

    container                   recordImport;

    boolean                     importHasHeader         = true;

    //import

    DataAreaId                  companyCode;

    HSOChannelCode              channelCode;

    HSOAPSeasonCodeId           seasonCodeId;

    HSOAPItemCategoryId         categoryId;

    ItemId                      itemId;

    InventTable                 inventTable;

    HSOChannelTable             hsoChannelTable;

    int                         counter;

    int                         errorCnt;

    boolean                     rowOk;

    ;

    //import

    fileNameImport = WinAPI::getOpenFileName(infolog.hWnd(), filter, initPath , 'Item Channel code - Load CSV file',"csv","",1);

    if(!fileNameImport)

        return ;

    fileImport = new CommaTextIo(fileNameImport,#IO_Read);

    fileImport.inFieldDelimiter(importInfieldDelimiter);

    if (! fileImport || fileImport.status() != IO_Status::Ok)

    {

        throw error("@SYS19358");

    }

    //import

 

    while (fileImport.status() == IO_Status::Ok)

    {

        recordImport = fileImport.read();// To read file

 

        if (recordImport)

        {

            if (importHasHeader)  //To skip header

            {

                importHasHeader = false;

            }

            else

            {

                rowOk               = true;

                companyCode         = "3000";

                itemId              = strLRTrim(conpeek(recordImport, 1));

                seasonCodeId        = strLRTrim(conPeek(recordImport, 2));

                categoryId          = strLRTrim(conPeek(recordImport, 3));

                channelCode         = strLRTrim(conPeek(recordImport, 4));

 

                ttsBegin;

 

                select forUpdate inventTable

                    where inventTable.ItemId    == itemId

                    && inventTable.dataAreaId   == companyCode;

               

                if (!inventTable)

                {

                    rowOk = checkFailed(strFmt(inventTable::txtNotExist(), itemId));

                }

                if (rowOk)

                {

                    inventTable.HSOAPSeasonCodeId   = seasonCodeId;

                    inventTable.HSOAPItemCategoryId = categoryId;

                    inventTable.HSOChannelCode      = channelCode;

                    inventTable.doUpdate();

                    counter ++;

                }

                else

                {

                    errorCnt ++;

                }

                ttsCommit;

            }

        }

    }

    info(strFmt("Import is finished. %1 record(s) were updated, %2 error(s) occured.", counter, errorCnt));

    info("@SYS112206");

}

Export CSV file 

static void ExportTextfile(Args _args)
{
    CustTable   custTable;
    BinData     binData;
    TextBuffer  textBuffer;
    ;

    textBuffer = new TextBuffer();
    textBuffer.setText('');

    while select custTable where custTable.AccountNum || custTable.Currency || custTable.PaymMode
    {
        textBuffer.appendText(strfmt('%1---%2---%3\r\n ',custTable.AccountNum,custTable.Currency,custTable.PaymMode));
    }

    textBuffer.getText();

    binData = new BinData();
    binData.setStrData(textBuffer.getText());
    binData.saveFile(@"C:\New2.txt");
 
}
                         Export Table Data to Excel 

static void DataExportToExcel(Args _args)
{
    //InventTable inventTable;
    Assettable  assettable;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();
    worksheets = workbook.worksheets();
   // worksheet = worksheets.itemFromNum(1);
   worksheet    = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');
    cell = cells.item(1,1);
    cell.value("Asset Id");
    cell = cells.item(1,2);
    cell.value("Asset Group");
    cell = cells.item(1,3);
    cell.value("Name");
    cell = cells.item(1,4);
    cell.value("Name Alias");
    cell = cells.item(1,5);
    cell.value("Responsible");
    cell = cells.item(1,6);
    cell.value("Location");
    cell = cells.item(1,7);
    cell.value("Asset Type");
    row = 1;
    while select assettable order by assettable.AssetId asc
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(assettable.AssetId);
        cell = cells.item(row, 2);
        cell.value(assettable.AssetGroup);
         cell = cells.item(row, 3);
        cell.value(assettable.Name);
         cell = cells.item(row, 4);
        cell.value(assettable.NameAlias);
         cell = cells.item(row, 5);
        cell.value(assettable.Responsible);
         cell = cells.item(row, 6);
        cell.value(assettable.Location);
         cell = cells.item(row, 7);
        cell.value(assettable.AssetType);

    }
    application.visible(true);
}

Import Excel to Ax 

static void ImportInventLocation(Args _args)

{

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Name                    name;

    FileName                filename;

    //ProductType productType;

    InventLocation          inventLocation;

    int row;
    Dialog          dialog;
    Dialogfield     dialogfield;

    

    boolean first = true;

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    

    //filename = @"C:\Users\Desktop\ImportWareHouse.xlsx";
    dialog = new dialog('Excel Import');
    dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), 'File Name');
   
    dialog.run();
    filename =(dialogfield.value());

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

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

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1); 

    cells = worksheet.cells();

    do

    {

        row++;

       

        inventLocation.InventLocationId = cells.item(row, 1).value().bStr();

        inventLocation.Name             = cells.item(row, 2).value().bStr(); 

        inventLocation.InventSiteId     = cells.item(row, 3).value().bStr();

        inventLocation.insert();

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

        //For time import using excel
/* timesheet.StartTime             = time2Str(str2time(cells.item(row,6).value().bStr()), TimeSeparator::Colon, TimeFormat::Hour24);//cells.item(row,6).value().bStr();
timesheet.EndTime               = time2Str(str2time(cells.item(row,7).value().bStr()), TimeSeparator::Colon, TimeFormat::Hour24);//cells.item(row,7).value().bStr();
timesheet.Work                  = COMVariant2Str(cells.item(row, 8).value());*/

        

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    workbooks.close();

    

    info("Done");

}

Export Excel Template
public  void Template()
{
    SysExcelWorksheetHelper worksheetHelper;
    SysExcelHelper          sysExcelHelper;
    SysExcelWorksheet       worksheet;
    int                     column = 1;
    int                     row = 1;
    str                     worksheetName;
    SysDictField            dictField;
    SysExcelCell            excelCell;
    int                     redColor = WinAPI::RGB2int(255, 0, 0);
    SysExcelRange           range;
    COMVariant              cellValue = new COMVariant(COMVariantInOut::Out);
    DictTable DictTable;

    int fieldCount, _x, fieldId,enumValues, enumValue;

    sysExcelHelper = SysExcelHelper::construct();

    sysExcelHelper.initialize();

    worksheet = sysExcelHelper.addWorksheet("Template");
    worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

    DictTable = new DictTable(tableNum(Fcc_TimeSheetManagementTable));// Use your table name here.

    fieldCount = DictTable.fieldCnt();
    fieldId = DictTable.fieldNext(0);

worksheetHelper.addColumn(1,"Worker Num",Types::String);
worksheetHelper.addColumn(2,"Worker Date",Types::Date);
    worksheetHelper.addColumn(3,"Worker Hospital",Types::String);
    worksheetHelper.addColumn(4,"Report No",Types::String);
    worksheetHelper.addColumn(5,"Equipment",Types::String);
    worksheetHelper.addColumn(6,"Start Time",Types::String);
    worksheetHelper.addColumn(7,"End Time",Types::String);
     worksheetHelper.addColumn(8,"Work",Types::Integer);
     worksheetHelper.addColumn(9,"OT",Types::Integer);
     worksheetHelper.addColumn(10,"Travel",Types::Integer);
     worksheetHelper.addColumn(11,"Office",Types::Integer);
     worksheetHelper.addColumn(12,"Total",Types::Integer);


    sysExcelHelper.launchExcel();
  //  return ret;


}