C# Excel Interop: How to format cells to store values as text

Question

I'm writing numbers to an Excel spreadsheet from a DataTable and all of these numbers are 5 digits long with preceding 0s if the number itself is less than 5 digits long (so 395 would be stored as 00395, for example).

When entering these numbers into Excel (using C#), it's storing them as numbers and eliminating the preceding 0s. Is there any way I can format the cells from C# in order to get the values to be stored as text rather than numbers?

Answer 1

You can SomeRange.NumberFormat = "@"; or if you prefix the value with a ' and write it to the cell excel will treat it as a number-stored-as-text and provide a visual cue.

Answer 2

This answer just solved a major problem with a solution from one of our company's software, I had to retrieve the value as displayed, but once I set it to the new sheet, it was being inserted as a number. Simple solution. I cant vote up as yet, but down follows how it ended up.

for (int h = 1; h <= 1; h++)
{
    int col = lastColl(sheets);
    for (int r = 1; r <= src.Count; r++)
    {
        sheets.Cells[r, col + 1] = "'"+src.Cells[r, h].Text.ToString().Trim();
    }
}