Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Monday, April 3, 2023

Import Excel using x++

 Import Excel using x++.


using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ImportExcel
{
    public static void main(Args _args)
    {
        System.IO.Stream            stream;
        ExcelSpreadsheetName        sheeet;
        FileUploadBuild             fileUpload;
        DialogGroup                 dlgUploadGroup;
        FileUploadBuild             fileUploadBuild;
        FormBuildControl            formBuildControl;
        Dialog                      dialog = new Dialog("Import File");

        dlgUploadGroup          = dialog.addGroup('Select the file');
        formBuildControl        = dialog.formBuildDesign().control(dlgUploadGroup.name());
        fileUploadBuild         = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
        fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
        fileUploadBuild.fileTypesAccepted('.xlsx');

        if (dialog.run() && dialog.closedOk())
        {
            FileUpload fileUploadControl     = dialog.formRun().control(dialog.formRun().controlId('Upload'));
            FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();

            if (fileUploadResult != null && fileUploadResult.getUploadStatus())
            {
                stream = fileUploadResult.openResult();
                using (ExcelPackage Package = new ExcelPackage(stream))
                {
                    int                         rowCount, i;
                    Package.Load(stream);
                    ExcelWorksheet  worksheet   = package.get_Workbook().get_Worksheets().get_Item(1);
                    OfficeOpenXml.ExcelRange    range = worksheet.Cells;
                    rowCount                    = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
                    for (i = 2; i<= rowCount; i++)
                    {
                        str company     = range.get_Item(i, 1).value;
                        str Id          = range.get_Item(i, 2).value;
                        str parentId    = range.get_Item(i, 3).value;
                        str Name        = range.get_Item(i, 4).value;
                    }
                    info("Import completed :)");
                }
            }
            else
            {
                error("Error occurred.");
            }
        }
    }
}


Keep Daxing!!


Monday, May 23, 2022

Create excel file and send through email in d365FO

 Hi guys, today we see how to generate excel file and send that file through email using x++ in D365FO.


