If you are looking into how to export data into an excel file and save/upload it to SharePoint from Dynamics 365 Finance and Operations, here is the solution. The initial phase of the code is for the declaration and assigning of the SharePoint URL to our local variable to fetch the host and build the connection. The second phase is to create an excel file which included creating a workbook and worksheets through code and saving it into a memory stream which at the end will be stored at the destination folder in SharePoint.

SharePoint instance declaration:

//Uribuilder for the instance of SharePoint your company is using

System.UriBuilder builder = new System.UriBuilder(‘https://XXXX.SharePoint.com’);

str host = builder.Host;
str extId = xUserInfo::getExternalId();

Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider   provider;

Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation    documentLocation = new Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation();

//provider saves the location of folder on SharePoint site where you want the file to get uploaded

provider = new Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(host,’sites/XXXX’,’Folder/XXXX’,extId);

Excel generation code:

System.IO.Stream workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();

using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var currentRow=1;
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add(“First sheet”);
var cells = worksheet.get_Cells();

//set the title on the cell values

var cell = cells.get_Item(currentRow,1);
System.String value=”SNo.”;
cell.set_Value(value);

cell=null;
value=”Purchae order”;
cell=cells.get_Item(currentRow,2);
cell.set_Value(value);

cell=null;
value=”Line number”;
cell=cells.get_Item(currentRow,3);
cell.set_Value(value);

cell=null;
value=”Item number”;
cell=cells.get_Item(currentRow,4);
cell.set_Value(value);

cell=null;
value=”Site”;
cell=cells.get_Item(currentRow,5);
cell.set_Value(value);

cell=null;
value=”Warehouse”;
cell=cells.get_Item(currentRow,6);
cell.set_Value(value);

cell=null;
value=”Quantity”;
cell=cells.get_Item(currentRow,7);
cell.set_Value(value);

cell=null;
value=”Unit price”;
cell=cells.get_Item(currentRow,8);
cell.set_Value(value);

cell=null;
value=”Line net amount”;
cell=cells.get_Item(currentRow,9);
cell.set_Value(value);

while select PurchParmLineSelect
where  PurchParmLineSelect.OrigPurchId == PurchParmLine.OrigPurchId
&& PurchParmLine.SMJ_SNo != “”
{

currentRow ++;
cell=null;

cell=cells.get_Item(currentRow,1);
cell.set_Value(PurchParmLineSelect.SMJ_SNo);
//set the data in each column
cell=null;

cell=cells.get_Item(currentRow,2);
cell.set_Value(PurchParmLineSelect.OrigPurchId);
cell=null;

cell=cells.get_Item(currentRow,3);
cell.set_Value(PurchParmLineSelect.PurchaseLineLineNumber);
cell=null;

cell=cells.get_Item(currentRow,4);
cell.set_Value(PurchParmLineSelect.ItemId);
cell=null;

cell=cells.get_Item(currentRow,5);
cell.set_Value(InventDim::find(PurchParmLineSelect.InventDimId).InventSiteId);
cell=null;

cell=cells.get_Item(currentRow,6);
cell.set_Value(InventDim::find(PurchParmLineSelect.InventDimId).InventLocationId);
cell=null;

cell=cells.get_Item(currentRow,7);
cell.set_Value(PurchParmLineSelect.ReceiveNow);
cell=null;

cell=cells.get_Item(currentRow,8);
cell.set_Value(PurchParmLineSelect.PurchPrice);
cell=null;

cell=cells.get_Item(currentRow,9);
cell.set_Value(PurchParmLineSelect.LineAmount);
cell=null;
}

package.Save();
}

memoryStream.Seek(0, System.IO.SeekOrigin::Begin);

//Save file with overwrite replace the file every time the job is executed

//memory stream will hold the instance of created excel file

documentLocation = provider.SaveFileWithOverwrite(newGuid(),’FileName.xlsx’, System.Web.MimeMapping::GetMimeMapping(‘FileName.xlsx’), memoryStream);

The post Microsoft Dynamics 365 Finance and Operations: Create and export Excel file to SharePoint appeared first on Impulz Technologies LLC.