Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Cells / Managing Data on a Sheet / Creating Data Type for Custom Objects
In This Topic
    Creating Data Type for Custom Objects
    In This Topic

    Spread.NET provides support to add Data types for custom objects just like Excel. Users can access information about a wide range of subjects and areas within the Spread interface. This feature will allow users to access all the required information with an easy card pop up and extract key data from relevant objects effortlessly.

    When a data type is added to a cell, a glyph is added to it indicating the data type. So, when the glyph is clicked, a data card pops up displaying the properties of the object. By default, data types are disabled in Spread. To enable them, CalcFeatures enumeration should be enabled.

    You can add a data type to a cell using the IRichValue interface, which represents a rich cell value. For this purpose, you can implement a class which implements IRichValue interface and set the instance to the worksheet. Here, we have used the Employee class with the built-in class RichValue<T>.

    In Spread, DataTable and DataView are built-in supported. Users can create the IRichValue object that allows to extract data from columns of DataTable or DataView.

     

    C#
    Copy Code
    IRichValue richValue = BuiltInRichValue.FromDataTable(tbl, defaultColumnName, showHeaders);
    
    VB
    Copy Code
    Dim richValue As IRichValue = BuiltInRichValue.FromDataTable(tbl, defaultColumnName, showHeaders)
    

    In the use-case image shown below, the class Employee implements the IMedia interface. Employee class implements IMedia interface to customize the inline rich value icon of cell. This class has fields like ID, First Name, Last Name, Designation, Department, Gender, Age and Year of Joining. To add data type in the worksheet, you can set objects in cell values using the Value property of IRange interface, and set formulas in cells using the Formula property of IRange interface. Here, we have set values in cells from B2 : B8 and C2 : C3.

    In case a normal cell containing a data type is activated, the insert button is displayed at the top-right of cell and users can add any of its related property (field) to the nearest empty cell at the right. The cell where the property is filled contains the related formula. On the other hand, if a table cell containing a data type is activated, the insert button is displayed at the top-right of table and users can add any of its related property (field). Once the user selects a property, a new column is added towards the right. The newly added column has the name of the added property and fills relevant values of the property in the column. It contains the related formula.

    In order to use formula to access properties of a custom .NET data object we need to use the following syntax. 
    B2.Property1
    or
    B2.[The Property 1]

    Image depicting data pop-up card in cells of SpreadJS, a feature like in Excel for different datatypes.

    The code below implements the use-case depicting adding data types:

    C#
    Copy Code
    private void Form1_Load(object sender, EventArgs e)
            {
                // Enable CalcFeatures to All
                fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
                fpSpread1.ActiveSheet.Columns[1, 10].Width = 120;
                GrapeCity.CalcEngine.RichValue<Employee> andreySmith = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 61371,
                    FirstName = "Andrey",
                    LastName = "Smith",
                    Designation = "Tech Lead",
                    Department = "IT",
                    Gender = "M",
                    Age = 34,
                    YearOfJoining = 2013
                });
                GrapeCity.CalcEngine.RichValue<Employee> jackShang = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 37123,
                    FirstName = "Jack",
                    LastName = "Shang",
                    Designation = "Manager",
                    Department = "Sales",
                    Gender = "M",
                    Age = 34,
                    YearOfJoining = 2017
                });
                GrapeCity.CalcEngine.RichValue<Employee> joanaJordan = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 37564,
                    FirstName = "Joana",
                    LastName = "Jordan",
                    Designation = "GraphicsDesigner",
                    Department = "IT",
                    Gender = "F",
                    Age = 28,
                    YearOfJoining = 2016
                });
                GrapeCity.CalcEngine.RichValue<Employee> williamSmith = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 28034,
                    FirstName = "William",
                    LastName = "Smith",
                    Designation = "AVP",
                    Department = "HR",
                    Gender = "M",
                    Age = 42,
                    YearOfJoining = 2012
                });
                GrapeCity.CalcEngine.RichValue<Employee> selinaWing = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 65134,
                    FirstName = "Selina",
                    LastName = "Wing",
                    Designation = "Technical Engineer",
                    Department = "IT",
                    Gender = "F",
                    Age = 34,
                    YearOfJoining = 2019
                });
                GrapeCity.CalcEngine.RichValue<Employee> melinaJackson = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 45978,
                    FirstName = "Melina",
                    LastName = "Jackson",
                    Designation = "Senior S/W Engineer",
                    Department = "IT",
                    Gender = "F",
                    Age = 34,
                    YearOfJoining = 2014
                });
                GrapeCity.CalcEngine.RichValue<Employee> puffDuplacy = new GrapeCity.CalcEngine.RichValue<Employee>(new Employee()
                {
                    ID = 32700,
                    FirstName = "Puff",
                    LastName = "Duplacy",
                    Designation = "Team Lead",
                    Department = "IT",
                    Gender = "M",
                    Age = 36,
                    YearOfJoining = 2012
                });
    
                // set object in cells value
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B2"].Value = andreySmith;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B3"].Value = jackShang;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B4"].Value = joanaJordan;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B5"].Value = williamSmith;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B6"].Value = selinaWing;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B7"].Value = melinaJackson;
                fpSpread1.AsWorkbook().ActiveSheet.Cells["B8"].Value = puffDuplacy;
                // set formulas in cells
                fpSpread1.AsWorkbook().ActiveSheet.Cells["C2"].Formula = "B2.[Designation]";
                fpSpread1.AsWorkbook().ActiveSheet.Cells["C3"].Formula = "B3.Designation";
    
            }
        }
        [System.Reflection.DefaultMember("FirstName")]
        public class Employee : IMedia
        {
            public int ID { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }
            public string Designation { get; set; }
            public string Department { get; set; }
            public string Gender { get; set; }
            public int Age { get; set; }
            public int YearOfJoining { get; set; }
            string IMedia.ContentType => "image/png";
            Stream IMedia.Data
            {
                get
                {
                    return typeof(FpSpread).Assembly.GetManifestResourceStream("FarPoint.Win.Spread.SpreadResources.EditShape.png");
                }
            }
    }
    
    VB
    Copy Code
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
        fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All
        fpSpread1.ActiveSheet.Columns(1, 10).Width = 120
        Dim andreySmith As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 61371,
            .FirstName = "Andrey",
            .LastName = "Smith",
            .Designation = "Tech Lead",
            .Department = "IT",
            .Gender = "M",
            .Age = 34,
            .YearOfJoining = 2013
        })
        Dim jackShang As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 37123,
            .FirstName = "Jack",
            .LastName = "Shang",
            .Designation = "Manager",
            .Department = "Sales",
            .Gender = "M",
            .Age = 34,
            .YearOfJoining = 2017
        })
        Dim joanaJordan As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 37564,
            .FirstName = "Joana",
            .LastName = "Jordan",
            .Designation = "GraphicsDesigner",
            .Department = "IT",
            .Gender = "F",
            .Age = 28,
            .YearOfJoining = 2016
        })
        Dim williamSmith As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 28034,
            .FirstName = "William",
            .LastName = "Smith",
            .Designation = "AVP",
            .Department = "HR",
            .Gender = "M",
            .Age = 42,
            .YearOfJoining = 2012
        })
        Dim selinaWing As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 65134,
            .FirstName = "Selina",
            .LastName = "Wing",
            .Designation = "Technical Engineer",
            .Department = "IT",
            .Gender = "F",
            .Age = 34,
            .YearOfJoining = 2019
        })
        Dim melinaJackson As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 45978,
            .FirstName = "Melina",
            .LastName = "Jackson",
            .Designation = "Senior S/W Engineer",
            .Department = "IT",
            .Gender = "F",
            .Age = 34,
            .YearOfJoining = 2014
        })
        Dim puffDuplacy As GrapeCity.CalcEngine.RichValue(Of Employee) = New GrapeCity.CalcEngine.RichValue(Of Employee)(New Employee() With {
            .ID = 32700,
            .FirstName = "Puff",
            .LastName = "Duplacy",
            .Designation = "Team Lead",
            .Department = "IT",
            .Gender = "M",
            .Age = 36,
            .YearOfJoining = 2012
        })
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B2").Value = andreySmith
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B3").Value = jackShang
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B4").Value = joanaJordan
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B5").Value = williamSmith
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B6").Value = selinaWing
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B7").Value = melinaJackson
        fpSpread1.AsWorkbook().ActiveSheet.Cells("B8").Value = puffDuplacy
        fpSpread1.AsWorkbook().ActiveSheet.Cells("C2").Formula = "B2.[Designation]"
        fpSpread1.AsWorkbook().ActiveSheet.Cells("C3").Formula = "B3.Designation"
    End Sub
    

    Customizing DataCard PopUp

    Users can customize the datacard popup using FpSpread.ResolveCustomControl event. Here, the datacard has been customized to show text "Designation" and on clicking the datacard, it populates the empty cell with the Designation of the Employee.

    Image depicting a customized card popup.

    The code below implements the use-case customizing datacard popup:

    C#
    Copy Code
     private void FpSpread1_ResolveCustomControl(object sender, ResolveCustomControlEventArgs e)
            {
                if (e.Type == CustomControlType.RichValueInsert)
                {
                    Button button = new Button();
                    button.Click += (object sender1, EventArgs buttonEventArgs) =>
                    {
                        e.Command.Execute("Designation", e.Spread);
                    };
                    button.Text = "Designation";
                    e.Control = button;
                }
                else
                {
                    e.Control = e.CreateDefaultControl();
                }
            }
    
    VB
    Copy Code
    Private Sub FpSpread1_ResolveCustomControl(ByVal sender As Object, ByVal e As ResolveCustomControlEventArgs)
        If e.Type = CustomControlType.RichValueInsert Then
            Dim button As Button = New Button()
            button.Click += Function(ByVal sender1 As Object, ByVal buttonEventArgs As EventArgs)
                                e.Command.Execute("Designation", e.Spread)
                            End Function
            button.Text = "Designation"
            e.Control = button
        Else
            e.Control = e.CreateDefaultControl()
        End If
    End Sub