Worksheet.GetExistingCells() Method

Gets the existing cells in the worksheet.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

IEnumerable<Cell> GetExistingCells()

VB.NET

Function GetExistingCells As IEnumerable(Of Cell)

Returns

Type	Description
IEnumerable<Cell>	An enumerator that supports iteration over the cell collection.

Remarks

Use the GetExistingCells method to iterate through the collection of existing cells in the worksheet. The GetExistingCells collection includes the following cells:

• Cells that contain a CellRange.Value or CellRange.Formula;
• Cells that have any Formatting properties which are not null;
• Top-left cells of merged cells;
• Cells that contained a value or had cell formatting options applied at any time. For example, if the cell A1 contained a value that was later deleted, the cell A1 is included in the GetExistingCells collection.

Example

The following code snippet iterates through existing cells to remove the name prefix of the user-defined function (UDF) of the Excel XLL Add-in. You can implement your own user-defined function instead.

Form1.cs

IEnumerable<DevExpress.Spreadsheet.Cell> existingCells = spreadsheetControl1.ActiveWorksheet.GetExistingCells();
spreadsheetControl1.Document.BeginUpdate();
foreach (DevExpress.Spreadsheet.Cell cell in existingCells)
{
    if (cell.HasFormula)
        // The worksheet references functions from the DiscountXLL.MyFunctions xll Add-in. 
        // The name prefix is removed leaving only the function name.
        cell.Formula = cell.Formula.Replace("_xll.DiscountXLL.MyFunctions.", string.Empty);
}
spreadsheetControl1.Document.EndUpdate();

Form1.vb

Dim existingCells As IEnumerable(Of DevExpress.Spreadsheet.Cell) = spreadsheetControl1.ActiveWorksheet.GetExistingCells()
spreadsheetControl1.Document.BeginUpdate()
For Each cell As DevExpress.Spreadsheet.Cell In existingCells
    If cell.HasFormula Then
        ' The worksheet references functions from the DiscountXLL.MyFunctions xll Add-in. 
        ' The name prefix is removed leaving only the function name.
        cell.Formula = cell.Formula.Replace("_xll.DiscountXLL.MyFunctions.", String.Empty)
    End If
Next cell
spreadsheetControl1.Document.EndUpdate()