The cell represents the fundamental unit within a worksheet, serving as the primary container for various types of content and settings.

Key features and functionalities associated with cells include:

• Merging: Individual cells can be combined to form larger cells, facilitating grouped data presentation and layout customization.
• Data Storage: Cells can hold a wide range of data types, from simple text and numbers to more complex data structures.
• Styling: Cells can be individually styled, allowing the application of custom styles that affect fonts, colors, borders, and more, to enhance visual distinction and readability.
• Data Formatting: The presentation of cell data can be controlled through data formatting settings, enabling content to be displayed in various formats such as currency, date, percentage, etc.
• Customization: Cells can host custom cell bodies, enabling the integration of specialized functionalities such as drop-down lists, buttons, or other interactive elements.

This versatility makes cells the core building blocks of a worksheet, supporting a broad spectrum of applications from simple data tables to complex interactive dashboards.

Locating a Cell in a Worksheet

In ReoGrid, a cell within a worksheet can be identified using one of three common methods, each suited to different scenarios and preferences:

• Address String: This method uses a conventional spreadsheet cell address format, combining the column letter(s) with the row number. For example, “D5” refers to the cell at the 4th column (D) and the 5th row.
• Row and Column Numbers: Direct specification of row and column indices provides a straightforward numerical approach to cell location. For instance, the coordinates (4, 3) target the cell at the 5th row and 4th column, given that both row and column indices are zero-based.
• CellPosition Structure: This structured approach uses the CellPosition structure to encapsulate the cell’s location. The structure is initialized with row and column indices, offering a clear and type-safe method for referring to a cell’s position within the worksheet.

These methods provide flexibility in cell referencing, accommodating a range of use cases from simple cell access to more complex programmatic interactions within the ReoGrid environment.

CellPosition structure

Create an instance of CellPosition to locate a cell:

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

Or use a numeric index to locate a cell; row and column indices are zero-based.

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

Once a CellPosition instance is created, it can be converted to an address string using the ToAddress method.

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

The following static methods can be used to check whether 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 the worksheet and used in formula calculations.

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 calling a method:

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

Setting Range Data

Set array data into a range:

sheet["A1:C1"] = new object[] {"A", "B", "C", "D"};  // data fills only within the range

sheet["A2"] = new object[] {"D", "E", "F"}; // no boundary limit, fills 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 } });

Setting Data and Undo

Using DoAction and passing a SetCellDataAction instance to set data provides the ability to undo the operation.

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

// undo the change
grid.Undo();

// redo the change
grid.Redo();

Handling Cell Data Types in ReoGrid

ReoGrid intelligently evaluates the data entered into cells and automatically selects an appropriate data type to apply formatting and display the content. The treatment of cell data operates as follows:

• Null Data: When a cell’s data is set to null, the cell remains empty and displays no content.
• Object Data: If the data assigned to a cell is an object type, including custom user-defined objects, ReoGrid calls the object’s ToString method to obtain a string representation for display purposes. The original object is also retained and used in any formula calculations involving the cell.

This dynamic data type determination and formatting mechanism allows ReoGrid to seamlessly handle various types of cell content, enhancing the user experience by providing contextually appropriate data presentation and functionality.

Cell Instances

Upon initial creation of a ReoGrid component, cell instances are not immediately generated for every possible cell position. Instead, ReoGrid adopts a more efficient approach, creating cell instances on demand. A specific cell instance is only instantiated when it becomes necessary — such as when data or styles are assigned to that cell.

Consider a situation where data or styling is applied to cell A1 but not to B1. In this case, ReoGrid creates an instance for cell A1 to store its data and styling information. Since no information is set for B1, it remains without an instantiated cell object.

This approach conserves resources by avoiding the instantiation of cell objects until they are genuinely needed, contributing to the overall efficiency and performance of the ReoGrid component.

Getting a Cell Instance

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

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

