Export to excel set all fields to text

15 posts, 0 answers
  1. Suzy
    Suzy avatar
    66 posts
    Member since:
    Nov 2014

    Posted 18 Nov 2014 Link to this post

    I have a RadGrid with AutoGenerateColumns set to true.  I also set up the grid so it can be exported to excel using format Xlsx. 
    There are several columns in the grid with data.  One field in a column start with a minus sign (-) which makes the export go into error with following message :

    We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it.\n\nNot trying to type a formula? When the first character is an equal (=) or minus (-) sign, RadSpreadsheet thinks it is a formula. For example, when you type =1+1 the cell shows 2.

    The other fields in that column are all text.  Is it possible to set the RadGrid (or the Export settings) that all columns are TEXT without defining all columns seperate? 

    Thank you for any response.

    Suzy
  2. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 21 Nov 2014 Link to this post

    Hello Suzy,

    I am afraid I was unable to replicate the issue on my side. I prepared a small sample and attached it to this thread. Could you please give it a try and let me know how it differs from your real setup?

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Suzy
    Suzy avatar
    66 posts
    Member since:
    Nov 2014

    Posted 15 Dec 2014 in reply to Kostadin Link to this post

    I think you misunderstood me.  The field is a alfanumeriek field with data beginning with '-' or '='.
    for example
    John
    Linda
    - David
    Sue
    = Ellen

    if you try to export this, the system fails.
  5. Daniel
    Admin
    Daniel avatar
    4943 posts

    Posted 18 Dec 2014 Link to this post

    Hello Suzy,

    The solution would be to force the text format to these cells.
    For example:
    protected void RadGrid1_InfrastructureExporting(object sender, Telerik.Web.UI.GridInfrastructureExportingEventArgs e)
    {
        var rows = e.ExportStructure.Tables[0].Rows;
        var rowCount = rows.Count;
        var desiredColumnIndex = 1;
        for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
        {
            rows[rowIndex].Cells[desiredColumnIndex, rowIndex].Format = "@";
        }
    }

    This should work properly for autogenerated columns.

    Regards,
    Daniel
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  6. Matt
    Matt avatar
    5 posts
    Member since:
    Dec 2006

    Posted 09 Feb 2015 Link to this post

    Hi Daniel,

    The solution above doesn't work for telephone numbers beginning with '+', e.g.,
    +1 415-123-4567
    +1 (415) 123-4567

    Thanks for your help,
    Matt
  7. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 12 Feb 2015 Link to this post

    Hello Matt,

    I prepared a small runnable sample where I used the same values as yours and on my side no exception is thrown. Could you please try the attached sample and let me know how it differs from your real scenario? I would appreciate if you can replicate the issue in a small runnable sample in order to investigate it further.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  8. Matt
    Matt avatar
    5 posts
    Member since:
    Dec 2006

    Posted 14 Feb 2015 in reply to Kostadin Link to this post

    Hi Kostadin,

    Thanks for your response. I tried the attached sample, and I'm still getting the error. Please see the attached screenshots. I don't why it doesn't work on my side.

    Matt
  9. Matt
    Matt avatar
    5 posts
    Member since:
    Dec 2006

    Posted 14 Feb 2015 in reply to Matt Link to this post

    Attached screenshots
  10. Suzy
    Suzy avatar
    66 posts
    Member since:
    Nov 2014

    Posted 17 Feb 2015 Link to this post

    Hi
    I finally had the time to try your proposal but it does not do the trick.
    Still getting the same error 'We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it.\n\nNot trying to type a formula? When the first character is an equal (=) or minus (-) sign, RadSpreadsheet thinks it is a formula. For example, when you type =1+1 the cell shows 2. '

    In the screenshot you can see how the data looks like in the RadGrid.  The problem is in the column 'Serial Number'. You see that the second  row has a value beginning with minus (-).
    I added the OnInfrastructureExporting="grdEnquiry_InfrastructureExporting  to my grid and the code in the event :
    protected void grdEnquiry_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
    {
        var rows = e.ExportStructure.Tables[0].Rows;
     
        var rowCount = rows.Count;
     
        var  desiredColumnIndex = 8;
        for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
        {
            rows[rowIndex].Cells[desiredColumnIndex, rowIndex].Format = "@";
        }
     
     
    }

    but still the same message.

    Please advice.
    Suzy

  11. Suzy
    Suzy avatar
    66 posts
    Member since:
    Nov 2014

    Posted 17 Feb 2015 in reply to Suzy Link to this post

    the attachment :-)
  12. Kostadin
    Admin
    Kostadin avatar
    1708 posts

    Posted 18 Feb 2015 Link to this post

    Hi Suzy and Matt,

    Could you please let me know which version of the controls you are using? I was able to replicate the issue in another project with Q3 2014 version, but our developers has fixed it in Q1 2015 BETA. I would recommend you to download the BETA version from your account and check out whether the issue is resolved.

    Regards,
    Kostadin
    Telerik
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  13. Voodoo
    Voodoo avatar
    4 posts
    Member since:
    Aug 2007

    Posted 14 Sep 2015 in reply to Kostadin Link to this post

    Did this get resolved?
    I’m having the same issue and used the method in the post but it doesn’t work. The only difference is we use auto generated columns.
    Please help.
  14. Suzy
    Suzy avatar
    66 posts
    Member since:
    Nov 2014

    Posted 15 Sep 2015 in reply to Voodoo Link to this post

    Hi Voodoo,

     my priority at work changed and therefor I had no time to test more with this.

    Sorry, but as far as I know it is not solved yet.

    Kind regards

    Suzy

  15. Voodoo
    Voodoo avatar
    4 posts
    Member since:
    Aug 2007

    Posted 15 Sep 2015 in reply to Suzy Link to this post

    - Thank you

     I'll start another thread and reply if it ever gets fixed.

  16. Voodoo
    Voodoo avatar
    4 posts
    Member since:
    Aug 2007

    Posted 16 Sep 2015 in reply to Suzy Link to this post

    Hi Suzy,

    I got it to work, in may case i needed all the fields to be a string because my grid uses different data sets plus any field could cause this. Hope this helps you  in the future.

    protected void RadGridUploadTables_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
            {
     
                var rows = e.ExportStructure.Tables[0].Rows;
                var rowCount = rows.Count;
                for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++)
                {
                    for (int i = 1; i <= rows[rowIndex].Cells.Count; i++)
                    {
                        rows[rowIndex].Cells[i, rowIndex].Format = "@";
                        rows[rowIndex].Cells[i, rowIndex].Value = rows[rowIndex].Cells[i, rowIndex].Text; //"Row" + rowIndex + " Column" + i;//
     
                    }
                }
            }

Back to Top
UI for ASP.NET Ajax is Ready for VS 2017