Excel-DNA / Excel-DNA/Registration
InvalidCastException for nullable DateTimes and nullable custom types
- Dominant language
- C#
- Stars
- 37
- Forks
- 19
- PR merge metrics
- No merged PRs in 30d
Description
### Repro steps:
in Source\Samples\Registration.Sample\ParameterConversionExamples.cs, add this function in the `ParameterConversionExamples` class
```
[ExcelFunction] public static string dnaNullableOptionalDateTime(DateTime? val = null) => val.HasValue ? "VAL: " + val : "NULL";
```
Then in Excel, if you call `=dnaNullableOptionalDateTime(43101)` the result is:
```
!!! ERROR: System.InvalidCastException: Specified cast is not valid. at dnaNullableOptionalDateTime(Closure , Object ) at WrapperType.Wrapped_f32_Invoke(Object , Object )
--
```
I would have expected "1-Jan-18", which is what you get if call `=dnaDirectDateTime(43101)`. Also, `=dnaNullableOptionalDateTime()` returns "NULL" as expected.
The same thing happens with custom nullable structs. If you add this type to ParameterConversionExamples.cs
```
public struct TestStruct {
public string _value;
public TestStruct(string value) => _value = value;
public override string ToString() => $"TestStruct:{_value}";
}
```
and add these test functions in the `ParameterConversionExamples` class:
```
[ExcelFunction] public static string dnaTestStruct(TestStruct s) => s.ToString();
[ExcelFunction] public static string dnaNullableTestStruct(TestStruct? s) => s.HasValue ? s.Value.ToString() : "NULL";
[ExcelFunction] public static string dnaNullableOptionalTestStruct(TestStruct? s = null) => s.HasValue ? s.Value.ToString() : "NULL";
```
and add a conversion to `ExampleAddIn.GetParameterConversionConfig`:
```
.AddParameterConversion((string value) => new TestStruct(value))
````
Then you get similarly unexpected results: `=dnaNullableOptionalTestStruct("hey")` returns that same `InvalidCastException`.
### Versions
I reproduced this with the current HEAD of ExcelDNA.Registration (27bc193), Excel 1812 (Build 11126.20196 Click-to-Run) on Windows 10.
### Impact
We're currently on an ancient version of ExcelDNA.CustomRegistration (i.e. from before the rename). We can't migrate to the latest version of ExcelDNA.Registration because we have functions that take `DateTime?` parameters and other nullable parameters (using a `ParameterConversion` specific to `DateTime?`). I.e. our ParameterConversionConfig currently looks like:
```
.AddParameterConversion(ParameterConversions.GetOptionalConversion(treatEmptyAsMissing: true))
.AddParameterConversion(ParameterConversions.GetNullableConversion(true))
.AddParameterConversion((object o) => ConvertAnyObjectToNullableDateTime(o));
```
In other words, we were previously implementing our own custom logic for nullable custom types. This also no longer works with the latest version, and fails with
```
System.NullReferenceException: Object reference not set to an instance of an object.
at
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversions(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 72 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversion(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 62 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversions.NullableConversion(ExcelDna.Registration.ParameterConversionConfiguration config, System.Type type, ExcelDna.Registration.ExcelParameterRegistration paramReg, bool treatEmptyAsMissing, bool treatNAErrorAsMissing) Line 51 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversions.GetNullableConversion.AnonymousMethod__0(System.Type type, ExcelDna.Registration.ExcelParameterRegistration paramReg) Line 28 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionConfiguration.ParameterConversion.Convert(System.Type paramType, ExcelDna.Registration.ExcelParameterRegistration paramReg) Line 45 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.GetParameterConversions(ExcelDna.Registration.ParameterConversionConfiguration conversionConfig, System.Type initialParamType, ExcelDna.Registration.ExcelParameterRegistration paramRegistration) Line 74 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.d__0.MoveNext() Line 26 C#
ExcelDna.Registration.dll!ExcelDna.Registration.AsyncRegistration.d__0.MoveNext() Line 36 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParameterConversionRegistration.d__0.MoveNext() Line 15 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ParamsRegistration.d__0.MoveNext() Line 19 C#
ExcelDna.Registration.dll!ExcelDna.Registration.FunctionExecutionRegistration.d__0.MoveNext() Line 13 C#
ExcelDna.Registration.dll!ExcelDna.Registration.ExcelRegistration.RegisterFunctions(System.Collections.Generic.IEnumerable registrationEntries) Line 44 C#
Registration.Sample!Registration.Sample.ExampleAddIn.AutoOpen() Line 26 C#
```
I'm only including this in case the additional information is helpful--I don't need this bug to be fixed, because I'm happy to switch to the new way of using `ParameterConversionConfiguration.AddNullableConversion`.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.