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]
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.
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
|
|