Write an Excel file through X++ code

Write an Excel file through X++ code :

How to write an Excel file through X++ code

In This Post you will found two code sample
1.Write data in excel through X++ code.
2. Read from an Excel through X++ code

1.Write data in excel through X++ code.

static void thaAxapta_Write2Excel(Args _args)
{
InventTable inventTable;
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);
cells = worksheet.cells();
cells.range('A:A').numberFormat('@');
cell = cells.item(1,1);
cell.value("Item");
cell = cells.item(1,2);
cell.value("Name");
row = 1;
while select inventTable
{
    row++;
    cell = cells.item(row, 1);
    cell.value(inventTable.ItemId);
    cell = cells.item(row, 2);
    cell.value(inventTable.ItemName);
}
application.visible(true);
}

2. Read from an Excel through X++ code

static void theAxapta_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
ItemId itemid;
Name name;
FileName filename;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "C:\\item.xls";
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++;
    itemId = cells.item(row, 1).value().bStr();
    name = cells.item(row, 2).value().bStr();
    info(strfmt('%1 - %2', itemId, name));
    type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
}

static void BIT_Insert_Storage_Track_Model(Args _args)

{

    EcoResStorageDimensionGroupItem     _EcoResStorageDimensionGroupItem;

    EcoResTrackingDimensionGroupItem    _EcoResTrackingDimensionGroupItem;

    InventModelGroupItem                _InventModelGroupItem;

    InventTable                         _Inventtable;

    /*ttsBegin;

        while select _EcoResStorageDimensionGroupItem

            where _EcoResStorageDimensionGroupItem.StorageDimensionGroup == 0

        {

            _EcoResStorageDimensionGroupItem.StorageDimensionGroup     = 5637144577; // SiteWarehouse

            _EcoResStorageDimensionGroupItem.insert();

        }

    ttsCommit;

    ttsBegin;

        while select _EcoResTrackingDimensionGroupItem

            where _EcoResTrackingDimensionGroupItem.TrackingDimensionGroup == 0

            {

            _EcoResTrackingDimensionGroupItem.TrackingDimensionGroup   = 5637144576; // NONE

            _EcoResTrackingDimensionGroupItem.insert();

            }

    ttsCommit;

    */

    ttsBegin;

        while select forUpdate _InventModelGroupItem //join _inventTable

            where _InventModelGroupItem.ModelGroupId == ""

                //&& _InventModelGroupItem.ItemId == _Inventtable.ItemId

            {

                 _InventModelGroupItem.ModelGroupId = "FIFO";     // FIFO

                 _InventModelGroupItem.update();

            }

    ttsCommit;

    info("Finish");

}

static void BIT_Update_PCS_And_ItemGroup(Args _args)

{

    InventTable                         _InventTable;

    EcoResStorageDimensionGroupItem     _EcoResStorageDimensionGroupItem;

    EcoResTrackingDimensionGroupItem    _EcoResTrackingDimensionGroupItem;

    InventItemGroupItem                 _InventItemGroupItem;

    InventTableModule                   _InventTableModule;

    InventModelGroupItem                _InventModelGroupItem;

    FileNameFilter                      filter;

    Dialog                              dialog;

    DialogField                         field;

    FilenameOpen                        filenameOpen;

    COMVariant                          COMVariant;

    COMVariantType                      type;

    SysExcelApplication                 app;

    SysExcelWorkbooks                   Workbooks;

    SysExcelWorkbook                    Workbook;

    SysExcelWorksheets                  Worksheets;

    SysExcelWorksheet                   Worksheet;

    SysExcelCells                       Cells;

    SysExcelCell                        Cell;

    int                                 i,j;

    #excel

    ;

    filter = ["*.xlsx","*.xls"];

    dialog = new Dialog("Choose Excel");

    // field  = dialog.addField(typeId(FilenameOpen), "File Name");         // AX 2009

    field = dialog.addField(extendedTypeStr(filenameOpen), "File Name");    // AX 2012

    dialog.run();

    if (dialog.closedOK())

    {

         app = SysExcelApplication::construct();

         Workbooks = app.Workbooks();

         COMVariant = new COMVariant();

         COMVariant.bStr(field.value());

         Workbook = Workbooks.Add(COMVariant);

         Worksheets = Workbook.worksheets();

         Worksheet = Worksheets.itemFromNum(1);

         Cells = Worksheet.Cells();

    }

        Worksheet = Worksheets.itemFromNum(1);

        Cells = Worksheet.cells();

        i  = 2;

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

        while (Cells.item(i,2).value().variantType() != COMVariantType::VT_EMPTY)

         {

                select forUpdate _InventItemGroupItem where _InventItemGroupItem.ItemDataAreaId == InventTable::find(cells.item(i,1).value().bstr()).dataAreaId

                && _InventItemGroupItem.ItemId  == InventTable::find(cells.item(i,1).value().bstr()).ItemId;

                _InventItemGroupItem.ItemGroupId = Cells.item(i, 4).value().bStr();

                select forUpdate _InventItemGroupItem where _InventItemGroupItem.ItemId == InventTable::find(cells.item(i,1).value().bstr()).itemid;

                _InventTableModule.UnitId       = Cells.item(i, 5).value().bStr();

                if(Cells.item(i, 1).value().bStr() != "")

                   {

                      i++;

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

                      _InventItemGroupItem.update();

                      _InventTableModule.update();

                   }

         }

         info ("Success");

}

