Import Excel data with Cell Formatting (i.e. Date is not coming date format )

Question

1.00/5 (1 vote)

See more:

Excel sheet cell has date as "11-30-11" but when i import it convert to "78608". So i want import those data with date as "11-30-11".

You can find that line after first for loop part

 private void processExcel(string filename)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;

            var missing = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();

            xlWorkBook = xlApp.Workbooks.Open(filename, false, true, missing, missing, missing, true,
                Excel.XlPlatform.xlWindows, missing, false, false, 0, false, true, 0);
            
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Excel.Range xlRange = xlWorkSheet.UsedRange;
            Array myValues = (Array)xlRange.Cells.Value2;

            int vertical = myValues.GetLength(0);
            int horizontal = myValues.GetLength(1);

            DataTable mainDt = new DataTable();
            DataTable MiscDt = new DataTable();

            // must start with index = 1
            // get header information
            for (int i = 1; i <= horizontal; i++)
            {
                mainDt.Columns.Add(new DataColumn(myValues.GetValue(1, i).ToString()));
            }

            // Get the row information
            for (int a = 2; a <= vertical; a++)
            {


//SEE Below line for QUESTION..

                //Excel sheet cell has data as "11-30-11" but when i import it convert to "78608".  So i want import those data with data as "11-30-11".  


                string x = Convert.ToString(myValues.GetValue(a, 2));
                object[] poop = new object[horizontal];
                //if (x == "11-30-11")
                //{                    
                    for (int b = 1; b <= horizontal; b++)
                    {                        
                        poop[b - 1] = myValues.GetValue(a, b);
                    }
                    DataRow row = mainDt.NewRow();
                    row.ItemArray = poop;
                    mainDt.Rows.Add(row);
                //}                
            }

            // assign table to default data grid view
            dataGridView1.DataSource = mainDt;

            xlWorkBook.Close(true, missing, missing);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

Posted 21-Sep-12 9:22am

Vineet_2109

Updated 16-Oct-19 23:57pm

v2

Add a Solution

Comments

[no name] 21-Sep-12 15:47pm

What's the question?

Vineet_2109 21-Sep-12 15:53pm

Excel sheet cell has date as "11-30-11" but when i import it convert to "78608". So i want import those data with date as "11-30-11".

Vineet_2109 21-Sep-12 15:52pm

Excel sheet cell has date as "11-30-11" but when i import it convert to "78608". So i want import those data with date as "11-30-11".

4 solutions

Solution 2

This code will convert string in MM-dd-yy format

IFormatProvider yyyymmddFormat = new System.Globalization.CultureInfo("en-GB",false);
string x = myValues.GetValue(a, 2).ToString();
double d = double.Parse(x);
DateTime conv = DateTime.FromOADate(d);
string dtY = conv.ToString("MM-dd-yy",yyyymmddFormat);

Posted 21-Sep-12 23:42pm

Vineet_2109

Solution 3

for (int a = 2; a <= vertical; a++)
{
if (a.Date.HasValue)
worksheet.Cells[recordIndex, 15].Value = item.Date.Value.ToShortDateString();
}

it will works fine.

Posted 16-Oct-19 23:58pm

Member 14625927

Solution 5

I make more time on it so it is solution, may be help someone :
firt you get excel format by function

public static string GetExcelCellFormat(string cellFormat )//= "G")
      {
          // MessageBox.Show("ok date !" + cellFormat);
          //switch (cellFormat.Substring(0, 1))
          //{
             if (cellFormat.Contains("#"))
              {
              return "Number";

              }
              if (cellFormat.Contains("d"))
              {
              return "Date";

              }




                  return "General";

         // }
      }

and user fuction below tocheck is number ?

public static bool IsValidnumber(string number)
{


    Regex regex = new Regex(@"^[-+]?[0-9]*\.?[0-9]+$");
    return regex.IsMatch(number);


}

then you find in excel , if the value is number , you charge it to date string value by function

strdate = Convert.ToDateTime(rg.Value).ToString("dd/MM/yyyy");

detail like the example below:

public static cExcel.Worksheet GetworksheetObject(string FileName)
       {


           //       SetConnectionString();


           // tim sheetName
           cExcel.Application ExcelObj = new cExcel.Application();

           cExcel.Workbook theWorkbook = null;

           string strPath = FileName;// "MENTION PATH OF EXCEL FILE HERE";

           theWorkbook = ExcelObj.Workbooks.Open(strPath);
           cExcel.Sheets sheets = theWorkbook.Worksheets;

           cExcel.Worksheet worksheet = (cExcel.Worksheet)sheets.get_Item(1);

            cExcel.Range xlRange = worksheet.UsedRange;



            foreach (var item in xlRange)
            {
                cExcel.Range rg = (cExcel.Range)item;


                String format =  GetExcelCellFormat(rg.NumberFormat.ToString());

                     if (format == "Date")
                       {
                           string strdate;

                           if (Utils.IsValidnumber(rg.Value2.ToString()))
                           {

                               strdate = Convert.ToDateTime(rg.Value).ToString("dd/MM/yyyy");
                             //  MessageBox.Show("ok date !" + strdate);
                           }
                           else
                           {
                               strdate = rg.Value.ToString();
                           }


                     //   MessageBox.Show("ok date !" + strdate);
                       rg.Value = strdate;


                        // rg.set_Value( string , strdate);

                       }



            }



           return worksheet;

       }

Posted 23-Mar-24 20:04pm

namphongjst

Add a Solution

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)

Kenneth Haugland · Accepted Answer · 2012-09-21T10:23:00

Solution 1

In automated excel you need import DateTime as this:

C#

double d = double.Parse(b);
DateTime conv = DateTime.FromOADate(d);

Posted 21-Sep-12 10:23am

Kenneth Haugland

Comments

IRQ MISHELL 5-Mar-21 6:04am

Please tell me how to detect is current cell datetime data jet.