Reading text from Excel, numeric data is very simple, there is no other conversion in Excel,
However, a problem occurs when reading date data:
The date data saved in Excel is actually a timestamp, which starts from January 1, 1900.
That is to say, 1900-1-1 is represented as 1 in Excel.
OK. After understanding this principle, you can start the operation:
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)
{
Timespan TS = Timespan. parse (exceldate );
Datetime dt = December31st1899 + TS;
If (DT > = March1st1900)
{
ReturnDT-After1stmarchadjustment;
}
Return DT;
}
Train of Thought reference
Http://blogs.msdn.com/eric_carter/archive/2004/08/14/214713.aspx
But this articleArticleCalculation method and I have some ways out:
1. You cannot directly convert the date data read from an Excel file to datetime, and an exception is thrown.
2. It should be calculated from 1899-12-31, rather
3. data before January 1, does not need to be added.
I don't know if it is. Net or Excel ???
PS: I used. NET 1.1 + Excel 2003
AllCode:
Add reference excel. dll first
Create an Excel file. In sheet1, enter A1, A2, and A3, respectively, 1900-1-1, 1900-2-28, and 1900-3-1.
The following code is C:
Class Exceldatetest
{
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)
{
Timespan TS = Timespan. parse (exceldate );
Datetime dt = December31st1899 + TS;
If (DT > = March1st1900)
Return DT - After1stmarchadjustment;
Return DT;
}
[Stathread]
Static Void Main ( String [] ARGs)
{
String Filename = @" E: \ project \ exceltest \ dt.xls " ; // Excel File Location
Excel. Application app = New Excel. Application ();
Excel. workbooks = App. workbooks;
Excel. _ workbook = Workbooks. Open (filename, type. Missing, type. Missing,
Type. Missing,
Type. Missing,
Type. Missing, type. Missing );
Excel. Sheets sheets = Workbook. worksheets;
Excel. worksheet Sheet = (Excel. worksheet) sheets [ 1 ];
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 ();
}
}