Exception while reading nullable values in excel file

[jc-akkodis]

Excel Type

• [ ] XLSX
• [x] XLSM
• [ ] CSV
• [ ] OTHER

Upload Excel File

Mappe1.xlsm.txt Note: I was not able to upload xlsm file due to your github restrictions. You need to remove .txt from the file name.

MiniExcel Version

1.31.3

Description

I have a column of type double. Sometimes the cell is empty and I declacred the model property as nullable double:

I get this exception while reading the excel file: var data= MiniExcel.Query(path, sheetName: "proj").ToList();

I get the same issue with DateTime properties.

Please help.

[pszybiak]

The cell probably contains string.Empty value, not null value. How was this excel document saved? Was it created manually or generated by some tools? Can you send a sample file?

[jc-akkodis]

@pszybiak I uploaded the file. The link is in the issue description. In the file you will also find the column Date with the same issue. Good question. I can't tell you if the file was generated or created manually. I assume it was at least modified manually.

[pszybiak]

As I thought, an empty string is inserted in the specified fields.

Changing application behavior in this context can be difficult, because it may break the backward compatibility of the library. We can introduce a modification that will convert the string.Empty value to NULL for types other than string (of course if this type can be NULL). But how should the library behave with string fields? Leave the string.Empty value or change it to NULL? Or maybe we need to add an additional configuration option (an attribute on a field or a DynamicColumn), e.g. ReadEmptyStringAsNull. Or a global option like ReadEmptyStringAsNull for CSV files.

@shps951023 what do you think?

[shps951023]

@pszybiak ❤❤❤ I think ReadEmptyStringAsNull option is better one. Would you like to PR?