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

ExportToExcel Format

4 Answers 120 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Tab Alleman
Top achievements
Rank 1
Tab Alleman asked on 06 Sep 2010, 09:54 PM
I am trying to control the format that excel chooses for my cells when I do an ExportToExcel() of my grid.

I have a column that holds percentage values.   In the column declaration, I have the following: 

DataFormatString

 

="{0:N1}%"  FooterAggregateFormatString="{0:N1}%"

 


But although both format strings are the same, in the excel sheet that is generated, the data rows are formatted with the "Number" category, and so they have values like "0.36", while the footer is formatted with the "Percentage" category and has values like "36.4%" (which is what I want).

I have tried at least a dozen things and I can't get the data rows to display in a percentage format.   Is there any way to control the format of data rows when you use ExportToExcel?

4 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 2
answered on 07 Sep 2010, 08:14 AM
Hello Tab,

Try the following method to display the cell value in percentage format when its exporting to excel.

ASPX:
<telerik:GridBoundColumn DataField="Percentage" HeaderText="Percentage" 
UniqueName="Percentage" DataFormatString="{0:N1}%" FooterAggregateFormatString="{0:N1}%" Aggregate="Sum" />

C#:
    bool isExcelExport = false;
 
    protected void RadGrid1_ItemCommand(object source, GridCommandEventArgs e)
    {
       if (e.CommandName == RadGrid.ExportToExcelCommandName)
        {
             isExcelExport = true;
        }
     }
 
protected void RadGrid1_PreRender(object sender, EventArgs e)
    {
        if (isExcelExport)
        {
              foreach (GridDataItem item in RadGrid1.MasterTableView.Items)
              {
                  string value = item["Percentage"].Text;
                  item["Percentage"].Text = value + "%";
              }
        }

Thanks,
Princy.
0
Tab Alleman
Top achievements
Rank 1
answered on 07 Sep 2010, 05:09 PM
Thanks for your reply.   Unfortunately it didn't work for me.   In Excel, the values for "Percentage" are still getting formatted with the "Number" category, and look like "0.35" instead of "35%".
0
Daniel
Telerik team
answered on 07 Sep 2010, 09:37 PM
Hello Tab,

I recommend that you set the desired format manually on the GridExportCellFormatting event:

protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
   if (e.FormattedColumn.UniqueName == "MyColumn")
       e.Cell.Style["mso-number-format"] = "Percent"; //or 0%
}

Word/Excel export (HTML-based)

Best regards,
Daniel
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Tab Alleman
Top achievements
Rank 1
answered on 07 Sep 2010, 10:15 PM
Yes!    That's exactly the sort of functionality I was hoping existed.   Thanks, Daniel!
Tags
Grid
Asked by
Tab Alleman
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Tab Alleman
Top achievements
Rank 1
Daniel
Telerik team
Share this question
or