Data tables are a useful tool for organizing information in an easily-accessible manner. Spread incorporates these tables without the use of confusing code and excessive logic, providing a simple and quick way to fill in spreadsheets and keep them updated. This article walks through a sample Windows Forms application that serves as a database of information about employees and shows the ease with which a user can create bound data tables and multiple forms that are connected using Spread. To start off, I created five different forms: Employees, EmployeeDetails, AddEmployee, SearchForm, and SearchResult. The Employees form is the main one that starts the application and contains a Spread component, which represents the visualization of the data table. EmployeeDetails is the form that opens when a user wants to view or edit an employee’s information. The AddEmployee form opens when a user adds an employee, and gives the user fields to fill in. The SearchForm allows users to search for an employee by certain categories (ID, First Name, etc.). The SearchResult opens once the user has chosen something to search by, and contains a Spread component with all the details for the employees in the data table that match the criteria. After creating those forms, I opened up the Employees form and added a Spread component to it. Once I opened that component in the Spread Designer, I changed the number of rows and columns and created the categories as displayed below: 
The main Employees Form The second sheet is used for formatting of an employee’s information in a row, and is copied over whenever an employee is added. For reference, that sheet looks like this: 
The second sheet used for formatting. Most of the cells are formatted as general cells, with the exception of the salary and bonus which are currency cells, and the “Remove Employee” and “View Details…” cells, which are buttons. The next step is to add the functionality behind the buttons as well as the data table to store the information. The button functionality is added in the ButtonClicked event:
private void fpSpread1_ButtonClicked(object sender, FarPoint.Win.Spread.EditorNotifyEventArgs e)
{
fpSpread1.SuspendLayout();
if (fpSpread1.Sheets[0].ActiveCell.Row.Index == 0 && fpSpread1.Sheets[0].ActiveCell.Column.Index == 6)
{
searchEmployeeDialog();
}
else if (fpSpread1.Sheets[0].ActiveCell.Row.Index == 1 && fpSpread1.Sheets[0].ActiveCell.Column.Index == 6)
{
if (addEmployeeForm())
{
FarPoint.Win.Spread.Model.CellRange cr = new FarPoint.Win.Spread.Model.CellRange(0, 0, 1, 8);
fpSpread1.Sheets[1].GetClipDataObject(false, cr, FarPoint.Win.Spread.ClipboardCopyOptions.Formatting);
FarPoint.Win.Spread.Model.CellRange cr2 = new FarPoint.Win.Spread.Model.CellRange(fpSpread1.Sheets[0].RowCount - 1, 0, 1, 8);
fpSpread1.Sheets[0].ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Formatting, fpSpread1.Sheets[1].GetClipDataObject(false, cr,
FarPoint.Win.Spread.ClipboardCopyOptions.Formatting), cr2);
}
}
else if (fpSpread1.Sheets[0].ActiveCell.Column.Index == 7)
{
viewEmployeeDetailsDialog(dt.Rows[fpSpread1.Sheets[0].ActiveCell.Row.Index - 2][0].ToString());
}
else if (fpSpread1.Sheets[0].ActiveCell.Column.Index == 6)
{
DialogResult dr = MessageBox.Show("Are you sure you want to remove " + fpSpread1.Sheets[0].Cells[fpSpread1.Sheets[0].ActiveCell.Row.Index, 1].Text + "?",
"Remove Employee",
MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dr == DialogResult.Yes)
{
fpSpread1.Sheets[0].RemoveRows(fpSpread1.ActiveSheet.ActiveRow.Index, 1);
}
}
fpSpread1.ResumeLayout();
}
Which has a corresponding handler in the Designer.cs file:
this.fpSpread1.ButtonClicked += new FarPoint.Win.Spread.EditorNotifyEventHandler(this.fpSpread1_ButtonClicked);
When a user clicks on the “Add Employee” button, the addEmployeeForm method is called, which creates a new instance of the AddEmployee form I created earlier:
public bool addEmployeeForm()
{
AddEmployee newEmployee = new AddEmployee();
newEmployee.ShowDialog();
if (newEmployee.DialogResult == System.Windows.Forms.DialogResult.Cancel)
return false;
return true;
}
The result of this call determines which button the user chose to click and whether to add the employee to the data table: 
The Add Employee form. From here, the user can add information about the employee in the fields listed, with the ID being the key for the data table. As such, this is the one field that must be unique in order for the table to be searched correctly. The only functions that are required to make this work are the button_clicked event handlers: one for “Accept” and the other for “Cancel”. Once the variables are set via the text boxes, the AddToDataTable function is called with all of the fields passed into it. This function adds the employee to the data table with their information:
public static void addToDataTable(string id, string firstName, string lastName, int salary, int bonus, string department, string hireDate, string phoneNumber, string email)
{
DataRow row = dt.NewRow();
row["id"] = id;
row["firstName"] = firstName;
row["lastName"] = lastName;
row["salary"] = salary;
row["bonus"] = bonus;
row["department"] = department;
row["hireDate"] = hireDate;
row["phoneNumber"] = phoneNumber;
row["email"] = email;
dt.Rows.Add(row);
}
A similar form is the EmployeeDetails form, which allows a user to edit an employee’s information, except for the ID because it is the key for the table: 
The Employee Details Form. Similar to the AddEmployee form, the only code required here is for the buttons, which edits the specified employee’s information using the EditDataTable function:
public static void editDataTable(string id, string firstName, string lastName, int salary, int bonus, string department, string hireDate, string phoneNumber, string email)
{
DataRow row = dt.Rows.Find(id);
row["id"] = id;
row["firstName"] = firstName;
row["lastName"] = lastName;
row["salary"] = salary;
row["bonus"] = bonus;
row["department"] = department;
row["hireDate"] = hireDate;
row["phoneNumber"] = phoneNumber;
row["email"] = email;
}
Once those forms are complete, the base functionality for the spreadsheet is complete. The only problem is that usually there are a lot of employees that need to have their information retained, and it could take a while to find a specific employee. In order to make it easier, I added search functionality using two forms: SearchForm and SearchResult. 
The Search Form used to search for employees. The SearchForm is the simplest one in this project, with two fields and one button. The “Category” field on the left is a drop-down list that contains all the categories of information to search by. The text box on the right is actually a part of a custom text box to display a hint in the text box on what to type, which I have called a CueTextBox. As with the other forms, the code for this form is really simple. It only requires code in the button_clicked event handler, and is used to open up the SearchResult form with the specified parameters (the category to search in and the text to search for):
private void button1_Click(object sender, EventArgs e)
{
SearchResult result = new SearchResult(toCamelCase(this.comboBox1.Text), this.cueTextBox1.Text);
result.ShowDialog();
}
The “toCamelCase” function is used to convert the user-facing names of the categories into the column names referred to in the data table. Once the SearchResult form is created, it is populated with the result of the search, which can include multiple results: 
The Search Result form to display the results of the search. Similar to the main form, this has another sheet titled “Employee”, which has a row formatted to the specific columns in the sheet. Just like the main form, the formatting of the row is copied over to the main sheet every time an employee is added. This sheet is also connected to the main data table of the application, so editing an employee’s information or removing them is reflected in the data table and the main form. The SearchResult form is initialized with both a column to search in and a string to search for. Those parameters are taken and a row filter is created out of that:
DataView dv = new DataView(Employees.getDataTable());
dv.RowFilter = column + " = '" + search +"'";
FarPoint.Win.Spread.Data.SpreadDataBindingAdapter data = new FarPoint.Win.Spread.Data.SpreadDataBindingAdapter();
data.DataSource = dv;
data.Spread = fpSpread2;
data.SheetName = "Employees Table";
data.MapperInfo = new FarPoint.Win.Spread.Data.MapperInfo(1, 0, dv.Count, 6);
data.FillSpreadDataByDataSource();
Any row in the data table that has the specified string in the specified column will be added to the data view, which is seen in the SearchResult form. After putting together all of the different components, it would be useful to save the information about the employees for use later. To do this, simply add a “File” toolbar menu item at the top of the main form with buttons for “Open” and “Save”: 
The File Menu with options for Opening and Saving files. In order to save both the sheet and the underlying data table, I have decided to save them as two separate XML files. Both files have the same name except “DataTable” is added onto the end of the XML file for the data table (In the windows explorer it is an “.XMLDataTable” file). To do this, I added an event for clicking the save button:
private void saveToolStripMenuItem_Click(object sender, EventArgs e)
{
string filename = "";
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "XML Files (*.xml)|*.xml|All files (*.*)|*.*";
DialogResult dr = sfd.ShowDialog();
if (dr == DialogResult.OK)
{
filename = sfd.FileName;
}
if (filename != "")
{
fpSpread1.Save(filename, FarPoint.Win.Spread.SaveXMLOptions.All);
dt.WriteXml(filename + "DataTable");
}
}
This function uses the Data Table’s WriteXml function, which writes the data table to an xml file with the specified name. To match this functionality, I also added the event for clicking the open button:
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
string filename = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "XML Files (*.xml)|*.xml|All files (*.*)|*.*";
DialogResult dr = ofd.ShowDialog();
if (dr == DialogResult.OK)
{
filename = ofd.FileName;
}
if (filename != "")
{
dt.Clear();
dt.ReadXml(filename + "DataTable");
fpSpread1.Open(filename);
FarPoint.Win.Spread.Data.SpreadDataBindingAdapter data = new FarPoint.Win.Spread.Data.SpreadDataBindingAdapter();
data.DataSource = dt;
data.Spread = fpSpread1;
data.SheetName = "Employees Table";
data.MapperInfo = new FarPoint.Win.Spread.Data.MapperInfo(2, 0, dt.Rows.Count, 6);
data.FillSpreadDataByDataSource();
for (int i = 2; i < dt.Rows.Count + 2; i++)
{
copyFormat(i, 1);
}
}
}
When opening an Employee data table, the previous information is cleared out, so as to prevent duplicate keys from being made. The data binding adapter is used to bind the new data table to the sheet, and updates the sheet to reflect the changes. In this blog, I have gone over the steps for creating an Employee spreadsheet that is bound to a data table. The data table was created column-by-column and bound to the spreadsheet with just a few lines of code. New forms were added and initialized by other forms with simple and intuitive code. Using Visual Studio to create the forms also made it easier to add fields and labels and position them in the exact place that I wanted. This blog has shown how, with little effort, an employee spreadsheet and bound data table can be created with only a small amount of code and the power of Spread. Attached is the project that shows these concepts in action. Employees
MESCIUS inc.