Free .NET Spreadsheet Control

Snapshots

Data Format

Border Setting Dialog (Available in ReoGrid Editor Library)

Data Format Setting Dialog (Available in ReoGrid Editor Library)

Macro and Script Execution

Customized Control Appearance

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:

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:

Create instance

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

unvell.ReoGrid.dll   // this control

Import namespaces:

using Unvell.ReoGrid;

Create instance for control:

// 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

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

Using Actions

1. Import this namespace:

using Unvell.ReoGrid.Actions;

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

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

To undo or redo last action:

grid.Undo();
grid.Redo();

To repeat last action to apply for another range:

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:

using Unvell.ReoScript;

Run Script:

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

Cells

Change rows and columns

grid.SetRows(6);
grid.SetCols(5); 

To get number of row and number of columns:

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

Change height and width

Set both height and width to 10 pixels

grid.SetRowsHeight(0, grid.RowCount, 10);
grid.SetColsWidth(0, grid.ColCount, 10); 

Get height and width

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

Set cell data

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()

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.

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:

grid.MergeRange(new ReoGridRange(2, 1, 3, 4));

Unmerge range:

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):

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

Background Pattern Color

Set background pattern color to entire grid:

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

Text color

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

Text alignments

Set cell's text horizontal alignment to Center:

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

Text wrap

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

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

Font

Change font name and size:

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

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:

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

Borders

Set bold outline border around specified range:

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

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

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

To remove borders:

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

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

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:

ReoGridRangeBorderInfo GetRangeBorder(ReoGridRange range);

Data Format

using Unvell.ReoGrid.DataFormat;

Specify cell data format by using SetRangeDataFormat method.

SetRangeDataFormat(ReoGridRange range, CellDataFormatFlag flag, object argument)

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

grid[1, 1] = 12345;
grid[1, 2] = 12345.67890;
grid[2, 1] = -1234;
grid[2, 2] = -1234.56789;

Table of built-in data formatter:

Cell Body

Events

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

Freeze

To set freeze started at row 5:

grid.FreezeToCell(5, 0);

To unfreeze:

Unfreeze

To get freeze position:

ReoGridPos pos = grid.GetFreezePos();

Zoom

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

Set scale factor

grid.SetScale(2f, Point.Empty);

Settings

Hide scroll bars and headers

grid.SetSettings(ReoGridSettings.View_ShowScrolls | ReoGridSettings.View_ShowHeaders, false);

Hide grid lines

grid.SetSettings(ReoGridSettings.View_ShowGridLine, false); 

Disable adjust height of row and width of column by mouse

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.

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

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.:

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

Customize Function

Add customize function in 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));
});

Add customize functions by running script

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

Run script to add functions in C#

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

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

XML Serialization

Save into file or stream:

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

Grid data saved in xml as below:

Load from file or stream:

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

Control Appearance

// 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:

Reset Control

Reset control to default status.

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.

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:

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.

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

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

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):

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

• 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:

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

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:

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:

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

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.

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:

• 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

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.

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.

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.

int skips = merge-end-cell.col - merge-start-cell.col;

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.

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.

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.

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.

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.

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:

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.

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.

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

Normal Layout

Page Layout

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.

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

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:

interface View {                     // called 'IPart' in source code
  void Draw();                       // draw content without scrolling
  Rectangle Bounds { get; set; }     // view boundaries 
}

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.

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.

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.

• 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.

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:

Multi-layer management like as below:

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.

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

History

About

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