Excel import datetime issue #11172 | Support Center

KarakTheWise created 2 years ago

Greetings,

API v 9.2.0 Angular

I'm having an odd bug where I can successfully import an Excel spreadsheet one time. If I try and do it again, a second time w/o recompiling the server code, I get a NullRef exception:

WARN 2022-07-13 17:00:04,517 [61 ] cts.Importing.ContactListExcelDataReader - DoB Exception: System.NullReferenceException: Object reference not set to an instance of an object. at NPOI.Util.LocaleUtil.GetLocaleCalendar(TimeZone timeZone) at NPOI.Util.LocaleUtil.GetLocaleCalendar() at NPOI.SS.UserModel.DateUtil.GetJavaCalendar(Double date, Boolean use1904windowing, TimeZone timeZone, Boolean roundSeconds) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date, Boolean use1904windowing) at NPOI.XSSF.UserModel.XSSFCell.get_DateCellValue() at Resolution.Contacts.Importing.ContactListExcelDataReader.ProcessExcelRow(ISheet worksheet, Int32 row) in D:\App Development\Resolution\ASPNET-Zero\Resolution\aspnet-core\src\Resolution.Application\Contacts\Importing\ContactListExcelDataReader.cs:line 53

It doesn't make sense as to why the DateOfBirth data is reading as nullref on the second import.

try
            {
                contact.FirstName = GetRequiredValueFromRowOrNull(worksheet, row, 0, nameof(contact.FirstName), exceptionMessage);
                \_logger.Warn("FirstName: " + contact.FirstName + " ex " + exceptionMessage);
                contact.LastName = GetRequiredValueFromRowOrNull(worksheet, row, 1, nameof(contact.LastName), exceptionMessage);
                \_logger.Warn("LastName: " + contact.LastName + " ex " + exceptionMessage);
                //contact.DateOfBirth = DateTime.Parse(GetRequiredValueFromRowOrNull(worksheet, row, 2, nameof(contact.DateOfBirth), exceptionMessage)).ToShortDateString();
                try
                {
                    //contact.DateOfBirth = DateTime.Parse(GetRequiredValueFromRowOrNull(worksheet, row, 2, nameof(contact.DateOfBirth), exceptionMessage));
                    c**ontact.DateOfBirth = worksheet.GetRow(row).Cells[2].DateCellValue;**
                    \_logger.Warn("DateOfBirth: " + contact.DateOfBirth + " ex " + exceptionMessage);
                }
                catch (Exception e)
                {
                    \_logger.Warn("DoB Exception: " + e);
                }

            }
            catch (System.Exception exception)
            {
                contact.Exception = exception.Message;
            }

The GetRequiredValueFromRowOrNull() method is the same as the method of the same name in UserListExcelDataReader.cs. I'm quite baffled and not sure what to look for. Could something be getting cached? But then I would think the entire set would be null or something, not just one column. I know the other columns are not null via logger entries:

WARN 2022-07-13 17:00:04,517 [61 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-13 17:00:04,517 [61 ] cts.Importing.ContactListExcelDataReader - FirstName: Zoe ex WARN 2022-07-13 17:00:04,517 [61 ] cts.Importing.ContactListExcelDataReader - LastName: James ex WARN 2022-07-13 17:00:04,517 [61 ] cts.Importing.ContactListExcelDataReader - DoB Exception: System.NullReferenceException: Object reference not set to an instance of an object.

This is screen grab of the Excel file:

Any advice would be so great. I've been on this for a couple of days now and not sure where else to look.

Thanks!

6 Answer(s)

0

musa.demir created 2 years ago

Hi @KarakTheWise

Can you please check if worksheet.GetRow(row).GetCell(column).StringCellValue is null.
0

KarakTheWise created 2 years ago

I would like to check for null but I can't pass the date value to that method because it's a seen as a number. So I get an error. And I guess the bigger issue is I'm not sure why it's fine the first time I run the import but not the times after? It's really odd. I've tried debugging but when it get's to that part of the code it just throws the exception error and the DateOfBirth spreadsheet column is being read as null. It doesn't make any sense :). If I recompile the server code, it imports correctly the first time.

