Excel interop - how to stop number (stored as text) being "evaluated"

Question

I was wondering if anyone had come across the following problem and had any ideas on how to resolve it: I'm exporting data from a C# application (.NET 3.5) to Excel (2003) via Interop. One of the columns stores a string value that appears to be numeric. That is to say it is a number that begins with a 0 e.g. 000123

I need the full number to be stored as it may be a serial number or something similar. Excel is not keen on this so I thought I could get around it by setting the destination cell to general. When I export to Excel I find 123 stored instead of 000123.

I've run through the app in debug and from the watchlist I've found that (for "Range range":

range.NumberFormat = "General"`
this.Rows[iGridRow].Cells[iGridCol].Value = "000123" '/* (datagrid is not truncating it)*/
range.Value2 = 123.0

It seems to be getting handled as a number even though I set the numberformat before this point:

range.NumberFormat = sNumberFormat;
range = (Range)sheet.Cells[iExcelRow, iExcelCol];
range.Value2 = this.Rows[iGridRow].Cells[iGridCol].Value.ToString();

Can anyone please help?

Answer 1

Add a single apostrophe ' before the number. Excel will then treat the number as a string.