//Import from Excel to Form Grid Table through X++ AX 2012

//Import from Excel to Form Grid Table;

Void clicked()
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    Dialog dialog;
    DialogField dlgfeild;
       
    PurchaseRe _PurchaseRe;
    VendRe _VendRe;
    ItemRe _ItemRe;
   // PurchaseRe _PurchaseRe;


    Args          args = new Args();
 

    int Qty;
    int Price;
    int row;
    str vendor;
    int ItemNumber1;
    date DIvDate;
   
    super();
    dialog = new Dialog();
    dlgfeild = dialog.addField(extendedTypeStr(FilenameOpen));
   
    dialog.run();
    filename = dlgfeild.value();
    row =1;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read

    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 3 is the worksheet Number
    cells = worksheet.cells();

         do

            {
                row++;

                ItemNumber1 = any2int(cells.item(row,1).value().double());
                Vendor = int2str(any2int(cells.item(row,2).value().double()));
                DIvDate = any2date(cells.item(row, 3).value().date());
                Qty = any2int(cells.item(row,4).value().double());
                Price = any2int(cells.item(row,5).value().double());
               
                _PurchaseRe.ItemId = int2str(ItemNumber1);
                _PurchaseRe.VendAccount = Vendor;
                _PurchaseRe.DelvDate = DIvDate;
                _PurchaseRe.Qty = Qty;
                _PurchaseRe.Price = Price;
                 _PurchaseRe.insert();
                type = cells.item(row+1, 1).value().variantType();
            }
                while (type != COMVariantType::VT_EMPTY);
                application.quit();

 
            PurchaseRe_ds.executeQuery();
            PurchaseRe_ds.reread();
            PurchaseRe_ds.research();
            PurchaseRe_ds.refresh();
 
}



//
static void ImportExcel(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;

   EmployeeBackup employeeBackup;
   // Args                    args = new Args();
    int row=1;

    int empcode;
    str salary;
    str age;
    str YearsOfexperience;
    str empname;

    TypeOfJob jobtype;


    EmployeeDesgination employeedesgination;
    employeedesgination tmpDesgination;

    CurrencyValue currencyValue;
    currencyValue tmpCurrency;

    date dateofbirth,dateofjoining,dateofreveling;
    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename = "C:\\Test.xlsx";//FIle path

        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
        cells = worksheet.cells();
            do

            {
                row++;

                empcode = any2int(cells.item(row,1).value().double());
                empname = cells.item(row,2).value().bstr();
                tmpDesgination = str2enum(employeedesgination,cells.item(row, 3).value().bStr());
                dateofjoining = any2date(cells.item(row,4).value().date());
                dateofreveling = any2date(cells.item(row,5).value().date());
                dateofbirth = any2date(cells.item(row,6).value().date());
                age = any2str(cells.item(row,7).value().toString());
                salary = any2str(cells.item(row,8).value().toString());
                tmpCurrency = str2enum(currencyValue,cells.item(row,9).value().bStr());
                jobtype = str2enum(jobtype,cells.item(row, 10).value().bStr());
                YearsOfexperience = any2str(cells.item(row,11).value().toString());



                 employeeBackup.EmpCode = empcode;
                 employeeBackup.EmpName = empname;
                 employeeBackup.EmployeeDesgination = tmpDesgination;
                 employeeBackup.DateofJoining = dateofjoining;
                 employeeBackup.DateOfTermination = dateofreveling;
                 employeeBackup.DateOfBirth = dateofbirth;
                 employeeBackup.Age = age;
                 employeeBackup.Salary = salary;
                 employeeBackup.CurrencyValue = tmpCurrency;
                 employeeBackup.TypeOfJob = jobtype;
                 employeeBackup.YearsOfExperience = YearsOfexperience;

                 employeeBackup.insert();

                type = cells.item(row+1, 1).value().variantType();
            }
                while (type != COMVariantType::VT_EMPTY);
                application.quit();
}

// import from Excel to table by direct file location:

Void clicked()
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    Name name;
    FileName filename;
    InspectionType     _Typeins;

    QualityInspectionTable _QualityInspectionTable;
    QualityInspectionLine _QualityInspectionLine;
    InspectionLine _InspectionLine;


    Args          args = new Args();
    int row=1;

    int TotalProgress;
    ActivityNumber _ActivityNumber;
    str DrawingNo;
    str InspectionType;
    str JointNo;
    str ProjectID;
    str PersonnelNumber;
    str Remarks1;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    filename = "C:\\quality.xlsx";//FIle path

        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
        cells = worksheet.cells();

       //delete_from QualityInspectionTable;
            do

            {
                row++;

                ProjectID = any2str(cells.item(row,1).value().bStr());
                _ActivityNumber = any2str(cells.item(row,2).value().bStr());
                DrawingNo = any2str(cells.item(row, 3).value().bStr());
                JointNo = any2str(cells.item(row,4).value().bStr());
                InspectionType = any2str(cells.item(row,5).value().bStr());
                PersonnelNumber = any2str(cells.item(row,6).value().bStr());
                TotalProgress = any2int(cells.item(row,7).value().double());
                Remarks1 = any2str(cells.item(row,8).value().bStr());




                _QualityInspectionTable.ProjectID = ProjectID;
                _QualityInspectionTable.ActivityNumber = _ActivityNumber;
                _QualityInspectionTable.DrawingNo = DrawingNo;
                _QualityInspectionTable.JointNo = JointNo;
                _QualityInspectionTable.InspectionType = InspectionType;
                _QualityInspectionTable.PersonnelNumber = PersonnelNumber;
                _QualityInspectionTable.TotalProgress = TotalProgress;
                _QualityInspectionTable.Remarks1 = Remarks1;

                 _QualityInspectionTable.insert();

        _QualityInspectionTable.InspectionType = _Typeins;
        _Typeins =  cells.item(row,5).value().bStr();

                while select _InspectionLine where _InspectionLine.InspectionType == _Typeins

    {
        _QualityInspectionLine.ProjectId = cells.item(row,1).value().bStr();
        _QualityInspectionLine.ActivityNumber = cells.item(row,2).value().bStr();
        _QualityInspectionLine.InspectionType  = cells.item(row,5).value().bStr();
        _QualityInspectionLine.PersonnelNumber   = cells.item(row,6).value().bStr();
        _QualityInspectionLine.Stages = _InspectionLine.Stages;
        _QualityInspectionLine.Percent = _InspectionLine.Percent;
        _QualityInspectionLine.Insert();


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

                while (type != COMVariantType::VT_EMPTY);
                application.quit();
    super();
}