Welcome to the new Spread.NET WinForms 11! If you've been waiting for a reason to upgrade or ready to try out Spread again, now is the time.

Download Spread.NET 11

In real estate, they say it’s “location, location, location,” and in component software, it’s “performance, performance, performance” – speed, memory footprint, and the API flexibility to implement surgical code for your requirements. Let's look at how Spread.NET 11 has improved.

Speed Performances in Spread.NET 11: New Calculation, Layout, and Style Engines

The new spreadsheet core models – implemented inside the new GrapeCity.CalcEngine.dll, GrapeCity.Spreadsheet.dll, and GrapeCty.SpreadSheet.Win.dll – are optimized for high-performance data access scenarios, especially for the most common spreadsheet scenarios of accessing large sets of data primarily organized by column and grouped by data type.

SUBTOTAL function calculations are orders of magnitude faster – 340 times faster than Spread for WinForms 10

These new internal data structures are integrated into the new Calculation, Layout, and Style engines. These changes allow you to maximize performance for use cases involving large spreadsheets with many formulas, sorting and filtering large ranges, and applying conditional formats – everything is faster! Many of these speed enhancements are really, really astounding. SUBTOTAL function calculations are orders of magnitude faster – for example, this code snippet calculating 100,000 Subtotal formulas is 340 times faster using version 11:

      int numOfFormula = 10;
      string formula = "Subtotal(109, $A$1:$A$99999)";
      fpSpread1.ActiveSheet.RowCount = 100000;
      for (int i = 1; i < numOfFormula; i++)
      {
        fpSpread1.ActiveSheet.SetFormula(i, i, formula);
      }

      Stopwatch s = new Stopwatch();
      s.Start();
      for (int i = 20; i < 2000;)
      {
        fpSpread1.ActiveSheet.Rows[i, i + 5].Visible = false;
        i += 50;
      }
      s.Stop();
      this.Text = s.ElapsedMilliseconds.ToString();
      for (int i = 20; i < 2000;)
      {
        if (fpSpread1.ActiveSheet.Rows[i, i + 5].Visible != false)
          Console.WriteLine(i.ToString());
        i += 50;
      }

Subtotals comparison

SUBTOTAL test Spread Win 10 Spread Win 11
First calculation (ms) 170679 501
Update cell value (ms) 930 0

Import and export performance are also greatly enhanced, especially for very large and complex files. For example, the test file “Appendix 01 Function List.xlsx”* saves to XLSX 308 times faster using version 11:

Import and export performance in milliseconds
Import and export memory performance in MB

Appendix 01 Function List.xlsx Spread Win 10 Spread Win 11
Time (ms) Load 447 520
Save 109947 357
Memory (MB) Load 4.469 7.064
Save 0.026 0.187

Sort and filter performance are equally as impressive, especially for very large tables and sheets. For example, the test file “FilterData.xlsx”* contains roughly 7200 rows, and test results show the first filter applies 5 times faster, and the second filter 8 times faster, using version 11:

Sort and filter performance in milliseconds
Sort and filter memory performance in MB

FilterData.xlsx Spread Win 10 Spread Win 11
Time (ms) Memory (MB) Time (ms) Memory (MB)
Import 2041 19.78 1614 7.53
1st open menu 3142 3583
1st filter 7832 1537
2nd open menu 8581 3597
2nd filter 13880 19.78 17091

Save Excel 2016 templates: Ten times faster

We've also enhanced the filter menu logic, so that opening the filter menu is slightly slower the first time (but not noticeably so) and much faster on subsequent openings – more than twice as fast in this case above.

Internal testing of a wide variety of template Excel spreadsheet files shows that really every spreadsheet is faster in Spread Win 11!

Here's a sample of Excel 2016 template spreadsheet files, available from Microsoft using File – New in Excel 2016, and performance numbers for Spread WinForms version 10:

Excel 2016 template spreadsheet files in milliseconds

