One of the main advantages of Spread is its ability to help you design and create calculation-based applications quickly and easily with little code. To demonstrate these capabilities, let’s use something that we are all familiar with: GPA calculation. This article walks through a sample application that calculates a student grade point average and shows how easy it is to create a nice looking template, add formulas, and add UI with very little code using Spread. To begin, I created a template using the Spread Designer. The designer allows me to set colors, fonts, and other visual properties, as well as add formulas in an interface very similar to that of Excel. Once I have created the template, I can save it in an XML file which the Spread control in my application will load. You can see in the following screen shot that I have designed an application with a color theme, buttons, various cell types, and calculation fields. One of the key features here to note is that there are two cells at the top for the Add and Remove Semester buttons and column titles and one row at the bottom for the cumulative GPA:
The types of the cells are as follows: General in first column, number in columns B, D, and E, and combo box for C, except for the semester GPA and cumulative GPA cells. Spread templates are easy to use and include in the project: when you save a template, an XML file is created and then added to the project as an embedded resource at runtime. Spread handles this for you, so you don’t have to do anything. These templates can contain many more features than those I have added to this sample (this particular sample is kept simple to show Spread’s core features). For example, I can group rows so that only semester GPAs and totals show up, as in the second semester block in the picture below.
This is how my application works. As you have already seen, the main tab shows GPA by semester. Notice the two buttons, Add Semester and Remove Semester, at the top. When clicked, these add or remove a template block of cells for a semester to and from the sheet. This is done by storing that semester template in a separate, locked sheet. When the Add Semester button is clicked, that block is copied to the main sheet. The screen shot bellows shows an example of that semester block as a template set of cells, which gets copied to the main sheet.
To add or remove these cells, I only need to add a few lines of code to the ButtonClicked event, which is called whenever the “Add Semester” or “Remove Semester” buttons are clicked. In the code below, cell ranges are created to contain the copied data and to provide a location to copy the data to (after rows are added). When cells are copied, formatting, formulas, and cell types are retained.
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 == 3)
{
fpSpread1.Sheets[0].AddRows(fpSpread1.Sheets[0].Rows.Count-1, 19);
FarPoint.Win.Spread.Model.CellRange cr = new FarPoint.Win.Spread.Model.CellRange(0, 0, 19, 5);
fpSpread1.Sheets[2].GetClipDataObject(false, cr, FarPoint.Win.Spread.ClipboardCopyOptions.All);
FarPoint.Win.Spread.Model.CellRange cr2 = new FarPoint.Win.Spread.Model.CellRange(fpSpread1.Sheets[0].RowCount-20, 0, 19, 5);
fpSpread1.Sheets[0].ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.All, fpSpread1.Sheets[2].GetClipDataObject(false, cr,
FarPoint.Win.Spread.ClipboardCopyOptions.All), cr2);
fpSpread1.Sheets[0].AddRangeGroup(fpSpread1.Sheets[0].RowCount - 19, 16, true);
fpSpread1.Sheets[0].Rows[fpSpread1.Sheets[0].RowCount - 2].Height = 30;
semesterCount++;
fpSpread1.Sheets[0].Cells[fpSpread1.Sheets[0].RowCount - 20, 0].Text = "Semester " + semesterCount;
}
else if (fpSpread1.Sheets[0].ActiveCell.Row.Index == 0 && fpSpread1.Sheets[0].ActiveCell.Column.Index == 4)
{
fpSpread1.Sheets[0].RemoveRows(fpSpread1.Sheets[0].Rows.Count - 20, 19);
semesterCount--;
}
fpSpread1.ResumeLayout();
}
On the main sheet, the user can choose the letter grade from a drop down, and the application automatically assigns a point value (the “Grade” and “Scale” columns, or Columns C and D). There is also a separate sheet and table for the letter grade and quality point relationship used in this assignment.
This is utilized in conjunction with the VLOOKUP formula:
VLOOKUP(RC[-1],GradeLookup,2,FALSE)
The above table is called GradeLookup. This formula looks in that table for the specified letter grade that the user selects from a drop down cell in column C and places the result in column D. For the calculations in column E, the grade points are multiplied by the number of credits for each course through this formula:
IF(ISBLANK(RC[-3]),"",RC[-3]*RC[-1])
All of this is useful, but it would be better if you could save and open semesters that you have already filled out, so add toolbar menu items to the Form like so:
To add these buttons, simply open up the toolbox in Visual Studio and add the “MenuStrip” component to the top of the form by clicking it and dragging it into the form. Once you have done that, you can give the buttons names by clicking in the “Type Here” box and typing a name for the button. In this example, I used “File” as the menu header and added two menu items under it (“Open” and “Save”). Once the buttons are laid out, I have to add the code to make these buttons work. In the
private System.Windows.Forms.ToolStripMenuItem <buttonname>ToolStripMenuItem
In the case of this example, the Open button is referred to as:
private System.Windows.Forms.ToolStripMenuItem openToolStripMenuItem
A function should be added for each button using this name and adding “_Click” on the end of it. In this example, the Open button’s function definition is the following:
private void openToolStripMenuItem_Click(object sender, EventArgs e)
In this example the save and open buttons are the only buttons that need code to make them work:
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 != "")
{
fpSpread1.Open(filename);
}
}
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, false);
}
}
Now GPA sheets that have been created with this sample can be stored and opened at a later time. This sample has demonstrated how, with little effort, you can create an interactive calculator application. Thousands of Spread users apply these concepts for financial calculators, scientific data collection, sales reporting, and much more. Here is a link to download the Visual Studio solution for this project: GPA Calculator
MESCIUS inc.