[Solved] Formula Error during excel eport

[David]

I get the following error when exporting to an excel:

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.

One of the columns does contain a "-" in the first position, but it is not a formula.  Is there any way to export to excel and ignore formulas?

[Vasko]

Hi David,

The error occurs because Excel treats cell values starting with "=", "+", or "-" as formulas, even if you intend them as plain text. This is a common issue when exporting data that can include such characters, especially from dynamic data sources.

If you are using the Excel export (Xlsx format), you can set the cell format to text using the InfrastructureExporting event. This instructs Excel to treat the content as plain text, not as a formula.

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    RowCollection rows = e.ExportStructure.Tables[0].Rows;

    foreach (var row in rows)
    {
        foreach (var cell in row.Cells)
        {
            // Force all cells to be exported as text
            cell.Format = "@";
        }
    }
}

protected void RadGrid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    (sender as RadGrid).DataSource = OrdersTable();
}
private DataTable OrdersTable()
{
    DataTable dt = new DataTable();

    dt.Columns.Add(new DataColumn("OrderID", typeof(int)));
    dt.Columns.Add(new DataColumn("ShipName", typeof(string)));

    dt.PrimaryKey = new DataColumn[] { dt.Columns["OrderID"] };

    for (int i = 0; i < 2; i++)
    {
        int index = i + 1;

        DataRow row = dt.NewRow();

        row["OrderID"] = index;
        row["ShipName"] = "-Name " + index;

        dt.Rows.Add(row);
    }

    return dt;
}

<telerik:RadGrid ID="RadGrid1" runat="server" OnInfrastructureExporting="RadGrid1_InfrastructureExporting" OnNeedDataSource="RadGrid1_NeedDataSource">
    <ExportSettings>
        <Excel WorksheetName="Employees" Format="Xlsx" />
    </ExportSettings>
    <MasterTableView AutoGenerateColumns="False" DataKeyNames="OrderID" CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <telerik:GridBoundColumn DataField="OrderID" DataType="System.Int32"
                FilterControlAltText="Filter OrderID column" HeaderText="OrderID"
                ReadOnly="True" SortExpression="OrderID" UniqueName="OrderID">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="ShipName"
                FilterControlAltText="Filter ShipName column" HeaderText="ShipName"
                SortExpression="ShipName" UniqueName="ShipName">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

This will set all cells to text format, preventing Excel from interpreting any value as a formula.

Regards,
Vasko
Progress Telerik