![](\"https:\/\/mindfusion.dev\/samples\/winforms\/spreadsheet\/student_scores.png\" "\\"Student")
<\/a>\n
\nI. General Setup for the Project<\/strong><\/p>\n
We have installed the Spreadsheet for WinForms library and drag and drop the WorkbookView<\/a> 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,<\/a> you have an instance of the WorkbookView<\/a> and a Workbook<\/a> instance created.<\/p>\n 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<\/a> .<\/p>\n In order to speed up performance, we place all code for initializing the spreadsheet between the methods BeginInit<\/a> and EndInit<\/a> .<\/p>\n 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.<\/p>\n II. The Heading and Cell Headers<\/strong><\/p>\n We make the row with the heading 35 points tall and merge 8 cells that will span above the eight columns with the data.<\/p>\n In order to merge cells you need to call the Merge<\/a> method on a CellRanges<\/a> 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:<\/p>\n Formatting and styling of cells is done through the Style<\/a> class. Each UI unit in the spreadsheet: Cell<\/a>, CellRanges<\/a>, Column<\/a>, Row<\/a> etc. has a Style<\/a> property. Customizing the appearance of the visual is done through this property.<\/p>\n We initialize an array with the labels of the columns:<\/p>\n The content of a cell depends on the value of its Data<\/a> property. The rest of the formatting is done through the Style<\/a> field the same way we set it for the heading.<\/p>\n III. Data and Formulas<\/strong><\/p>\n We generate the points for the competition and Olympics in biology randomly:<\/p>\n 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:<\/p>\n In order to calculate the final score of the candidate, we apply a formula that uses the Max<\/a> function:<\/p>\n With that our data entry and calculation is done and we need just to implement data validation.<\/p>\n IV. Data Validation<\/strong><\/p>\n 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<\/a> class and its numerous properties. Just like Style,<\/a> Validation<\/a> is exposed as a member on every UI element of the data grid in a spreadsheet: Cell<\/a> , Column<\/a> , Row<\/a> etc. The Validation<\/a> 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<\/a> and Second<\/a> properties of the Validation<\/a> class to indicate which is the interval that is valid for cell data. We also use additional properties like InputMessage<\/a> , ErrorMessage<\/a> , ErrorTitle<\/a> and other to inform the user why data entry has failed.<\/p>\n Here is how the warning look like:<\/p>\n V. Conditional Formatting<\/strong><\/p>\n 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.<\/p>\n The ConditionalFormatType<\/a> 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. Spreadsheet for WinForms Sample Project with Data Validation, Conditional Formats and Formulas<\/a><\/p>\n About MindFusion Spreadsheet for WinForms:<\/i> 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.dev\/spreadsheet-winforms.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Continue reading var activeSheet = workbookView.ActiveWorksheet;\nactiveSheet.BeginInit(); \n...............\nactiveSheet.EndInit();<\/pre>\n
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\"};\n\t\t\t\nRandom rand = new Random();\n\nfor (int i = 0; i < candidates.Length; i++)\n{\n\tactiveSheet.Cells[0, i + 2].Data = candidates[i];\n\n\tif (i % 4 != 0)\n\t\tactiveSheet.Cells[1, i + 2].Data = rand.NextDouble() * 65;\n\tif ((i+1) % 6 != 0)\n\t\tactiveSheet.Cells[3, i + 2].Data = rand.Next(101);\n\tif ((i + 2) % 5 != 0)\n\t\tactiveSheet.Cells[4, i + 2].Data = rand.NextDouble() * 72;\n\tif ((i + 4) % 6 != 0)\n\t\tactiveSheet.Cells[6, i + 2].Data = rand.Next(101);\n}<\/pre>\nactiveSheet.Rows[0].Height = 35;\nvar heading = activeSheet.CellRanges[\"A1:H1\"];\nheading.Merge();<\/pre>\n
var headingCell = activeSheet.Cells[\"A1\"];\nheadingCell.Data = \"STUDENT TEST RESULTS\";\n\nvar headingStyle = headingCell.Style;\nheadingStyle.HorizontalAlignment = HorizontalAlignment.Center;\nheadingStyle.VerticalAlignment = VerticalAlignment.Middle;\nheadingStyle.Background = new MindFusion.Drawing.SolidBrush(Color.FromArgb(45, 52, 72));\nheadingStyle.FontSize = 14;\nheadingStyle.FontBold = true;\nheadingStyle.TextColor = Color.FromArgb(228, 223, 221);<\/pre>\n
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\" };\n\nfor (int i = 0; i < cellHeaders.Length; i++)\t\n\tactiveSheet.Cells[i, 1].Data = cellHeaders[i];<\/pre>\nstring[] 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\"};\n\t\t\t\nRandom rand = new Random();\n\nfor (int i = 0; i < candidates.Length; i++)\n{\n\tactiveSheet.Cells[0, i + 2].Data = candidates[i];\n\n\tif (i % 4 != 0)\n\t\tactiveSheet.Cells[1, i + 2].Data = rand.NextDouble() * 65;\n\tif ((i+1) % 6 != 0)\n\t\tactiveSheet.Cells[3, i + 2].Data = rand.Next(101);\n\tif ((i + 2) % 5 != 0)\n\t activeSheet.Cells[4, i + 2].Data = rand.NextDouble() * 72;\n if ((i + 4) % 6 != 0)\n\t\tactiveSheet.Cells[6, i + 2].Data = rand.Next(101);\n}<\/pre>\nfor (int i = 3; i < 3 + candidates.Length; i++)\n{\n\tstring row = i.ToString();\n\tactiveSheet.Cells[\"C\" + row].Data = string.Format(\"=B{0}\/65\", row);\n\tactiveSheet.Cells[\"C\" + row].Style.Format = \"P2\";\n}<\/pre>\nactiveSheet.Cells[\"H\" + row].Data = string.Format(\"=Max(C{0} * 100, D{0}) + Max(F{0}*100, G{0})\", row);<\/pre>\nvar bioOlympicsCells = activeSheet.CellRanges[\"B3:B30\"];\nbioOlympicsCells.Validation.Type = ValidationType.Decimal;\nbioOlympicsCells.Validation.First = \"0\";\nbioOlympicsCells.Validation.Second = \"65\";\nbioOlympicsCells.Validation.Operator = ComparisonOperator.Between;\nbioOlympicsCells.Validation.AllowBlankCells = true;\nbioOlympicsCells.Validation.ShowInputMessage = true;\nbioOlympicsCells.Validation.InputMessage = \"Points mus be max 65.\";\nbioOlympicsCells.Validation.ShowError = true;\nbioOlympicsCells.Validation.ErrorTitle = \"Invalid input.\";\nbioOlympicsCells.Validation.ErrorMessage = \"Points must be non-negative and 65 or less.\";<\/pre>\n
<\/a><\/p>\n\/\/ Set some conditional formats\nfor (int c = 2; c < candidates.Length + 3; c++)\n{\n\t\/* set a background for the cell of biology competition\n\tthe condition is that its value is higher than the one\n\tfrom the biology olympics *\/\n\tvar biologyCompetitionCell = activeSheet.Cells[3, c];\n\n\tvar cf1 = biologyCompetitionCell.Style.ConditionalFormats.Add();\n\tcf1.Type = ConditionalFormatType.CellValue;\n\tcf1.Operator = ComparisonOperator.GreaterThan;\n\n\tvar biologyPercent = 0.0;\n\tif (activeSheet.Cells[1, c].Data != null)\n\t{\n\t\tif (Double.TryParse(activeSheet.Cells[1, c].Data.ToString(), out biologyPercent))\n\t\t\tcf1.Second = (biologyPercent\/65 * 100).ToString();\n\t}\n\telse\n\t\tcf1.Second = \"0\";\t\n\n\tcf1.Style.Background = new MindFusion.Drawing.SolidBrush(Color.FromArgb(255, 229, 238, 221));\n}<\/pre>\n
\nWe 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:<\/p>\n