This is a migrated thread and some comments may be shown as answers.

Format Date Display, Keep Date Format

5 Answers 503 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Chris
Top achievements
Rank 1
Chris asked on 20 Jun 2013, 02:05 AM
I need to format a columns datetime Display, but keep it's value in both the RadGrid And Excel Export using Biff(or someway)

<telerik:GridBoundColumn DataField="Recorded Date2" <br>            FilterControlAltText="Filter Reported Date column" HeaderText="Reported Date" <br>            ReadOnly="True" SortExpression="Recorded Date" UniqueName="Recorded_Date" ItemStyle-HorizontalAlign="Right" DataType="System.DateTime" DataFormatString="{0:MM/dd/yy}"><br>        </telerik:GridBoundColumn>

I want my column to Display 06/18/13 in my Grid.

And I want it to Display 06/18/13 in my Excel Export.

However, I want my DateTime field in Excel to Retain it's DateTime attribute and be sortable.

Tuesday, June 18, 2013 8:00:00 AM 06/18/13
Tuesday, June 18, 2013 8:00:00 AM Tuesday, June 18, 2013 12:00:00 AM

This is my export before and after.  I modified the  Excel Cell Format of column B2 after export.  I want it to display Tuesday, June 18, 2013 8:00:00 AM instead of Tuesday, June 18, 2013 12:00:00 AM.

5 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 20 Jun 2013, 09:10 AM
Hi Chris,

To obtain date as 06/18/13 in Grid as well as Excel.Please try the code snippet.

ASPX:
<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true" >
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
     <CommandItemSettings ShowExportToExcelButton="true"  />
       <Columns>
          <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                SortExpression="ShippedDate" DataFormatString="{0:d}">
            </telerik:GridDateTimeColumn>          
       </Columns>
   </MasterTableView>
</telerik:RadGrid>

To obtain date time as Tuesday, June 18, 2013 8:00:00 AM. Please try this code snippet.

ASPX:
<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true" >
   <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
     <CommandItemSettings ShowExportToExcelButton="true"  />
       <Columns>
          <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                SortExpression="ShippedDate" DataFormatString="{0:f}" PickerType="DateTimePicker">
            </telerik:GridDateTimeColumn>          
       </Columns>
   </MasterTableView>
</telerik:RadGrid>

Thanks,
Princy
0
Chris
Top achievements
Rank 1
answered on 20 Jun 2013, 10:47 AM
Princy,
Is it possible to Show date 06/13/2013, but retain the full Date/Time info in Excel?
0
Jayesh Goyani
Top achievements
Rank 2
answered on 20 Jun 2013, 12:54 PM
Hello,

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        dynamic data = new[] {
            new { ShippedDate = DateTime.Now.AddDays(1)},
            new { ShippedDate = DateTime.Now.AddHours(1)},
            new { ShippedDate = DateTime.Now.AddMonths(1)},
            new { ShippedDate = DateTime.Now},
            new { ShippedDate = DateTime.Now}
        };
 
        RadGrid1.DataSource = data;
 
    }
 
    protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
    {
        if (e.CommandName == RadGrid.ExportToExcelCommandName)
        {
            foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
            {
                item["ShippedDate"].Text = Convert.ToDateTime(item.GetDataKeyValue("ShippedDate")).ToString();
            }
        }
    }
<telerik:RadGrid ID="RadGrid1" runat="server" AllowSorting="true"
      OnNeedDataSource="RadGrid1_NeedDataSource" onitemcommand="RadGrid1_ItemCommand">
      <MasterTableView AutoGenerateColumns="False" DataKeyNames="ShippedDate"  CommandItemDisplay="Top">
          <CommandItemSettings ShowExportToExcelButton="true" />
          <Columns>
              <telerik:GridDateTimeColumn HeaderText="ShippedDate" DataField="ShippedDate" UniqueName="ShippedDate"
                  SortExpression="ShippedDate" DataFormatString="{0:d}">
              </telerik:GridDateTimeColumn>
          </Columns>
      </MasterTableView>
  </telerik:RadGrid>



Thanks, Jayesh Goyani.
0
Chris
Top achievements
Rank 1
answered on 20 Jun 2013, 09:04 PM
<asp:Button OnClientClick="return gridExport()" ID="Button1" runat="server"
            Text="Export To Excel" UseSubmitBehavior="False" />
This is what I'm using this for my Export button.

How can I intercept this in the C#?
0
Princy
Top achievements
Rank 2
answered on 21 Jun 2013, 06:07 AM
Hi Chris,

PLease try the following code snippet.


ASPX:
<telerik:RadButton ID="rbtIGTExportToExcel"  Text="Export To Excel" OnClick="btnExcel_Click" runat="server" />

C#:
protected void btnExcel_Click(object sender, EventArgs e)
{
 foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
 {
   item["ShippedDate"].Text = Convert.ToDateTime(item["ShippedDate"].Text).ToString("dd-MM-yy hh:mm:ss");
 }        
   RadGrid1.MasterTableView.ExportToExcel();      
}

Thanks,
Princy
Tags
Grid
Asked by
Chris
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Chris
Top achievements
Rank 1
Jayesh Goyani
Top achievements
Rank 2
Share this question
or