There are many situations in our development, where we must write the x++ codes to generate excel files in a specific format. These requirements may be special and do not available with the standard integration.
For this reason, I have written this article to get the basic coding concept to create an excel file and export the excel file using X++ coding.
If you are a beginner or new to dynamics 365 Finance & Operations, you can use Microsoft's Free Virtual Machine. Learn How to Set Up a Free Virtual Machine for Dynamics 365 Development
For this, I have created a Runnable Class (Jobs) with the name ExcelCreateAndExport,
After creating the runnable class(Job) copy and paste the below code, here I have written static and dynamic codes for adding records to the excel file.
class ExcelCreateAndExport
{Â Â Â Â Â Â Â
public static void main(Args _args)
{
CustTable custTable;
DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation("@ApplicationPlatform:OfficeDefaultWorkbookFileName","xlsx", null, "excel create and export");
if (saveResult&& saveResult.parmAction() != DocuFileSaveAction::Cancel)
{
saveResult.parmOpenParameters('web=1');
saveResult.parmOpenInNewWindow(false);
System.IO.Stream workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
using(var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add("Sheet1");
var cells = worksheet.get_Cells();
var currentRow=1 ;
 /*-------HEADER PART -------*/
var cell = cells.get_Item(currentRow,1);
cell.set_Value("Customer Name");
cell=null;
cell = cells.get_Item(currentRow,2);
cell.set_Value("Customer Address");
/*-------HEADER PART END-------*/
/*-------RECORD 1-------*/
currentRow=2;
cell= cells.get_Item(currentRow, 1);
cell.set_Value("ABCD Trading");
cell= null;
cell= cells.get_Item(currentRow, 2);
cell.set_Value("ABCD Complex, P.O Box :xxxxxx, XYZ Street");
/*-------RECORD 1 END-------*/
/*-------RECORD 2-------*/
currentRow=3;
cell= cells.get_Item(currentRow, 1);
cell.set_Value("XYZ Trading");
cell = null;
cell = cells.get_Item(currentRow, 2);
cell.set_Value("XYZ Complex, P.O Box :xxxxxx, ABC Street");
/*-------RECORD 2 END-------*/
package.Save();
 }
memoryStream.Seek(0,System.IO.SeekOrigin::Begin);
//Download the file.
DocuFileSave::processSaveResult(memoryStream,saveResult);
}
}
}
After writing these codes, build the project, and set this Runnable Class as starting Object by right-clicking on the class name in Solution Explorer. Then run the project, you can see the output,
Click on the Download button, generated excel file will be downloaded to the local download folder.
Writing x++ code for generating and exporting excel files in d365 fo is not a hard task, but the complexity of coding will increase if we require complex formats and adding more functionalities using x++ code. I have written this article for d365 for beginners and learners. Keep coding and Happy coding with D365 Snippets
