Spread for WinForms 11 performance improved to 300 times faster!

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;
      }

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:

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:

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:

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

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.

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:

• Download the new Spread WinForms 11.
• Install the new Spread WinForms 11 as described here.
• Follow the project upgrade steps as described here.

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.