After looking at my code some more throughout the day, what doesn't make sense is the fact that the first impor is working fine. Here's some log outputs: WARN 2022-07-14 17:14:09,205 [18 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:14:09,210 [18 ] cts.Importing.ContactListExcelDataReader - FirstName: Jane ex WARN 2022-07-14 17:14:09,210 [18 ] cts.Importing.ContactListExcelDataReader - LastName: Doe ex WARN 2022-07-14 17:14:09,211 [18 ] cts.Importing.ContactListExcelDataReader - DateOfBirth: 3/22/1997 12:00:00 AM ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - FirstName: Jim ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - LastName: Smith ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - DateOfBirth: 10/8/1992 12:00:00 AM ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - FirstName: Carry ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - LastName: Brown ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - DateOfBirth: 9/23/1972 12:00:00 AM ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - FirstName: Zoe ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - LastName: James ex WARN 2022-07-14 17:14:09,212 [18 ] cts.Importing.ContactListExcelDataReader - DateOfBirth: 6/13/2005 12:00:00 AM ex

But when I submit the very same sheet again, I get this for all four row. I only listed one. And I'm checking for duplicates yet, so that's not throwing any errors: WARN 2022-07-14 17:17:34,933 [7 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:17:34,933 [7 ] cts.Importing.ContactListExcelDataReader - FirstName: Jane ex WARN 2022-07-14 17:17:34,933 [7 ] cts.Importing.ContactListExcelDataReader - LastName: Doe ex WARN 2022-07-14 17:17:34,944 [7 ] cts.Importing.ContactListExcelDataReader - DoB Exception: System.NullReferenceException: Object reference not set to an instance of an object. at NPOI.Util.LocaleUtil.GetLocaleCalendar(TimeZone timeZone) at NPOI.Util.LocaleUtil.GetLocaleCalendar() at NPOI.SS.UserModel.DateUtil.GetJavaCalendar(Double date, Boolean use1904windowing, TimeZone timeZone, Boolean roundSeconds) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date, Boolean use1904windowing) at NPOI.XSSF.UserModel.XSSFCell.get_DateCellValue() at Resolution.Contacts.Importing.ContactListExcelDataReader.ProcessExcelRow(ISheet worksheet, Int32 row) in D:\App Development\Resolution\ASPNET-Zero\Resolution\aspnet-core\src\Resolution.Application\Contacts\Importing\ContactListExcelDataReader.cs:line 54 WARN 2022-07-14 17:17:34,944 [7 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:17:34,944 [7 ] cts.Importing.ContactListExcelDataReader - FirstName: Jim ex WARN 2022-07-14 17:17:34,944 [7 ] cts.Importing.ContactListExcelDataReader - LastName: Smith ex WARN 2022-07-14 17:17:34,944 [7 ] cts.Importing.ContactListExcelDataReader - DoB Exception: System.NullReferenceException: Object reference not set to an instance of an object. at NPOI.Util.LocaleUtil.GetLocaleCalendar(TimeZone timeZone) at NPOI.Util.LocaleUtil.GetLocaleCalendar() at NPOI.SS.UserModel.DateUtil.GetJavaCalendar(Double date, Boolean use1904windowing, TimeZone timeZone, Boolean roundSeconds) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date, Boolean use1904windowing) at NPOI.XSSF.UserModel.XSSFCell.get_DateCellValue()

I've been using the user import code as my template for this process. I've compared the code line by line but that doesn't mean I'm not missing something. But, it would be one thing if it consistenlty was miss data in x column, but that's not the case only on subsequent submissions after the first submission succesfully imports.
0
KarakTheWise created 2 years ago
Alright, after doing some more digging I found something interesting that you may wish to know about:

This is from NPOI's github: Not sure if it's the same verison but, it's still having NullRef issues. https://github.com/nissl-lab/npoi/issues/358

I found this Stack post that is dealing with the exact same type of issue I am: https://stackoverflow.com/questions/54465099/npoi-icell-datecellvalue-return-nullreferenceexception

"Npoi ICell.DateCellValue return NullReferenceException"

In this post, someone posted a solution that I think will work:

 

// my code var doBString = GetStringValue(worksheet.GetRow(row).Cells[2]);

 code from Stack minus the static method:

 

private string GetStringValue(ICell cell) { switch (cell.CellType) { case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) { try { return cell.DateCellValue.ToString(); } catch (NullReferenceException) { return DateTime.FromOADate(cell.NumericCellValue).ToString(); } } return cell.NumericCellValue.ToString(); case CellType.String: return cell.StringCellValue; case CellType.Boolean: return cell.BooleanCellValue.ToString(); default: return string.Empty; } }

 Log results show the NullRef AND the correct date returned by the returned value of var doBString above: WARN 2022-07-14 17:35:30,817 [7 ] cts.Importing.ContactListExcelDataReader - Process Excel Row WARN 2022-07-14 17:35:30,817 [7 ] cts.Importing.ContactListExcelDataReader - FirstName: Jane ex WARN 2022-07-14 17:35:30,817 [7 ] cts.Importing.ContactListExcelDataReader - LastName: Doe ex WARN 2022-07-14 17:35:30,817 [7 ] cts.Importing.ContactListExcelDataReader - GetStringValue result: 3/22/1997 12:00:00 AM WARN 2022-07-14 17:35:30,828 [7 ] cts.Importing.ContactListExcelDataReader - DoB Exception: System.NullReferenceException: Object reference not set to an instance of an object. at NPOI.Util.LocaleUtil.GetLocaleCalendar(TimeZone timeZone) at NPOI.Util.LocaleUtil.GetLocaleCalendar() at NPOI.SS.UserModel.DateUtil.GetJavaCalendar(Double date, Boolean use1904windowing, TimeZone timeZone, Boolean roundSeconds) at NPOI.SS.UserModel.DateUtil.GetJavaDate(Double date, Boolean use1904windowing) at NPOI.XSSF.UserModel.XSSFCell.get_DateCellValue()

So my take on this thus far is it's most likely problem with NPOI. Thought I'd put that out there. Any thoughts?
0
KarakTheWise created 2 years ago
Just to finish off for anyone needing to use this. I will be adding null checks etc. before call the DateTime.Parse() but, this what the 'happy path' looks like at this point :)

 

var doBString = GetStringValue(worksheet.GetRow(row).Cells[2]); contact.DateOfBirth = DateTime.Parse(doBString);

Works great and inserts into SQL just fine. I hope this helps.
0

ismcagdas created 2 years ago
Support Team

Thank you for sharing the problem and the solution @KarakTheWise :)
0

KarakTheWise created 2 years ago

You're welcome! Sorry for the walls of text. I hope it's useful information.

Have an answer to this question? Log in and write your answer.