Keep excel cell format as text with "date like" data

Question

This seems silly, but I haven't been able to get my values in the format of #/#### to write as the literal string rather than becoming formatted as a date within excel.

I'm using ClosedXML to write to excel, and using the following:

// snip
IXLRangeRow tableRow = tableRowRange.Row(1);
tableRow.Cell(1).DataType = XLCellValues.Text;
tableRow.Cell(1).Value = "2/1997";
// snip

Looking at the output excel sheet I get in the cell 2/1/1997 - even though I'm setting the format as text in code, I'm getting it as a "Date" in the excel sheet - I checked this by right clicking the cell, format cell, seeing "date" as the format.

If I change things up to:

// snip
IXLRangeRow tableRow = tableRowRange.Row(1);
tableRow.Cell(1).Value = "2/1997";
tableRow.Cell(1).DataType = XLCellValues.Text;
// snip

I instead get 35462 as my output.

I just want my literal value of 2/1997 to be displayed on the worksheet. Please advise on how to correct.

Answer 1

try this

ws.Cell(rowCounter, colCounter).SetValue<string>(Convert.ToString(fieldValue));

Answer 2

Not sure about from ClosedXML, but maybe try Range.NumberFormat (MSDN Link)

For example...

Range("A1").NumberFormat = "@"

Or

Selection.NumberFormat = "#/####"