17

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?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Jack1987
  • 727
  • 1
  • 14
  • 26

3 Answers3

34

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";
Sid Holland
  • 2,871
  • 3
  • 27
  • 43
  • In the case of set range to date xlYourRange.NumberFormat = "DD/MM/YYYY"; it set the range to custom type I try xlYourRange.NumberFormat = "DD-MM-YYYY"; and then set it to date type – Jack1987 Dec 13 '12 at 13:46
  • @Jack1987 Looks like dates are even more complicated than I at first thought. I've amended my answer to include more combinations but it looks like it's mostly a case of trial and error. In applications I've coded what I usually do is set the date as a string and format it as something like "23, September 2011" so that it stays as a string. That way I can always guarantee the format no matter what the user's regional settings are. – Sid Holland Dec 13 '12 at 16:04
  • If you are thinking about localization problems in the date formats (for example, in Russian localization your date format can be "ДД.MM.ГГГГ"), see my answer here: http://stackoverflow.com/a/35418176/2199512 – Jordan Feb 16 '16 at 08:18
0

Yes, with the date format everything is more complicated - even more complicated than Sid Holland has mentioned. The reason is in some localization problems. For example, if your Windows system has Russian localization, you should use Russian letters in the date formats, like "ДД.MM.ГГГГ" or "ГГГГ-MM-ДД", and, therefore, you should be able to extract and apply these letters. See more or less complete description and solution here: https://stackoverflow.com/a/35418176/2199512

Community
  • 1
  • 1
Jordan
  • 585
  • 1
  • 7
  • 18
0

Concatenate an apostrophe in the beginning of the value fix my problem:

row["Total Sold/Off"] = "'" + row["Sold"].ToString() + "/" + row["Offered"].ToString();
Syscall
  • 19,327
  • 10
  • 37
  • 52
Jose R. Lopez
  • 21
  • 4