Click here to Skip to main content
15,860,972 members
Articles / Multimedia / GDI+

Free .NET Spreadsheet Control

Rate me:
Please Sign up or sign in to vote.
4.93/5 (174 votes)
23 Jan 2014LGPL321 min read 281.5K   41.5K   288   14
.NET Spreadsheet Control written in C#. Supports Cells Merge, Border Style, Pattern Background Color, Data Format, Freeze, Formula, Macro and Script execution.

Index

Introduction

This article is about a .NET Spreadsheet Control written in C#. Supports Cells Merge, Border Style, Pattern Background Color, Data Format, Freeze, Formula and Script execution.

Image 1
 
Image 2

What's New!

New feature: Group and Outline

Image 3

New feature: Cell Body

Image 4

Other changes in 0.8.5:

  1. Reduce DLL references. Now two unvell.common.dll and unvell.UIControls.dll are not needed. All source code in these DLL has been merged into ReoGrid, and become to open source.
  2. For DLL merging, namespace changed to 'unvell' from 'Unvell'
  3. Many Cell events, like CellMouseDown and FocusPosChanged are now available
  4. Performance improvement and several bug fixed

Snapshots

Image 5
Data Format
Image 6
Border Setting Dialog
(Available in ReoGrid Editor Library)
Image 7
Data Format Setting Dialog
(Available in ReoGrid Editor Library)
Image 8
Macro and Script Execution
Image 9
Customized Control Appearance
Image 10
Demo Form

Usage Guideline 

Install

Add Control by Form Designer 

  1. Right-click on Toolbox panel, click 'Choose Items...' 
  2. Choose '.NET Framework Components' tab 
  3. Click 'Browse...' button
  4. Choose 'Unvell.ReoGrid.dll' in open file dialog 
  5. Click 'ok' to close the dialog 
  6. Choose the Control appeared in Toolbox and drag it into designer, control added looks like:
Image 11
Image 12

Add Control by programming

Add following required files into reference list for different distribute library:

  • Minimum - Only the Grid Control and core features support 
  • Editor - Grid Control, Core features, and GUI Editor support 
  • Full - Grid Control, Core features, GUI Editor and Script Execution 

Table of Library References:

DLL Name Desc Minimum Editor Full
unvell.ReoGrid.dll ReoGrid Control, Core features Yes Yes Yes
unvell.ReoGridEditor.dll Controls and forms for ReoGrid Editor Yes Yes
unvell.ReoScript.dll .NET script language engine Yes
unvell.ReoScriptEditor.dll Controls and forms for script editing Yes
Antlr3.Runtime.dll [3rd-party] ANTLR grammar tool runtime [^] Yes
FastColoredTextBox.dll [3rd-party] Powerful and fast colored highlight text editor [^] Yes

Create instance

Make following two Dlls are added into reference list of proejct:

C#
unvell.ReoGrid.dll   // this control

Import namespaces:

C#
using Unvell.ReoGrid;

Create instance for control:

C#
// create instance
var grid = new Unvell.ReoGrid.ReoGridControl()
{
  // put to fill the parent control
  Dock = DockStyle.Fill,
};

this.Controls.Add(grid);

API Usage

Three ways to call control:

  • Call methods of Control - call methods of control directly like SetCellData.
  • Using Actions - perform operations by doing action gives the ability to undo operations.
  • Run Script - powerful macro and script execution like VBA in Excel can also change the behavior of control.

Action mechanism provides the ability to support Do/Undo/Redo/Repeat actions for a lot of operations. Operations raised from End-user should better to be performed by doing actions.

Call Methods of Control

C#
grid.SetCellData(new ReoGridPos(2,1), "hello world");

Using Actions

1. Import this namespace:

C#
using Unvell.ReoGrid.Actions;

2. Invoke 'DoAction' method of grid control, pass action as argument

C#
grid.DoAction(new RGSetCellDataAction(new ReoGridPos(2, 1), "hello world"));

To undo or redo last action:

C#
grid.Undo();
grid.Redo();

To repeat last action to apply for another range:

C#
grid.RepeatLastAction(new ReoGridRange(2, 3, 5, 5));

Run script

Make sure the modules that used to support the Script Execution are all referenced to your project. See Library References above.

Import the namespace:

C#
using Unvell.ReoScript;

Run Script:

C#
string script = "grid.getCell(2,1).data = 'hello world';";
grid.RunScript(script);

Cells

Change rows and columns

C#
grid.SetRows(6);
grid.SetCols(5); 
Image 13 Some parameters in ReoGrid:
InitialMinMax
Number of Rows20011,048,576
Number of Columns100132,768
Row Height200No limited
Column Width700No limited

To get number of row and number of columns:

