Removing all formula from an existing spreadsheet

I want to remove all formulas, array formulas, dynamic array formulas and their calculated values from an existing spreadsheet. Removing regular formulas is not a problem. However, I am having an issue with the arrays. I can parse the spreadsheet and find the formula associated with the array. However, the formula only shows up in one cell. When I delete the formula, all of the associated values are retained. I want them removed as well. I could remove the values programmatically. However, I don’t know the extent of the array, so I don’t know if the value is a result of the formula or simply a value entered by hand.

I came across the following the explains the SetDynamicArrayFormula.

However, it does not explain how to get/remove one. Also, at the end of the page it says

“The following example shows how you can create dynamic and legacy array formulas. It also shows the calculation difference between a dynamic array formula and a regular formula.”

However, there is no example.

Hi Nick,

We have now added the ExcelCell.FormulaRange property, so please try again with this bugfix version:

Or this NuGet package:
Install-Package GemBox.Spreadsheet -Version 49.0.1308-hotfix

And try using the following:

var workbook = ExcelFile.Load("input.xlsx");

foreach (var cell in workbook.Worksheets
    .SelectMany(ws => ws.Rows.SelectMany(r => r.AllocatedCells)
    .Where(c => !string.IsNullOrEmpty(c.Formula))))
    if (cell.FormulaRange != null)
        foreach (var formulaCell in cell.FormulaRange)
            formulaCell.Value = null;

    cell.Formula = null;



This worked perfectly. Thank you much.