![Import & Export excel file D365 FO [X++]](https://media.licdn.com/dms/image/v2/D4D12AQFKoI886zKUiw/article-cover_image-shrink_720_1280/article-cover_image-shrink_720_1280/0/1714295581631?e=2147483647&v=beta&t=npNobFobOgGntvbE_tTfhvu68KJM1bfgn862V2tK7Dc)
Import & Export excel file D365 FO [X++]
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);
}
}
2. Import data from excel sheet to table (grid)
Example is : salesLine with financial dimensions
领英推荐
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[FormControlEventHandler(formControlStr(SalesTable, ImportLines), FormControlEventType::Clicked)]
public static void ImportLines_OnClicked(FormControl sender, FormControlEventArgs e)
{
System.IO.Stream stream;
FormControl callerButton = sender as FormControl;//Retrieves the button that we're reacting to
FormRun form = callerButton.formRun(); //Gets the running SalesEditLines form
OfficeOpenXml.ExcelWorksheet worksheet;
OfficeOpenXml.ExcelRange range;
FileUploadTemporaryStorageResult fileUploadResult;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUpload fileUploadControl;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
ExcelSpreadsheetName sheeet;
int i, excelRowCount;
InventDim _LineDim;
SalesLine _salesLineBuffer;
//Get the salesId that was selected in the SalesEditLines form
FormDataSource SalesTable_ds = form.dataSource(formDataSourceStr(SalesTable, SalesTable)) as FormDataSource;
SalesTable _SalesTable= SalesTable_ds.cursor();
Dialog dialog = new Dialog("Upload sales lines excel file");
dlgUploadGroup = dialog.addGroup("Upload file");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
fileUploadBuild.fileTypesAccepted('.xlsx'); // Upload excel file only
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
if (dialog.run() && dialog.closedOk())
{
fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream))
{
Package.Load(stream);
worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
range = worksheet.Cells;
excelRowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
try
{
ttsbegin;
for (i = 2; i<= excelRowCount; i++) // Start with 2 due to header excluded.
{
_salesLineBuffer.clear();
_LineDim.clear();
_salesLineBuffer = xxxxx_SalesTable_EventHandeller::findSalesLine( _SalesTable.SalesId , range.get_Item(i, 1).value , range.get_Item(i, 2).value , range.get_Item(i, 3).value);
_salesLineBuffer.SalesId = _SalesTable.SalesId;
_salesLineBuffer.ItemId = range.get_Item(i, 1).value;
_salesLineBuffer.WorkOrder = range.get_Item(i, 2).value;
_salesLineBuffer.SerialNumber = range.get_Item(i, 3).value;
// Sample code
if(_salesLineBuffer.RecId)
{
ttsbegin;
_salesLineBuffer.update(); // To update existing record
ttscommit;
}
else
{
_salesLineBuffer.SalesQty = 0; //your qty from form
_LineDim.InventSiteId ="Main";
_LineDim.InventLocationId="DAM";
_salesLineBuffer.setInventDimIdFromInventDim(_LineDim);
_salesLineBuffer.createLine(true, true, true, true, true, true);
// _salesLineBuffer.Insert(); // For new record
}
}
ttscommit;
info("Data inserted succesfully");
}
catch (Exception::Error)
{
ttsabort;
throw error("error during upload");
}
}
}
}
}
resources :
Solution Architect / Digital Transformation / Functional Consultant
7 个月It is neat, but I would say there are some other options available to do the same action without the need for code. As long as all options are explored then you end with the correct one for that particular client or scenario.
Senior Finance consultant Microsoft Dynamics AX / D365 FO (Business Analyst - Finance)
7 个月You could leverage Electronic Reporting (ER) for that.