Cell is the smallest unit on a worksheet. Multiple cell can be merged to a larger cell.
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 } });
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';
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.
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);
Text overflow (Cells Clipping):
Can you set overflow on each cell independently? (only allowed for empty cell)
(question also posted on codeProject)
Currently it is impossible. But might be implemented in future versions.
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?
Thanks very much! Hope it helps you.
if (worksheet.Cells["A1"].DataFormat == CellDataFormatFlag.DateTime)
{
}
Is it possible to have a column containing (each cell) an image (or a thumbnail size image) ?
A cell type called ImageCell that allows you put images in cell, please refer to https://reogrid.net/document/built-in-cell-types/
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?
Solved. From here https://reogrid.net/document/data-format/ using SetRangeDataFormat on the range before assigning a value to the cell.
Thanks. Hope it helps. Any other problems or questions please us know by post as comment or in forum.