Introducing Spreadsheet Rich Cell Data Types for .NET Objects

Introducing Spreadsheet Rich Cell Data Types for .NET Objects

The Spread.NET v14 release includes a new Cell Data Type feature that supports creating Data Types for custom objects. This feature allows any cell to contain a rich set of structured data that can be called remotely in the Spread instance. Like Excel, the Data Type can be applied to cells or as a data table and easily identifiable by a set glyph icon. Users can click on the icon and reveal a list of fields and corresponding values, all depending on the data. There could be numerous field/value pairs that you can work with and see.

Note, when using this feature, you must enable CalcFeatures to FieldValue like so:

// Enable CalcFeatures to FieldValue
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.FieldValue;

Or you can enable all-new calculation engine features like so:

// Enable CalcFeatures to All
fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;

End-users can access a custom object’s information through the Spread interface with an easy pop-up Data Card. The pop-up Data Card is also customizable; below, we demonstrate this by loading an image from a URL to be displayed on the Data Card, allowing users to see and add the image to the Spread instance. Another useful behavior is the Insert Button used to insert the selected data into the cell or column on the right quickly. Developers can define the data that is available when using the insert button as well. We have broken down the features included with this new Cell Data Type and different use cases when implementing Cell Data Types.

As seen here, users can display the Data Card to see the structured data fields by clicking on the glyph icon (1). Users can use the Insert Button to insert the selected data field (2). Another way to insert data is by using the Data Card pop-up (3). In the gif below, when inserting data using the Data Card, an image field with custom code is set to display the cells' image cell type. We go into more detail on this custom code later on in this blog.

How to Use the Glyph and Data Card

Users can apply their glyph to indicate data types in the Spread instance. With an image as an Embedded Resource, the class gets and returns the image as a stream to display in the Spread instance. For example, below, we add the camera.png glyph to indicate the data type cells.

// Add glyph that will be used to indicate data type cells
string IMedia.ContentType => "image/png";
Stream IMedia.Data
{
  get
  {
    return GetType().Assembly.GetManifestResourceStream("CellDataType_Movies.Camera.png");
  }
}

Note our project's name is CellDataType_Movies; this is where the camera.png file is added. Also, be sure to set the included glyph file properties to the Build Action to be an Embedded Resource:

Now, when the Spread instance runs, the glyph is displayed. When it is clicked, a data card pops up to reveal the object's available fields and values. Note, Spread supports DataTable and DataView, allowing data to be extracted from columns of the DataTable or cells' DataView. Spread supports setting Cell Data types to a DataTable and or DataView that enables data to be removed from columns of DataTable or cells' DataView. When applying the data type to a DataView and inserting data using the Data Card or Insert Button, the selected data field will be inserted to the nearest empty cell at the right. The cell that now displays the chosen data field contains a related formula (1). When the data type is applied to a DataTable, and the user is inserting data using the Data Card or Insert Button, a new table column of data will be added to the table's right that fills with relevant values of the property that was selected (2).

Inserting data in both scenarios are depicted here:

Developers can also customize the DataCard pop-up and display an image when the property is selected and insert that image into the Spread instance. For example, below, we are loading Movie posters from URLs. To accomplish this, we must first create an image using the System.Drawing.Image class and create a new WebClient instance using the System.Net.WebClient class to take an images' URL and stream the image from the URL to the Spread instance:

public static Image GetPosterFromUrl(string imageUrl)
        {
            Image image = null;
            using (WebClient webClient = new WebClient())
            {
                ServicePointManager.Expect100Continue = true;
                ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

                byte[] data = webClient.DownloadData(imageUrl);

                using (MemoryStream mem = new MemoryStream(data))
                {
                    image = Image.FromStream(mem);
                }
            }
            return image;
        }

Next, define a new class member called Poster that will be an Image:

public class Movie : IMedia
    {
        public Image Poster { get; set; }
        …
    }

Then when creating the custom objects, the Poster member will use the Movie class to create an image that is streamed from the URL using GetPosterFromUrl:

// Create custom objects using Movie class for the top 10 highest box office movies
GrapeCity.CalcEngine.RichValue<Movie> AvengersEndgame = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/0/0d/Avengers_Endgame_poster.jpg"),
                Title = "Avengers: Endgame",
                Rank = 1,
                BoxOffice = "$2.798 billion",
                Year = 2019,
                Director = "Anthony Russo & Joe Russo",
                Producer = "Kevin Feige",
                RunTime = "181 minutes",
            });

Next, create a custom function to create a ListBox and get the cell's information to display the image:

