Use X++ code to open the Excel file, insert data and save it to the specified location

Finance and Operations/AX
1

I want Use X++ code to open the Excel file, insert data and save it to the specified location,The examples I found are all adding a new excel, as shown below：

using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
class RunnableClass1
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        FilePath    templateFile;
        Filename    fileName;
       
       
        
        CustTable custTable;
        MemoryStream memoryStream = new MemoryStream();

        using (var package = new ExcelPackage(memoryStream))
        {
            var currentRow = 1;

            var worksheets = package.get_Workbook().get_Worksheets();
            var CustTableWorksheet = worksheets.add('excel');
            var cells = CustTableWorksheet.get_Cells();
            OfficeOpenXml.ExcelRange cell = cells.get_Item(currentRow, 1);
            System.String value = "Account Number";
            cell.set_Value(value);
            cell = null;
            value = "Currency";
            cell = cells.get_Item(currentRow, 2);
            cell.set_Value(value);

            while select CustTable
            {
                currentRow ++;
                cell = null;

                cell = cells.get_Item(currentRow, 1);
                cell.set_Value(CustTable.AccountNum);
                cell = null;

                cell = cells.get_Item(currentRow, 2);
                cell.set_Value(CustTable.Currency);
            }
            package.Save();
            file::SendFileToUser(memoryStream,'textcexcel');
        }
      
    }

}
2

What do you mean by the specified location when we’re talking about D365FO, i.e. a web application running in cloud? For example, would a blob in Azure storage meet your requirements?
The example above allows users to download the file from the cloud to their local machines.
Also, where do you want to get the file that you want to modify?
Regarding how to open the file, notice that you’re passing an empty stream to ExcelPackage’s constructor. But you could use a stream with data from an existing spreadsheet instead.

3

save to designated spot,
such as :C:\Temp\

4

That’s not possible. The fact that you connect to a web server through a browser doesn’t (fortunately!) give the web server access to files on your machine. You’ll have to change your design to something that makes sense with web applications.
Here are a few options:

  • User will get the file for download, as above.
  • The file will be stored in a common location in Azure. Then users can download the file from there, or they could even map an Azure storage as a drive in Windows.
  • You’ll have an application that downloads the file from the cloud and stores it in a shared location in your network. For example, look at Recurring Integrations Scheduler.
1 Like