ClosedXML - Setting data type for cell does not work

Question

I have similar question about ClosedXML, like this one but a little bit diferent. The SetDataType method does not work. For example, I have value string dps = "3.12.02" which is not a date nor number, it is valid text.

When I do this: ws.Cell(1, 1).Value = dps; ws.Cell(1, 1).SetDataType(XLCellValues.Text);

and save the file and then open it in Excel, it still convert it to some rubish like 37593 I tried to put it before and after setting the value, no change.

Can anybody help me please?

Answer 1

The .Value method tries to guess the type of the value that you're setting. In your case, the value is a valid (i.e. parseable) DateTime, which are internally stored as numbers.

To set a value explicitly, without guessing, use:

string dps = "3.12.02";
ws.Cell(1, 1).SetValue(dps);

Answer 2

Although I can't tell you why it's not working, one easy way is to prepend your string with an apostrophe ('). This forces Excel to treat everything that follows as text.

string dps = "'3.12.02";
ws.Cell(1, 1).Value = dps;