ClosedXml "display value" and "real value"

0
votes

I am not sure if there is such a thing in Excel (xlsx) file, but if you see the screenshot

enter image description here

I can type in certain format of value what is displayed in cell is different from formula bar.

But if I use ClosedXml to create Excel, 

currentCell.Value = "1-Jan-2017";
currentCell.DataType = XLCellValues.DateTime;

The file generated by the code looks like (just see the last cell)

enter image description here

Is there a way I can use ClosedXml to achieve what I need? Our business person likes to see the value "1-Jan-2017".

1
excelclosedxml

1 Answers

0
votes

That format seems to be number 15 from the predefined date formats in Excel (see here). You can use the following to set it

currentCell.Style.DateFormat.SetNumberFormatId(15);

For other (not predefined) formats you can also use something like

currentCell.Style.DateFormat.SetDateFormat("d-mmm-yy");