在datatable和gridview中导入excel但是日期没有显示？ [英] Importing excel in datatable and then gridview but date does not shows ?

问题描述

大家好，

我有一个excel文件，我想在asp.net c＃中的Grdiview中导入。

我在gridview中得到了记录但 某些日期未显示在gridview中 。

我的代码是。

Hello everyone,
I have one excel file which i want to import in Grdiview in asp.net c#.
I got the records in gridview but some dates does not shows in gridview.
my code is.

if (FileUpload1.HasFile)
       {
           ddlParty.Enabled = true;
       }
       String strConnection = "ConnectionString";
       string connectionString = "";
       if (FileUpload1.HasFile)
       {
           string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
           string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
           string fileLocation = Server.MapPath("~/App_Data/" + fileName);
           FileUpload1.SaveAs(fileLocation);
           if (fileExtension == ".xls")
           {
               connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                 fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
           }
           else if (fileExtension == ".xlsx")
           {
               connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                 fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
           }
           OleDbConnection con = new OleDbConnection(connectionString);
           OleDbCommand cmd = new OleDbCommand();
           cmd.CommandType = System.Data.CommandType.Text;
           cmd.Connection = con;
           OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
           DataTable dtExcelRecords = new DataTable();
           con.Open();
           DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

           string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
           cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
           dAdapter.SelectCommand = cmd;
           dAdapter.Fill(dtExcelRecords);

           gvExcelFile.DataSource = dtExcelRecords;
           gvExcelFile.DataBind();
       }

这是我的Excel文件链接



所以请帮我解决我的问题。



我尝试了什么：



我尝试过代码并使用Format Cells更改excel文件中的日期格式这么多时间和这么多种类。但是没有得到解决方案。

Here is my Excel file LINK

So please help me to solve my problem.

What I have tried:

I have tried code and changed the date format in excel file with Format Cells so many time and with so many types. But didn't get solution.

推荐答案

你需要在你的设置中设置参数IMEX = 1连接字符串，如使用MS Excel（ xls / xlsx）使用MDAC和Oledb [ ^ ]以确保OLEDB不会忽略不是日期的字段。

You need to set the parameter "IMEX=1" in your connection string as described in Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] to ensure that OLEDB does not ignore fields that are not dates.

这篇关于在datatable和gridview中导入excel但是日期没有显示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！