ClosedXML - Setting data type for cell does not work

4
votes

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?

3
c#.netexcelclosedxml

3 Answers

9
votes

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);
2
votes

I realize you are trying to set a Text value but I have found that LibreOffice created Excel files seem to somehow mess up ClosedXMLs ability to set a column to a DateTime data type.

// What I tried without success.
cell.Clear(XLClearOptions.All);
cell.SetValue<DateTime>((DateTime)cellValue);
cell.SetDataType(XLDataType.DateTime);

Nothing I did would fix the issue until I recreated the spreadsheet in Excel.

// This worked just fine on a proper Excel file
cell.SetValue((DateTime)cellValue);

As soon as I opened and saved the spreadsheet with LibreOffice, the ability to correctly set the column type disappeared.

1
votes

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;