One way to improve the performance of the component, if there are changes to many cells, is to hold or suspend the repainting until all the changes are done. By holding the repainting (suspending the layout) while all the changes and recalculations are done, and then resuming the layout and repainting all the cells, the component can save a lot of time and still deliver a refreshed interface to the user.

Layout Objects

A layout is an object that stores calculated values (mostly widths and heights of cells, spans, and viewports) used for painting the component in its current state. This may include how many viewports there are, what the top left cell in each viewport is, how big each column and row is and how many are currently visible in each viewport, etc. The purpose of the layout object is to optimize painting of the component by storing off the calculated layout values used during painting and reusing them each time the component repaints instead of recalculating them each time. When a change is tracked that requires the layout object to be regenerated, it is discarded and a new one is calculated by the paint code. The layout objects are not part of the public API, but they cache all of the layout information required to paint the sheet, like the column widths, row heights, cell spans, cell overflows and the rectangles of cell notes that are always visible (Cell.NoteStyle = NoteStyle.StickyNote).

Suspending the Layout Logic

To improve performance, you can suspend the layout, which stops the layout object from being updated and thus the component does not spend any time making calculations for repainting until the layout is resumed. Two methods accomplish this, the SuspendLayout and ResumeLayout methods in the FpSpread class. Be sure to use the two methods together within a particular scope of operation, otherwise a problem may occur with the layout being suspended and not able to resume.

The SuspendLayout method prevents the component from recomputing the layout of columns, rows, and cells when changes are made to the sheet. If you are making lots of changes to the sheet in a block of code, using SuspendLayout prevents the component from doing redundant intermediate recalculations of the layout objects as each change is made, and using ResumeLayout (true) recomputes the layout once after all of your changes are made. That increases performance greatly, but there is more you can do depending on what features your sheets require.

Example

Dim st As System.DateTime = System.DateTime.Now
With FpSpread1
.SuspendLayout()
.Sheets(0).ActiveSkin = FarPoint.Win.Spread.SheetSkin.Load("d:\temp\skin.skn")
.ResumeLayout()
End With
MsgBox("Duration (ticks)=" & System.DateTime.Now.Ticks - st.Ticks)
private void PerformInitialSetup()
    {
      // (0) used for property labels.
      _propertyLabelOrdinal = 0;
      // Set up the spread
      spread.SuspendLayout();
   
      SheetView sheet = spread.ActiveSheet;
      sheet.Models.ColumnHeaderData = new HeaderDataModel(spread, _orientation);
      sheet.Models.Style = new SheetStyleModel(_orientation);
      spread.NamedStyles = _config.Styles.NamedStyles;

      // Insert initial data
      sheet.Columns.Count = 2;
      foreach (Block block in _config.Blocks)
      {
        // Insert any leading blank rows
        if (block.SpaceBefore > 0) sheet.Rows.Add(sheet.Rows.Count, block.SpaceBefore);
        if (block.DealProperties != null)
        {
          int rowIndex = sheet.Rows.Count;
          sheet.Rows.Add(rowIndex, block.DealProperties.Count);
          foreach (DealProperty property in block.DealProperties)          {
            sheet.Cells[rowIndex, _propertyLabelOrdinal].Value = property;
            sheet.Rows[rowIndex].StyleName = block.StyleName;
            rowIndex++;
           }
        }
   
        // Insert any trailing blank rows
        if (block.SpaceAfter > 0) sheet.Rows.Add(sheet.Rows.Count, block.SpaceAfter);
     }
   
     // Set initial styles
    sheet.Columns[_propertyLabelOrdinal].StyleName = "dealPropertyLabels";
   
    spread.ResumeLayout();
   }

Suspended Notification

If the layout is suspended without a corresponding resume method in the same scope and an exception occurs, the component displays a notification "layout is suspended". If the state of the component changes such that the layout object contains invalid data (mostly the wrong number of something) then an exception can result when the component tries to paint with the invalid layout data. The notification is shown whenever there is an unhandled exception that occurs during the painting of the control, and the layout is suspended when the exception occurred.

This should only happen when the layout is suspended with SuspendLayout, and then changes are made to the component state and the component somehow made to paint again with an invalid layout object. It is possible that there could be an exception that causes this message to show which is not related to the layout being suspended, for example, if an exception is thrown by a custom cell type object during a call to IRenderer.PaintCell. Any changes made to the component state could trigger layout recalculation, but not all changes do so. Changes that rearrange rows or columns, such as sorting and filtering, definitely require it, but setting text only does it under certain circumstances, for example, when you have AllowCellOverflow turned on. If the layout is suspended, but the Spread is able to paint using old layout information without any problems, then the Spread may act in unexpected ways, for example, it will not scroll when you try, but the notification is not displayed.

Other Performance Improvements

Beyond the suspending (and subsequent resuming) of the layout logic, there are few other things you can consider that may improve performance.

If you are not using sticky notes, then you can set AutoUpdateNotes to false to prevent the component from checking for sticky notes that need to be made visible or hidden or moved. If you are using AllowCellOverflow, turning that off increases the performance of the layout calculations, because that feature requires lots of text width calculations on each change to the data in a cell. If you are using formulas, setting AutoCalculation to false before your updates and then setting it back to true and calling Recalculate afterwards eliminates redundant intermediate recalculations of your formulas.

Some other things you could do would be to reduce the size of the control or show fewer columns and rows at once (the layout objects only calculate the visible portion of the sheet), or implement your own sheet model objects (like your own data model object implementing ISheetDataModel) which remove features that you do not require (for example, if you do not require data binding, the data binding interfaces do not need to be implemented).

Using the Methods Together

So a rough outline of the code would be:

     SuspendLayout

                       insert your code here

     ResumeLayout

The methods are intended for temporarily ignoring changes to the layout so that many changes can be made without performing the redundant layout recalculations between each change. While layout calculation is suspended, event handlers tracking changes to the component are not able to recalculate the layout and the paint code does not access the new layout. For a nested loop that makes a change to every cell, say changing a value in each cell, this is a case that would definitely benefit from suspending the layout before and resuming the layout afterward.

Remember, do not use these methods unless the changes are such that the performance can benefit from the layout being suspended temporarily.

Remember, always use the two methods together in the same scope, otherwise the component might not paint correctly if SuspendLayout is called without a matching call to ResumeLayout in the same scope.

Example

This example shows the two methods used together within a specific scope around code that changes the cells. This suspends the repainting of the Spread component while changing the color of the cells and then resumes the repainting.

fpSpread1.SuspendLayout();
fpSpread1.Sheets[0].Cells[0, 0, 499, 499].BackColor = Color.White;
fpSpread1.ResumeLayout(true);

FpSpread1.SuspendLayout()
FpSpread1.Sheets(0).Cells(0, 0, 499, 499).BackColor = Color.White
FpSpread1.ResumeLayout(True)

Copyright © GrapeCity, inc. All rights reserved.