C#
int rows = grid.RowCount;
int cols = grid.ColCount;

Change height and width

Set both height and width to 10 pixels

C#
grid.SetRowsHeight(0, grid.RowCount, 10);
grid.SetColsWidth(0, grid.ColCount, 10); 
Image 14

Get height and width

C#
int height = GetRowHeight(int row);
int width = GetColumnWidth(int col);

Set cell data

C#
grid[5, 2] = "hello world"
	grid["A2"] = 100;
	grid["C3"] = new object[,] { {1,2,3}, {4,5,6} }; // set to range
	grid[new ReoGridPos("B10")] = new MyClass(); // need ToString()
Image 15
Auto data format

By default the data-format for all cells is General, once the cell data has been changed, ReoGrid try to test the format of data and choose a default cell formatter. If data type like number or percent was detected, the text horizontal alignment will be set to Right automatically.

To disable auto-format-check, set Edit_AutoFormatCell settings of control to false.

C#
grid.SetSettings(ReoGridSettings.Edit_AutoFormatCell, false);

Merge and Unmerge

Merge range start at (2,1) and the number of rows is 3, number of columns is 4:

C#
grid.MergeRange(new ReoGridRange(2, 1, 3, 4));
Image 16

Unmerge range:

C#
grid.UnmergeRange(new ReoGridRange(0, 0, 10, 10));

Styles

Always set style to range even just one cell needed. ReoGrid uses an enumerate value called PlainStyleFlag to determine what styles should be handled.

Background Color

Set background to range (2,1,3,4):

C#
grid.SetRangeStyle(new ReoGridRange(2, 1, 3, 4),
  new Unvell.ReoGrid.ReoGridRangeStyle
  {
    Flag = PlainStyleFlag.FillColor,
    BackColor = Color.SkyBlue,
  });
Image 17

Background Pattern Color

Set background pattern color to entire grid:

C#
grid.SetRangeStyle(ReoGridRange.EntireRange, 
  new Unvell.ReoGrid.ReoGridRangeStyle {
    Flag = PlainStyleFlag.FillPattern | PlainStyleFlag.FillColor,
    BackColor = Color.LightYellow,
    FillPatternColor = Color.SkyBlue,
    FillPatternStyle = System.Drawing.Drawing2D.HatchStyle.DiagonalBrick
  }); 
Image 18

Text color

C#
grid.SetRangeStyle(new ReoGridRange(1, 1, 1, 1), new ReoGridRangeStyle()
{
  Flag = PlainStyleFlag.TextColor,
  TextColor = Color.Red,
});
Image 19

Text alignments

Image 20

Set cell's text horizontal alignment to Center:

C#
grid.DoAction(new RGSetRangeStyleAction(new ReoGridRange(0, 0, 3, 3),
  new ReoGridRangeStyle
  {
    Flag = PlainStyleFlag.HorizontalAlign,
    HAlign = ReoGridHorAlign.Center,
  })); 
Image 21

Text wrap

Set text wrap mode to 'WordWrap': (default is no-wrap)

C#
grid[1, 1] = "How many beers can you drink?";
grid.SetRangeStyle(new ReoGridRange(1, 1, 1, 1), new ReoGridRangeStyle()
{
  Flag = PlainStyleFlag.TextWrap,
  TextWrapMode = TextWrapMode.WordBreak,
});
Image 22

Font

Change font name and size:

C#
grid.SetRangeStyle(new ReoGridRange(1, 1, 1, 1), new ReoGridRangeStyle()
{
  Flag = PlainStyleFlag.FontSize | PlainStyleFlag.FontName,
  FontName = "Arial",
  FontSize = 20,
});
Image 23

Remove Style

Always remove style from range even single cell needed. By specifying PlainStyleFlag to set what styles to be removed.

Remove background color from specified range:

C#
grid.RemoveRangeStyle(new ReoGridRange(2, 2, 3, 3), PlainStyleFlag.FillAll);

Borders

Set bold outline border around specified range:

C#
grid.SetRangeBorder(new ReoGridRange(2, 1, 5, 3), ReoGridBorderPos.Outline,
  new ReoGridBorderStyle
  {
    Color = Color.Magenta,
    Style = BorderLineStyle.BoldSolid,
  }); 
Image 24

By pass different ReoGridBorderPos flag to change where to set borders:

C#
grid.DoAction(new RGSetRangeBorderAction(new ReoGridRange(2, 1, 5, 3), 
  ReoGridBorderPos.All,
  new ReoGridBorderStyle
  {
    Color = Color.Red,
    Style = BorderLineStyle.Dashed
  }));  // set by action
Image 25

To remove borders:

C#
grid.RemoveRangeBorder(new ReoGridRange(2, 1, 5, 1), ReoGridBorderPos.All);

