How to read excel file using x++ in Dynamics 365 Finance and operation
Below is the Runnable class we can use to read the excel file.
internal final class TestReadExcelFile
{
FileUploadTemporaryStorageResult fileUploadResult;
int totalRows;
/// <summary>
/// Parm method for the file upload storage
/// </summary>
/// <param name =
"_fileUplaodResult">File upload storage</param>
/// <returns>File upload storage</returns>
public FileUploadTemporaryStorageResult
parmFileUploadResult(FileUploadTemporaryStorageResult _fileUplaodResult = fileUploadResult)
{
fileUploadResult = _fileUplaodResult;
return fileUploadResult;
}
/// <summary>
/// Class entry point. The system will call this method
when a designated menu
/// is selected or when execution starts and this class
is set as the startup class.
/// </summary>
/// <param name = "_args">The specified
arguments.</param>
public static void main(Args _args)
{
TestReadExcelFile readExcelFile = new TestReadExcelFile();
try
{
FileUploadTemporaryStorageResult tempStorageResult = File::GetFileFromUser() as FileUploadTemporaryStorageResult;
if (tempStorageResult &&
tempStorageResult.getUploadStatus())
{
readExcelFile.parmFileUploadResult(tempStorageResult);
readExcelFile.readExcelFile();
}
}
catch
{
Error("Error");
}
}
/// <summary>
/// Get the excel ranges to read the excel
/// </summary>
/// <returns>Excel range</returns>
private OfficeOpenXml.ExcelRange getRange()
{
int
firstRow, lastRow;
System.IO.Stream stream;
OfficeOpenXml.ExcelPackage excelPackage;
OfficeOpenXml.ExcelRange range;
OfficeOpenXml.ExcelWorksheet excelWorksheet;
stream = fileUploadResult.openResult();
excelPackage = new OfficeOpenXml.ExcelPackage(stream);
excelPackage.Load(stream);
//get the first
worksheet. here you can specify the sheet name as well
excelWorksheet =
excelPackage.get_Workbook().get_Worksheets().get_Item(1);
firstRow = excelWorksheet.Dimension.Start.Row;
lastRow = excelWorksheet.Dimension.End.Row;
totalRows = lastRow - firstRow + 1;
range = excelWorksheet.Cells;
return range;
}
/// <summary>
/// Read excel file
/// </summary>
public void readExcelFile()
{
int recordsUpdated;
CustGroup custGroup;
OfficeOpenXml.ExcelRange range = this.getRange();
//First row is
header so we we reading the values from second row.
for (int i = 2; i <=
totalRows; i++)
{
CustGroupId custGroupLocal = range.get_Item(i,1).Value;
Description
custGroupName =
range.get_Item(i,2).Value;
custGroup.clear();
custGroup.CUstGroup =
custGroupLocal;
custGroup.Name = custGroupName;
custGroup.insert();
}
Info(strFmt("Record %1 created", totalRows-1));
}
}