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

Grid column value changed to 0 when exporting to Excel

1 Answer 52 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Meng
Top achievements
Rank 1
Meng asked on 18 Apr 2017, 08:42 PM

Grid Column value changed to 0 when exporting Grid to Excel. Please see the following demo code, the value of "Freight" column changed to 0 when clicking the Export button, but as soon as I change the DataFormatString="{0}%" to DataFormatString="{0}", everything works fine when exporting. But I do need to display the number as percentage (no decimals) in my senario. Any help would be much appreciated!

RadGridExport.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="RadGridExport.aspx.cs" Inherits="RadGridExport" %>
<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
<!DOCTYPE html>
 
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <telerik:RadScriptManager ID="RadScriptManager1" runat="server">
            <Scripts>
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js" />
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js" />
                <asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js" />
            </Scripts>
        </telerik:RadScriptManager>
        <script type="text/javascript">
            //Put your JavaScript code here.
        </script>
        <telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">
        </telerik:RadAjaxManager>
        <telerik:RadGrid ID="RadGrid1" runat="server" AllowPaging="True" CellSpacing="0" ShowFooter="True"
            GridLines="None" Width="800px" OnNeedDataSource="RadGrid1_NeedDataSource"
            OnItemCommand="RadGrid1_ItemCommand" ExportSettings-Excel-DefaultCellAlignment="Center">
            <ExportSettings ExportOnlyData="true"></ExportSettings>
            <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
                <CommandItemSettings ShowExportToExcelButton="true" />
                <Columns>
                    <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                        FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
                        ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
                    </telerik:GridBoundColumn>
                    <telerik:GridDateTimeColumn DataField="OrderDate" DataType="System.DateTime"
                        FilterControlAltText="Filter OrderDate column" HeaderText="OrderDate"
                        SortExpression="OrderDate" UniqueName="OrderDate">
                    </telerik:GridDateTimeColumn>
                    <telerik:GridNumericColumn DataField="Freight" DataType="System.Int32"
                        FilterControlAltText="Filter Freight column" DataFormatString="{0}%" HeaderText="Freight" Aggregate="Avg"
                        SortExpression="Freight" UniqueName="Freight">
                    </telerik:GridNumericColumn>
                    <telerik:GridBoundColumn DataField="ShipName"
                        FilterControlAltText="Filter ShipName column" HeaderText="ShipName"
                        SortExpression="ShipName" UniqueName="ShipName">
                    </telerik:GridBoundColumn>
                    <telerik:GridBoundColumn DataField="ShipCountry"
                        FilterControlAltText="Filter ShipCountry column" HeaderText="ShipCountry"
                        SortExpression="ShipCountry" UniqueName="ShipCountry">
                    </telerik:GridBoundColumn>
                </Columns>
            </MasterTableView>
        </telerik:RadGrid>
    </form>
</body>
</html>

 

RadGridExport.aspx.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Telerik.Web.UI;
 
public partial class RadGridExport : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
    {
        RadGrid1.DataSource = GetGridSource();
    }
    private DataTable GetGridSource()
    {
        DataTable dataTable = new DataTable();
 
        DataColumn column = new DataColumn();
        column.DataType = Type.GetType("System.Int32");
        column.ColumnName = "OrderID";
        dataTable.Columns.Add(column);
 
        column = new DataColumn();
        column.DataType = Type.GetType("System.DateTime");
        column.ColumnName = "OrderDate";
        dataTable.Columns.Add(column);
 
        column = new DataColumn();
        column.DataType = Type.GetType("System.Int32");
        column.ColumnName = "Freight";
        dataTable.Columns.Add(column);
 
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "ShipName";
        dataTable.Columns.Add(column);
 
        column = new DataColumn();
        column.DataType = Type.GetType("System.String");
        column.ColumnName = "ShipCountry";
        dataTable.Columns.Add(column);
 
        DataColumn[] PrimaryKeyColumns = new DataColumn[1];
        PrimaryKeyColumns[0] = dataTable.Columns["OrderID"];
        dataTable.PrimaryKey = PrimaryKeyColumns;
 
        for (int i = 0; i <= 80; i++)
        {
            DataRow row = dataTable.NewRow();
            row["OrderID"] = i + 1;
            row["OrderDate"] = DateTime.Now;
            row["Freight"] = i + 1;
            row["ShipName"] = "Name " + (i + 1);
            row["ShipCountry"] = "Country " + (i + 1);
 
            dataTable.Rows.Add(row);
        }
 
        return dataTable;
    }
    protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
    {
        RadGrid1.ExportSettings.FileName = "Success - " + DateTime.Now.ToLongTimeString();
    }
}

1 Answer, 1 is accepted

Sort by
0
Meng
Top achievements
Rank 1
answered on 21 Apr 2017, 05:20 PM
I also tried to change the DataType to System.Decimal for "Freight" field. It displays for example "2%" in the grid, and "0.02" in the exported excel. Any idea how to fix this? Please help!
Tags
Grid
Asked by
Meng
Top achievements
Rank 1
Answers by
Meng
Top achievements
Rank 1
Share this question
or