Hi
I am trying to import data from excel to dynamics FO through X code. I have been able to import various fields but facing issue in importing date field from excel to dynamics. I have put some infolog to see if the date fields is imported through code but it is not visible while other fields are fetched successfully.
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class RP_TimesheetImportClass extends RunBase
{
public static void main(Args _args)
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
TSTIMESHEETTABLE timesheetTable;
TSTIMESHEETLINE timesheetLine;
TSTIMESHEETLINEWEEK timesheetLineWeek;
ResourceView resourceView;
ProjPeriodId periodId;
//TrvExpTrans expTrans, expTransLoc;
//TrvExpTable trvExpTable, trvExpTableLoc;
//LedgerParameters ledgerParameters;
str timesheetNumber, resourceId, resourceName, resourceLegalEntity,projectLegalEntity, projectId, projectName, categoryId, approvalStatus;
TransDate startDate, endDate;
str totalHrs, day1, day2, day3, day4, day5, day6, day7;
int k, identifierValue, counterIdentifierValue;
boolean isInterCompanyProject;
RecId tstimesheettable;
str purpose, legalEntityValue, projIDValue, employeeId;
Str1260 tripFromAndToDateValue, expenseCategory;
TransDate expSubmissionDate, invoiceReceiptDate;
//str custAccount;
//str employee;
//str employeeWorkLocation;
//str horizontal;
//str incedoOfficeLocation;
//str project, expnum;
//str subHorizontal;
//str usSalaryComponent;
//str vendor;
//Amount expAmount, exchangeRate;
//str currencyCode, billableStatus;
//
HcmWorker hcmWorker;
HcmEmployment hcmEmployment;
ProjTable projTable;
RecId currentLegalEntity;
DataAreaId compDataAreaId;
//str startDateStr, endDateStr;
// TransDate startDate, endDate;
Dialog dialog = new Dialog('Import the data from Excel');
dlgUploadGroup = dialog.addGroup('@SYS54759');
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted('.xlsx');
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (ExcelPackage Package = new ExcelPackage(stream))
{
int rowCount, i;
Package.Load(stream);
ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row 1;
for (i = 2; i<= rowCount; i )
{
try
{
k ;
timesheetNumber = any2Str(range.get_Item(i, 1).value);
resourceId = any2Str(range.get_Item(i, 2).value);
resourceLegalEntity = any2Str(range.get_Item(i, 3).value);
projectLegalEntity = any2Str(range.get_Item(i, 4).value);
projectId = any2Str(range.get_Item(i, 5).value);
categoryId = any2Str(range.get_Item(i, 6).value);
periodId = any2Str(range.get_Item(i, 7).value);
startDate = str2Date(range.get_Item(i,8).value,213);
endDate = str2Date(range.get_Item(i,9).value,213);
//startDate = str2Date(startDateStr, 213);
//endDate = str2Date(endDateStr, 213);
info(strFmt("Period ID : %1",periodId));
// info(strFmt("Start Date str : %1, End Date str : %2",startDateStr, endDateStr));
info(strFmt("Start Date : %1, End Date : %2",startDate, endDate));
day1 = any2Str(range.get_Item(i, 10).value);
day2 = any2Str(range.get_Item(i, 11).value);
day3 = any2Str(range.get_Item(i, 12).value);
day4 = any2Str(range.get_Item(i, 13).value);
day5 = any2Str(range.get_Item(i, 14).value);
day6 = any2Str(range.get_Item(i, 15).value);
day7 = any2Str(range.get_Item(i, 16).value);
approvalStatus = any2Str(range.get_Item(i, 17).value);
Info(strFmt("Approval Status %1", approvalStatus));
compDataAreaId = CompanyInfo::find().DataArea;
//currentLegalEntity = CompanyInfo::find().RecId;
//ledgerParameters = LedgerParameters::find();
select RecId from resourceView
where resourceView.ResourceId == resourceId;
//if(timesheetTable)
//{
// ttsbegin;
timesheetTable.clear();
timesheetLine.clear();
timesheetLineWeek.clear();
if(approvalStatus == "Approved") timesheetTable.ApprovalStatus = TSAppStatus::Approved;
else if(approvalStatus == "Draft") timesheetTable.ApprovalStatus = TSAppStatus::Create;
else if(approvalStatus == "In Review") timesheetTable.ApprovalStatus = TSAppStatus::Pending;
//else if(approvalStatus == "Draft") timesheetTable.ApprovalStatus = TSAppStatus::;
//timesheetTable.ApprovalStatus =str2Enum(ApprovalStatus,approvalStatus);
timesheetTable.TimesheetNbr =timesheetNumber;
timesheetTable.Resource = resourceView.RecId;
//timesheetTable.Resource =name;
timesheetTable.ProjPeriodId = periodId;
timesheetTable.PeriodFrom =startDate;
timesheetTable.PeriodTo =endDate;
info(strFmt("Period ID : %1",timesheetTable.ProjPeriodId));
Info(strFmt("Start Date: %1, End Date : %2",timesheetTable.PeriodFrom, timesheetTable.PeriodTo));
//timesheetTable.D =resourceLegalEntity;
timesheetTable.insert();
Info("Inserted successfully to timesheettable");
//ttscommit;
// }
//if(timesheetLine)
// {
//ttsbegin;
timesheetLine.TimesheetNbr =timesheetNumber;
timesheetLine.ProjId =projectId;
timesheetLine.ProjectDataAreaId =projectLegalEntity;
timesheetLine.CategoryId =categoryId;
timesheetLine.Resource =resourceView.RecId;
//timesheetLine.ApprovalStatus =str2Enum(ApprovalStatus,approvalStatus);
if(approvalStatus == "Approved") timesheetTable.ApprovalStatus = TSAppStatus::Approved;
else if(approvalStatus == "Draft") timesheetTable.ApprovalStatus = TSAppStatus::Create;
else if(approvalStatus == "In Review") timesheetTable.ApprovalStatus = TSAppStatus::Pending;
timesheetLine.insert();
Info("Inserted successfully to timesheetLine");
// ttscommit;
select RecId from timesheetLine where timesheetLine.TimesheetNbr==timesheetNumber;
//}
//if(timesheetLineWeek)
// {
//ttsbegin;
timesheetLineWeek.TimesheetNbr =timesheetNumber;
timesheetLineWeek.DayFrom =startDate;
timesheetLineWeek.DayTo =endDate;
timesheetLineWeek.Hours[1] =any2Real(day1);
timesheetLineWeek.Hours[2] =any2Real(day2);
timesheetLineWeek.Hours[3] =any2Real(day3);
timesheetLineWeek.Hours[4] =any2Real(day4);
timesheetLineWeek.Hours[5] =any2Real(day5);
timesheetLineWeek.Hours[6] =any2Real(day6);
timesheetLineWeek.Hours[7] =any2Real(day7);
timesheetLineWeek.TSTimesheetLine = timesheetLine.RecId;
timesheetLineWeek.insert();
Info("Inserted successfully to timesheetLineWeek");
// ttscommit;
//}
}
catch(Exception::Error)
{
info(strFmt("The line no. %1 has been skipped due to an error", i));
//System.Exception ex = Exception::Error;
//error(ex.Message);
//Info(strFmt("Error %1",
continue;
}
}
}
}
}
}
}
These are the columns of Excel file which I want to import.
