Spread.NET Windows Forms 12 introduces a new flat-style model with many new enhancements. Now, you can import and export Excel workbooks containing gradient fills and pattern fills in cell formats, and the new flat-style model renders much faster than before. The old hierarchical style model required composing styles to render each cell. The new flat-style model is much more efficient for both painting and Excel serialization, greatly improving performance.
These enhancements come at the cost of the old hierarchical style model. This model is still supported for backwards compatibility. When creating an instance of the spreadsheet control, you can specify in the code (or in the form designer) LegacyBehaviors.Style or LegacyBehaviors.All, and the control will use the old hierarchical style model. The new flat-style model will not be used (and gradient fills and pattern fills won't import as before). In that case, all old APIs will continue to operate as before, including inheriting of style properties from higher levels of the style hierarchy, as described here.
When creating the control without using LegacyBehaviors.Style, then the new flat-style model is enabled. In this case, many old APIs will operate differently than before, as they are wrapping the new flat-style model now instead of the old hierarchical style model. The old hierarchical style model supported setting properties at the Sheet level, the Column level, the Row level, the AlternatingRow level, and the Cell level, with each lower-level object overriding inherited settings from the higher-level object.
A style property such as BackColor, CellType, or Locked could be set for a higher-level object such as the Sheet or Column, then set on a lower-level object such as the Row or Cell; or the code could set those properties in the reverse order, setting the lower-level Row or Cell object's BackColor, CellType, or Locked, and then setting those properties on the higher-level Sheet or Column.
Using the old hierarchical style model, the ordering doesn't matter, because the resulting composite style is determined dynamically when the style levels are composed before painting.
Using the new flat-style model, the ordering of how style properties are set does matter. In fact, you must set style properties in the proper order using the new flat-style model, or you will not get the desired result. It is important to understand how the new flat-style model works differently in this respect.
Fortunately, adjusting to these changes is easy.
Simply follow these three steps:
1. Set worksheet style settings from the outside in
You should always set top-level worksheet style settings first, then column settings, then row settings, then cell settings, to ensure that the lower-level settings take precedence.
The new flat-style model does not keep default sheet settings at all (SheetView.DefaultStyle properties are mapped to the column default settings) and setting style properties on the column or row-level will operate like VSTO and apply those settings to all cells in the column or row.
For example, using the new flat-style model, setting Column.BackColor = System.Drawing.Color.Red will set the BackColor on the column style and then apply that column style to each cell in the column and all cells will become red. This happens even if the cell had previously had its BackColor set to some other color.
2. Use a Conditional Format to create alternating row styles instead of AlternatingRows
Since AlternatingRows isn't supported in the flat style model (using it has no effect), the application must use a different approach to create alternating row styles. A typical approach to this found in many common Microsoft Excel template spreadsheets uses conditional formats to apply a repeating sequence of styles to a range of rows in the worksheet.
An example of this can be found in the sample code for "Annual Financial Report" which can be found in the Control Explorer Showcase Demo samples and installed to Spread.Examples\GrapeCity.AnnualFinancialReport. In that sample, the code applies a conditional format to a range using a Formula Rule with the formula "=MOD(ROW(),2)=0" and applying a style with a light-gray background fill and lighter-gray border lines along the top and bottom of the cells:
IWorksheet worksheet = fpSpread1.AsWorkbook().ActiveSheet;
IFormatCondition formatCondition = worksheet.Cells["B16:L26"].FormatConditions.Add(FormatConditionType.Expression, formula1: "MOD(ROW(),2)=0");
formatCondition.Interior.ThemeColor = GrapeCity.Core.ThemeColors.Background2;
formatCondition.Borders[BordersIndex.Top].LineStyle = BorderLineStyle.Thin;
formatCondition.Borders[BordersIndex.Top].Color = Color.FromThemeColor(ThemeColors.Background2, -0.5);
formatCondition.Borders[BordersIndex.Bottom].LineStyle = BorderLineStyle.Thin;
formatCondition.Borders[BordersIndex.Bottom].Color = Color.FromThemeColor(ThemeColors.Background2, -0.5);
3. Use the new core APIs directly
The new version 12 release introduces the new GrapeCity.Spreadsheet Namespace: a totally new API layer based on VSTO object model. This new API layer enables many new features introduced in version 12. Diving right into this new API layer and using the new IRange, IWorkbook, IWorksheet, and dozens of other new interfaces will greatly enhance the performance of your code, and using the new API to apply styles will enable your code to take full advantage of the new built-in style support, pattern-fill and gradient-fill, and themes support.
To apply styles, you can use the new IRange.ApplyStyle method to specify a BuiltInStyle – here is sample some code from the Spread.NET Control Explorer demo sample for Features – Worksheet – Cell Styles:
IWorkbook wb = fpSpread2.AsWorkbook();
IWorksheet ws = wb.ActiveSheet;
fpSpread2_Sheet1.RowCount = 50;
// set styles
ws.Cells[1, 0].ApplyStyle(BuiltInStyle.Normal);
ws.Cells[1, 1].ApplyStyle(BuiltInStyle.Bad);
ws.Cells[1, 2].ApplyStyle(BuiltInStyle.Good);
ws.Cells[1, 3].ApplyStyle(BuiltInStyle.Neutral);
IWorksheet has properties for Cells, Columns, and Rows that operate directly on the flat-style model when applying or changing styles using either IRange.ApplyStyle method overload or the IRange.Style property. When you use the flat-style model together with the SheetView properties for Cells, Columns, and Rows to set style properties such as BackColor, Font, and ForeColor, then those properties are wrapping the settings in the IStyle for the IRange.Style returned by the corresponding IWorksheet property, rather than wrapping the old style sheet model.
The new GrapeCity.Spreadsheet APIs will be covered more in future posts – download your free 30-day trial version of Spread.NET 12 today and give this awesome new API a try!
Sean Lawyer