Click here to Skip to main content
11,638,758 members (72,642 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Excel WinForm
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
Edited 21-Sep-12 9:55am
v2
Comments
Vineet_2109 at 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 at 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".
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

In automated excel you need import DateTime as this:
double d = double.Parse(b);
DateTime conv = DateTime.FromOADate(d);
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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);
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 314
1 stibee 233
2 Suvendu Shekhar Giri 230
3 Richard Deeming 190
4 Mika Wendelius 178
0 OriginalGriff 9,061
1 Sergey Alexandrovich Kryukov 8,773
2 Mika Wendelius 7,027
3 Suvendu Shekhar Giri 2,494
4 F-ES Sitecore 2,438


Advertise | Privacy | Mobile
Web03 | 2.8.150728.1 | Last Updated 22 Sep 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100