or set border's color to Color.Empty to remove borders

C#
grid.SetRangeBorder(new ReoGridRange(2, 1, 5, 3), ReoGridBorderPos.All,
  new ReoGridBorderStyle
  {
    Color = Color.Empty
  };

Method GetRangeBorder can be used to get border info from a range:

C#
ReoGridRangeBorderInfo GetRangeBorder(ReoGridRange range);

Data Format

Import namespace to use data format feature:
C#
using Unvell.ReoGrid.DataFormat;

Specify cell data format by using SetRangeDataFormat method.

C#
SetRangeDataFormat(ReoGridRange range, CellDataFormatFlag flag, object argument)
e.g.
C#
grid.SetRangeDataFormat(ReoGridRange.EntireRange, CellDataFormatFlag.Number,
  new NumberDataFormatter.NumberFormatArgs()
  {
    // decimal digit places 0.1234
    DecimalPlaces = 4,
					
    // negative number style: (123) 
    NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
 
    // use separator: 123,456
    UseSeparator = true,
  });

// or set by using action
RGSetRangeDataFormatAction(ReoGridRange range, CellDataFormatFlag format,
  object dataFormatArgs);
Test:
C#
grid[1, 1] = 12345;
grid[1, 2] = 12345.67890;
grid[2, 1] = -1234;
grid[2, 2] = -1234.56789;
Image 26

Table of built-in data formatter:

Type CellDataFormatFlag Argument
Number CellDataFormatFlag.Number NumberDataFormatter.NumberFormatArgs
DateTime CellDataFormatFlag.DateTime DateTimeDataFormatter.DateTimeFormatArgs
Percent CellDataFormatFlag.Percent PercentDataFormatter.PercentFormatArgs
Currency CellDataFormatFlag.Currency CurrencyDataFormatter.CurrencyFormatArgs
Text CellDataFormatFlag.Text None

Cell Body

ReoGrid provides an interface that support you to create own customize cell, like to create a normal Windows Control.

Image 27

Cell Body

A custom cell body must inherit from CellBody or implement from ICellBody. The CellBody class provides the following virtual methods that can be override to handle user's operations:

C#
public interface ICellBody
{
  void OnSetup(ReoGridControl ctrl, ReoGridCell cell);
  
  Rectangle Bounds { get; set; }
  void OnBoundsChanged(ReoGridCell cell);
  
  bool AutoCaptureMouse();
  bool OnMouseDown(RGCellMouseEventArgs e);
  bool OnMouseMove(RGCellMouseEventArgs e);
  bool OnMouseUp(RGCellMouseEventArgs e);
  bool OnMouseEnter(RGCellMouseEventArgs e);
  bool OnMouseLeave(RGCellMouseEventArgs e);
  void OnMouseWheel(RGCellMouseEventArgs e);
  
  bool OnKeyDown(KeyEventArgs e);
  bool OnKeyUp(KeyEventArgs e);
  
  void OnPaint(RGDrawingContext dc);
  
  bool OnStartEdit(ReoGridCell cell);
  object OnEndEdit(ReoGridCell cell, object data);
  void OnGotFocus(ReoGridCell cell);
  void OnLostFocus(ReoGridCell cell);
  
  object OnSetData(object data);
  object OnSetText(string text);
  object GetData();
}

Owner Drawing

By overriding the OnPaint method to draw anything in cell:

C#
private class MyCellBody : CellBody
{
  public override void OnPaint(RGDrawingContext dc)
  {
    // draw an ellipse inside cell, 'Bounds' is the cell's boundary
    dc.Graphics.DrawEllipse(Pens.Blue, base.Bounds);
  }
}
Image 28

Handle mouse and keyboard events

By overriding such as OnKeyDown and OnMouseDown methods to handle the keyboard and mouse events.

C#
public override bool OnMouseDown(RGCellMouseEventArgs e)
{
  // translate cursor position to percent
  int value = (int)Math.Round(x * 100f / (Bounds.Width));

  // update cell's data, but do not set the data to cell directly, call methods of control instead
  grid.SetCellData(e.CellPosition, value);
}
Image 29

Data bind by using formula

It is possible to bind two cell's data by using formula like this:

C#
grid["C7"] = "=E7";
Image 30

Events

ReoGrid provides many events, for detailed list please check here.

Example: Set editable range by handling event

Only allow text editing in specified range:

C#
var editableRange = new ReoGridRange(3,1,2,3);

grid.SetRangeBorder(editableRange, ReoGridBorderPos.Outline, ReoGridBorderStyle.SolidBlack);  

grid[2, 1] = "Edit only be allowed in this range:";
grid.BeforeCellEdit += (s, e) => e.IsCancelled = !editableRange.Contains(e.Cell.GetPos()); 
Image 31

Pick range

PickRange method usage:

PickRange(Func<...,bool> handler, Cursor)

where

  • handler is an anonymous function that is called when range picked by user. Return true from this handler if range is expected. Return false will cause continue to pick range until return true from this func.
  • Cursor is the cursor displayed when user picking range. Cursor will be restored to default if picking has been terminated.

e.g.

grid.PickRange((inst, range) =>
{
  MessageBox.Show("User picked range: " + range.ToString());
  return true;
}, Cursors.Hand); 
Image 32

Freeze

To set freeze started at row 5:

C#
grid.FreezeToCell(5, 0);
Image 33

To unfreeze:

C#
Unfreeze

To get freeze position:

C#
ReoGridPos pos = grid.GetFreezePos();

Zoom

C#
grid.ZoomIn(); // +0.1f scale factor
grid.ZoomOut(); // -0.1f scale factor
grid.ZoomReset(); // reset to 1f scale factor 
Image 34

Set scale factor

C#
grid.SetScale(2f, Point.Empty);
Image 35

Group and Outline

Outlines can attached to rows or columns, for example:

Image 36

To add outline:

C#
grid.AddOutline(RowOrColumn.Row, 3, 5);      // group rows from 3, number of rows is 5
grid.AddOutline(RowOrColumn.Column, 5, 2);   // group columns
Note the following exceptions may caused:
  • OutlineIntersectedException - when the outline to be added, intersects with another one which was added into the sheet.
  • OutlineAlreadyDefinedException - when the outline to be added, is same as another one which was already added into the sheet.
  • OutlineTooMuchException - when number of levels of outline reached the maximum available levels 10.

And these functions used to collapse and expand outlines:

C#
grid.CollapseOutline(RowOrColumn.Row, 3, 5);
grid.ExpandOutline(RowOrColumn.Row, 3, 5);

And to remove or clear outlines:

C#
grid.RemoveOutline(RowOrColumn.Row, 3, 5);
grid.ClearOutlines(RowOrColumn.Row);

Auto Behavior: Collapse, Expanding and Removing

If an expanded outline, which attaching to (ending at) the row has been hidden, or height of the row set to be zero, the outline will be collapsed automatically. If the collapsed outline which grouping some rows are all unhidden, or height of rows is not zero, the outline will be expanded automatically. This behavior cannot be disabled. If an outline, which attaching to the rows or columns are all removed, this outline will be removed automatically.

Outline with Freeze

Outline can work with freeze, and maximum number of lines is 9, for example:

Image 37

Settings

Hide scroll bars and headers

C#
grid.SetSettings(ReoGridSettings.View_ShowScrolls | ReoGridSettings.View_ShowHeaders, false);
Image 38

Hide grid lines

C#
grid.SetSettings(ReoGridSettings.View_ShowGridLine, false); 
Image 39

Disable adjust height of row and width of column by mouse

C#
grid.SetSettings(ReoGridSettings.Edit_AllowAdjustRowHeight | ReoGridSettings.Edit_AllowAdjustColumnWidth, false); 

Formula

Text of cell start with '=' will be processed as formula automatically. Cells with formula will be updated automatically when data of referenced cells have been changed.

Image 40

Value of formula will be calculated and displayed when formula has been changed.

Image 41

Data of cell start with (') will be ignored as formula.

Script Execution

ECMAScript-like script execution is supported by ReoGrid control. However, you can use the minimum build package, it does not provide script feature. Please check the reference table above.

Set data by script

variable 'grid' is a global object points to grid control. script e.g.:

JavaScript
// get cell at pos(0,0) and set data of cell to 'hello world'
grid.getCell(0,0).data = 'hello world'
Image 42

ReoGrid uses ReoScript to implement the script execution. ReoScript is ECMAScript-like script language engine for .NET Application, more details available at reoscript.codeplex.com.

handle data changed event

Image 43

Cancel operations by returning false

Event handled in script returns false to notify control cancelling current operations.

C#
grid.cellBeforeEdit = function(cell) {
  if(cell.pos.row == 2 && cell.pos.col == 3) {
    return false;
  }
};

More details about script execution available at here.

Customize Function

Add customize function in C#

C#
grid.Srm["sqrt"] = new NativeFunctionObject("sqrt", (ctx, owner, args) =>
{
  if (args.Length < 1)
    return NaNValue.Value;
  else
    return Math.Sqrt(ScriptRunningMachine.GetDoubleValue(args[0], 0));
});
Use in formula:
Image 44
Result:
Image 45

Add customize functions by running script

Add functions to script object will make it available in both formula and script.

Image 46
Use in formula:
Image 47
Result:
Image 48

Run script to add functions in C#

It is also possible to run the script to create functions in C#: (e.g. lambda expression)

C#
grid.RunScript("script.myfunc = data => '[' + data + ']';");

XML Serialization

Save into file or stream:

C#
grid.Save(@"d:\path\grid.rgf");
grid.Save(stream);  // save into stream

Grid data saved in xml as below:

Image 49

Load from file or stream:

C#
grid.Load(@"d:\path\grid.rgf");
grid.Load(stream);  // load from stream

Control Appearance

C#
// create control style instance with theme colors
ReoGridControlStyle rgcs = new ReoGridControlStyle(Color.White, Color.DarkOrange, false);

// set text color to 'white'
rgcs.SetColor(ReoGridControlColors.GridText, Color.White);

// apply appearance style
grid.SetControlStyle(rgcs);

The control appearance will be changed to:

Image 50

Reset Control

Reset control to default status.

C#
grid.Reset();

Implementations

Sheet data management

Paging

Using a default two-dimensional array to manage the cells data will take many lot of memory, even there are no elements be initialized. One idea is to allocate memory only for the used arrays which have attached elements. And reduce the memory usage by separating one large array to a lot of small arrays, that is paging.

Image 51

Before finding a cell, it is need to find its page firstly. For an one-dimensional array to calculate its page-index and cell-index:

C#
int pageIndex = index / pageSize;
int cellIndex = index % pageSize;

If the size of page is power of 2, it will be more effective to use bit-computing instead of division.

Tree

By combining some pages, link them into a larger page as parent index-page, can help to decrease the memory usage.

Image 52

All the cells data will be attached to the most bottom of index-pages, for example:

Image 53

Any never been accessed index-pages, and the cells will be null, they do not use memory.

Image 54

To access a cell, according to the depth of tree, an index of cell will be calculated two times (the depth of tree is 2):

Image 55

Smaller size of page will take fewer memory, but if they are too small, a more deeper tree is necessary to be created in order to get larger capacity of array. The deeper tree will spend more time during once cell accessing. Consider to get a good balance between page-size and tree-depth is most important. Currently ReoGrid uses the following values of page size:

  • Number of rows: 128
  • Number of columns: 32
  • Depth of tree: 3
So the physical maximum supported capacity of array is:
  • Number of rows: 128^3 = 2,097,152
  • Number of columns: 32^3 = 32,768

Although the row capacity of cells can be 2,097,152, but there is need to set a limit on rows to 1,048,576 in order to fit the data struct of header of rows.

Code implementation

To calculate index, get and set array elements by paging-indexed two-dimensional array in ReoGrid, the getter and setter looks like:

C#
get
{
  Node node = root;

  // MaxDepth is 3
  for (int d = MaxDepth - 1; d > 0; d--)                 
  {
    // RowBitLen is 7 when row page size is 128
    int r = (row >> (RowBitLen * d)) % RowSize;    
    // ColBitLen is 5 when col page size is 32
    int c = (col >> (ColBitLen * d)) % ColSize;

    node = node.nodes[r, c];

    if (node == null) return default(T);
  }

  // RowSize is 128, and ColSize is 32
  return node.data[row % RowSize, col % ColSize];        
}
C#
set {
  Node node = root;

  for (int d = MaxDepth - 1; d > 0; d--) {
    // RowBitLen is 7 when row page size is 128
    int r = (row >> (RowBitLen * d)) % RowSize;    
    // ColBitLen is 5 when col page size is 32
    int c = (col >> (ColBitLen * d)) % ColSize;    

    Node child = node.nodes[r, c];

    if (child == null) {
      if (value == null) {
        return;
      } else {
        child = node.nodes[r, c] = new Node();
        if (d > 1) { 
	        child.nodes = new Node[RowSize, ColSize];
        }
      }
    }

    node = child;
  }

  if (node.data == null) {
    if (value == null) {
	    return;
    } else {
	    node.data = new T[RowSize, ColSize];
    }
  }

  node.data[row % RowSize, col % ColSize] = value;
}

What to be improved

There is an issue, also a performance issue, is ReoGrid doesn't know the boundary of data. Think about when user selecting entire row or column, and when ReoGrid is requested to calculate the sum value. Only one thing to do is get the data from cells one by one, and calculate their sum, it will take very long time if there is huge sheet.

Solution 1: Add valid data boundary to each pages?

Something like adding the data boundary to identify the valid data starting at where, and ending at where, seems can improve the iteration speed. The code like as below:

C#
double sum = 0;
foreach(var page in sheetData) {
  for(int r = page.minRow; r <= page.maxRow; r++) {
    for(int c = page.minCol; c <= page.maxCol; c++) {
      sum += page[r, c].data;
    }
  }
}
Solution 2: Valid row and valid column identification

Add flag to row headers and column headers to identify whether this row or column has any data. The code like as below:

C#
double sum = 0;

for (int r = 0; r < sheet.RowCount; r++) {
  if(sheet.IsEmptyRow(r)) continue;

  for (int c = 0; c < sheet.ColCount; c++) {
    if(sheet.IsEmptyCol(c)) continue;

    sum += sheet[r, c].data;
  }
}
Other solutions?

An effective approach to memory management at following points is need improved, if you have any answeres, please let me know.

  • Fewer memory usage
  • Faster access speed
  • Efficient data aggregation to calculate sum, avg, count and etc.

Performance Test

Monday, January 13, 2014 (ReoGrid version 0.8.5)
test itemtotal cycleselapsed (ms)one cycle (μs)memory usage (MB)
Sequential Read & Write (1000x1000)2,000,0001300.0659.6
Sequential Read & Write (1,000,000x100)20,000,00012970.0648546
Interval Read & Write
(10,000x10,000, Interval: 100)
20,000814.0535
Random Read & Write20,00026313.1573
Iterate Over Empty Range200,000,0001500.00075-
Fill 1,000,000 Rows1,000,0001100.11145
Sum 1,000,000 Rows (Iterator)1,000,0001130.113-
Sum 1,000,000 Rows (For)1,000,000500.05-

Test cases available in TestCases solution of ReoGrid project.

Cell data updating

When user does edit on cell, or cell need to be updated with new data, ReoGrid perform the following actions during cell data updating.

Image 56

Iteration - Search in cells

Since the index-pages, cells what never been accessed will be null, it is possible to skip these page and cells, skiping process will help to improve the iteration speed. Currently ReoGrid performs the skipping process when following cases:

Image 57
  • Empty cells - A cell without any data and own styles set, it will be skipped during iteration
  • Empty index-pages - An index-page without any cells attached, it will be skipped during iteration
  • Invalid cells - A cell merged by another cell, it will become invalid and skipped by during iteration
Code implementation of skipping process:
C#
for (int r = startRow; r < r2; r++) {
  for (int c = startCol; c < c2; ) {
    if (IsPageNull(r, c)) {

      // skip current page (+= remains)
      c += (ColSize - (c % ColSize));

    } else {

      T obj = this[r, c];
      int cspan = 1;
      if (!ignoreNull || obj != null)
      {
        cspan = iterator(r, c, obj); // iterator callback 
        if (cspan <= 0) return;
      }
       
      // skip merged cells when cspan > 1
      // cspan returned by high level iterator
      c += cspan;
    }
  }
}

Merge implementation

The points was considered during designing the merge implementation

  • Faster way to find boundary of merged range (when user clicked inside a cell) - When control receives mouse events, mouse-down or mouse-over, a full entire merged range should be selected, rather than a single cell. It is need to check whether the cell which has been clicked, is a part of merged range, and if it is, how to find the range quickly.
  • Grid lines drawing should be skipped inner a merged range - By default a spreadsheet will draw the horizontal and vertical grid lines to fill the view-port which displayed to user. But the grid lines should be skipped inside merged ranges, although the merged range displayed without any back color filled.
Image 58

Two cells are defined as special cells, one is called merge-start-cell, another one is merge-end-cell. They are defined at the position top-left and bottom-right of the merged range. All of cells in this range will be initialized instantly, and they will keeping two pointers reference to the merge-start-cell and merge-end-cell.

Image 59

When user clicked inside any cells which is contained by a range, now it is possible to find the range quickly by two pointers, merge-start-cell and merge-end-cell. Another thing is need to skip drawing grid lines inside a merged range, it is also possible to calculate how many cells should be skipped by this two references.

C#
int skips = merge-end-cell.col - merge-start-cell.col;
Image 60

Need to be improved

Initialize the inner cells to keep references take many memory, create instance of cells also need many time during merging. Is there a solution which is not necessary to init inner cells?

Borders rendering

The idea to speed up drawing borders

A not very good design is drawing the borders cell by cell, of course it is the easiest way to implement. To speed up drawing borders, a direct idea is find the same borders in either horizontal or vertical direction, and draw it once.

Image 61

The timing to find same borders

Find same borders during drawing, it is acceptable solution, but still can be optimized. The time of spreadsheet rendering is very expensive, it is need to reduce as much as possible to do many things at the time of rendering. Consider something what can be performed, calculated in advance and save them into memory, get and use them directly during drawing will be more efficient (convert space to time).

The solution

ReoGrid uses a variable called span to identify the same borders in both horizontal and vertical direction. And this variable will be managed and updated when new borders to be set, or any borders to be removed. ReoGrid maintains the information and the relation of same borders when borders change, and always drawing same borders once.

Image 62

Continuous decreasing spans

Use span can greatly improve drawing speed. But there is a thorny issue is scrolling. Always just to draw the content inside visible region to user is a good way to improve rendering speed. But when the border, which keeps the span information has been scrolled out from the visible region, the followed borders will also disappear.

Image 63

To solve this issue, an idea is attaching the span to each borders. Then do not care about borders where to start, each borders lead the followers to decide how many span will be used to draw.

Image 64

What to be improved

Although use span to speed up the rendering is efficient, but the span maintaining becomes very complex, especially in the cells inserting and removing. And when rows and columns become invisible, the span information will not be excluded, it still be included in calculation and rendering.

Another I am thinking about is just keep one border in memory, which has a property span = 5, but when it scrolled out from a visible region, how to detect and draw it. and when the column inserting there is same issue.

Cell styles management

Usually the user will set style to an entire row, or column. This is need to be considered when the styles management implementation.

Image 65

Put some styles to headers, rather than cells

ReoGrid saves styles information to each cells, except some special cases. They are:

  • Apply styles to entire spreadsheet (whatever how many cells exist)
  • Apply styles to entire row or column (whatever how many cells exist)

When user selecting entire spreadsheet, and set the styles to entire spreadsheet, ReoGrid save the style to a variable called RootStyle which is a member variable of sheet. And every header of rows, header of columns have same struct to store a row-style and a column-style. Example of setting styles to three special positions:

Image 66

What to be improved

Although consider to put styles to three special positions, can help to decrease memory usage. But I think there is more better solution available. Like always to save styles to range, rather than cells, even just one cell use it. For this implementation the SetCellStyle currently be defined as non-public method. I suggest that only the SetRangeStyle to be used.

Image 67

Viewport controller

A MVC-like design was imported in ReoGrid to implement the rendering of presentation layer. According to Excel, there are at least two different layouts needed to be presented to the end-user.

Image 68

One important thing is what different layout has different behaviors, like scrolling behavior and page header editing.

Normal Layout

Image 69

Page Layout

Image 70

Implementations in ReoGrid

The first thing to do is to separate the entire display region of control to a few small views, and all views just render the content of itself.

Image 71

Since the header of rows, header of columns have different scrolling directions, to separate the views by scrolling direction seems more better.

Image 72

How to scroll

The GDI+ graphics object provides the method TranslateTransform, it can be used to change the origin of drawing context, it will be useful to implement the scrolling. No need to change each position of cells in spreadsheet, just use this method to change the start position of rendering context by specifying a offset value.

Interface definitions

Some views are not necessary to scroll, decrease times of performing TranslateTransform will help to save the time of rendering, there is a different sets of interface was designed in ReoGrid, the views without scrolling:

C#
interface View {                     // called 'IPart' in source code
  void Draw();                       // draw content without scrolling
  Rectangle Bounds { get; set; }     // view boundaries 
}
And the views with scrolling:
C#
interface ScrollView : View {        // called 'IViewPart' in source code
  void Draw();                       // draw content without scrolling,
                                        and perform TranslateTranform by ViewStart
  void DrawView();                   // draw content with scrolling
  void Scroll(int x, int y);         // perform scrolling, apply offset to ViewStart
  Point ViewStart { get; set; }      // offset value in horizontal and vertical direction
}

Visible region

Always to render the visible region which is part of entire spreadsheet will improving rendering performance. Maintains the visible region to each views after view has been scrolled, draw the content which is only contained or intersected by a visible region.

Image 73

Freeze implementation

once a view such as header of row which is used to render the header of rows is implemented, just by making a clone of the view, disabling it's scrolling ability or changing its scrolling direction, will implement the freeze with an easier way.

Image 74

Difference scrolling effect between Excel and ReoGrid

ReoGrid uses pixel-based scrolling calculating, but seems like Excel uses cell-based scrolling calculating. I think there are different pros and cons to each method.

Image 75
  • Excel
    • Scrolling starting position: cell's top-left
    • Scrolled by number of cells
    • pros - Since scrolled by number of cells, once the value of scroll-bar changed, it is possible to find the position of cell to be displayed very quickly. And when the freeze, outline collapsed or any rows or columns hiding, even in the spitted window rendering, it still work quickly.
    • cons - Cannot force the scrolling start at the middle of row. When the row is large, scrolling starts at either top of the row, or bottom of the row, this is a little bit bad on user experience.
  • ReoGrid
    • Scrolling starting position: any pixels
    • Scrolled by pixels of screen
    • pros - improving user experience, make scrolling more smooth
    • cons - since scrolled by pixels of screen, when the value of scroll-bar changed, it is need to decide the spreadsheet start to render at which row and column, it also need to convert the pixels to cells index, this conversion will spend a few time during scrolling. I had considered and tried to solve this by several methods, one is by doing binary search.

Convert from pixel position to cell index by binary search

All of headers of both row and column have the (x,y) position in pixel, and the width or height are saved in memory together. Since headers always be sorted by both index and pixels, it is possible to convert the pixels to cell index by using binary search, I found this is very fast.

Image 76

Layer management

Divide and create a several presentation layers, make sure they only do the different things, will make everything easier. By analyzing the UI of Excel, I think 4 layers may necessary to be implemented:

Image 77

Multi-layer management like as below:

Image 78
ViewportController is one layer of control, see the Viewport Controller sector above.

Event dispatch

Like other UI frameworks, applications, when there are several layers, they do different things inside one presentation region of control, they are all able to receive the events from the mouse and keyboard. How to decide that which layer should to get the mouse events, or keyboard events should be dispatched to which layer, a mechanism and a collection of interfaces will be designed into ReoGrid, the main class called LayerManagement and the interface for each layer called ILayerController. One of LayerController is IViewportController, And the events will be routed into each layer, request them to handle the event as below.

Image 79

Thanks

Thanks many people for help this software and article, I am very sorry that I cannot list you all here. (sort by date)

  • Thank TL Wallace for nice suggestion about Cell Types.
  • Thank i00 for offering the support on spell check.
  • Thank Bill Woodruff for help to solve the platform compliant issue, and suggestions to improve this article.
  • Thank fred for help on translation into French
  • Thank Champion Chen for proposing many ideas about this control
  • Thank niujingui for proposing the Group and Outline feature (to be available in v0.8.5)
  • Thank ves.p for proposing and developing on some new features of this control
  • Thank V.V.Shinkevich and Teruteru314 for help to solve the loading error with non-English culture
  • Thank José Santos for many suggestions to this control
And the 3rd-party softwares:

History

Date Control Version Updates
24 Jan 2014 ReoGrid 0.8.5 New: Outline & Cell Body
Performance improvement
Namespace changed to 'unvell' from 'Unvell'
15 Jan 2014 ReoGrid 0.8.4 Stability improvement
Article: Add implementations description
16 Dec 2013 ReoGrid 0.8.2 Add text-wrap feature
Add text alignment settings page
Add Clipboard events support
Add more event support for script
Add more demos
9 Dec 2013ReoGrid 0.8.1Created

About

ReoGrid currently still in development, getting latest downloads, reporting bugs, and follow us at:

http://reogrid.codeplex.com

Please feel free to let me know your suggestions, questions and feedback.
Technical support and customize development service available at unvell.com.

THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANT-ABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
CEO unvell inc.
Japan Japan
He loves programming, traveling, and photograph, he currently manages a small software development studio in Tokyo, Japan, he is the author of ReoScript[^] and ReoGrid[^].

Comments and Discussions

 
Questionhow to use reo grid Pin
Member 1334482126-Dec-23 11:25
Member 1334482126-Dec-23 11:25 
GeneralMy vote of 5 Pin
tarco26-Nov-23 13:00
professionaltarco26-Nov-23 13:00 
QuestionEdit cell by enter Pin
Abdo Abdelaziz6-Mar-22 23:57
Abdo Abdelaziz6-Mar-22 23:57 
Questionerrors Pin
JJ GAMES27-Mar-21 11:35
JJ GAMES27-Mar-21 11:35 
GeneralMy vote of 5 Pin
BillWoodruff1-Feb-20 1:48
professionalBillWoodruff1-Feb-20 1:48 
Questionstrong name Pin
Member 1214327413-Nov-19 22:42
Member 1214327413-Nov-19 22:42 
QuestionEditing cell background Pin
barbie8618-Jul-19 21:25
barbie8618-Jul-19 21:25 
Questionit's not support .xls or .xlsx excel file? Pin
Member 1103869331-Mar-19 20:15
Member 1103869331-Mar-19 20:15 
QuestionExport to Excel Pin
mario.a.nunes14-Feb-19 5:27
mario.a.nunes14-Feb-19 5:27 
PraiseAwesome, is it compatible with Farpoint Spread by chance Pin
NeWi4-Dec-18 5:41
NeWi4-Dec-18 5:41 
QuestionCan you edit in Form? Pin
Member 1403816730-Oct-18 5:58
Member 1403816730-Oct-18 5:58 
GeneralMy vote of 5 Pin
Franc Morales6-Sep-18 19:06
Franc Morales6-Sep-18 19:06 
QuestionCannot found column header view from specified position Pin
Luchengwei24-Aug-18 5:26
Luchengwei24-Aug-18 5:26 
QuestionSet cell formula in range and autofill Pin
amitgsc23-Jul-18 9:27
amitgsc23-Jul-18 9:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.