Export to excel set all fields to text

[Suzy]

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

[Kostadin]

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

 

 

[Suzy]

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.

[Daniel]

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

 

 

[Matt]

Hi Daniel,

The solution above doesn't work for telephone numbers beginning with '+', e.g.,
[phone]
[phone]

Thanks for your help,
Matt

[Kostadin]

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

 

 

[Matt]

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

[Matt]

Attached screenshots

[Suzy]

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

​

[Suzy]

the attachment :-)

[Kostadin]

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

 

 

[Voodoo]

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.

[Suzy]

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

[Voodoo]

- Thank you

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

[Voodoo]

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;//

                }
            }
        }

[Scott]

I could not get the propsed "@" solution to work either.  However, I implemented this instead, which is working for me.  It replaces the (- or + or =) with a ([-] or [+] or [=]) value so that it will export.  It leaves numeric values intact (e.g., if someone had entered a "-9" it sill exports a negative value).

Protected Sub RadGrid_InfrastructureExporting(sender As Object, e As Telerik.Web.UI.GridInfrastructureExportingEventArgs)
    If e.ExportStructure.Tables.Count > 0 Then
        Dim rows As ExportInfrastructure.RowCollection = e.ExportStructure.Tables(0).Rows

        Dim cols As ExportInfrastructure.ColumnCollection = e.ExportStructure.Tables(0).Columns

        For Each row As ExportInfrastructure.Row In rows
            For Each cell As ExportInfrastructure.Cell In row.Cells
                If Text.RegularExpressions.Regex.IsMatch(cell.Value.ToString, "^(-|=|\+).") Then
                    Dim cellValue As String = cell.Value.ToString
                    Dim dbl As Double
                    Dim isNumeric As Boolean = Double.TryParse(cellValue, dbl)

                    If Not isNumeric Then
                        cell.Value = Regex.Replace(cellValue, "^(-|=|\+)(?=.)", "[$1]")
                    End If
                End If
            Next
        Next
    End If
End Sub

[michael]

Thank you Scott. This worked for me and is greatly appreciated.

[Atif]

Thanks :)