Reading date data in Excel in. net

Source: Internet
Author: User

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 ();
}
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.