static void BIT_Insert_InventItemGroupItem(Args _args)

{

    InventItemGroupItem                 _InventItemGroupItem;

    InventTableModule                   _InventTableModule;

    /*

    FileNameFilter                      filter;

    Dialog                              dialog;

    DialogField                         field;

    FilenameOpen                        filenameOpen;

    COMVariant                          COMVariant;

    COMVariantType                      type;

    SysExcelApplication                 app;

    SysExcelWorkbooks                   Workbooks;

    SysExcelWorkbook                    Workbook;

    SysExcelWorksheets                  Worksheets;

    SysExcelWorksheet                   Worksheet;

    SysExcelCells                       Cells;

    SysExcelCell                        Cell;

    int                                 i,j;

    #excel

    ;

    filter = ["*.xlsx","*.xls"];

    dialog = new Dialog("Choose Excel");

    // field  = dialog.addField(typeId(FilenameOpen), "File Name");         // AX 2009

    field = dialog.addField(extendedTypeStr(filenameOpen), "File Name");    // AX 2012

    dialog.run();

    if (dialog.closedOK())

    {

         app = SysExcelApplication::construct();

         Workbooks = app.Workbooks();

         COMVariant = new COMVariant();

         COMVariant.bStr(field.value());

         Workbook = Workbooks.Add(COMVariant);

         Worksheets = Workbook.worksheets();

         Worksheet = Worksheets.itemFromNum(1);

         Cells = Worksheet.Cells();

    }

        Worksheet = Worksheets.itemFromNum(1);

        Cells = Worksheet.cells();

        i  = 2;

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

    */

        //while (Cells.item(i,2).value().variantType() != COMVariantType::VT_EMPTY)

    ttsBegin;

        while select forUpdate _InventItemGroupItem where _InventItemGroupItem.ItemDataAreaId == ""

        {

                _InventItemGroupItem.ItemDataAreaId             = "rum";

                _InventItemGroupItem.ItemGroupDataAreaId        = "rum";

                //if(Cells.item(i, 1).value().bStr() != "")

                   //{

                     // i++;

                      //type = cells.item(i, 1).value().variantType();

                 _InventItemGroupItem.update();

                    //   info(strFmt("%1 %2", _InventItemGroupItem.ItemDataAreaId, _InventItemGroupItem.ItemGroupDataAreaId));

                   //}

         }

         info ("Success");

    ttsCommit;

}

static void BIT_Insert_InventTableModule(Args _args)

{

    InventTableModule                   _InventTableModule;

    FileNameFilter                      filter;

    Dialog                              dialog;

    DialogField                         field;

    FilenameOpen                        filenameOpen;

    COMVariant                          COMVariant;

    COMVariantType                      type;

    SysExcelApplication                 app;

    SysExcelWorkbooks                   Workbooks;

    SysExcelWorkbook                    Workbook;

    SysExcelWorksheets                  Worksheets;

    SysExcelWorksheet                   Worksheet;

    SysExcelCells                       Cells;

    SysExcelCell                        Cell;

    int                                 i,j;

    #excel

    ;

    filter = ["*.xlsx","*.xls"];

    dialog = new Dialog("Choose Excel");

    // field  = dialog.addField(typeId(FilenameOpen), "File Name");         // AX 2009

    field = dialog.addField(extendedTypeStr(filenameOpen), "File Name");    // AX 2012

    dialog.run();

    if (dialog.closedOK())

    {

         app = SysExcelApplication::construct();

         Workbooks = app.Workbooks();

         COMVariant = new COMVariant();

         COMVariant.bStr(field.value());

         Workbook = Workbooks.Add(COMVariant);

         Worksheets = Workbook.worksheets();

         Worksheet = Worksheets.itemFromNum(1);

         Cells = Worksheet.Cells();

    }

        Worksheet = Worksheets.itemFromNum(1);

        Cells = Worksheet.cells();

        i  = 2;

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

     ttsBegin;

        while (Cells.item(i,2).value().variantType() != COMVariantType::VT_EMPTY)

        {

                while select forUpdate _InventTableModule where _InventTableModule.ItemId == InventTable::find(cells.item(i,1).value().bstr()).itemid

                    && _InventTableModule.ModuleType == ModuleInventPurchSales::Sales

            {

                _InventTableModule.UnitId       = Cells.item(i, 5).value().bStr();

                if(Cells.item(i, 1).value().bStr() != "")

                   {

                      i++;

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

                      _InventTableModule.update();

                   }

            }

        }

    ttsCommit;

    info ("Success");

}