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.