Filter list empty on custom cell type

Posted by: pauld on 30 May 2019, 8:15 am EST

    • Post Options:
    • Link

    Posted 30 May 2019, 8:15 am EST

    We have a custom DateTimeCellType that translates long values into dates (don’t ask, it’s an old system).

    When we use the custom celltype in our grid it works fine for displaying the date values and sorting the column, however, the filter dropdown for the custom column is empty (shows none of the cell values), except for the default “(All)” and “(Blanks)” items.

    How do we have to modify our custom celltype (code below) to work with filtering?

    Thanks. – Paul

    
    Public Class MCS_DateTimeCellType_ConvertLongToDate
        Inherits FarPoint.Win.Spread.CellType.DateTimeCellType
        Private WithEvents Editor As New FarPoint.Win.Spread.CellType.GeneralEditor
    
        Public Overrides Function GetEditorControl(ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal zoomFactor As Single) As System.Windows.Forms.Control
            ' Setting the Static property does NOT work for the first cell double-clicked on.
            ' It will still enter edit mode.
            ' However, as suggested by C1, setting the editor to NOTHING does the trick.
            '   Me.Static = True
            Return Nothing ' Me.Editor
        End Function
    
        Public Overrides Sub PaintCell(g As Graphics, r As Rectangle, appearance As FarPoint.Win.Spread.Appearance, value As Object, isSelected As Boolean, isLocked As Boolean, zoomFactor As Single)
            Dim brushBackground As Brush
    
            Dim dteValue As Date
    
            If value = 0 Then
                ' Draw a blank rectangle in the cell.
                brushBackground = New SolidBrush(appearance.BackColor)
                g.FillRectangle(brushBackground, r)
            Else
                dteValue = Mcs.ConvertDateFromLong(value)
                MyBase.PaintCell(g, r, appearance, dteValue, isSelected, isLocked, zoomFactor)
            End If
        End Sub
    End Class
    
  • Posted 30 May 2019, 8:56 pm EST

    Hello Paul,

    What I understoold from this, you have the “Long” type column in your database and you want to change it to Date format at runtime.

    Could you please share the example of long value that you are converting to date. Also, please share the code of following function:

    ConvertDateFromLong

    Actually, I am observing the different incorrect output at my end. "ArgumentOutOfRangeException: Ticks must be between DateTime.MinValue.Ticks and DateTime.MaxValue.Ticks.

    Parameter name: ticks

    " exception occurs at my end on clicking the Fiter icon. However, I am not able to reproduce the exact issue. Hence, could you please provide theabove information so that I can replicate the exact output.

    I am using the Spread v12.45.20191.0. Could you please share the exact version of Spread that you are using

    Here’s the sample code that i am using at my end:

    
    Public Class Form1
        Sub New()
    
            ' This call is required by the designer.
            InitializeComponent()
    
            ' Add any initialization after the InitializeComponent() call.
            FpSpread1.DataSource = GetTable()
            Dim ct As New MCS_DateTimeCellType_ConvertLongToDate()
            FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu
    
            FpSpread1.ActiveSheet.Columns(3).AllowAutoFilter = True
            FpSpread1.ActiveSheet.Columns(3).CellType = ct
        End Sub
    
        Private Shared Function GetTable() As DataTable
            Dim table As DataTable = New DataTable()
            table.Columns.Add("Dosage", GetType(Integer))
            table.Columns.Add("Drug", GetType(String))
            table.Columns.Add("Patient", GetType(String))
            table.Columns.Add("Date", GetType(Long))
            table.Rows.Add(25, "Indocin", "David", 10102018)
            table.Rows.Add(50, "Enebrel", "Sam", 10112018)
            table.Rows.Add(10, "Hydralazine", "Christoff", 11112018)
            table.Rows.Add(21, "Combivent", "Janet", 11102018)
            Return table
        End Function
    
    
    End Class
    Public Class MCS_DateTimeCellType_ConvertLongToDate
        Inherits FarPoint.Win.Spread.CellType.DateTimeCellType
        Private WithEvents Editor As New FarPoint.Win.Spread.CellType.GeneralEditor
    
        Public Overrides Function GetEditorControl(ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal zoomFactor As Single) As System.Windows.Forms.Control
            ' Setting the Static property does NOT work for the first cell double-clicked on.
            ' It will still enter edit mode.
            ' However, as suggested by C1, setting the editor to NOTHING does the trick.
            'Me.Static = True
            Return Nothing
        End Function
    
        Public Overrides Sub PaintCell(g As Graphics, r As Rectangle, appearance As FarPoint.Win.Spread.Appearance, value As Object, isSelected As Boolean, isLocked As Boolean, zoomFactor As Single)
            Dim brushBackground As Brush
    
            Dim dteValue As Date
    
            If value = 0 Then
                ' Draw a blank rectangle in the cell.
                brushBackground = New SolidBrush(appearance.BackColor)
                g.FillRectangle(brushBackground, r)
            Else
                'If Date.TryParseExact(value.ToString(), "ddMMyyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, dteValue) Then
    
                'End If
    
                dteValue = Date.ParseExact(value.ToString(), "ddMMyyyy", CultureInfo.InvariantCulture)
    
                MyBase.PaintCell(g, r, appearance, dteValue, isSelected, isLocked, zoomFactor)
                End If
        End Sub
    End Class
    
    

    Thanks and Regard,

    Mohit

  • Posted 31 May 2019, 9:02 am EST

    You can test this without the “ConvertDateFromLong” function. Just alter the code as follows …

    INSTEAD OF:

    dteValue = Mcs.ConvertDateFromLong(value)

    USE THIS:

    dteValue = #1/2/2003#

    But, if you want to use values from the datatable, add the function (below) and use these values:

    130942721, 137563167, 132253441, 131008030

    ConvertDateFromLong:

    
       Public Function ConvertDateFromLong(argLong As Integer) As Date
            Dim dteValue As Date
            Dim intDay As Integer
            Dim intMMDD As Integer
            Dim intMonth As Integer
            Dim intYear As Integer
    
            If IsNumeric(argLong) = False OrElse argLong = 0 Then
                Return #1/1/1900#
            End If
    
            intYear = argLong / 65536
            intMMDD = argLong Mod 65536
            intMonth = intMMDD / 256
            intDay = intMMDD Mod 256
    
            ' Verify that the date "part" values constitute a valid date.
            ' If not, return 1/1/1900 to the caller.
    
            If IsDate(intMonth & "/" & intDay & "/" & intYear) = False Then
                Return #1/1/1900#
            Else
                dteValue = DateSerial(intYear, intMonth, intDay)
            End If
    
            ' At this point we have a valid date in the "dteValue" variable.
    
            If dteValue < #1/1/1900# Then
                dteValue = #1/1/1900#
            ElseIf dteValue > #12/31/2099# Then
                dteValue = #12/31/2099#
            End If
    
            Return dteValue
    
        End Function
    
  • Posted 2 June 2019, 10:48 pm EST

    Hello,

    I am observing the ArgumentOutOfRangeException with your code also. I think this difference is due to a different version of Spread at my end. Could you please share the exact version that you are using?

    However, I have escalated the issue to our development team(273008) with current observation and will inform you once I get any information from them.

    Thanks.

  • Posted 3 June 2019, 3:37 am EST

    The Spread version for this project is 10.40.20174.0

  • Posted 3 June 2019, 5:02 am EST

    FYI: The same issue occurs using Spread 12.45.20181.0

    Also, in my testing today with version 10.xxx and 12.xxx, the filter list only shows the “(Blanks)” item.

  • Posted 3 June 2019, 9:08 pm EST

    Hello Pauld,

    Could you please modify the attached sample so that I am able to replicate the exact issue at my end.

    Also, as per developer, Like the excel, spread support dateTime to OADAte, the code like this:

    ```

    DateTime date = DateTime.Now;

    double d = date.ToOADate()

    And from OADate to DateTime, the code like this:
            ```
    
             DateTime date=    DateTime.FromOADate(dobuleValue);
    

    And the doublevalue should be between from DateTime.Minvalue and DateTime.MaxValue

    Thanks.

    WindowsApp16 (2).zip

  • Posted 5 June 2019, 8:06 am EST - Updated 30 September 2022, 4:51 am EST

    I can open the project but I cannot open the form in the project or run it.

    The error is attached.

  • Posted 6 June 2019, 12:21 am EST

    Hello,

    Please refer to the modified application.

    Thanks.

    WindowsApp1.zip

  • Posted 6 June 2019, 2:40 am EST - Updated 30 September 2022, 4:51 am EST

    The application will run but I get an exception when I click on the filter icon (Note: On my side, I have to change the dotnet version to 4.5.2; the highest we have):

    Also, I cannot view the form in the designer in Visual Studio; I get the error below:

  • Posted 6 June 2019, 3:28 am EST

    Hello,

    The application will run but I get an exception when I click on the filter icon

    Same behavior is observed at my end. That’s why I have asked to change the attached application so that I can observe the same issue(blank filter icon).

    Also, I cannot view the form in the designer in Visual Studio; I get the error below:

    Could you please just a drag and drop a FPSpread on the newly created application and used the code which is given in the attached application.

    Thanks.

  • Posted 7 June 2019, 3:00 am EST

    Add this line to the “New()” event of your form:

    FpSpread1_Sheet1.AutoFilterMode = AutoFilterMode.FilterGadget
    

    The application will now run and the filter list will only show the “Blanks” option.

    Interestingly, when hovering over the column header or the filter icon, the “PaintCell()” event in the custom date control fires; don’t know that it means anything.

  • Posted 9 June 2019, 9:14 pm EST

    Thanks, Paul for the information. I am able to replicate the exact behavior at my end. I have escalated the issue to our development team(273008) with current observation and will inform you once I get any information from them.

  • Posted 13 June 2019, 1:51 am EST

    Hello,

    As per developer reply:

    When get filter items spread will get value of cell and using the custom cellType of customer format this value to the text. In the custom cellType of customer only override painting logic so logic formating still return empty in this case. We can check by get the text of cell [0,3], the code like this

    Dim text As String = FpSpread1.ActiveSheet.Cells(0, 3).Text 'result is empty

    The format method in class MCS_DateTimeCellType_ConvertLongToDate should be change like this :

    Public Overrides Function Format(obj As Object) As String

    If Not obj Is Nothing AndAlso TypeOf obj Is Long Then
      obj = ConvertDateFromLong(obj)
    End If
    Return MyBase.Format(obj)
    

    End Function

    Note: It will only work when AutoFilterMode set to AutoFilterMode.FilterGadget

    Thanks.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels