blog for Dynamics Axapta

Insert Values to Item Group Table from Excel

Hi Everyone,

Here I published  the X++ code for Inserting Excel Data in to ItemGroup Table.

Condition: If the importing values are same as the values in the table then the values will not be uploaded, if the description of the item differs then the description only updated in the table.

 

Void Clicked()

{

    SysExcelApplication              application;

    SysExcelWorkbooks              workbooks;

    SysExcelWorkbook                workbook;

    SysExcelWorksheets             worksheets;

    SysExcelWorksheet               worksheet;

    SysExcelCells                        cells;

    COMVariantType                  type;

    System.DateTime                 ShlefDate;

 

    FilenameOpen                     filename;

    dialogField                           dialogFilename;

 

    Dialog                                  dialog;

    InventItemGroup                 inventItemGroup1;

    ItemGroupId                        itemGroupId;

    Name                                  itemGroupName,itemGroupName1;

    int                                        row;

    #Excel

 

    // convert into str from excel cell value

    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)

    {

        switch (_cv.variantType())

        {

            case (COMVariantType::VT_BSTR):

                return _cv.bStr();

 

            case (COMVariantType::VT_R4):

                return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

 

            case (COMVariantType::VT_R8):

                return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

 

            case (COMVariantType::VT_DECIMAL):

                return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

 

            case (COMVariantType::VT_DATE):

                return date2str(_cv.date(),123,2,1,2,1,4);

 

            case (COMVariantType::VT_EMPTY):

                return "";

 

            default:

                throw error(strfmt("@SYS26908", _cv.variantType()));

        }

        return "";

    }

 

    ;

 

 

    dialog                       =   new Dialog("Upoad from Excel");

    dialogFilename        =   dialog.addField(typeId(FilenameOpen));

 

    dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);

    dialog.filenameLookupTitle("Upload from Excel");

    dialog.caption("Upload from Excel");

 

    dialogFilename.value(filename);

 

    if(!dialog.run())

        return;

 

    filename                =   dialogFilename.value();

 

    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);

    cells                    =   worksheet.cells();

 

    try

    {

       ttsbegin;

 

        do

        {

            row++;

            itemGroupId                  =   COMVariant2Str(cells.item(row, 1).value());

            itemGroupName           =   COMVariant2Str(cells.item(row,2).value());

 

            if(row>1)

            {

                if(substr(itemGroupId,1,1) ==’6′)

                {

                    itemGroupId           = substr(itemGroupId,2,strlen(itemGroupId));

                    inventItemGroup    = inventItemGroup::find(itemGroupId);

                    itemGroupName1   = inventItemGroup::find(itemGroupId).Name;

 

                    if(inventItemGroup)

                    {

                        if(itemGroupName1!=itemGroupName)

                        {

 

                        select forupdate inventItemGroup1 where inventItemGroup1.ItemGroupId == itemGroupId;

                        inventItemGroup1.Name = itemGroupName;

                        inventItemGroup1.update();

                        info(strfmt("Item Description for Item GroupId (%1) Updated succesfully",ItemGroupId));

                        }

 

                    }

                    else

                    {

                        inventItemGroup1.ItemGroupId    = itemGroupId;

                        inventItemGroup1.Name              = itemGroupName;

                        inventItemGroup1.insert();

                        info(strfmt("Item GroupId (%1) uploaded succesfully",ItemGroupId));

                    }

                }

            }

 

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

 

        }while (type != COMVariantType::VT_EMPTY);

 

        application.quit();

 

        ttscommit;

 

    }

    catch

    {

        Error("Upload Failed");

        application.quit();

    }

}

Excel Format that I used for Upload :

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: