在datatable和gridview中导入excel但是日期没有显示? [英] Importing excel in datatable and then gridview but date does not shows ?
本文介绍了在datatable和gridview中导入excel但是日期没有显示?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我有一个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屋!
查看全文