Export grid to ExcelML with custom formated GridBoundColumn

[Wagner]

Hi.

I´m trying to export a grid in the ExcelML format but the columns are generated dynamically and the DataFormatString is set dynamically too.

I can´t use the ExcelMLExportStylesCreated because in this moment I don´t know which format is the column.

Have RadGrid some method to set the excel export format during the column create? For example, I need to show a column in a specific date format!

Thanks.

[Daniel]

Hello Wagner,

I'm not sure why you can't use a dynamic custom format when exporting to ExcelML. Could you please provide some more information about the problem?
How do you decide which column will get a custom format on ColumnCreated and how do you choose the format itself?

Best regards,
Daniel
the Telerik team

[Wagner]

Hello Daniel.

I created a dynamic routine that create the columns. The parameters I get via the column name (column title, width, data format, etc) of the SQL.

So, I don´t use ColumnCreated because when I created the column I put the data format.

Example:

Dim gtcField As New GridBoundColumn

With dicColunas(intElemento).Child(intElemento2)
    gtcField.UniqueName = "gbc" & .ColumnName
    gtcField.HeaderText = .ColumnTitle
    gtcField.FilterTemplate = New clsGridViewTemplate(GridItemType.FilteringItem, .ColumnName, .ColumnTitle, .FilterType)
    gtcField.DataField = .ColumnName
    gtcField.Visible = .Visible
    gtcField.ItemStyle.HorizontalAlign = .HorizontalAlignment
    gtcField.HeaderStyle.Width = .Width

    If .Format <> "" Then
        gtcField.DataFormatString = "{0:" & .Format & "}"
    End If
End With

radGrid.MasterTableView.Columns.Add(gtcField)

This routine is in a loop, I simplified it to show you.

In ExcelMLExportStylesCreated I don´t know what is the Data Format for a Date Field column (for example) because I don´t have more this value.

Exists some manner to put the Excel Data Format String in the creation of the column?

[Daniel]

Hello Wagner,

A possible approach would be to traverse the columns (in the ExcelMLExportRowCreated event) and then assign your own number style according to the DataFormatString value:

Protected Sub RadGrid1_ExcelMLExportRowCreated(sender As Object, e As GridExportExcelMLRowCreatedArgs)
    If e.RowType = GridExportExcelMLRowType.DataRow Then
        For Each column As GridColumn In RadGrid1.MasterTableView.RenderColumns
            If TypeOf column Is GridBoundColumn Then
                Select Case TryCast(column, GridBoundColumn).DataFormatString
                    Case "{0:n}"
                        e.Row.Cells.GetCellByName(column.UniqueName).StyleValue = "NumberStyle"
                        Exit Select
                    Case "{0:p}"
                        e.Row.Cells.GetCellByName(column.UniqueName).StyleValue = "PercentStyle"
                        Exit Select
                    Case "{0:c}"
                        e.Row.Cells.GetCellByName(column.UniqueName).StyleValue = "CurrencyStyle"
                        Exit Select
                End Select
            End If
        Next
    End If
End Sub

Protected Sub RadGrid1_ExcelMLExportStylesCreated(sender As Object, e As Telerik.Web.UI.GridExcelBuilder.GridExportExcelMLStyleCreatedArgs)
    Dim numberStyle As New StyleElement("NumberStyle")
    numberStyle.NumberFormat.FormatType = NumberFormatType.General
    e.Styles.Add(numberStyle)

    Dim percentStyle As New StyleElement("PercentStyle")
    percentStyle.NumberFormat.FormatType = NumberFormatType.Percent
    e.Styles.Add(percentStyle)

    Dim currencyStyle As New StyleElement("CurrencyStyle")
    currencyStyle.NumberFormat.FormatType = NumberFormatType.Currency
    e.Styles.Add(currencyStyle)
End Sub

Regards,
Daniel
the Telerik team