无法在数据集列GridView中格式化日期

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.

我正在从Excel工作表中读取数据并将其显示在数据网格视图中。在Excel中有一些日期列。当我从excel读取数据并将其绑定到dataGridView时。日期以“02”格式显示/ 02/2009 12:00:00 AM“但excel列中的实际数据格式为”2009年2月2日“。那么如何更改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}"

由于我绑定数据集中的数据,我没有任何模板列或绑定列集,所以我不知道在哪里设置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");

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

但是这个值没有被指定为“mm / dd / yyyy”它也会再次占用时间默认时间(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.

我只是将数据集分配给gridview。问题是数据集正在以mm / dd / yyyy hh:mm:ss AM格式读取日期列。我也无法更改数据集中的数据。

=============================================================

Finaly i got the answer from ScottE:

最后我得到了ScottE的答案:

we have to add the below code in the itemdatabound of the datagridview :

我们必须在datagridview的itemdatabound中添加以下代码:

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

} 

5 个解决方案

#1

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.

好的,试试这个,其中“Item”是列名(可能是多个),这是一个需要格式化的日期。这当然是vb.net,但你可以解决这个问题。我确信有更好的方法,但这有效。

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

#2

If you're binding it as a asp:BoundField you'll need to set htmlencode to false.

如果你将它绑定为asp:BoundField,你需要将htmlencode设置为false。

<asp:BoundField HtmlEncode="false" DataField="Blah" DataFormatString="{0:d}" />

#3

Date format you should define in your GridView column. For example:

您应在GridView列中定义的日期格式。例如:

<asp:GridView>
...
<asp:TemplateField>
 <ItemTemplate>
   <asp:Label ID="Label1" runat="server" Text='<%# Eval("Date", "{0:T}") %>'></asp:Label>
  </ItemTemplate>
...
</asp:GridView>

#4

Here is a solution using a custom control that derives from GridView:

这是一个使用从GridView派生的自定义控件的解决方案:

using System;
using System.Collections;
using System.Web.UI.WebControls;

namespace CustomControls {

  public class FormattedGridView : GridView {

    protected override ICollection CreateColumns(PagedDataSource dataSource, bool useDataSource) {

      // Call base method and return the collection as an ArrayList
      var columns = (ArrayList) base.CreateColumns(dataSource, useDataSource);

      for (var i = 0; i < columns.Count; i++) {

        var agf = columns[i] as AutoGeneratedField;

        if (agf != null && agf.DataType == typeof(DateTime)) {

          // create a new column because the AutoGeneratedField does not support
          // the modification of the DataFormatString property
          var bf = new BoundField();

          // copy some of the original properties
          bf.DataField = agf.DataField;
          bf.HeaderText = agf.HeaderText;
          bf.HtmlEncode = false;

          // set the format for the DateTime types
          bf.DataFormatString = "{0:T}";

          // replace the existing auto-generated colums
          columns[i] = bf;
        }

      }

      return columns;
    }
  }

}

#5

Finaly i got the answer from ScottE:

最后我得到了ScottE的答案:

we have to add the below code in the itemdatabound of the datagridview :

我们必须在datagridview的itemdatabound中添加以下代码:

protected void dg_ItemDataBound1(object sender, DataGridItemEventArgs e) {

protected void dg_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);
        }
    }

}

But the above code will check all the data that is bound to the datagrig.It will try to parse the data as datetime in the cell.If it is a valid datetime then it will convert the data into the format which we applied.

但是上面的代码将检查绑定到datagrig的所有数据。它将尝试将数据解析为单元格中的日期时间。如果它是有效的日期时间,那么它将数据转换为我们应用的格式。