在 c# 中使用 Microsoft.Office.Interop.Excel 在 excel 列中设置数字、文本和日期

问题描述

我正在尝试将数据类型设置为 C# 中的 excel 列，在本例中为数字、文本和日期.

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

如何为整个 excel 列设置格式?

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

推荐答案

设置文本范围:

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

将范围设置为数字

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;

日期有点复杂，也取决于您的区域设置:

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

这篇关于在 c# 中使用 Microsoft.Office.Interop.Excel 在 excel 列中设置数字、文本和日期等数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！