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.

score 38 · Accepted Answer · answered Nov 03 '15 at 03:42

38

try this

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

answered Nov 03 '15 at 03:42

Alexey Miller

498
4
6

2

This worked for me but can anyone comment on why this works?? – jaredbaszler Dec 08 '16 at 21:58
I was also able to trim down the code above to this knowing that my `fieldValue` was already a string: `ws.Cell(rowCounter, colCounter).SetValue(fieldValue);` – jaredbaszler Dec 08 '16 at 22:04
6

@jaredbaszler this is a documented in the Wiki for ClosedXML. If you set a value using cell.Value = blah it always does automatic conversion of the values for you so it automatically recognizes the string as a date and converts it (much like Excel annoyingly does with CSV files by default). However the cell.SetValue() specifically avoids this and if the value is a string, it remains a string in the Excel file. – Kendall Bennett Oct 16 '17 at 18:40
1

wiki mentioned by @KendallBennett https://github.com/ClosedXML/ClosedXML/wiki/Text-with-numbers-are-getting-converted-to-numbers – Luizgrs Dec 10 '21 at 19:32

score 4 · Answer 2 · answered Aug 25 '15 at 14:23

4

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

For example...

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

Or

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

answered Aug 25 '15 at 14:23

u8it

3,956
1
20
33

1

This indeed works: worksheet.Range(firstCell, lastCell).Style.NumberFormat.Format = "@"; – Mill Feb 11 '21 at 12:46

score 2 · Answer 3 · edited Aug 25 '15 at 12:42

2

Consider:

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

Note the single quote.

edited Aug 25 '15 at 12:42

Kritner

13,557
10
46
72

answered Aug 25 '15 at 12:29

Gary's Student

95,722
10
59
99

that's pretty close - my only issue with it is if i hit F2 or enter the formula bar on the cell, the format then gets updated to date in the format of "mmm/yyyy". Any ideas on that? – Kritner Aug 25 '15 at 12:43

score 1 · Answer 4 · answered Oct 28 '16 at 03:57

1

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

answered Oct 28 '16 at 03:57

Rio Stephen

245
1
3
8

score 1 · Answer 5 · answered Jul 29 '21 at 12:55

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.

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

5 Answers5