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 :)
<br>
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.
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:
<br>
// my code
var doBString = GetStringValue(worksheet.GetRow(row).Cells[2]);
<br> code from Stack minus the static method:
<br>
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;
}
}
<br> 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?
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.
I found a solid solution and thought I'd post it here:
In my .ts file I added a formData.append()
<br>
uploadExcel(data: { files: File }): void {
const formData: FormData = new FormData(); const file = data.files[0];
formData.append('file', file, file.name);
formData.append('guestEventId', this.guestEventId.toString());
<br> Then on the server, I added the following roughed in code <br>
[HttpPost]
public async Task<JsonResult> ImportAttendeesFromExcel()
{
long currentGuestEventId = 0;
try
{
var eventAttendeeFile = Request.Form.Files.First();
var hasGuestEventId = Request.Form.ContainsKey("guestEventId");
_logger.Warn("Has GuestEventId: " + hasGuestEventId);
var formKeys = Request.Form.Keys;
foreach (var formKey in Request.Form)
{
var guestEventId = formKey.Value;
_logger.Warn("GuestEventId: " + guestEventId);
currentGuestEventId = long.Parse(guestEventId);
_logger.Warn("GuestEventId Int64: " + currentGuestEventId);
}
if (eventAttendeeFile == null)
{
throw new UserFriendlyException("You must submit a file");
}
<br> I have some clean up to do but it is working well. Just thought I'd share.
I sent an email with the TeamView information just now. Thanks again!
Well, I would but I do not have an App_Data folder in my production application. I'm not sure what else to say about that. As far as going to that Url, I get this error:
I'm responding to the related email with the TeamViewer information.
Just so I this posted here and not lost in the email void, I'm also done the following:
1.) I've installed IIS CORS 2.) I've added some of cors xml attributes to the web.config: <cors enabled="true" failUnlistedOrigins="true"> <add origin="*" /> <add origin="http://karakthemad:4500" allowCredentials="true" maxAge="120"> <allowHeaders allowAllRequestedHeaders="true"> <add header="header1" /> <add header="header2" /> <add header="abp.tenantid" /> </allowHeaders> <allowMethods> <add method="GET" /> <add method="HEAD" /> <add method="POST" /> <add method="PUT" /> <add method="DELETE" /> </allowMethods> <exposeHeaders> <add header="header1" /> <add header="header2" /> </exposeHeaders> </add> </cors>
At his point I'm getting: GET http://karakthemad/Error?statusCode=404 500 (Internal Server Error) scripts.9470d603b68735921d8d.js:1 WARN: scripts.9470d603b68735921d8d.js:1 Could not find localization source: AbpWeb
I tried adding part of the web.config from the Angular UI to the bottom of the existing web.config: <staticContent> <remove fileExtension=".json" /> <mimeMap fileExtension=".json" mimeType="application/json" /> <mimeMap fileExtension="woff" mimeType="application/font-woff" /> <mimeMap fileExtension="woff2" mimeType="application/font-woff" /> </staticContent> <!-- IIS URL Rewrite for Angular routes --> <rewrite> <rules> <rule name="Angular Routes" stopProcessing="true"> <match url=".*" /> <conditions logicalGrouping="MatchAll"> <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" /> <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" /> <add input="{REQUEST_URI}" pattern="^/(api)" negate="true" /> </conditions> <action type="Rewrite" url="/" /> </rule> </rules> </rewrite> When I do get an for index.html <span class="colour" style="color: rgb(34, 34, 34);">I get a "too many rewrites error".</span> Anyway, I've emailed all of this plus my TeamViewer info a little while ago.
Thank you so much!
Greetings,
I know this is a lot to ask in the above post. If I could at least get a little direction on the appconfig.production.json settings, that would be really helpful. Also, for some reason I cannot edit the appconfig.production.json once it's in the wwwroot/. Should I make virtual directory and have the actual path just somewhere on the drive and not in the wwwroot?
Thanks again!
Excellent! So glad it helpled.