Export to Excel - Using cell's value member

[Johan]

Hi,

Is it possible to export a cell's value member instead of the display member when exporting to excel using ExportToExcelML?

Thanks

[Richard Slade]

Hi Ryno,

You should be able to do this I believe with the ExcelCellFormatting event.
Please can you try the following

AddHandler export.ExcelCellFormatting, AddressOf Export_ExcelCellFormatting
Private Sub Export_ExcelCellFormatting(ByVal sender As Object, ByVal e As ExcelCellFormattingEventArgs)
    If e.GridColumnIndex = Me.RadGridView1.Columns("ColumnName").Index And e.GridRowIndex > -1 Then
        Dim sb As New System.Text.StringBuilder(Me.RadGridView1.Rows(e.GridRowIndex).Cells(e.GridColumnIndex).Value.ToString())
        e.ExcelCellElement.Render(sb)
    End If
End Sub

let me know if that helps. If not, i'll try and put a full sample together
Richard

[Richard Slade]

Hi Ryno,

I'm trying some various tests on this and it seems like this may not be the solution. I'll come back to you as soon as I can.
Richard

[Johan]

Ah, thanks Richard. I was just busy trying out your first suggestion :-)

[Richard Slade]

Hi,

Ok, only a slight update. I believe that this will work for you

Private Sub Export_ExcelCellFormatting(ByVal sender As Object, ByVal e As ExcelCellFormattingEventArgs)
    If e.GridColumnIndex = Me.RadGridView1.Columns("ColumnName").Index And e.GridRowIndex > -1 Then
        e.ExcelCellElement.Data.DataItem = Me.RadGridView1.Rows(e.GridRowIndex).Cells(e.GridColumnIndex).Value.ToString()
    End If
End Sub

Hope that helps
Richard

EDIT// Note you might need to handle looking for NULL values too.
R

[Johan]

Awesome, that is exactly what I was looking for.

Thanks Richard! 

[Richard Slade]

Excellent! Glad that worked for you.