custom date format reading from excel

Question

0.00/5 (No votes)

See more:

hi,

my custom date format is Excel cell contains date value in the format Mon-YYYY.
example = excel cell contain apr-14 but actual cell value is 28-04-2014 and second cell value apr-14 but value is 13-04-2014.

but i used function following ways:

C#

public static DateTime? CleanDateField(string DateField)
    {

        double d = double.Parse(DateField);

        // Get the converted date from the OLE automation date.
        DateTime conv = DateTime.FromOADate(d);


        // Convert the text to DateTime and return the value or null
        DateTime? CleanDate = new DateTime();
        int intDate;
        bool DateIsInt = int.TryParse(DateField, out intDate);
        if (DateIsInt)
        {
            // If this is a serial date, convert it
            CleanDate = DateTime.FromOADate(intDate);
        }
        else if (DateField.Length != 0 && DateField != "1/1/0001 12:00:00 AM" &&
            DateField != "1/1/1753 12:00:00 AM")
        {
            // Convert from a General format
            CleanDate = (Convert.ToDateTime(DateField));
        }
        else
        {
            // Date is blank
            CleanDate = null;
        }
        return CleanDate;
    }

but i am getting cell values for apr-14 is 01-04-2014 which is wrong i have to retrieve date
values while importing
1) first cell value is 28-04-2014
2) second cell value is 13-04-2014

Plz help i search on google i did not find correct solution
thanks in advance

Posted 5-May-14 19:51pm

sadhana4

Add a Solution

Comments

Laiju k 6-May-14 2:03am

what is your code for getting values from excel

How are retrieving the data from excel.
are you using Gridview or you simply getting values from excel

sadhana4 6-May-14 2:33am

string strTempPath = System.Configuration.ConfigurationManager.AppSettings["TempPath"].ToString();
string strFileName = fluplEarningsFile.FileName.ToString();
string strMainFilename = strTempPath + strFileName;
string oledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strMainFilename + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
fluplEarningsFile.SaveAs(strTempPath + strFileName);
if (fluplEarningsFile.HasFile)
{
DataSet ds;
DataSet dssubsector;
OleDbDataAdapter dataadapter;
SqlConnection con = new SqlConnection(connString);
OleDbConnection OLEConn = new OleDbConnection(oledbCon);
OleDbCommand oledbComm = new OleDbCommand();
string strQuarterMonth;
string strQuarterYear;
oledbComm.Connection = OLEConn;
OLEConn.Open();
dataadapter = new System.Data.OleDb.OleDbDataAdapter(oledbComm);
string strSelect = "select * from [Earnings$]";
oledbComm.CommandText = strSelect;
ds = new System.Data.DataSet();
dataadapter.Fill(ds);

dataset is using to read data and update the table

sadhana4 6-May-14 3:18am

plz tell me how to do

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)