// ListBox_Click to load display the image in a Spread cell
        private void ListBox_Click(object sender, EventArgs e)
        {
            ListBox list = sender as ListBox;
            if (list.SelectedIndex == 0)
            {
                SheetView sheet = fpSpread1.ActiveSheet;
                int row = sheet.ActiveRowIndex;
                int activeColumn = sheet.ActiveColumnIndex;
                int column;
                for (column = sheet.ActiveColumnIndex + 1; sheet.GetValue(row, column) != null; column++) ;
                ImageCellType imgCellType = new ImageCellType();
                imgCellType.Style = FarPoint.Win.RenderStyle.StretchAndScale;
                IWorksheet ws = fpSpread1.AsWorkbook().ActiveSheet;
                while (ws.Cells[row, activeColumn].Table != null)
                    sheet.Cells[row++, column].CellType = imgCellType;
            }
            list.Click -= ListBox_Click;
        }

Finally, we will add a click event listen to display the image in the spread instance by attaching the custom function to the FpSpread.ResolveCusomerControl event handler to FpSpread1 like so:

private void FpSpread1_ResolveCustomControl(object sender, ResolveCustomControlEventArgs e)
       {
               e.Control = e.CreateDefaultControl();
               ListBox listBox = e.Control.Controls[0] as ListBox;
               Image img = null;
               if (listBox != null)
               {
                   RichValue<Movie> richMovie = e.Spread.ActiveSheet.ActiveCell.Value as RichValue<Movie>;
                   object value = null;
                   if (richMovie != null && richMovie.TryGetFieldValue("Poster", out value))
                   {
                       img = value as Image;
                   }
                   if (img != null)
                   {
                       //listBox.Items.RemoveAt(0);
                       listBox.Top += img.Height;
                       e.Control.Height += img.Height;
                       PictureBox pictureBox = new PictureBox();
                       e.Control.Controls.Add(pictureBox);
                       pictureBox.Image = img;
                       pictureBox.SetBounds(0, 0, img.Width, img.Height);
                       listBox.Click += ListBox_Click;

                      }
           }
       }

Be sure to reference the ResolveCustomControl after applying the code logic:

// Attach Event Handler
this.fpSpread1.ResolveCustomControl += new System.EventHandler<FarPoint.Win.Spread.ResolveCustomControlEventArgs>(this.FpSpread1_ResolveCustomControl);

After applying this code logic, the image from the URL will be displayed on the data pop-up card, and when selected from the datacard, the image will show in the Spread Control like so:

How to Use the Insert Button

Spread. NET's Cell Data Types feature includes an Insert Button displayed on cells with a data type added to them. End-users can use this insert button to add a related property to the nearest empty cell to its right. A formula is then used to add and display the selected data. When applying a table to those cells and using the Insert Button, Spread will add a column with the added property's name and fill in the column's relevant values using a formula. Users can also use the built-in keyboard shortcut Ctrl + Shift + F5 to invoke the ShowCard action for the cell data type or click the cell's glyph. Developers can also apply a formula in the Spread instance to access properties of the customer .NET data object without using the insert button.

When using formulas, be sure to follow this syntax: B2.Property1

If your properties’ name has spaces, be sure to encapsulate the property name in brackets like so: B2.[The Property 1]

Note, you can see the formulas that are applied to cells using a formulaTextBox that can be added during design time and attached to the Spread instance like so:

Users can customize the data in the insert button's pop-up card using the FpSpread.ResolveCustomControlEvent. Then create a button, and on the button click display the desired data. For this example, inside the Spread event, the data card is customized to show the text "Box Office." When clicking this insert button, the cell on the outer right will populate with the BoxOffice of the Movie:

       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("BoxOffice", e.Spread);
               };
               button.Text = "Box Office";
               e.Control = button;
           }
           else
           {
               e.Control = e.CreateDefaultControl();
               ListBox listBox = e.Control.Controls[0] as ListBox;
               Image img = null;
               if (listBox != null)
               {
                   RichValue<Movie> richMovie = e.Spread.ActiveSheet.ActiveCell.Value as RichValue<Movie>;
                   object value = null;
                   if (richMovie != null && richMovie.TryGetFieldValue("Poster", out value))
                   {
                       img = value as Image;
                   }
                   if (img != null)
                   {
                       //listBox.Items.RemoveAt(0);
                       listBox.Top += img.Height;
                       e.Control.Height += img.Height;
                       PictureBox pictureBox = new PictureBox();
                       e.Control.Controls.Add(pictureBox);
                       pictureBox.Image = img;
                       pictureBox.SetBounds(0, 0, img.Width, img.Height);
                       listBox.Click += ListBox_Click;
                   }
               }
           }
       }