Excel 2016 templates test Spread Win 10
Time (ms) Memory (MB)
Filename Load Save Load Save
Academic calendar (any… 385 1672 5.291 0.052
Any year calendar (1 mo… 390 13275 1.411 0.009
Any year one-month cal… 321 1263 0.17 0.007
Back to School Planner 2046 4877 7.286 0.018
Employee time sheet (… 313 15575 6.33 0.01
Expense trends budget 416 15660 5.101 0.005
Family budget (monthly) 325 4218 2.177 0.007
Gantt project planner 220 1296 0.207 0.006
Home inventory 218 2411 0.558 0.007
Inventory list with reor… 212 1263 0.254 0.006
Invoice with finance ch… 224 1395 1.53 0.006
Personal expenses calc… 249 3750 0.648 0.001
Project tracker 227 2395 0.487 0.006
Sales invoice tracker 383 9774 2.356 0.009
Service invoice 237 2546 0.539 0.006
Simple monthly budget 237 3483 0.401 0.002
Student schedule 434 2516 1.828 0.007
Time card 215 1301 0.21 0.006
To-do list for projects 333 3568 0.373 0.018
Travel expense log 312 1315 0.45 0.012
Warehouse inventory 323 3431 0.542 0.007
Avg: 381.90476 4618.286 1.816619 0.009857

Now here are those same Excel 2016 template spreadsheet files performance numbers for Spread WinForms version 11:

Spread Win 11
Time (ms) Memory (MB)
Filename Load Save Load Save
Academic calendar (any… 352 214 5.05 0.177
Any year calendar (1 mo… 388 1221 1.389 0
Any year one-month cal… 305 192 0.162 0.003
Back to School Planner 1579 404 5.368 0.063
Employee time sheet (… 328 216 0.16 0
Expense trends budget 378 272 1.892 0.053
Family budget (monthly) 426 2551 0.616 0.078
Gantt project planner 329 218 0.218 0
Home inventory 338 284 0.364 0.064
Inventory list with reor… 323 212 0.17 0.052
Invoice with finance ch… 327 217 0.168 0.053
Personal expenses calc… 347 222 0.415 0.044
Project tracker 336 215 0.296 0.057
Sales invoice tracker 361 236 0.799 0.108
Service invoice 339 235 0.455 0.057
Simple monthly budget 337 216 0.405 0.046
Student schedule 330 217 0.326 0.055
Time card 326 218 0.458 0.057
To-do list for projects 322 225 0.66 0.054
Travel expense log 326 206 0.178 0.055
Warehouse inventory 334 216 0.396 0.07
Avg: 401.4762 390.8095 0.949762 0.054571
Diff: 19.57143 -4227.48 -0.86686 0.044714
% Diff: 5% -1082% -91% 82%

Note that on average, these files load nearly as fast (20 ms slower is negligible), but they now save more than ten times faster!

Memory Footprint

There is an unavoidable tradeoff between speed and memory, and faster implementations usually require more memory – the cases described above note the marginal increased memory use in version 11 to get those great increases in speed. We've also focused on optimizing use cases with lots of styles, since most spreadsheets contain many borders, cell formats, conditional formats, and other style information; these improvements trade off a little bit of speed for great improvements in memory footprint – this code tests setting many redundant styles and shows how memory increase is negligible now:

private void TestStyleModel()  
     {  
       SheetView TestActiveSheet = fpSpread1.ActiveSheet;  
       ISheetStyleModel styleModel = TestActiveSheet.Models.Style;  
       StyleInfo styleInfo = new StyleInfo();  
       ICellType cellType;  
       int count = 100000;  
       TestActiveSheet.RowCount = count;  
       Stopwatch a = new Stopwatch();  
       GC.Collect();  
       a.Start();  
       cellType = new NumberCellType();  
       for (int modelrow = 0; modelrow < count; modelrow++)  
       {  
         styleModel.GetDirectInfo(modelrow, 1, styleInfo);  
         styleInfo.CellType = cellType;  
         styleInfo.BackColor = Color.Blue;  
         styleModel.SetDirectInfo(modelrow, 1, styleInfo);  
       }  
       GC.Collect();  
       a.Stop();  

       string msg = (string.Format("Set {0} items", count));  
       msg += (((double)(a.ElapsedMilliseconds)).ToString() + " ms");  
       msg += (((double)(GC.GetTotalMemory(true) / 1048576)).ToString() + " MB");  
       MessageBox.Show(msg);        
     }

Here's a visual look at the memory improvement.

Set 10,000 Style

Set 100,000 Style

Spread Win 10 Spread Win 11
Time (ms) Memory (MB) Time (ms) Memory (MB)
Set 10000 STYLE 33 4.18 44 0
Set 100000 STYLE 505 41.69 416 0

API

Spread.NET has the most extensive application programming interface and feature set of any spreadsheet component for .NET on the market today:

  • ALL the newest Excel 2016 chart types supported (Histogram, Pareto, Funnel, Waterfall, Box & Whisker, Treemap, Sunburst)
  • Conditional formats ad sparkline charts, now even faster!
  • NEW validators and extensive validation enhancements!
  • FormulaTextBox and NameBox for enhanced formula editing and auditing with visual indicators for references!
  • Grouping and outlines support!
  • Hierarchical sheets and built-in support for relational data sources!
  • Data binding to any .NET data source, including custom object sources!
  • Full-featured calculation engine with new support for array formulas!
  • All the basic, expecting user interface operations of a spreadsheet built-in, such as drag-drop, drag-fill, rearranging columns/rows, copy/paste, etc., including full touch support!
  • 100% backwards compatible with earlier releases!

Upgrading to Spread.NET 11

If you have projects using an older version of Spread for WinForms, and you're using Visual Studio 2010 (or later) and targeting .Net 4.0 or later, then you should upgrade.

Upgrading a project from any earlier version to the new release is very easy:

If you have any problems or issues upgrading, please let us know and we can help!

Notes About Backwards Compatibility, Breaking Changes, and Custom Sheet Model Classes

While we've made every effort to maintain backwards compatibility as much as possible, this degree of refactoring is simply not possible to accomplish without some breaking changes. We've kept these minimal and documented them here. There are also some obsolete classes which are no longer used internally, but they're still supported in the API for backwards compatibility; these are the old “sheet document model classes” that formed the core of the Spread.NET sheet document in earlier versions:

  • DefaultSheetDataModel
  • DefaultSheetAxisModel
  • DefaultSheetSelectionModel
  • DefaultSheetSpanModel
  • DefaultSheetStyleModel
  • SheetView.DocumentModels (all above + more)

If you have code using these classes, or using the interfaces on which those classes are based, and are using that code to assign a custom model object to a property in SheetView.DocumentModels, then that code should be phased out and refactored to remove the dependency on the custom model and implement the logic another way. If you have such code and require assistance porting that code to version 11, please let us know and we can help!

* “Appendix 01 Function List.xlsx” created by Norman J Harker and available here.

** “FilterData.xlsx” created by some anonymous tester at GrapeCity in Sendai, Japan and available here.