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

(1) Share

Report

Posted on by Apratim

166

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!

I have the same question (0)

All responses (13)

Answers (2)

Verified answer

Layan Jwei 8,158 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Apratim 166 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

Thankyou so much !!!!

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

Thanks again!!!!

Was this reply helpful? Yes No
Verified answer

Martin Dráb 238,286 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Apratim 166 on at

Like (1)

Report
Copy link

Link copied!

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!!!!

Was this reply helpful? Yes No
Martin Dráb 238,286 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Apratim 166 on at

Like (1)

Report
Copy link

Link copied!

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!!!

Was this reply helpful? Yes No
Layan Jwei 8,158 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Apratim 166 on at

Like (1)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Suggested answer

Layan Jwei 8,158 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Apratim 166 on at

Like (0)

Report
Copy link

Link copied!

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!!!!

Was this reply helpful? Yes No

Join the conversation

Community site session details

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

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the December Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

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

Helpful resources

News and Announcements

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the December Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates