从EXCEL中读取文本,数值型数据都很简单,EXCEL没有做其他转换,
可是读取日期数据就出现了一个问题:
EXCEL保存日期数据实际上存放的是时间戳,从1900年开始算起。
也就是说 1900-1-1 在EXCEL中表示为 1 。
OK,明白了这个原理之后就可以开始操作了 :
static readonly DateTime march1st1900 = new DateTime(1900, 03, 01);
static readonly DateTime december31st1899 = new DateTime(1899, 12, 31);
static readonly TimeSpan after1stMarchAdjustment = new TimeSpan(1, 0, 0, 0);
public static DateTime ConvertExcelDateToDate(string excelDate)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
TimeSpan ts = TimeSpan.Parse(excelDate);
DateTime dt = december31st1899+ts;
if (dt >= march1st1900)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return dt - after1stMarchAdjustment;
}
return dt;
}
思路参考
http://blogs.msdn.com/eric_carter/archive/2004/08/14/214713.aspx
不过这篇文章的计算方式和我有些出路:
1. 不能将从 Excel 文件中 读取到的日期数据直接转换为 DateTime, 会抛出异常
2. 应当从1899-12-31开始算而非1900-01-01
3. 1900-03-01 之前的数据不用再加1,而之后的才要加
不知道是 .net 版本的问题还是 Excel ???
ps: 我用 .net 1.1+Excel 2003
以下贴出全部代码:
先添加引用EXCEL.dll
再新建一个EXCEL文件,在sheet1中 A1,A2,A3 分别输入 1900-1-1 , 1900-2-28, 1900-3-1
以下是 C# 代码:
![](/Images/OutliningIndicators/None.gif)
class ExcelDateTest
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
static readonly DateTime march1st1900 = new DateTime(1900, 03, 01);
static readonly DateTime december31st1899 = new DateTime(1899, 12, 31);
static readonly TimeSpan after1stMarchAdjustment = new TimeSpan(1, 0, 0, 0);
public static DateTime ConvertExcelDateToDate(string excelDate)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
TimeSpan ts = TimeSpan.Parse(excelDate);
DateTime dt = december31st1899+ts;
if (dt >= march1st1900)
return dt - after1stMarchAdjustment;
return dt;
}
![](/Images/OutliningIndicators/InBlock.gif)
[STAThread]
static void Main(string[] args)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string fileName = @"E:\Project\EXCELTEST\dt.xls"; // EXCEL文件位置
Excel.Application app = new Excel.Application();
Excel.Workbooks workbooks = app.Workbooks;
Excel._Workbook workbook = workbooks.Open(fileName,Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Excel.Sheets sheets = workbook.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets[1];
![](/Images/OutliningIndicators/InBlock.gif)
string s = sheet.get_Range("A1",Type.Missing).Value2.ToString();
DateTime dt = ConvertExcelDateToDate(s);
Console.WriteLine(dt);
s = sheet.get_Range("A2",Type.Missing).Value2.ToString();
Console.WriteLine(ConvertExcelDateToDate(s));
s = sheet.get_Range("A3",Type.Missing).Value2.ToString();
Console.WriteLine(ConvertExcelDateToDate(s));
app = null;
GC.Collect();
Console.Read();
}
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)