Export to Excel Format

[Mike]

In my grid the column is formatted correctly as an integer.  However, when an export to excel is performed the column shows up as a decimal.  How can I format the column to export the same as it is displayed in the grid?

Thanks!

[Daniel]

Hi George,

This is discussed in the following thread:
Export to Excel - prventing long numbers from scientific notation

Kind regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

Daniel,

How do I impliment that with this?

public void ConfigureExport()
{
    RadGrid1.ExportSettings.ExportOnlyData = true;
    RadGrid1.ExportSettings.IgnorePaging = true;
    RadGrid1.ExportSettings.OpenInNewWindow = true;
}

protected void ButtonExcel_Click(object sender, System.EventArgs e)
{
    ConfigureExport();
    RadGrid1.MasterTableView.ExportToExcel();
}

[Daniel]

Hello George,

In order to make an example for you we would need to know the following:

- which export format is used - ExcelML or HTML (I suppose HTML)
- RadGrid columns type - autogenerated or declarative
- which data type is your column as it comes from DataSource

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

This is all I am doing to export the grid:

<asp:ImageButton ID="ImageButtonExcel" OnClick="ButtonExcel_Click" ImageUrl="~/images/office/xls.gif" AlternateText="Export to Excel" runat="server" />

 

protected void ButtonExcel_Click(object sender, System.EventArgs e)
 {
  ConfigureExport();
  RadGrid1.MasterTableView.ExportToExcel();
 }
public void ConfigureExport()
 {
  RadGrid1.ExportSettings.ExportOnlyData = true;
  RadGrid1.ExportSettings.IgnorePaging = true;
  RadGrid1.ExportSettings.OpenInNewWindow = true;
 }

-which export format is used - ExcelML or HTML (I suppose HTML)
    HTML
- RadGrid columns type - autogenerated or declarative
     declarative
- which data type is your column as it comes from DataSource
    int

[Daniel]

Hello George,

I tried to reproduce your behavior using the details you provided but unfortunately to no avail. Please take a look at the attached example.

We believe the best way would be to send us your project attached to a regular support ticket.

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

<asp:ImageButton ID="ImageButtonExcel"
                 OnClick="ButtonExcel_Click"
                 ImageUrl="~/images/office/xls.gif"
                 AlternateText="Export to Excel"
                 runat="server" />
<telerik:RadToolTip ID="RadToolTip1"
                    TargetControlID="ImageButtonExcel"
                    RelativeTo="Element"
                    Text="Click here to <strong>OPEN</strong> Grid in <strong>EXCEL</strong> " Position="BottomLeft" runat="server" />

<telerik:RadGrid ID="RadGrid1"
                 AllowMultiRowSelection="True"
                 AllowSorting="True"
                 AllowPaging="False"
                 AllowNaturalSort="True"
                 AutoGenerateColumns="False"
                 AlternatingItemStyle-HorizontalAlign="Center"
                 BackColor="#ffffff"
                 EnableEmbeddedSkins="False"
                 GridLines="None"
                 GroupingEnabled="True"
                 Height="425px"
                 HeaderStyle-HorizontalAlign="Center"
                 ItemStyle-HorizontalAlign="Center"
                 OnSortCommand="RadGrid1_SortCommand"
                 OnNeedDataSource="RadGrid1_NeedDataSource"
                 OnItemDataBound="RadGrid1_ItemDataBound"
                 OnItemCommand="RadGrid1_ItemCommand"
                 ShowFooter="True"
                 ShowHeader="True"
                 ShowGroupPanel="True"
                 ShowStatusBar="True"
                 Skin="MySkin"
                 Width="100%"
                 runat="server">

<MasterTableView DataKeyNames="ID,NAME"
                 ClientDataKeyNames="ID"
                 AllowMultiColumnSorting="True"
                 TableLayout="Fixed"
                 Width="99%">
<Columns>
<telerik:GridClientSelectColumn UniqueName="ClientSelectColumn"
                                HeaderStyle-Width="25" />
<telerik:GridBoundColumn DataField="ID"
                         Visible="False" />
<telerik:GridBoundColumn SortExpression="NAME"
                         HeaderText="Priority"
                         HeaderButtonType="TextButton"
                         HeaderStyle-Width="50"
                         DataField="NAME" />
