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?

Ectogenous

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";

Pennant

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 – Ectogenous

@Ectogenous 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. – Pennant

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: https://mcmap.net/q/744003/-how-to-set-localized-short-date-format-for-a-cell-in-excel-with-c – Loeb

Answer 2

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://mcmap.net/q/744003/-how-to-set-localized-short-date-format-for-a-cell-in-excel-with-c

Loeb

Answer 3

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

row["Total Sold/Off"] = "'" + row["Sold"].ToString() + "/" + row["Offered"].ToString();

Abigael