In this blog post we will demonstrate how the developer can use validation, formulas and conditional formatting on cells in a spreadsheet. We will build a sample spreadsheet that calculates application score for students. The final mark is the sum of the points in chemistry and biology. The points in these majors can come from Olympics or a competition: the bigger is considered. The points from the Olympics need to be converted to match the scale of the points from the competition.
Here is the final application:
I. General Setup for the Project
We have installed the Spreadsheet for WinForms library and drag and drop the WorkbookView icon on an empty work area of a blank WinForms project. You can add the spreadsheet manually, without installing the library if you add the MindFusion.Spreadsheet.WinForms dll to the toolbox. Either way, after you’ve dropped the WorkbookView, you have an instance of the WorkbookView and a Workbook instance created.
If you run the application you will see an empty spreadsheet, which you can edit. We want to add data to our spreadsheet, so, we get the spreadsheet from the Workbook .
var activeSheet = workbookView.ActiveWorksheet; activeSheet.BeginInit(); ............... activeSheet.EndInit();
In order to speed up performance, we place all code for initializing the spreadsheet between the methods BeginInit and EndInit .
The data for the spreadsheet is randomly generated. We have a list with the names of the students and we generate random scores in biology and chemistry. Some students have no marks in one of the majors.
string[] candidates = new string[] { "James Smith", "Mary Johnson", "Patricia Brown", "Jennifer Jones", "Robert Garcia", "John Davis", "Linda Rodriguez", "Elizabeth Martinez", "Michael Fernandez", "Barbara Lopez", "William Wilson", "Susan Anderson", "Jessica Gonzalez", "David Thomas", "Richard Taylor", "Joseph Moore", "Sarah Jackson", "Karen Marten", "Nancy Lee", "Sandra Perez", "Steven Harris", "Paul Clark", "Andrew Ramirez", "Emily Lewis", "Donald Robinson", "Michelle Walker", "Betty Young", "Matthew Wright"};
Random rand = new Random();
for (int i = 0; i < candidates.Length; i++)
{
activeSheet.Cells[0, i + 2].Data = candidates[i];
if (i % 4 != 0)
activeSheet.Cells[1, i + 2].Data = rand.NextDouble() * 65;
if ((i+1) % 6 != 0)
activeSheet.Cells[3, i + 2].Data = rand.Next(101);
if ((i + 2) % 5 != 0)
activeSheet.Cells[4, i + 2].Data = rand.NextDouble() * 72;
if ((i + 4) % 6 != 0)
activeSheet.Cells[6, i + 2].Data = rand.Next(101);
}
II. The Heading and Cell Headers
We make the row with the heading 35 points tall and merge 8 cells that will span above the eight columns with the data.
activeSheet.Rows[0].Height = 35; var heading = activeSheet.CellRanges["A1:H1"]; heading.Merge();
In order to merge cells you need to call the Merge method on a CellRanges instance. Next we get the merged cell that represents the heading and apply some formatting on it. But before that, we set the heading text:
var headingCell = activeSheet.Cells["A1"]; headingCell.Data = "STUDENT TEST RESULTS"; var headingStyle = headingCell.Style; headingStyle.HorizontalAlignment = HorizontalAlignment.Center; headingStyle.VerticalAlignment = VerticalAlignment.Middle; headingStyle.Background = new MindFusion.Drawing.SolidBrush(Color.FromArgb(45, 52, 72)); headingStyle.FontSize = 14; headingStyle.FontBold = true; headingStyle.TextColor = Color.FromArgb(228, 223, 221);
Formatting and styling of cells is done through the Style class. Each UI unit in the spreadsheet: Cell, CellRanges, Column, Row etc. has a Style property. Customizing the appearance of the visual is done through this property.
We initialize an array with the labels of the columns:
string[] cellHeaders = new string[] { "Name", "Biology Olympics\nPoints, Max 65", "Biology Olympics\n Percents", "Biology Competition\nPoints, Max 100", "Chemistry Olympics\nPoints, Max 72", "Chemistry Olympics\nPercents", "Chemistry Competition\nPoints, Max 100", "Total Points\nMax Biology + Max Chemnistry" };
for (int i = 0; i < cellHeaders.Length; i++)
activeSheet.Cells[i, 1].Data = cellHeaders[i];
The content of a cell depends on the value of its Data property. The rest of the formatting is done through the Style field the same way we set it for the heading.
III. Data and Formulas
We generate the points for the competition and Olympics in biology randomly:
string[] candidates = new string[] { "James Smith", "Mary Johnson", "Patricia Brown", "Jennifer Jones", "Robert Garcia", "John Davis", "Linda Rodriguez", "Elizabeth Martinez", "Michael Fernandez", "Barbara Lopez", "William Wilson", "Susan Anderson", "Jessica Gonzalez", "David Thomas", "Richard Taylor", "Joseph Moore", "Sarah Jackson", "Karen Marten", "Nancy Lee", "Sandra Perez", "Steven Harris", "Paul Clark", "Andrew Ramirez", "Emily Lewis", "Donald Robinson", "Michelle Walker", "Betty Young", "Matthew Wright"};
Random rand = new Random();
for (int i = 0; i < candidates.Length; i++)
{
activeSheet.Cells[0, i + 2].Data = candidates[i];
if (i % 4 != 0)
activeSheet.Cells[1, i + 2].Data = rand.NextDouble() * 65;
if ((i+1) % 6 != 0)
activeSheet.Cells[3, i + 2].Data = rand.Next(101);
if ((i + 2) % 5 != 0)
activeSheet.Cells[4, i + 2].Data = rand.NextDouble() * 72;
if ((i + 4) % 6 != 0)
activeSheet.Cells[6, i + 2].Data = rand.Next(101);
}
Our criteria dictates that the points from the Olympics need to be converted to percents in order to match the scale of the points from the competition. We do this by applying formula and formatting. First, we need to recalculate the points as a percentage of the maximum number of points. This is 65 for the first Olympics. The fraction we receive is less than 1 but the formatting of the number as a percent (“P2”) compensates this:
for (int i = 3; i < 3 + candidates.Length; i++)
{
string row = i.ToString();
activeSheet.Cells["C" + row].Data = string.Format("=B{0}/65", row);
activeSheet.Cells["C" + row].Style.Format = "P2";
}
In order to calculate the final score of the candidate, we apply a formula that uses the Max function:
activeSheet.Cells["H" + row].Data = string.Format("=Max(C{0} * 100, D{0}) + Max(F{0}*100, G{0})", row);
With that our data entry and calculation is done and we need just to implement data validation.
IV. Data Validation
Data validation steps in to ensure that whenever data is edited or new data is added, the values are correct. It is implemented through the Validation class and its numerous properties. Just like Style, Validation is exposed as a member on every UI element of the data grid in a spreadsheet: Cell , Column , Row etc. The Validation class has various properties for spcifying the restrictions. First, there are various types of validation – data, text, length, date time values etc. In our case we want the entry to be decimal values. We use the First and Second properties of the Validation class to indicate which is the interval that is valid for cell data. We also use additional properties like InputMessage , ErrorMessage , ErrorTitle and other to inform the user why data entry has failed.
var bioOlympicsCells = activeSheet.CellRanges["B3:B30"]; bioOlympicsCells.Validation.Type = ValidationType.Decimal; bioOlympicsCells.Validation.First = "0"; bioOlympicsCells.Validation.Second = "65"; bioOlympicsCells.Validation.Operator = ComparisonOperator.Between; bioOlympicsCells.Validation.AllowBlankCells = true; bioOlympicsCells.Validation.ShowInputMessage = true; bioOlympicsCells.Validation.InputMessage = "Points mus be max 65."; bioOlympicsCells.Validation.ShowError = true; bioOlympicsCells.Validation.ErrorTitle = "Invalid input."; bioOlympicsCells.Validation.ErrorMessage = "Points must be non-negative and 65 or less.";
Here is how the warning look like:
V. Conditional Formatting
Conditional formats are a powerful way to mark cells that answer given criteria. In our case we would like to mark the cell, which provides the mark in biology or chemistry. Since we take the maximum value from the points from the Olympics or the competition, we will mark the cell, whose value we take.
// Set some conditional formats
for (int c = 2; c < candidates.Length + 3; c++)
{
/* set a background for the cell of biology competition
the condition is that its value is higher than the one
from the biology olympics */
var biologyCompetitionCell = activeSheet.Cells[3, c];
var cf1 = biologyCompetitionCell.Style.ConditionalFormats.Add();
cf1.Type = ConditionalFormatType.CellValue;
cf1.Operator = ComparisonOperator.GreaterThan;
var biologyPercent = 0.0;
if (activeSheet.Cells[1, c].Data != null)
{
if (Double.TryParse(activeSheet.Cells[1, c].Data.ToString(), out biologyPercent))
cf1.Second = (biologyPercent/65 * 100).ToString();
}
else
cf1.Second = "0";
cf1.Style.Background = new MindFusion.Drawing.SolidBrush(Color.FromArgb(255, 229, 238, 221));
}
The ConditionalFormatType property specifies if the criteria will be based on the cell value or a formula. We will compare the cell value to the value of the neighbouring cell to decide which is bigger: the mark from the olympics or the competition. We need to convert the value of the olympics to percents so that the comparison is done on similar values.
We repeat the conditional formatting on the next column, which guarantees that the cell with the bigger value will be highlighted With that our sample is finished. You can download the final project, together with all libraries used from this link:
Spreadsheet for WinForms Sample Project with Data Validation, Conditional Formats and Formulas
About MindFusion Spreadsheet for WinForms: This is a powerful spreadsheet control written in pure C# that provides Excel-like features into any .NET application. The library is easy to integrate and customize. The control boasts a powerful calculation engine, validation, filter and sort of data, functions, various data types and more. Each spreadsheet can contain various charts, hyperlinks, images and differently styles cells. Appearance customization is done through multiple properties, which can be applied on cells, rows, columns, cell ranges etc. The control supports import and export into various formats and is optimized for speed and performance of large data sets. Learn more at https://mindfusion.eu/spreadsheet-winforms.html