We want to use GemBox.Spreadsheet for importing Excel data into a SQL database.
In order to avoid data type conflicts (as Excel allows “mixed” types due to its non-database nature), we first try to determine if all cells in a column have the same value type (e.g. String or Double).
Unfortunately, it seems that the ValueType property does not always match the cell data type definition given in the Excel spreadsheet.
We took a rather simple table (please download from this link as I can only attach pictures to here) for our tests and calling
worksheet.Cells[7, 1].ValueType
resulted in the type String being returned although this cell (B8 in Excel) is formatted as Number (which should return Double in that case as decimal places are permitted) in the spreadsheet.
Maybe we are simply doing something wrong or making incorrect “assumptions” concerning the return value of ValueType. That’s why I just ask here in the forum instead of opening a support ticket - I assume that it’s just a misunderstanding and not a bug in GemBox.Spreadsheet.
Could somebody please shed some light on this case and give us a hint how we can achieve a simple “data type check” on all data in a certain column?
Have you noticed that there is a warning message shown for cell B8?
See the following screenshot:
In short, Microsoft Excel detected that there is a cell with a text which could be a number (but is not).
Another way how you can notice that this is not a number is if you change the number format to something else (let’s say to Percentage) nothing happens.
Anyway, cell B8 really has a string value, it doesn’t matter what its number format is, the number format is used for defining how the numerical or date values should be displayed, but it doesn’t do anything to string value.
I hope this helps, let me know if you have any questions.
Hi Mario,
you are indeed right and I perfectly understood your explanation.
So it means that the number format defined within Excel (that the user can apply to each cell) does not have any impact on the “real” (low-level) data stored in the backend - which is great news!
We can simply reply on the ValueType property when “analyzing” the data before importing them into a SQL table and if there is a mismatch, inform the user about it so he / she can correct the data.
So thanks a lot for pointing this technical details out which has not been clear to me so far.
