Save excel cell format as text with date type data

Question

This seems silly, but I was not able to get my values ​​in the format #/#### to write as a literal string, rather than formatting as a date in excel.

I am 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 excel output sheet, I get in cell 2/1/1997 - although I set the format as text in the code, I get it as "Date" on the excel sheet - I checked this by right-clicking on the cell, formatting the cell, view "date" in format.

If I change things to:

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

Instead, I get 35462 as my output.

I just want my literal value 2/1997 appear on the sheet. Please advise how to fix it.

Answer 1

try it

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

Answer 2

Consider:

 tableRow.Cell(1).Value = "'2/1997"; 

Note the single quotation mark.

Answer 3

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

For example...

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

Or

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

Answer 4

 ws.Cell(rowCounter, colCounter).Value="'"+Convert.ToString(fieldValue));