Worksheet.GetExistingCells() Method

Gets the existing cells in the worksheet.

Namespace: DevExpress.Spreadsheet

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

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 Range.Value or Range.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.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/udf-custom-function-how-to-replace-excel-xll-add-in-udf-with-a-custom-function-e5178

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

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the GetExistingCells() method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

udf-custom-function-how-to-replace-excel-xll-add-in-udf-with-a-custom-function-e5178/CS/UDF_Discount/Form1.cs#L32

#region #GetExistingCells
IEnumerable<DevExpress.Spreadsheet.Cell> existingCells = spreadsheetControl1.ActiveWorksheet.GetExistingCells();
spreadsheetControl1.Document.BeginUpdate();

VB.NET

udf-custom-function-how-to-replace-excel-xll-add-in-udf-with-a-custom-function-e5178/VB/UDF_Discount/Form1.vb#L31

'            #Region "#GetExistingCells"
            Dim existingCells As IEnumerable(Of DevExpress.Spreadsheet.Cell) = spreadsheetControl1.ActiveWorksheet.GetExistingCells()
            spreadsheetControl1.Document.BeginUpdate()