Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#

Question

I am trying to set the data type to an excel column in C#, in this case the data types number, text and date.

How does one set a format to an entire excel column?

Answer 1

To set a range to text:

xlYourRange.NumberFormat = "@";

You can also prefix a value you put in a cell with an apostrophe for it to format it as text:

xlYourRange.Value = "'0123456";

To set a range to number

xlYourRange.NumberFormat = "0";

Obviously if you want to set the format for the entire column then your range will be the column.

xlYourRange = xlWorksheet.get_Range("A1").EntireColumn;

EDIT:

Dates are a bit more complicated and will also depend on your regional settings:

// Results in a Date field of "23/5/2011"

xlRange.NumberFormat = "DD/MM/YYYY";
xlRange.Value = "23/5/2011";

// Results in a Custom field of "23/5/2011"

xlRange.NumberFormat = "DD-MM-YYYY";
xlRange.Value = "23/5/2011";

// Results in a Custom field of "05/23/2011"

xlRange.NumberFormat = "MM/DD/YYYY";
xlRange.Value = "5/23/2011";

// Results in a Custom field of "05-23-2011"

xlRange.NumberFormat = "MM-DD-YYYY";
xlRange.Value = "5/23/2011";

// Results in a Date field of "23/05/2011"

xlRange.NumberFormat = "DD/MM/YYYY";
xlRange.Value = "5/23/2011";

// Results in a Custom field of "23-05-2011"

xlRange.NumberFormat = "DD-MM-YYYY";
xlRange.Value = "5/23/2011";

// Results in a Custom field of "23/5/2011"

xlRange.NumberFormat = "MM/DD/YYYY";
xlRange.Value = "23/5/2011";

// Results in a Custom field of "23/5/2011"

xlRange.NumberFormat = "MM-DD-YYYY";
xlRange.Value = "23/5/2011";