Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Export data From Sales...
Finance | Project Operations, Human Resources, ...
Answered

Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !

(1) ShareShare
ReportReport
Posted on by Apratim 149
Hi Community,
Thanks in advance!
I have requirement where I have to export data from Sales table and lines to an excel sheet using x++ code. The reason why I want to achieve this through X++ Code is because I want to mark a noYes flag in salesTable once exported; So that it wont be considered in the next export. Also I'm storing all salesID in a Customtable  once exported. Issue is when i write same code in a SysOpearationFramework its not working. Although, the flag is getting ticked and my Customtable is also getting filled with the salesID which are exported. Only issue is the Excel sheet is not getting generated.
using System.IO;using OfficeOpenXml;using OfficeOpenXml.Style;using OfficeOpenXml.Table;class Prefix_ClassName extends SysOperationServiceBase {    public void process()    {        Salestable  salesTab, salesTab2, salesTabExport;        SalesLine   salesLi;        Prefix_ExportedSalesIdCustomTable exportedSalesOrder;        SalesId     salesId1 = //;        str         salesId2 = //, dataId = //;        MemoryStream memoryStream = new MemoryStream();        int         i = 0, _dd = 0, _yy = 0;        str         _mthName;        TransDate   _today = DateTimeUtil::getSystemDate(DateTimeUtil::getUserPreferredTimeZone());        _dd         =   dayOfMth(_today);        _mthName    =   mthName(mthOfYr(_today));        _yy         =   Year(_today);                //DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation(strFmt(/ExportedSalesOrderForRBTD%1%2%3/,_dd,_mthName,_yy),/Xlsx/,null,null);        using (var package = new ExcelPackage(memoryStream))        {            var currentRow = 1;            var currentRowHeader = 1;            Filename fileName = strFmt(/ExportedSalesOrder%1%2%3.Xlsx/,_dd,_mthName,_yy);            var worksheets = package.get_Workbook().get_Worksheets();            var linesWorksheet = worksheets.Add(/ExportLines/);            var headerWorksheet = worksheets.Add(/ExportHeader/);            var cells = linesWorksheet.get_Cells();            var headerCells = headerWorksheet.get_Cells();            // my logics            package.Save();                                    file::SendFileToUser(memoryStream, fileName);        }    }}


Please help me out!
  • Verified answer
    Layan Jwei Profile Picture
    Layan Jwei 7,788 Super User 2025 Season 1 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Apartim,
     
    I'm glad it worked with you. And as I mentioned in my previous replies, if you want to send it as batch, then you need to save the file somewhere else like azure blob storage or email..etc
     
     
    If your issue is fixed, then please mark the answers that helped you as verified.
     
    Thanks,
    Layan Jweihan
  • Apratim Profile Picture
    Apratim 149 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Martin, 


    Thankyou so much !!!!

    Will have to talk with the user now, and will come back to you !!!!

    Thanks again!!!! 
  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 233,029 Most Valuable Professional on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    No, the web server running in an Azure data center can't connect to users' PCs and modify files there.
     
    The batch needs to save files in the cloud, not on users' machines. Users then can download files from cloud to their PCs if needed.
  • Apratim Profile Picture
    Apratim 149 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Martin,

    Thanks for the reply!!!

    So if I can't use File::sendFileToUser() in batch processing; then can I use a contract class to pass the location where the user wants to save the excel sheet without using the File::sendFileToUser(). If Yes, can you please suggest what modification do I need to make in my code!

    Thanks in advance!!!!
  • Martin Dráb Profile Picture
    Martin Dráb 233,029 Most Valuable Professional on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    You have conflicting requirements. You can't run the process in batch without a user connection and have a user selecting a location on his machine and downloading the file.
     
    If you want to give the file to user for download, it can't run in a batch.
     
    If you want to run the process in batch, you can't use File::sendFileToUser(). You need to save the file on your own, e.g. to a blob storage. User will download the file later from there.
  • Apratim Profile Picture
    Apratim 149 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Layan,

    I ran the batch job without marking batch processing as true; With and without "try and and catch" both. In both the cases the the excel sheet is getting downloaded now. Thankyou for suggesting to run that without marking the batch process as true! But I want to run it as batch process in background! Is there anything or any approach which i need to do! Please suggest!

    Thankyou!!!

     
  • Layan Jwei Profile Picture
    Layan Jwei 7,788 Super User 2025 Season 1 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Apartim,

    yes please try to use (try& catch) in the service class and let us know. And does the debugger reach sendFileToUser method?

    I can also see that the execution method is synchronous. But make sure when you open the dialog, to not mark batch processing as true. leave it as false and let us know if it gets downloaded.
  • Apratim Profile Picture
    Apratim 149 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Layan,

    Thanks for the reply!

    This is my code on Controller class:
     
    class PRE_ExportSOExcelBatchController_DataIntegration extends SysOperationServiceController
    {
        
        protected void new()
        {
            super(classStr(PRE_ExportSOExcelBatchService_DataExport), methodStr(PRE_ExportSOExcelBatchService_DataExport, process), SysOperationExecutionMode::Synchronous);
        }
        public ClassDescription defaultCaption()
        {
            return "Export sales order to excel batch job";
        }
        public static PRE_ExportSOExcelBatchController_DataIntegration construct(SysOperationExecutionMode _executionMode = SysOperationExecutionMode::Synchronous)
        {
            PRE_ExportSOExcelBatchController_DataIntegration controller;
            controller = new PRE_ExportSOExcelBatchController_DataIntegration();
            controller.parmExecutionMode(_executionMode);
            return controller;
        }
        public static void main(Args _args)
        {
                PRE_ExportSOExcelBatchController_DataIntegration controller;
                controller = PRE_ExportSOExcelBatchController_DataIntegration::construct();
                controller.parmArgs(_args);
                controller.startOperation();
        }
    }

    Ill try try & catch and let you know!



     
  • Suggested answer
    Layan Jwei Profile Picture
    Layan Jwei 7,788 Super User 2025 Season 1 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Apratim,

    As mentioned before, can you please show us the whole code for the controller class? as I want to see the execution method.
    As Martin said, SendFileToUser will only work in synchronous, because when you are using batch, it doesn't make sense to send file to user as there is no user browser.
    In this case you need to save the file somewhere else, maybe send it via email or send it to azure blob storage.

    Also in order to see the error, you can add a try catch

    System.Exception ex;
    try
    {
    }
    catch(ex)
    {
    }

    and you can check the batch log to see errors as well (batch job -- batch history -- log)

    Thanks,
    Layan Jweihan
    Please mark this answer as "Verified" if it solved your issue. In order to help others who will face a similar issue in the future
     
  • Apratim Profile Picture
    Apratim 149 on at
    Export data From Sales Table and lines to an excel workbook using SysOpearationFramework !
    Hi Everyone, 

    Thanks for the reply!

    I ran this code in a normal class in the main method. This code works absolutely fine!
    The excel sheet is getting downloaded in my local VM     "Downloads" File!

    By not working i meant the code is getting executed without any error in the batch process and 
    the flags are also getting ticked in SalesHeader. Even my Custom table where all the exported SalesId should populate
    is also getting populated !

    Only issue is that the Excel sheet is not getting downloaded in my local VM as it was getting earlier when I ran the same code without
    SysOperationFramework!

    Thankyou!!!!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,351 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 233,029 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans