Completed
Last Updated: 24 Nov 2016 06:27 by ADMIN
ADMIN
Dess | Tech Support Engineer, Principal
Created on: 04 Nov 2016 06:50
Category: GridView
Type: Bug Report
0
FIX. RadGridView - Export.DisplayFormatType.Custom doesn't allow to specify numeric ExcelExportFormatString
To reproduce:

Sub New()

    InitializeComponent()

    Dim dt As New DataTable()
    dt.Columns.Add("Price", GetType(System.Double))
    dt.Columns.Add("Name", GetType(System.String))
    dt.Columns.Add("Nr", GetType(System.Double))
    For i As Integer = 0 To 49
        dt.Rows.Add(i, "Data" & i, i)
    Next
    With Me.RadGridView1
        .DataSource = dt
        .Columns("Price").FormatString = "{0:C2}"
        .Columns("Nr").FormatString = "{0:N1}"
        .Columns("Price").ExcelExportType = Export.DisplayFormatType.Currency
        '
        .AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill
    End With
    Me.RadGridView1.Columns("Price").ExcelExportType = Export.DisplayFormatType.Currency

    Me.RadGridView1.Columns("Nr").ExcelExportType = Export.DisplayFormatType.Custom
    Me.RadGridView1.Columns("Nr").ExcelExportFormatString = "{0:N1}"

    Me.RadGridView1.AutoSizeColumnsMode = Telerik.WinControls.UI.GridViewAutoSizeColumnsMode.Fill

End Sub

Private Sub RadButton1_Click(sender As Object, e As EventArgs) Handles RadButton1.Click
    Dim spreadStreamExport As New GridViewSpreadStreamExport(Me.RadGridView1)
    spreadStreamExport.ExportVisualSettings = True
    Dim fileName As String = "..\..\" + DateTime.Now.ToLongTimeString().Replace(":", "_").ToString() + ".xlsx"
  
    spreadStreamExport.RunExport(fileName, New SpreadStreamExportRenderer())
    Process.Start(fileName)
End Sub

Workaround:
Me.RadGridView1.Columns("Nr").ExcelExportType = Export.DisplayFormatType.Fixed

AddHandler spreadStreamExport.CellFormatting, AddressOf CellFormatting
Private Sub CellFormatting(sender As Object, e As SpreadStreamCellFormattingEventArgs)
        If e.ExportCell.ColumnIndex = 2 Then
            e.ExportCell.ExportFormat = "0.0"
        End If
    End Sub
1 comment
ADMIN
Ralitsa
Posted on: 24 Nov 2016 06:27
We are planning to include the fix in our next release – R1 2017. Meanwhile, we would like to clarify that MS Excel use little different number formats than standard numeric formats. In order to achieve the same formatting as ‘N1’ from decimal column, you need to set the ExcelExportFormatString property to ‘0.0’. Here is the code snippet demonstrating the set up: 

radGridView1.Columns["DecimalColumn"].FormatString = "{0:N1}";
radGridView1.Columns["DecimalColumn"].ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.Custom;
radGridView1.Columns["DecimalColumn"].ExcelExportFormatString = "0.0";

More examples with valid MS Excel number formats can be found on the following online sources: 
https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68 
http://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/features/format-codes