为什么这些MMM YY值在我的Excel电子表格中显示为YY-MMM？ [英] Why are these MMM YY values displayed as YY-MMM in my Excel spreadsheet?
问题描述
我需要13个单元格，其中包含三个字符的月份，然后是一个空格，然后是两个字符的年份，例如：
4月14日
5月14日
...
1月15日
...
4月15日
从用户那里获得开始（和结束）月份和结束年份，我写了这个函数来存储通用列表中的所有val：
私人列表< string> GetMonthAndTruncatedYears（ string monthBeginAndEnd， string endYear）
{
const int MONTHS_TO_ADD = 13 ;
String endYear2Digit =（Convert.ToInt32（endYear））。ToString（）;
endYear2Digit = endYear2Digit.Substring（ 2 ）;
String beginYear2Digit =（Convert.ToInt32（endYear） - 1 ）。ToString（）;
beginYear2Digit = beginYear2Digit.Substring（ 2 ）;
String yearToUse = beginYear2Digit;
List< String> listMonthsYears = new List< string>（）;
listMonthsYears.Add（ String .Format（ {0} {1}，monthBeginAndEnd，yearToUse））;
int valsAdded = 1 ;
String curMonth = monthBeginAndEnd;
while （valsAdded < MONTHS_TO_ADD）
{
curMonth = GetMonthStr（curMonth， 1 ）;
if （curMonth.Equals（ Jan ））
{
yearToUse = endYear2Digit;
}
listMonthsYears.Add（ String .Format（ {0} {1}，curMonth，yearToUse））;
valsAdded ++;
}
return listMonthsYears;
}
private string GetMonthStr（ string beginMonth， int offset）
{
int curIndex = UsageRptConstsAndUtils.months.IndexOf（beginMonth）;
if （curIndex == 11 ）
{
curIndex = -1;
}
return UsageRptConstsAndUtils.months [curIndex + offset];
}
public static List< String> months = new 列表< String>
{
Jan，
Feb，
Mar，
Apr，
5月，
Jun，
Jul，
Aug，
Sep，
Oct，
Nov，
Dec
};
这些值根据需要存储在通用列表中。
我将值作为字符串添加到单元格中：
_xlSheet.Cells [ 7， C] = monthsTruncatedYears [ 0 ];
但是，虽然我希望这些项目显示原始/逐字，但它们在Excel单元格中显示为14-Apr等等。为什么？谁告诉他们改变格式？
NOte：我使用的是Microsoft.Office.Interop.Excel，v2.0.50727
那么怎么样我可以告诉Excel保留格式吗？
对于日期而言，Excel是一个有趣的兔子。它总是将日期存储为int。如果你告诉它存储4月14日，它将尝试将其解析为日期，并始终假设该数字为当天（至少在2032年之前），因为这提供了最高精度。
因为它总是将日期存储为整数（以天为单位），所以需要知道每月的哪一天使用。你将不得不告诉它01 apr 2014，例如。
它不会继承你给它的日期项目的格式。它会尝试推断这个，但它不能来自'MMM YY'，因为它必须假设那是'MMM-DD'（因此是奇怪的格式）
所以 - 两个选项。将日期添加为字符串并将单元格格式化为文本，或将日期添加为完整日期（即dd mmm yyyy）并将自定义格式添加到列中。
用于格式化列的方法取决于您正在使用的Office APi
您需要知道Excel会自动解释输入的所有字符串。
在Excel中
要强制Excel保留您输入的内容，您需要一个'作为第一个字符
类型 08 将显示 8
类型 '08 将显示 08
<来自C＃或VBA的
在单元格中存储08，Excel将显示 8
在单元格中存储'08，Excel将显示 08
引用：为什么这些MMM YY值在我的Excel电子表格中显示为YY-MMM？
所以答案是简单：
你的程序进入JAN 15， excel将其解释为日期（当月1日）然后应用当前格式的日期。
来防止这种情况，
- 您可以根据日期设置单元格格式根据您的需要。
- 您可以通过在值'JAN 15之前添加前导标记来告诉Excel不要解释该值。
这是有效的，特别感谢Andy和Jochen：
Microsoft.Office.Interop.Excel.Range monthYearCells = _xlSheet.Range [_xlSheet.Cells [ 7 ， 3 ]，_ xlSheet.Cells [ 7 ， 15 ]];
monthYearCells.NumberFormat = @;
I need 13 cells containing three-character months followed by a space and then a two-character year, such as:
Apr 14
May 14
...
Jan 15
...
Apr 15
Getting the beginning (and ending) month and the ending year from the user, I wrote this function to store all the vals in a generic list:
private List<string> GetMonthAndTruncatedYears(string monthBeginAndEnd, string endYear)
{
const int MONTHS_TO_ADD = 13;
String endYear2Digit = (Convert.ToInt32(endYear)).ToString();
endYear2Digit = endYear2Digit.Substring(2);
String beginYear2Digit = (Convert.ToInt32(endYear) - 1).ToString();
beginYear2Digit = beginYear2Digit.Substring(2);
String yearToUse = beginYear2Digit;
List<String> listMonthsYears = new List<string>();
listMonthsYears.Add(String.Format("{0} {1}", monthBeginAndEnd, yearToUse));
int valsAdded = 1;
String curMonth = monthBeginAndEnd;
while (valsAdded < MONTHS_TO_ADD)
{
curMonth = GetMonthStr(curMonth, 1);
if (curMonth.Equals("Jan"))
{
yearToUse = endYear2Digit;
}
listMonthsYears.Add(String.Format("{0} {1}", curMonth, yearToUse));
valsAdded++;
}
return listMonthsYears;
}
private string GetMonthStr(string beginMonth, int offset)
{
int curIndex = UsageRptConstsAndUtils.months.IndexOf(beginMonth);
if (curIndex == 11)
{
curIndex = -1;
}
return UsageRptConstsAndUtils.months[curIndex + offset];
}
public static List<String> months = new List<String>
{
"Jan",
"Feb",
"Mar",
"Apr",
"May",
"Jun",
"Jul",
"Aug",
"Sep",
"Oct",
"Nov",
"Dec"
};
The values are stored as desired in the generic list.
I am adding the values to the cells as a string:
_xlSheet.Cells["7", "C"] = monthsTruncatedYears[0];
However, although I expect the items to display raw/verbatim, instead they display in the Excel cells as "14-Apr" etc. Why? Who told them to change format?
NOte: I am using Microsoft.Office.Interop.Excel, v2.0.50727
So how can I tell Excel to leave the formatting be?
Excel is a funny bunny when it comes to dates. It will always store the date as an int. If you tell it to store "Apr 14" it will try to parse that as a date and will always assume the number to be the day (at least before 2032 it will), as that provides the most precision.
Because it will always store a date as an integer (in days), it will need to know what day of the month to use. You will have to tell it "01 apr 2014" for eg.
It will not inherit the formatting of the item you date item you give it. It will try to "infer" this, but it can't from 'MMM YY' because it must assume that that is 'MMM-DD' (hence the odd format)
So - Two Options. add the date as a string and format the cell as text, or add the date as a full date (i.e. dd mmm yyyy) and add custom formatting to the column.
The methods used to format columns depends of what Office APi you're using
You need to know that Excel is automatically interpreting all string entered.
In Excel
To force Excel to keep the exactly what you typed, you need a "'" as first char
type 08 will display 8
type '08 will display 08
from C# or VBA
Store "08" in a cell and Excel will display 8
Store "'08" in a cell and Excel will display 08
Quote:Why are these MMM YY values displayed as YY-MMM in my Excel spreadsheet?
so the answer is simple:
Your program enters "JAN 15", excel interpret it as a date (the 1st of the month) and then apply current formatting for a date.
to prevent this,
- You can set the cell format according to your needs.
- You can tell Excel that the value is not to be interpreted by adding a leading mark before the value "'JAN 15".
This is what worked, thanks to Andy and Jochen especially:
Microsoft.Office.Interop.Excel.Range monthYearCells = _xlSheet.Range[_xlSheet.Cells[7, 3], _xlSheet.Cells[7, 15]]; monthYearCells.NumberFormat = "@";
这篇关于为什么这些MMM YY值在我的Excel电子表格中显示为YY-MMM？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