The cell collection always creates a cell instance before returning it. Do not iterate over all cells using this collection, as it will create many empty instances and consume memory. Instead, use the following method:

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

Note that a cell instance can be null when no data or resources have been set on a cell.

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

There is another way to get a non-null cell instance: the CreateAndGetCell method will create a new cell instance if one does not already exist before returning it. (This method has the same effect as the cell collection.)

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

Auto Data Format

ReoGrid employs an Auto Data Format system that activates upon the initial entry of data into a cell. This system analyzes the input and automatically assigns an appropriate data type, applying corresponding formatting rules. For instance:

• Numerical Data: If the input is recognized as a number, ReoGrid applies the default number format, ensuring numerical data is displayed consistently.
• Text Alignment: For certain data types, such as numbers, dates, and percentages, ReoGrid automatically adjusts the text alignment to the right, enhancing readability and maintaining a standard presentation style across similar data types.

Disabling Auto Data Format

While Auto Data Format is beneficial in many scenarios, there may be cases where manual control over cell formatting is preferred. To turn off this automatic feature, modify the Edit_AutoFormatCell setting within the worksheet settings:

// Disabling Auto Data Format for the worksheet
sheet.SetSettings(ReoGridSettings.Edit_AutoFormatCell, false);

See more about Data Format and Worksheet Settings.

Auto Data Type Conversion

ReoGrid will convert data to other types to speed up formula calculations. For example, when the string “10” is entered into a cell that has a number format specified, the string is automatically converted to a number (double in .NET). To prevent this conversion and preserve the original string “10” in the cell, change the cell’s data format to Text. See Data Format.

ReoGrid intelligently manages data types to optimize performance, particularly during formula calculations. When data is entered into a cell, ReoGrid evaluates the context and the cell’s specified format to determine whether a data type conversion is needed. For instance:

• String to Number Conversion: If a string representing a numeric value, such as “10”, is entered into a cell formatted for numbers, ReoGrid will automatically convert the string to a numeric data type (typically double in .NET) to facilitate rapid calculations.

Preserving Original Data Types

To preserve the original data type and prevent automatic conversions, explicitly set the cell’s data format to Text. This approach ensures that the input, such as the string “10”, retains its string data type without being automatically converted to a numeric type.

Example: Demonstrating Auto Data Type Conversion

The following code snippet illustrates how ReoGrid handles data type conversions:

// By default, string "10" is automatically converted to a numeric value
sheet[3, 1] = "10";  // The string "10" is auto-converted to the numeric value 10
AssertEquals(grid.GetCellData<int>(3, 1), 10);

// Setting the cell's data format to 'Text' to preserve the original string data type
sheet.SetRangeDataFormat(3, 2, 1, 1, DataFormat.CellDataFormatFlag.Text, null);
sheet[3, 2] = "10";  // The string "10" is preserved as a string, without auto-conversion

// Despite being stored as a string, the data can still be interpreted as a numeric value when needed
AssertEquals(grid.GetCellData<string>(3, 2), "10");  // Validates the data is stored as a string
AssertEquals(grid.GetCellData<int>(3, 2), 10);      // Demonstrates the string "10" can be interpreted as an integer
AssertEquals(grid.GetCellData<double>(3, 2), 10d);  // Demonstrates the string "10" can be interpreted as a double

Managing Text Overflow in Cells

ReoGrid, like Excel, allows the text content of a cell to overflow into adjacent cells if it exceeds the cell’s width. This default behavior ensures that lengthy text remains visible, provided the adjacent cell(s) contain no text.

Disabling Text Overflow

If text overflow behavior is not desired, it can be easily disabled through the worksheet’s settings:

// Disabling cell text overflow in the worksheet
sheet.DisableSettings(ReoGridSettings.View_AllowCellTextOverflow);

This adjustment prevents text from spilling over into neighboring cells, ensuring that text content is confined to the boundaries of its originating cell. This feature allows for greater control over the presentation and readability of cell data within a ReoGrid worksheet.