You're welcome! Sorry for the walls of text. I hope it's useful information.
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.
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!
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.
API 9.2.0 Angular UI
Greetings,
I'm using the code from the user Angular component as a template to import data from an Excel spreadsheet. The importing process is working very well. What I'm needing is a solution to get an eventId to the server. I need that id when I import the data into the db. So far I've tried using params in the HttpClient post() as such below:
<br>
this._httpClient
//.post<any>(this.uploadUrl, formData)
.post<any>(this.uploadUrl, formData, {params: {'guestEventId': this.guestEventId.toString()}})
.pipe(finalize(() => this.excelFileUpload.clear()))
.subscribe(response => {
<br> The Url is appended as expected a https://localhost:44301/GuestEvent/ImportAttendeesFromExcel?guestEventId=21 like this. But the GuestEventController does not know how to deal with the appended parameters. I don't have to do it this way if you have another solution? Or if you could give me some advice as to how to deal with the 'new' Url so I can use and then how to grab the parameters once I get to my method on the server: <br>
[HttpPost]
public async Task<JsonResult> ImportAttendeesFromExcel()
{
try
{
var eventAttendeeFile = Request.Form.Files.First();
<br> The above is a snippet from the controller code that is catching the JsonResult. Any advice would be awesome!
Thanks,
Scott
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.