Spread for Windows Forms

Custom Celltype, Export to Excel,Filter issues

Tagged: 

  •  echernyak said 3 years, 6 months ago:

    Below is a sample code which binds spread to the datasource and uses Custom Celltype to format dispay value. It works fine except two scenarios:

    1. When exporting to Excel it exports underlined data instead of display data (Using FarPoint.Excel.ExcelSaveFlags.SaveAsViewed)

    2. When applying Formula to this column (AllowRowFilter = True)  Filter list displays undelined values as well

    It’s possible that these issues are related to the fact that .GetText returns underlined (not display value).  Below is the code which populates data in the FPSpread control

     

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          Try
             PopulateSpread()

          Catch ex As Exception
            
          End Try
       End Sub
    Private Sub PopulateSpread()
          Try
             Dim dtTest As New DataTable
             With dtTest
                .Columns.Add("Code", GetType(String))
                .Rows.Add("Y")
             End With

             With FpSpread1.ActiveSheet
                .AutoGenerateColumns = False
                .DataAutoSizeColumns = False
                .DataAutoCellTypes = False

                .DataSource = dtTest.DefaultView

                .ColumnHeader.Cells(0, 0).Text = "Code"
                .Columns(0).CellType = New TranslateCdTextCell
                .BindDataColumn(0, dtTest.Columns("Code").ColumnName)
                .Columns(0).Visible = True
                .Columns(0).Width = 80

             End With

     

          Catch ex As Exception

          End Try
       End Sub
      
       Public Class TranslateCdTextCell
       Inherits FarPoint.Win.Spread.CellType.GeneralCellType

       Public Overrides Sub PaintCell(ByVal g As System.Drawing.Graphics, ByVal r As System.Drawing.Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal value As Object, ByVal isSelected As Boolean, ByVal isLocked As Boolean, ByVal zoomFactor As Single)
          Dim DislpayText As Object = Nothing
          DislpayText = ConvertCodeToText(value.ToString)
          MyBase.PaintCell(g, r, appearance, DislpayText, isSelected, isLocked, zoomFactor)
       End Sub
       Public Overrides Function GetEditorValue() As Object

          Return ConvertTextToCode(MyBase.GetEditorValue().ToString)

       End Function
       Public Overrides Sub SetEditorValue(ByVal value As Object)

          MyBase.SetEditorValue(ConvertCodeToText(value.ToString))

       End Sub

       Private Function ConvertCodeToText(ByVal value As String) As String
          If value = "Y" Then
             Return "Yes"
          Else
             Return "No"
          End If
       End Function

       Private Function ConvertTextToCode(ByVal value As String) As String
          If value = "Yes" Then
             Return "Y"
          Else
             Return "N"
          End If
       End Function
    End Class

  •  scotts FP said 3 years, 5 months ago:

    Hello,

    It looks as if you worked with Paul on this issue in a different thread. Did you get your questions answered?

  •  echernyak said 3 years, 5 months ago:

    Hi Scott:

    Issue #2 with Custom Filtering was resolved with Paul’s help. Issue #1 still exists for Excel Export. When using Custom CellType with databinding Excel file contains underlined values from the data source (vs displayed valus).

    Thank you for your help

  •  scotts FP said 3 years, 5 months ago:

    Hello,

    This is the correct behavior of the Spread (to write out the data directly from the DataModel to Excel). We have an enhancement request entered for a future version of Spread to add an ExcelSaveFlag to call the Format method of the CellType to format the data before writing to Excel. Then, you would be able to override the Format method of the custom celltype to convert the data. This is not in the current version.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.