导入具有单元格格式的Excel数据(即日期不是即将到来的日期格式) [英] Import Excel data with Cell Formatting (i.e. Date is not coming date format )

问题描述

Excel工作表单元格的日期为"11-30-11"，但在我导入时将其转换为"78608".因此，我想导入日期为"11-30-11"的那些数据.

您可以在第一个for循环部分之后找到该行

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

推荐答案

在自动化excel中，您需要这样导入DateTime:

In automated excel you need import DateTime as this:

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

此代码将转换MM-dd-yy格式的字符串

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

这篇关于导入具有单元格格式的Excel数据(即日期不是即将到来的日期格式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！