using System.IO; using OfficeOpenXml; using OfficeOpenXml.Style; using OfficeOpenXml.Table; class SendEmailProcessSevice extends SysOperationServiceBase { Filename fileName; SysEmailSystemTable sysEmailTable; SysEmailMessagesystemTable messageTable; // sending Email with attachment private void sendEmail(System.IO.MemoryStream attachmentStream) { if (attachmentStream != null) { var user = xUserInfo::find(); Map sysMailers = SysMailerFactory::getMailers(); sysEmailTable = SysEmailSystemTable::find('TestEmail'); select firstonly messageTable where messageTable.EmailId == sysEmailTable.EmailId; str s = date2Str(today(), 321, DateDay::Digits2, DateSeparator::Hyphen, // separator1 DateMonth::Digits2, DateSeparator::Hyphen, // separator2 DateYear::Digits4 ); messageBuilder.setSubject(messageTable.Subject + '-' + s); messageBuilder.setBody(messageTable.Mail); messageBuilder.setFrom(sysEmailTable.SenderAddr); var message = messageBuilder.getMessage(); messageBuilder.addAttachment(attachmentStream,fileName); SysIMailer mailer = sysMailers.lookup('SMTP'); SysMailerSMTP smtp = new SysMailerSMTP(); if (mailer is SysIMailerNonInteractive) { SysIMailerNonInteractive nonInteractiveMailer = mailer; messageSent = nonInteractiveMailer.sendNonInteractive(message); } else if (mailer is SysIMailerInteractive) { SysIMailerInteractive interactiveMailer = mailer; messageSent = interactiveMailer.sendInteractive(message);// messageBuilder.getMessage(); } } else { warning("File is empty."); } } // creating excel file public void fileCreation(MyContract contract) { Query query = new Query(); MemoryStream memoryStream = new MemoryStream(); QueryRun qr; RowNumber row = 1; query.addDataSource(tablenum(Mytable)).addRange(fieldnum(Mytable, Feild1)).value(queryValue(contract.parmtype))); using (var package = new ExcelPackage(memoryStream)) { var worksheets = package.get_Workbook().get_Worksheets(); var worksheet = worksheets.Add('Test Records'); var cells = worksheet.get_Cells(); OfficeOpenXml.ExcelRange cell = cells.get_Item(row, 1); cell.set_Value('Field1'); cell = cells.get_Item(row,2); cell.set_Value('Field2'); cell = cells.get_Item(row,3); cell.set_Value('Field3'); qr = new QueryRun(query); while (qr.next()) { Mytable = qr.get(tableNum(Mytable)); row++; cell = null; cell = cells.get_item(row, 1); cell.set_Value(Mytable.Field1); cell = cells.get_item(row, 2); cell.set_Value(Mytable.Field2); cell = cells.get_item(row, 3); cell.set_Value(Mytable.Field3); } package.save(); file::SendFileToUser(memoryStream,fileName); memoryStream.Seek(0, System.IO.SeekOrigin::Begin); this.sendEmail(memoryStream); } } }


Keep Daxing !!

Sunday, November 1, 2020

Import General Journals through Excel in AX 2012 X++


 Hi guys, Today we see how to Import General Journals through Excel in Ax 2012 using Excel.


Run the below job.

static void JournalImport(Args _args)

{

    Dialog                  _dialog;

    DialogField             _file;

    SysExcelApplication     application;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    COMVariantType          type;

    Name                    name;

    FileName                filename;

    LedgerJournalACType     LedgerJournalACType;



    AxLedgerJournalTable    axLedgerJournalTable ;

    AxLedgerJournalTrans    axLedgerJournalTrans;


    container               accPattern,offSetPattern;

    int                     row = 1;


    str                     _Name;

    str                     _Description;

    AmountCurDebit          _Debit;

    str                     _LedgerDimension;

    str                     _OffsetAccountType;

    str                     _AccountType;

    str                     _OffsetLedgerDimension;



    axLedgerJournalTable = new AxLedgerJournalTable();


    axLedgerJournalTrans = new AxLedgerJournalTrans();


    _dialog = new Dialog("Import Data From Excel");

    _dialog.addText("Select file:");


    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();


    if (_dialog.closedOK())

    {


        info(_file.value() );


    application = SysExcelApplication::construct();

    workbooks   = application.workbooks();

    //specify the file path that you want to read

    filename   =_file.value(); //ExcelSheet File Name

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

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

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

        _Debit                      = cells.item(row, 3).value().double();

        _OffsetAccountType          = cells.item(row, 5).value().bStr();

        _AccountType                = cells.item(row, 6).value().bStr();



        axLedgerJournalTable.parmJournalName(_Name);


        axLedgerJournalTable.save();


        axLedgerJournalTrans.parmJournalNum(axLedgerJournalTable.ledgerJournalTable().JournalNum);


        axLedgerJournalTrans.parmTransDate(systemDateGet());

        axLedgerJournalTrans.parmCurrencyCode('USD');

        axLedgerJournalTrans.parmTxt(_Description);

        axLedgerJournalTrans.parmAmountCurDebit(_Debit);

        axLedgerJournalTrans.parmExchRate(Currency::exchRate('USD'));

        axLedgerJournalTrans.parmAccountType(str2enum(LedgerJournalACType,_AccountType));



        accPattern = ['110110','110110',2,'BusinessUnit',"001","Department","022"];

        axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accPattern));



        offSetPattern = ['112140',"112140",2,'BusinessUnit','003',"Department","024"];

        axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetPattern));


        axLedgerJournalTrans.save();


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

    }


        while (type != COMVariantType::VT_EMPTY);

        application.quit();

        info(strFmt("Journal %1 created", axLedgerJournalTable.ledgerJournalTable().JournalNum));

        info("Data is Imported");

    }

    }


Keep Daxing!!

Monday, September 28, 2020

Import data From Excel file to Ax 2012 using X++

 Hi guys ,Today we see How to Import data from Excel file to Ax 2012 using X++.


Write below code in job and Test it.

static void Job15(Args _args)

{

    Dialog                      _dialog;

    DialogField                 _file;

    SysExcelApplication         application;

    SysExcelWorkbooks           workbooks;

    SysExcelWorkbook            workbook;

    SysExcelWorksheets          worksheets;

    SysExcelWorksheet           worksheet;

    SysExcelCells               cells;

    COMVariantType              type;

    FileName                    filename;

    CustGroup                   custgroup; //Declaring Table Name

   

    CustGroupId                 _CustGroup;

    Name                         _Name;

    str                         _clearingPeriod;

    int                         row = 1;

    

    _dialog = new Dialog("Import Data From Excel");

    _dialog.addText("Select file:");


    _file   = _dialog.addField(ExtendedTypeStr("FilenameOpen"));

    _dialog.run();


    if (_dialog.closedOK())

    {


        info(_file.value());


        application = SysExcelApplication::construct();

        workbooks   = application.workbooks();

        //specify the file path that you want to read

        filename   =_file.value(); //ExcelSheet File Name

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

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

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

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


            select forUpdate custgroup where custgroup.CustGroup==_CustGroup;

            if (custgroup)

            {

                    ttsBegin;


                custgroup.Name             = _Name  ;

                custgroup.ClearingPeriod   = _clearingPeriod;


                custgroup.update();

                ttsCommit;

            }

        

            else

            {


                custgroup.Name             = _Name  ;

                custgroup.ClearingPeriod   = _clearingPeriod;


                custgroup.insert();

            }


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

        }


        while (type != COMVariantType::VT_EMPTY);

        application.quit();

        info("Data is Imported");

    }



Keep Daxing!!