ReoGrid

Cell

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 are capable of holding a wide range of data types, from simple text and numbers to more complex data structures.
  • Styling: Cells can be individually styled, allowing for 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 the display of content 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 employs a conventional spreadsheet cell address format, combining the column letter(s) with the row number. For example, "D5" refers to the cell located 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, considering that both row and column indices are zero-based.
  • CellPosition Structure: This structured approach utilizes 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 to refer to a cell's position within the worksheet.

These diverse methods afford 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 using 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 an instance of CellPosition is created, it is possible to convert it into an address string 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");

Setting 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

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

Handling of 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 formatted content. The treatment of cell data operates as follows:

  • Null Data: When a cell's data is set to null, the cell will remain empty, displaying no content.
  • Object Data: If the data assigned to a cell is an object type, including custom user-defined objects, ReoGrid invokes the object's ToString method to obtain a string representation for display purposes. Furthermore, the original object is retained and utilized 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 the 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. This means that a specific cell instance is only instantiated when it becomes necessary—such as when data or styles are assigned to that cell.

Cell with instances

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

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

Geting 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 in 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");

Notices that a cell instance can be nullable, when there is no any data or resources set into a cell.

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

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 might be cases where manual control over cell formatting is preferred. To turn off this automatic feature, the Edit_AutoFormatCell setting within the worksheet settings can be modified:

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

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 if a data type conversion is necessary for efficiency. For instance:

  • String to Number Conversion: If a string that represents a numeric value, like "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 maintain the original data type and prevent automatic conversions, the cell's data format can be explicitly set 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

Consider the following code snippet, which 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, similar to Excel, is designed to allow 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) do not contain any text.

Disabling Text Overflow

Should the text overflow behavior not be 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.

Was the content of the page helpful?