ExportToExcel Format

5 posts, 0 answers
  1. Tab Alleman
    Tab Alleman avatar
    18 posts
    Member since:
    Apr 2010

    Posted 06 Sep 2010 Link to this post

    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?
  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 07 Sep 2010 Link to this post

    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.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Tab Alleman
    Tab Alleman avatar
    18 posts
    Member since:
    Apr 2010

    Posted 07 Sep 2010 Link to this post

    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%".
  5. Daniel
    Admin
    Daniel avatar
    4945 posts

    Posted 07 Sep 2010 Link to this post

    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
  6. Tab Alleman
    Tab Alleman avatar
    18 posts
    Member since:
    Apr 2010

    Posted 07 Sep 2010 Link to this post

    Yes!    That's exactly the sort of functionality I was hoping existed.   Thanks, Daniel!
Back to Top
UI for ASP.NET Ajax is Ready for VS 2017