Note, be sure to attach the event handler to the Spread instance:

// Attach Event Handler
this.fpSpread1.ResolveCustomControl += new System.EventHandler<FarPoint.Win.Spread.ResolveCustomControlEventArgs>(this.FpSpread1_ResolveCustomControl);

After applying this customization, the insert button will only show the Box Office button:

Use Cases: Implementing Cell Data Types

Below we cover some use cases that go over implementing cell data types. These use cases include implementing them to a single cell and data table, how to use them, and ways to implement custom objects from arrays into the Spread instance.

When creating a cell data type, you will follow these three main steps:

• Define the class for the cell data type and specify the properties that you want to show for the object.
• Create an instance of the class initialized with the appropriate properties and use RichValue < T > to create the IRichValue objects using your class instances.
• Use cell formulas to reference the cell data type object fields directly to get the value or perform some calculation with it, using a standard cell reference to the cell containing the cell data type object and ".[field name]" syntax, e.g., "B2.BoxOffice."
• For more control and faster implementation that does not require reflection, implement IRichValue on the cell data type class directly, and specify the fields through the interface.

Adding Cell Data Types to a Single Cell or Datatable

Cell data types allow users to implement and interact with custom objects by adding the new data types to a cell or DataTable. The section below details how to create and add the data types to cells and as a data table.

To accomplish this, follow these steps:

1. Define the class
2. Create custom objects and add them to cells
3. Add cells to a datatable

Step 1: Define the Class

C#:

// 1.) Defining a class: Movie class
 // Create class with member variables: Rank, Title, BoxOffice, Year, Director, Producer, RunTime, Post
 // Set the default class member to be Title
 [System.Reflection.DefaultMember("Title")]
 public class Movie : IMedia
 {
     public Image Poster { get; set; }
     public string Title { get; set; }
     public int Rank { get; set; }
     public string BoxOffice { get; set; }
     public int Year { get; set; }
     public string Director { get; set; }
     public string Producer { get; set; }
     public string RunTime { get; set; }

     // Add glyph that will be used to indicate data type cells
     string IMedia.ContentType => "image/png";
     Stream IMedia.Data
     {
         get
         {
             return GetType().Assembly.GetManifestResourceStream("CellDataType_Movies.Camera.png");
         }

     }
 }

Step 2: Create Custom Objects and Add to Cells

