When creating large spreadsheets, information can be hard to convey without enlarging cells or spanning multiple row and columns. It could be useful to hide information from the user until it is necessary, as well as provide a way to easily edit a cell without having to enlarge it if it is too small. Spread provides shape and subeditor functionality that will allow you to accomplish these tasks with little effort. In this blog, I will show you how to use shapes to create captions for cells as well as how to create a subeditor to make editing cells easier, as well as providing rich features or complex UI editors that cannot be displayed inline in a Windows Forms application using Visual Studio 2013. To start off, I created two forms: a MainForm where the application is run from, and a TextSubEditor form that represents the subeditor. The TextSubEditor forms is very simplistic, containing a text box and two buttons: 
The subeditor form. In this example, I named the text box “txt”, and the two buttons “OkButton” and “CancelButton”. To ensure that this was not just a form, I had to implement the ISubEditor interface, and the subsequent methods GetLocation, GetSubEditorControl, GetValue, SetValue, and GetPreferredSize:
public Point GetLocation(Rectangle rect)
{
Point pt = new Point(0);
Size sz = GetPreferredSize();
pt.Y = (Screen.PrimaryScreen.WorkingArea.Height / 2) - (sz.Height / 2);
pt.X = (Screen.PrimaryScreen.WorkingArea.Width / 2) - (sz.Width / 2);
return pt;
}
public Control GetSubEditorControl()
{
return this;
}
public object GetValue()
{
return txt.Text;
}
public void SetValue(object value)
{
txt.Text = value.ToString();
}
public Size GetPreferredSize()
{
return new Size(310, 200);
}
I also had to implement the event handlers for changing the text in the text box as well as clicking the buttons:
private void txt_TextChanged(object sender, EventArgs e)
{
Text = txt.Text;
}
private void okButton_Click(object sender, EventArgs e)
{
if (ValueChanged != null)
ValueChanged(this, EventArgs.Empty);
if (CloseUp != null)
CloseUp(this, EventArgs.Empty);
}
private void cancelButton_Click(object sender, EventArgs e)
{
if (CloseUp != null)
CloseUp(this, EventArgs.Empty);
}
Once that was done, I wanted to have a text cell that had an ellipsis button in it that would open the subeditor I had just created. To do this, I implemented my version of a text cell type, with code that only overrode the GetEditorControl method of the TextCellType:
public override System.Windows.Forms.Control GetEditorControl(System.Windows.Forms.Control parent, FarPoint.Win.Spread.Appearance appearance, float zoomFactor)
{
System.Windows.Forms.Control myControl = base.GetEditorControl(parent, appearance, zoomFactor);
((GeneralEditor)myControl).ButtonStyle = FarPoint.Win.ButtonStyle.PopUp;
return myControl;
}
This completed the pieces I needed to actually start putting the program together. On my MainForm, I added the Spread component from the toolbox and docked it to fit the entire form: 
The main form. To allow a cell to have the subeditor I created, I have to specify the cell type’s cell editor. In this case, I assign the first cell’s CellType and its subeditor to the celltype and subeditor I created:
MyTextCellType t = new MyTextCellType();
t.MaxLength = 100;
t.SubEditor = new TextSubEditor();
fpSpread1.ActiveSheet.Cells[0, 0].CellType = t;
fpSpread1.ActiveSheet.Cells[0, 0].Text = "This is an example cell with a subeditor.";
fpSpread1.ActiveSheet.SetColumnWidth(0, 100);
t.SubEditor.SetValue(fpSpread1.ActiveSheet.Cells[0, 0].Text);
Now when I enter edit mode in the first cell, I can click on the ellipsis button (specified as the PopUp button style in the Cell Type’s getEditorControl method that I overrode) and open up the subeditor for the cell. There is one problem with this however: the user might not know that they can open up the subeditor with the ellipsis button. I want a way to show them this information without having it always be visible in the sheet. I can get this functionality by using shapes. Most of the time, tutorials in programs involve speech bubbles or circling certain areas of the window to direct the user’s attention. Spread allows you to create shapes and move them around, serving as the basis for this functionality. To start off, I created two shapes and set their visible property to false:
private void CreateShapes()
{
FarPoint.Win.Spread.DrawingSpace.CaptionBalloonShape balloon = new FarPoint.Win.Spread.DrawingSpace.CaptionBalloonShape();
balloon.Parent = fpSpread1;
balloon.Name = "customTextBubble";
balloon.Height = 75;
balloon.Width = 150;
balloon.FlipVertical = true;
balloon.Text = "\\n\\nPress the ... to open subeditor";
balloon.TextWrap = true;
balloon.CanRenderText = true;
fpSpread1.ActiveSheet.AddShape(balloon, 0, 0);
balloon.Visible = false;
FarPoint.Win.Spread.DrawingSpace.SquareCaptionBalloonShape squareBalloon = new FarPoint.Win.Spread.DrawingSpace.SquareCaptionBalloonShape();
squareBalloon.Parent = fpSpread1;
squareBalloon.Name = "numberBubble";
squareBalloon.Height = 75;
squareBalloon.Width = 150;
squareBalloon.FlipVertical = true;
squareBalloon.Text = "\\n\\nThis is a number cell";
squareBalloon.TextWrap = true;
squareBalloon.CanRenderText = true;
fpSpread1.ActiveSheet.AddShape(squareBalloon, 0, 0);
squareBalloon.Visible = false;
}
For my custom text cell with its subeditor, I want the tutorial message to only be displayed when focus is set to the cell. For this, I have to use the EnterCell and LeaveCell event handlers:
private void fpSpread1_EnterCell(object sender, EnterCellEventArgs e)
{
if (fpSpread1.ActiveSheet.Cells[e.Row, e.Column].CellType is MyTextCellType)
{
fpSpread1.EditMode = true;
Rectangle cell = fpSpread1.GetCellRectangle(0, 0, e.Row, e.Column);
fpSpread1.ActiveSheet.GetShape("customTextBubble").Left = cell.X;
fpSpread1.ActiveSheet.GetShape("customTextBubble").Top = cell.Y;
fpSpread1.ActiveSheet.GetShape("customTextBubble").Visible = true;
}
}
private void fpSpread1_LeaveCell(object sender, LeaveCellEventArgs e)
{
if (fpSpread1.ActiveSheet.Cells[e.Row, e.Column].CellType is MyTextCellType)
{
fpSpread1.ActiveSheet.GetShape("customTextBubble").Visible = false;
}
}
When the user enters a cell that has the CellType I created, the customTextBubble shape is moved to its location and it is set to be visible. When the user leaves the cell, that shape is made invisible again. While this functionality is great, it might also be useful to show messages when the user simply hovers over a cell. To do this, I created a number cell to test it on and utilized the TextToolTip functionality of Spread. In order to implement this, I had to set the TextTipPolicy and its delay:
NumberCellType nt = new NumberCellType();
fpSpread1.ActiveSheet.Cells[2, 0].CellType = nt;
fpSpread1.ActiveSheet.Cells[2, 0].Value = 123456;
fpSpread1.TextTipPolicy = FarPoint.Win.Spread.TextTipPolicy.Floating;
fpSpread1.TextTipDelay = 500;
Then, I had to use the TextTipFetch event handler to show the shape I created rather than the text tool tip:
private void fpSpread1_TextTipFetch(object sender, TextTipFetchEventArgs e)
{
e.ShowTip = false;
if (fpSpread1.ActiveSheet.Cells[e.Row, e.Column].CellType is NumberCellType)
{
Rectangle cell = fpSpread1.GetCellRectangle(0, 0, e.Row, e.Column);
fpSpread1.ActiveSheet.GetShape("numberBubble").Left = cell.X;
fpSpread1.ActiveSheet.GetShape("numberBubble").Top = cell.Y;
fpSpread1.ActiveSheet.GetShape("numberBubble").Visible = true;
}
else
{
fpSpread1.ActiveSheet.GetShape("numberBubble").Visible = false;
}
}
Now the user can hover over cells with the NumberCellType and a message bubble will be displayed: 
The shape that utilizes the text tip fetch event handler. Another bubble will also be displayed when the user sets focus to the custom text cell: 
The shape that uses the enter/leave cell event handlers. In this blog, I have shown you how to create a subeditor to utilize functionality that cannot normally be displayed inline. I have also shown a way to create captions that can be used in many different ways. Spread is a powerful data manipulation tool, but it can also help users view and edit that data without having to know exactly how a particular spreadsheet might work. The following is a download link for the files for this project: WinformsSubeditor
MESCIUS inc.