radgrid export to excel formating

[Ruby]

I have a radgrid that is exporting grid to excel format. It is working correctly.. But the business is looking to get excel file cells in "text" format so that it can be entered in some software as "text" easily, not sure how to convert excel cells to text format. 
I am using Biff format 
Any help will be appreciated. Thanks

[Princy]

Hi Ruby,

You can control the text properties in ExcelExportCellFormatting event of the radgrid.Please try the following code snippet to obtain text format for column that are in different format.

ASPX:

<telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource"
    AutoGenerateColumns="false" OnExcelExportCellFormatting="RadGrid1_ExcelExportCellFormatting">
    <ExportSettings ExportOnlyData="true" Excel-Format="Biff">
    </ExportSettings>
    <MasterTableView CommandItemDisplay="Top" CommandItemSettings-ShowExportToExcelButton="true">
        <Columns>
            <telerik:GridBoundColumn DataField="Column1" HeaderText="Column1">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Column2" HeaderText="Column2">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Column3" HeaderText="Column">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

C#:

protected void RadGrid1_NeedDataSource(object sender, Telerik.Web.UI.GridNeedDataSourceEventArgs e)
{
    RadGrid1.DataSource = GeneratingTable();
}
private DataTable GeneratingTable()
{
    DataTable table = new DataTable();
    table.Columns.Add("Column1");
    table.Columns.Add("Column2");
    table.Columns.Add("Column3");

    for (int i = 0; i < 20; i++)
    {
        table.Rows.Add("Col1Row" + i, "Col2Row" + i, "10/1");
    }
    return table;
}

protected void RadGrid1_ExcelExportCellFormatting(object source, ExcelExportCellFormattingEventArgs e)
{
    if (e.FormattedColumn.UniqueName == "Column3")
    {
        e.Cell.Style["mso-number-format"] = @"\@"; //To export in text format
    }
}

Thanks,
Princy

[Ruby]

Hi Princy,

I tried your example above but ExcelExportCellFormatting event is not firing with excel biff format. So I tried using BiffExporting event and it is firing properly but not sure how to access cells using Biff format. My columns number in radgrid varies depending on values selected from the dropdown. So i am trying to convert the whole excel file to text format so that it can be used by the end user easily.

I am trying to do something like this , but it is not working as desired

 

 

 

   

Protected Sub radgrid_BiffExporting(ByVal sender As Object, ByVal e As GridBiffExportingEventArgs) Handles rgBCQReport.BiffExporting
'e.cells.Style("mso-number-format") = "\@"

Dim culture = System.Globalization.CultureInfo.GetCultureInfo("de-DE")

For j As Integer = 1 To e.ExportStructure.Tables(0).Columns.Count

For i As Integer = 1 To e.ExportStructure.Tables(0).Rows.Count

Dim cell As Xls.Cell = e.ExportStructure.Tables(0).Cells(j, i)


cell.Format = "\@"

Next

Next
 End Sub

  

   

  

   

  

Please help.

 

[Ruby]

Or please provide me some code which applies to all the cells in excel file which prevents using for loop

[Princy]

Hi Ruby,

 Here is an another approach to access each cell in the RadGrid in BiffExporting without many loops. Hope the below code snippet helps you.

VB:

Protected Sub RadGrid1_BiffExporting(sender As Object, e As GridBiffExportingEventArgs)
    For Each cell As xls.Cell In e.ExportStructure.Tables(0).Cells
        'Accessing each cell
        If cell.RowIndex > 1 Then     'To avoid the header row
            cell.Format = [String].Format("\@")  'Format the cell
        End If
    Next
End Sub

Thanks,
Princy.

[Ruby]

Thanks for your help Princy. The code you have provided worked for me.