C#:

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;
            // 2.) Create custom objects using Movie class for the top 10 highest box office movies
            GrapeCity.CalcEngine.RichValue<Movie> AvengersEndgame = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/0/0d/Avengers_Endgame_poster.jpg"),
                Title = "Avengers: Endgame",
                Rank = 1,
                BoxOffice = "$2.798 billion",
                Year = 2019,
                Director = "Anthony Russo & Joe Russo",
                Producer = "Kevin Feige",
                RunTime = "181 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> Avatar = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/thumb/b/b0/Avatar-Teaser-Poster.jpg/220px-Avatar-Teaser-Poster.jpg"),
                Title = "Avatar",
                Rank = 2,
                BoxOffice = "$2.79 billion",
                Year = 2009,
                Director = "James Cameron",
                Producer = "James Cameron",
                RunTime = "162 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> Titanic = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/1/19/Titanic_%28Official_Film_Poster%29.png"),
                Title = "Titanic",
                Rank = 3,
                BoxOffice = "$2.195 billion",
                Year = 1997,
                Director = "James Cameron",
                Producer = "James Cameron & Jon Landau",
                RunTime = "162 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> StarWars_TheForceAwakens = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/a/a2/Star_Wars_The_Force_Awakens_Theatrical_Poster.jpg"),
                Title = "Star Wars: The Force Awakens",
                Rank = 4,
                BoxOffice = "$2.068 billion",
                Year = 2015,
                Director = "J. J. Abrams",
                Producer = "Kathleen Kennedy, J.J.Abrams, & Bryan Burk",
                RunTime = "135 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> Avengers_InfinityWars = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/4/4d/Avengers_Infinity_War_poster.jpg"),
                Title = "Avengers: Infinity Wars",
                Rank = 5,
                BoxOffice = "$2.048 billion",
                Year = 2018,
                Director = "Anthony Russo & Joe Russo",
                Producer = "Kevin Feige",
                RunTime = "149 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> JurassicWorld = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/thumb/6/6e/Jurassic_World_poster.jpg/220px-Jurassic_World_poster.jpg"),
                Title = "Jurassic World",
                Rank = 6,
                BoxOffice = "$1.670 billion",
                Year = 2015,
                Director = "Colin Trevorrow",
                Producer = "Frank Marshall & Patrick Crowley",
                RunTime = "124 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> TheLionKing = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/9/9d/Disney_The_Lion_King_2019.jpg"),
                Title = "The Lion King",
                Rank = 7,
                BoxOffice = "$1.657 billion",
                Year = 2019,
                Director = "Jon Favreau",
                Producer = "Jon Favreau, Jeffrey Silver & Karen Gilchrist",
                RunTime = "118 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> TheAvengers = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/8/8a/The_Avengers_%282012_film%29_poster.jpg"),
                Title = "The Avengers",
                Rank = 8,
                BoxOffice = "$1.519 billion",
                Year = 2012,
                Director = "Joss Whedon",
                Producer = "Kevin Feige",
                RunTime = "143 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> Furious7 = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/thumb/b/b8/Furious_7_poster.jpg/220px-Furious_7_poster.jpg"),
                Title = "Furious 7",
                Rank = 9,
                BoxOffice = "$1.516 billion",
                Year = 2015,
                Director = "James Wan",
                Producer = "Neal H. Moritz, Vin Diesel & Michael Fottrell",
                RunTime = "137 minutes",
            });
            GrapeCity.CalcEngine.RichValue<Movie> FrozenII = new GrapeCity.CalcEngine.RichValue<Movie>(new Movie()
            {
                Poster = Movie.GetPosterFromUrl("https://upload.wikimedia.org/wikipedia/en/thumb/4/4f/Frozen_2_poster.jpg/220px-Frozen_2_poster.jpg"),
                Title = "Frozen II",
                Rank = 10,
                BoxOffice = "$1.450 billion",
                Year = 2019,
                Director = "Chris Buck & Jennifer Lee",
                Producer = "Peter Del Vecho",
                RunTime = "103 minutes",
            });
            // 2b.) Set formulas to apply Movies value
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A2"].Formula = "B2.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A3"].Formula = "B3.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A4"].Formula = "B4.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A5"].Formula = "B5.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A6"].Formula = "B6.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A7"].Formula = "B7.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A8"].Formula = "B8.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A9"].Formula = "B9.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A10"].Formula = "B10.Rank";
            fpSpread1.AsWorkbook().ActiveSheet.Cells["A11"].Formula = "B11.Rank";
            // 2b.) Set the Movie object in the cells
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B2"].Value = AvengersEndgame;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B3"].Value = Avatar;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B4"].Value = Titanic;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B5"].Value = StarWars_TheForceAwakens;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B6"].Value = Avengers_InfinityWars;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B7"].Value = JurassicWorld;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B8"].Value = TheLionKing;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B9"].Value = TheAvengers;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B10"].Value = Furious7;
            fpSpread1.AsWorkbook().ActiveSheet.Cells["B11"].Value = FrozenII;
        }

Outcome: Data Types Added to Cells

After applying this code logic, the Spread control will show the Movie members' values in column B and display the movie's Rank in column A using formulas to reference the Rank property. End-users can insert data to new cells by manually entering the formulas or by using Spread's UI Insert Button or Data Card to insert this formula:

Step 3: Add Cells to a DataTable

C#:

// 2b.) Set the Movie object in the cells
fpSpread1.AsWorkbook().ActiveSheet.Cells["B2"].Value = AvengersEndgame;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B3"].Value = Avatar;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B4"].Value = Titanic;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B5"].Value = StarWars_TheForceAwakens;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B6"].Value = Avengers_InfinityWars;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B7"].Value = JurassicWorld;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B8"].Value = TheLionKing;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B9"].Value = TheAvengers;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B10"].Value = Furious7;
fpSpread1.AsWorkbook().ActiveSheet.Cells["B11"].Value = FrozenII;

// 3.) Add cells to DataTable
fpSpread1.AsWorkbook().ActiveSheet.SetValue(0, 0, "Rank");
fpSpread1.AsWorkbook().ActiveSheet.SetValue(0, 1, "Title");
fpSpread1.AsWorkbook().ActiveSheet.Columns["A:B"].AutoFit();
fpSpread1.AsWorkbook().ActiveSheet.Tables.Add("A1:B11", YesNoGuess.Yes);

Outcome: DataTable with Inserted Columns

After applying this code logic, the sample will appear with a DataTable enabling end-users to insert columns of selected data by using the insert button and keyboard shortcut (Ctrl + Shift + F5). When inserting the selected data, the table will insert a new table column and will set the table column's formula to reference the selected property: