This is a migrated thread and some comments may be shown as answers.

radgrid export to excel formating

5 Answers 377 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Ruby
Top achievements
Rank 1
Ruby asked on 03 Jul 2013, 04:46 PM
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

5 Answers, 1 is accepted

Sort by
0
Princy
Top achievements
Rank 1
answered on 04 Jul 2013, 05:58 AM
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
0
Ruby
Top achievements
Rank 1
answered on 05 Jul 2013, 02:06 PM
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.

 

0
Ruby
Top achievements
Rank 1
answered on 05 Jul 2013, 07:53 PM
Or please provide me some code which applies to all the cells in excel file which prevents using for loop
0
Accepted
Princy
Top achievements
Rank 1
answered on 08 Jul 2013, 06:00 AM
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.
0
Ruby
Top achievements
Rank 1
answered on 08 Jul 2013, 03:00 PM
Thanks for your help Princy. The code you have provided worked for me.
Tags
Grid
Asked by
Ruby
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 1
Ruby
Top achievements
Rank 1
Share this question
or