无法在数据列的GridView格式化日期 [英] Unable to format date in dataset column,GridView
问题描述
我从一个Excel工作表中读取数据，并在数据gridview.There显示它是在excel.So一些日期列当我读从Excel中的数据并将其绑定到dataGridView.The日期显示在格式02/02/2009 12:00:00 AM，但在Excel中列的实际数据的格式为2/2/2009。那么如何改变在DataGridView的日期格式。
由于我是从数据集绑定数据我没有任何模板列或绑定列设置，所以我不知道在哪里设置HtmlEn code =FALSEDataFormatString ={0：T】
有没有办法做到this.Please帮助我。
请找到低于code样本。
字符串OleDbConnection的=供应商= Microsoft.Jet.OLEDB.4.0;数据源=+ FileUpload1.PostedFile.FileName +;扩展属性= \\Excel中8.0; HDR =是; IMEX = 1 \\;字符串strSheetName =工作表Sheet1
OleDbConnection的OleDbConnection的;
OleDbCommand的OleDbCommand的;
OleDbDataAdapter的oledbAdapter;OleDbCommand的=新的OleDbCommand（）;
oledbAdapter =新OleDbDataAdapter的（）;
数据集dsExcellData =新的DataSet（）;OleDbConnection的=新的OleDbConnection（OleDbConnection的）;
oledbConnection.Open（）;
oledbCommand.Connection = OleDbConnection的;
oledbCommand.CommandText =[+ strSheetName +$]，从选择*; //我想找到这个工作表名称
oledbAdapter.SelectCommand = OleDbCommand的;
oledbAdapter.Fill（dsExcellData）;oledbConnection.Close（）;GridView1.DataSource = dsExcellData.Tables [0];GridView1.DataBind（）;
=============================================== ===========
我试过
dsExcellData.Tables [0] .Rows [行数] [date_column]。的ToString（）] = dsExcellData.Tables [0] .Rows [行数] [date_column]。的ToString（）]。的ToString（ D）;
但价值是没有得到指定为MM / DD / YYYY这也抽空默认时间试（MM / DD / YYYY HH：MM：SS AM）。
=============================================== ==============
我只是分配数据设置为gridview.The问题就是数据读取格式MM / DD / YYYY HH日期列：MM：SS AM.I我无法改变数据在数据集中也
=============================================== ==============
Finaly我得到的答案从SCOTTE：
我们必须添加下面的code在DataGridView的ItemDataBound：
保护无效dgValidatedData_ItemDataBound1（对象发件人，DataGridItemEventArgs E）
{ 的for（int i = 0; I＆LT; = e.Item.Cells.Count - 1;我++）
{
System.DateTime的cellDate =默认（System.DateTime的）;
如果（System.DateTime.TryParse（e.Item.Cells [I]。文本，出cellDate））
{
e.Item.Cells [I]。文本=的String.Format（{0：D}，cellDate）;
}
}}
好吧，试试这个，其中物品是列名（可以是多个），这是需要格式化的日期。这当然是vb.net，但你可以排序了这一点。我敢肯定有一个更好的办法，但这种工作的。
保护小组gv_RowDataBound（BYVAL发件人为对象，BYVAL E上System.Web.UI.WebControls.GridViewRowEventArgs）
如果e.Row.RowType = DataControlRowType.DataRow然后
对于我作为整数= 0〜e.Row.Cells.Count - 1
如果gv.HeaderRow.Cells（I）。文本=项目然后
e.Row.Cells（我）。文本=的String.Format（{0：D}，CTYPE（e.Row.Cells（I）。文本，日期））
万一
下一个
万一
结束小组
或者，如果你不知道哪些列将有日期，下面的工作还有：
保护小组gv_RowDataBound（BYVAL发件人为对象，BYVAL E上System.Web.UI.WebControls.GridViewRowEventArgs）
如果e.Row.RowType = DataControlRowType.DataRow然后
对于我作为整数= 0〜e.Row.Cells.Count - 1
昏暗cellDate随着日期
如果Date.TryParse（e.Row.Cells（ⅰ）。文本，cellDate）然后
e.Row.Cells（我）。文本=的String.Format（{0：D}，cellDate）
万一
下一个
万一
结束小组
I am reading data from an excel sheet and displaying it in a data gridview.There are some date columns in the excel.So when i read the data from the excel and bind it to the dataGridView.The date is displayed in the format "02/02/2009 12:00:00 AM" but the actual data in the excel column is in the format "2/2/2009".So how to change the date format in the datagridview.
Since i am binding the data from the dataset i dont have any template columns or bound column set so i dont know where to set the HtmlEncode="False" DataFormatString = "{0:T}"
Is there any way to do this.Please help me.
Please find the below code sample.
string OleDbConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+ FileUpload1.PostedFile.FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
string strSheetName = "Sheet1";
OleDbConnection oledbConnection;
OleDbCommand oledbCommand;
OleDbDataAdapter oledbAdapter;
oledbCommand = new OleDbCommand();
oledbAdapter = new OleDbDataAdapter();
DataSet dsExcellData = new DataSet();
oledbConnection = new OleDbConnection(OleDbConnection);
oledbConnection.Open();
oledbCommand.Connection = oledbConnection;
oledbCommand.CommandText = "Select * from [" + strSheetName + "$]"; // i want to find this sheet name
oledbAdapter.SelectCommand = oledbCommand;
oledbAdapter.Fill(dsExcellData);
oledbConnection.Close();
GridView1.DataSource = dsExcellData.Tables[0];
GridView1.DataBind();
========================================================== I tried the
dsExcellData.Tables[0].Rows[rowcount]["date_column"].ToString()] = dsExcellData.Tables[0].Rows[rowcount]["date_column"].ToString()].ToString("d");
but the value is not getting assigned as "mm/dd/yyyy" It is also taking the time default time again (mm/dd/yyyy hh:mm:ss AM).
=============================================================
I am just assigning the data set to the gridview.The problem is the dataset is reading the date column in the format mm/dd/yyyy hh:mm:ss AM.I am unable to change the data in the dataset also.
=============================================================
Finaly i got the answer from ScottE:
we have to add the below code in the itemdatabound of the datagridview :
protected void dgValidatedData_ItemDataBound1(object sender, DataGridItemEventArgs e)
{
for (int i = 0; i <= e.Item.Cells.Count - 1; i++)
{
System.DateTime cellDate = default(System.DateTime);
if (System.DateTime.TryParse(e.Item.Cells[i].Text, out cellDate))
{
e.Item.Cells[i].Text = string.Format("{0:d}", cellDate);
}
}
}
Ok, try this, where "Item" is the column name (could be multiple) that is a date that needs formatting. This is of course vb.net, but you can sort that out. I'm sure there's a better way, but this works.
Protected Sub gv_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
For i As Integer = 0 To e.Row.Cells.Count - 1
If gv.HeaderRow.Cells(i).Text = "Item" Then
e.Row.Cells(i).Text = String.Format("{0:d}", CType(e.Row.Cells(i).Text, Date))
End If
Next
End If
End Sub
Or, if you don't know what columns will have dates, the following will work as well:
Protected Sub gv_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
For i As Integer = 0 To e.Row.Cells.Count - 1
Dim cellDate As Date
If Date.TryParse(e.Row.Cells(i).Text, cellDate) Then
e.Row.Cells(i).Text = String.Format("{0:d}", cellDate)
End If
Next
End If
End Sub
这篇关于无法在数据列的GridView格式化日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
