Numbers returned as formatted value about linqtoexcel

[mrworkman]

A PR sounds like a good idea. Let's refrain from using actual integer values though. Can you set it up with an enum with sensible names?

[tomchandler182]

Is there any progress on this?

[JohnHopstaken]

Hi Tom,

I will send the PR this week. I kinda forgot about this issue, but I will take it up this week.
Sorry for the delay.

[JohnHopstaken]

It turned I could only reproduce this problem in the Excel file mentioned above.
When I implemented the option and created integration tests with a newly created test Excel file, both the "formatted value" and "raw value" options returned the unformatted values. 🤔
I don't know why the problem in my original Excel files was solved when I used the IMEX=2 option.
Therefore, as my tests were unfortunately unable to prove the expected difference between IMEX=1 and IMEX=2, there is no point in sending a PR with my changes. 😕

[tomchandler182]

Hi John, i think this issue is also affected by the data types in first 8 rows of the excel. if the first 8 as string, the Engine sets the whole column for the sheet as strings so all values are returned as formatted values. If there is a mix of data types then the column is set as a mixed type and therefore depending on the imex used, either a formatted or unformatted value is returned.

https://stackoverflow.com/questions/50013333/reading-excel-with-oledb-not-displaying-correct-values

[JohnHopstaken]

Hi Tom,

I think you could be right. But in the mentioned Excel sheet I had either the formatted value or the unformatted value depending on the IMEX setting. It was consistent.
I don't understand why things behave differently in my newly created test Excel sheet. Even if I use mixed input in a given column.
As said, because I cannot get things to work as envisioned I will leave things as they are.