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 = "#/####"

Answer 3

Consider:

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

Note the single quote.

Answer 4

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

Answer 5

Formatting has to be done before you write values to the cells.

I had following mechanism, run after I make worksheet, right before I save it:

    private void SetColumnFormatToText(IXLWorksheet worksheet)
    {
        var wholeSheet = worksheet.Range(FirstDataRowIndexInExcel, StartCellIndex, RowCount, HeaderCount);
        wholeSheet.Style.NumberFormat.Format = "@";
    }

which didn't do squat.

Doing it before I write values to the cells in a row did it.

worksheet.Range(RowIndex, StartCellIndex, RowIndex, EndCellIndex).Style.NumberFormat.Format = "@";

with cell value assignments following immediately after.