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

Export to Excel Format

10 Answers 258 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Mike
Top achievements
Rank 1
Mike asked on 08 Sep 2008, 01:48 PM
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!

10 Answers, 1 is accepted

Sort by
0
Daniel
Telerik team
answered on 09 Sep 2008, 05:21 AM
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.
0
Mike
Top achievements
Rank 1
answered on 09 Sep 2008, 12:47 PM
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();  
0
Daniel
Telerik team
answered on 09 Sep 2008, 02:21 PM
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.
0
Mike
Top achievements
Rank 1
answered on 09 Sep 2008, 03:07 PM
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

0
Daniel
Telerik team
answered on 12 Sep 2008, 03:57 PM
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.
0
Mike
Top achievements
Rank 1
answered on 12 Sep 2008, 06:07 PM
<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

0
Daniel
Telerik team
answered on 15 Sep 2008, 06:02 PM
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.
0
Mike
Top achievements
Rank 1
answered on 15 Sep 2008, 06:24 PM
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
0
Accepted
Daniel
Telerik team
answered on 18 Sep 2008, 09:04 AM
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.
0
Mike
Top achievements
Rank 1
answered on 19 Sep 2008, 08:36 PM
This is all I needed to add, boy I feel stupid.

DataType="System.Int32"

Tags
Grid
Asked by
Mike
Top achievements
Rank 1
Answers by
Daniel
Telerik team
Mike
Top achievements
Rank 1
Share this question
or