当我在ASP.NET中导入Excel文件时，数据在gridview 2/31/200 12:00:00 AM突然变为这样 [英] When I import the Excel file in ASP.NET, the data suddenly becomes like this in the gridview 2/31/200 12:00:00 AM

问题描述

我正在运行时将Excel文件导入ASP.NET Gridview，某个列包含日期（2/31/2000），单元格格式为（* d / mm / yyyy）。



当我在ASP.NET中导入Excel文件时，数据在gridview中突然变成这样的数据



12/31 / 200 12:00:00 AM



我无法添加绑定文件，因为我在运行时加载数据我的数据加载代码是：

I'm importing an Excel file to ASP.NET Gridview at Runtime and a certain column includes dates (2/31/2000) with a cell format of (*d/mm/yyyy).

When I import the Excel file in ASP.NET, the data suddenly becomes like this in the gridview

12/31/200 12:00:00 AM

I can not add bound filed because I am loading data at run time my data loading code is:

Filename1 = FileUpload1.FileName.ToString();
       FileUpload1.SaveAs(MapPath("~/Temp/"+Filename1));
       if (File.Exists(MapPath("~/Temp/"+Filename1)))
       {
           string[] Arr = null;
           Arr = Filename1.Split('.');
           if (Arr.Length > 0)
           {
               if (Arr[Arr.Length - 1] == "xls")
                   sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + (MapPath("~/Temp/"+Filename1)).ToString() + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
           }
           if (Arr[Arr.Length - 1] == "xlsx")
           {
               sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (MapPath("~/Temp/"+Filename1)).ToString() + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
               //MessageBox.Show("excute constring");
           }
           if (Arr[Arr.Length - 1] == "xlsm")
           {
               sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + (MapPath("~/Temp/"+Filename1)).ToString() + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
               //MessageBox.Show("mecro constring");
           }
       }
       findsheetname1();
       FillGrid1();

推荐答案

当您选择数据时，您可以格式化日期时间：

有没有办法强制Microsoft.Jet.OLEDB从Excel中获取MM / DD / YYYY格式的日期列？ [ ^ ]

when you select data, you can format datetime:
Is there a way to force Microsoft.Jet.OLEDB to get date Columns in MM/DD/YYYY format from Excel?[^]

您好朋友我已经使用该代码解决了这个问题

Hi Friend I have solved this using that code

string date = "";
        DateTime newdate,d1;

        if (e.Row.RowType == DataControlRowType.DataRow)
            {
                for (int i = 1; i < e.Row.Cells.Count; i++)
                {
                    //BoundField field = (BoundField)((DataControlFieldCell)e.Row.Cells[i]).ContainingField;
                    date = e.Row.Cells[i].Text.ToString();
                    if(date.Length>7)
                    {
                        if (DateTime.TryParse(date, out newdate))
                        {
                            //DateTime.TryParseExact(date, "mm/dd/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out newdate);
                            d1 = newdate.Date;//DateTime.ParseExact(date, "MM/dd/yyyy", CultureInfo.InvariantCulture);

                            e.Row.Cells[i].Text = d1.Date.ToString("MM/dd/yyyy");//newdate.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture)
                        }
                    }

                }

            }

这篇关于当我在ASP.NET中导入Excel文件时，数据在gridview 2/31/200 12:00:00 AM突然变为这样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！