Spread Windows Forms 18 Product Documentation

Creating Data Type for Custom Objects

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.

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.

IRichValue richValue = BuiltInRichValue.FromDataTable(tbl, defaultColumnName, showHeaders);

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:

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");
            }
        }
}

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

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:

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();
            }
        }

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

Customizing DataCard PopUp

Creating Data Type for Custom Objects

Customizing DataCard PopUp