<telerik:GridBoundColumn SortExpression="DAYS"
                         HeaderText="Days"
                         HeaderButtonType="TextButton"
                         HeaderStyle-Width="50"
                         DataField="DAYS"
                         DataFormatString="{0:F0}" />
</Columns>
</MasterTableView>
<ClientSettings AllowColumnsReorder="True" AllowDragToGroup="True" AllowRowHide="True" EnablePostBackOnRowClick="False" ReorderColumnsOnClient="True">
<Resizing AllowColumnResize="True" AllowRowResize="False" EnableRealTimeResize="True" ResizeGridOnColumnResize="True" />
<Scrolling AllowScroll="True" UseStaticHeaders="True" SaveScrollPosition="True" />
<Selecting AllowRowSelect="True" />
</ClientSettings>
</telerik:RadGrid>

The Days column is exporting like this  7.00

[Daniel]

Hello George,

Please test the attached example which is based on the code you provided.
I'm looking forward to your feedback.

Regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

Not sure how I would implement your code, this is how I am binding to the Datable.

UserListBLL userList = new UserListBLL();
DataTable dt = new DataTable();

userList.StartDate = defaultStartDate;
userList.EndDate = defaultEndDate;
userList.Manager = manager;
userList.Status = pricingstatus;
dt = userList.GetUsers;

[Daniel]

Hello Mike,

Please take a look at the following code (part of the example):

aspx

<telerik:GridBoundColumn SortExpression="DAYS" HeaderText="Days DEC" HeaderButtonType="TextButton"
    HeaderStyle-Width="50" DataField="DAYS" DataType="System.Decimal" DataFormatString="{0:F0}" />
<telerik:GridBoundColumn SortExpression="DAYS" HeaderText="Days INT" HeaderButtonType="TextButton"
    HeaderStyle-Width="50" DataField="DAYS" DataType="System.Int32" DataFormatString="{0:F0}" />

code-behind

myDataTable.Columns.Add("ID", typeof(int));
myDataTable.Columns.Add("NAME", typeof(string));
myDataTable.Columns.Add("DAYS", typeof(float));

myDataTable.Rows.Add(1, "Name 1", 2.1);
myDataTable.Rows.Add(2, "Name 2", 4.4);
myDataTable.Rows.Add(3, "Name 3", 60.11);

What I was trying to show you was that DataFormatString behavior depends on the data type set in your columns.

Short conclusion:
  

	Original values	DataSet format	Column type {0:F0}	Exported as	Visible as		Original values	DataSet format	Column type (no DFS)	Exported as	Visible as
	2	int	Int32	2	2		2	int	Int32	2	2
	60	int	Int32	60	60		60	int	Int32	60	60
	0	int	Int32	0	0		0	int	Int32	0	0
	Original values	DataSet format	Column type {0:F0}	Exported as	Visible as		Original values	DataSet format	Column type (no DFS)	Exported as	Visible as
	2	int	Decimal	2	2.00		2	int	Decimal	2	2.00
	60	int	Decimal	60	60.00		60	int	Decimal	60	60.00
	0	int	Decimal	0	0.00		0	int	Decimal	0	0.00
	Original values	DataSet format	Column type {0:F0}	Exported as	Visible as		Original values	DataSet format	Column type (no DFS)	Exported as	Visible as
	2.1	float	Int32	2	2		2.1	float	Int32	2.1	2
	60.11	float	Int32	60	60		60.11	float	Int32	60.11	60
	0.1	float	Int32	0	0		0.1	float	Int32	0.1	0
	Original values	DataSet format	Column type {0:F0}	Exported as	Visible as		Original values	DataSet format	Column type (no DFS)	Exported as	Visible as
	2.1	float	Decimal	2	2.00		2.1	float	Decimal	2.1	2.10
	60.11	float	Decimal	60	60.00		60.11	float	Decimal	60.11	60.11
	0.1	float	Decimal	0	0.00		0.1	float	Decimal	0.1	0.10

As I said before it would be best if you isolate this issue in a simple, working example. Thus we will be able to give you the needed straight-to-the-point answer. I attached some screenshots showing how to submit a support ticket.

Best regards,
Daniel
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Mike]

This is all I needed to add, boy I feel stupid.

DataType="System.Int32"