Cell

Cell is the smallest unit on a worksheet. Multiple cell can be merged to a larger cell.

Cells

Cell can hold data, style, data format setting and cell body.

Locate a cell

Usually use the following three forms to locate a cell on worksheet:

  • Address string – Column code followed by row number. e.g. D5
  • Row and column number – e.g. (4, 3)
  • CellPosition structure – A .NET structure that contains row and column position

CellPosition structure

Create object of CellPosition structure to locate a cell:

var pos = new CellPosition("D5");   // locate an address D5

Or use a number index to locate a cell, row and column number index is zero-based.

var pos = new CellPosition(4, 3);   // row: 4, col: 3

When a CellPosition is created, it is possible to get its address by using ToAddress method.

string address = pos.ToAdderss();   // result is D5

The following static methods are used to check whether or not an address is valid:

CellPosition.IsValidAddress("D5");       // true
CellPosition.IsValidAddress("A1:D5");    // false
CellPosition.IsValidAddress("myrange");  // false, but sheet["myrange"] is available

Cell data

Cell data is the value displayed on worksheet as well as used in formula calculation.

Set single cell data

Set by cell address:

sheet["A1"] = 10;

Set by row and column number index: (zero-based)

sheet[0, 0] = 10;            // A1: number 
sheet[0, 1] = "string"       // B1: string 
sheet[0, 2] = DateTime.Now;  // C1: datetime

Set by cell position structure:

sheet[new CellPosition("A1")] = 10;
sheet[pos] = "string";

Set by named range:

// Define named range
sheet.DefineNamedRange("mycell", new RangePosition("A1"));

// Set data
sheet["mycell"] = 10.12345d;

Learn more about Named Range.

Set a custom data type:

public class MyData { 
  public override string ToString() { return "mydata"; }
}
sheet["D1"] = new MyData();

Set data by call method:

sheet.SetCellData(5, 2, "hello world");
sheet.setCellData(new CellPosition(5, 2), "hello world");

Set range data

Set array data into a range:

sheet["A1:C1"] = new object[] {"A", "B", "C", "D"};  // data fill only inside the range
sheet["A2"] = new object[] {"D", "E", "F"}; // no boundary limit, fill all data
sheet["A3:A5"] = new object[] {10, 11, 12}; // vertical fill

sheet[1,1] = new object[,] { { 'a', 'b', 'c' }, { 1, 2, 3 }, { 4, 5, 6 } });

sheet.SetRangeData(new RangePosition(1, 1, 3, 3), new object[ , ] { { 'a', 'b', 'c' }, { 1, 2, 3 }, { 4, 5, 6 } });

17_2

Set data and undo

By using DoAciton and passing SetCellDataAction instance to set data will give an ability to undo the operation.

sheet.DoAction(new SetCellDataAction("B5", "hello world"));

// undo the change
grid.Undo();

// redo the change
grid.Redo();

Set cell data in script

sheet.getCell(5,2).data = 'hello world';

High Importance-20 Script execution available in extension edition.

Some types as below, they are processed specially:

object[,]        // array of object will be copied to a range
PartialGrid      // partial grid will be copied to a range
CellBody         // instance of CellBody will be set into a cell

Cell data type

By default, ReoGrid will check the input data and choose a data type in order to format data and display the formatted text. If cell data is specified as null, nothing will be display; if an object type such as a custom user-object has been set, the method ToString of the object will be invoked, the object itself will be used in formula calculation.

Cell instance

When ReoGrid component is initially created, no instances of cell will be created. When any data or cell styles is set into a cell, ReoGrid creates the cell instance automatically.

For example, cell A1 has instance but B1 hasn’t.

Cell with instances

Get cell instance

To get cell instances, use the collection of cell as below:

var cell = sheet.Cells["A1"];      // cell from address
var cell = sheet.Cells[1, 2];      // cell in 1,2 
var cell = sheet.Cells["C2"];      // cell in 1,2 
var cell = sheet.Cells["myname"];  // first cell from named range

The collection of cell will always create cell instance before returning it, do not iterate over all cells by using this collection since it will create a lot of empty instances to eat memory, instead use the following method:

var cell = sheet.GetCell("A1");

Since instance could be null so it would be safer if have a check before using it:

if (cell != null) {
  ...
}

There is another way to get cell instance with non-null instance returning, the method CreateAndGetCell will create an new cell instance if instance does not exist before returning it. (this method has same effect as collection of cell)

var cell = sheet.CreateAndGetCell(2, 3);

Auto Data Format

At first time of data being set into a cell, ReoGrid checks the data and chooses a data type to format it. For example, if data was detected as number, ReoGrid uses default number data format to display it. And when data is number, date time or percent format, the text horizontal alignment will be set to right automatically.

To disable auto data format, set Edit_AutoFormatCell of the worksheet setting to false.

sheet.SetSettings(ReoGridSettings.Edit_AutoFormatCell, false);

See more about Data Format and Settings.

Auto data type conversion

ReoGrid will change data type to other types in order to speed up the formula calculation. For example, when a string data “10” set into a cell which has number format specified, the string will be converted into number (double in .NET) automatically. To prevent this conversion and store the original string “10”  in cell, change the data-format of the cell to ‘Text’. See Data Format.

Example: Auto data type conversion

// generic-cell-data-format will convert the value into number automatically
sheet[3, 1] = "10";  // "10" -> 10 auto convert
AssertEquals(grid.GetCellData<int>(3, 1), 10);

// set 3,2 to text
sheet.SetRangeDataFormat(3, 2, 1, 1, DataFormat.CellDataFormatFlag.Text, null);
sheet[3, 2] = "10";  // "10" without auto convert
AssertEquals(grid.GetCellData<int>(3, 2), 10);
AssertEquals(grid.GetCellData<double>(3, 2), 10d);

Text overflow (Cells Clipping)

Like Excel, ReoGrid will display a cells text overflowing its cell.

This behavior could be disabled by changing worksheet settings:

sheet.DisableSettings(ReoGridSettings.View_AllowCellTextOverflow);

Next: Cell Edit

9 Responses to “Cell”

  1. damien says:

    Text overflow (Cells Clipping):
    Can you set overflow on each cell independently? (only allowed for empty cell)
    (question also posted on codeProject)

  2. damien says:

    Congratulation for this marvelous grid!
    How to get the data format of a cell to know if the format is, for example, set to date?

    • Jing says:

      Thanks very much! Hope it helps you.

      if (worksheet.Cells["A1"].DataFormat == CellDataFormatFlag.DateTime)
      {
      }

  3. Paul says:

    Is it possible to have a column containing (each cell) an image (or a thumbnail size image) ?

  4. Danny says:

    Thanks for creating a very useful gird.
    When I do something like:
    sheet[0, 0] = “10.0”;
    It automatically formats this as a number (and removes the decimal places). Is there a way I can set the data type of worksheets/columns/cells before setting the value or force the cell to take the data type of the object I’m assigning the cell to?