Import Excel Data to C# Object -> Unable to map DateTime values correctly

[Yannis]

Hi,

I want to import the data from an Excel file into my application using XlsIO. Saving the Excel file works without problems. In the Excel file I have 2 columns that contain DateTime Values. Unfortunately I cannot import these values correctly. In the Excel file these values are not saved as DateTime by default. However, I have already converted them manually to DateTime in Excel and still remain unsuccessful.

My current approach looks like this:

public async Task ImportExcelData(string filename)
        {
            List<WorkItem> newWorkItems = new List<WorkItem>();


            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;


                application.DefaultVersion = ExcelVersion.Xlsx;


                FileStream fileStream = new FileStream(filename, FileMode.Open, FileAccess.ReadWrite);


                fileStream.Position = 0;


                IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);


                IWorksheet worksheet = workbook.Worksheets[0];


                int rows = worksheet.Rows.Length;




                for (int i = 2; i <= rows; i++)
                {
                    var startDateStr = worksheet.Range["C" + i].Value;
                    var startDate = DateTime.ParseExact(startDateStr, "dd.MM.yyyy HH:mm", CultureInfo.InvariantCulture);
                    worksheet.Range["C" + i].Replace(startDateStr, startDate);


                    var endDateStr = worksheet.Range["D" + i].Value;
                    var endDate = DateTime.ParseExact(endDateStr, "dd.MM.yyyy HH:mm", CultureInfo.InvariantCulture);
                    worksheet.Range["D" + i].Replace(endDateStr, endDate);
                }


                Dictionary<string, string> mappingProperties = new Dictionary<string, string>();


                mappingProperties.Add("Title", "Title");
                mappingProperties.Add("Description", "Description");
                mappingProperties.Add("Start Time", "Start Time");
                mappingProperties.Add("End Time", "End Time");
                mappingProperties.Add("Category", "WorkCategory");
                mappingProperties.Add("Type", "WorkType");
                mappingProperties.Add("Operation", "OperationNumber");
                mappingProperties.Add("Bolted Joint ID", "BoltedJointId");
                mappingProperties.Add("Crane", "CraneNumber");
                mappingProperties.Add("Operator", "OperatorId");
                mappingProperties.Add("Standard Worksheet ID", "StandardWorksheetId");


                newWorkItems = worksheet.ExportData<WorkItem>(1,1,rows,11,mappingProperties);


                fileStream.Close();
            }


            _context.AddRange(newWorkItems);
            await _context.SaveChangesAsync();
        }

The DateTime values are always stored in the database as 0001-01-01 00:00:00.0000000. All other values are stored correctly (even enums). My Excel file looks like follows:

Does anyone here perhaps have an idea how I could make that working?

Greetings

Yannis

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Yannis,

Greetings from Syncfusion.

We suspect that the Excel document contains date values as string. So, XlsIO is not able to identify these values as date-time values and the cell values are not exported to the DateTime variable in the object. We request you create an Excel document with date values. This way, the date values will be detected and exported as expected.

Example: If the system date format is M/d/yyyy, then have the date value in the Excel document as 12/25/2021. 25/12/2021 does not work in this case.

Kindly try the suggestion and let us know if this helps.

Regards,

Keerthi.

[Yannis]

Hi,

When converting custom format to DateTime in Excel, sometimes inexplicable things happen....

For example, I use a Syncfusion scheduler and export its data to Excel. Its start and end times are saved in Excel as custom format "dd.mm.yyyy HH:mm". When I edit the file I cannot convert the cells to DateTime (d.m.yy HH:mm). In the UI it shows that the format is DateTime, but it is not true. The value in the cell remains "dd.mm.yyyy HH:mm".

If I take a free cell and write there as an example "01.01.2022 10:00" and then format this cell everything works. The value in the cell changes to "1.1.22 10:00" and I can import the file then and all values are getting mapped correctly. Inexplicable for me. Anyway, the import with XlsIO works if the cells really contain DateTime values.

Thanks for your feedback!

Yannis

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Yannis,

Thanks for your valuable feedback.

As mentioned, please use the Excel files with date time cells.

Regards,

Keerthi.