How to Know If a Cell Has an Error in the Formula in C#

How to know if a cell has an error in the formula in C#

You can use the WorksheetFunction method:

Globals.ThisAddIn.Application.WorksheetFunction.IsErr(...)

[Your Excel Object].WorksheetFunction.IsErr(...)

The IsErr is semantically identical to the Excel worksheet function, only instead of the cell reference pass in the actual value - AFAIK.

How to check a Cell contains formula or not in Excel through oledb reader or excel library, excel datareader or NPOI etc (Except Interop)?

You can use the OpenXML SDK to read Xlsx files.

To do this you need to add a reference to the OpenXML library which can be done via the nuget package (you'll also need a reference to WindowsBase). You then need to load the spreadsheet, find the sheet you're interested in and iterate the cells.

Each Cell has a CellFormula property which will be non-null if there is a formula in that cell.

As an example, the following code will iterate each cell and output a line for any cell that has a formula. it will return true if any cell has a formula in it; otherwise it will return false:

public static bool OutputFormulae(string filename, string sheetName)
{
    bool hasFormula = false;

    //open the document
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        //get the workbookpart
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //get the correct sheet
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
        if (sheet != null)
        {
            //get the corresponding worksheetpart
            WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

            //iterate the child Cells
            foreach (Cell cell in worksheetPart.Worksheet.Descendants<Cell>())
            {
                //check for a formula
                if (cell.CellFormula != null && !string.IsNullOrEmpty(cell.CellFormula.Text))
                {
                    hasFormula = true;
                    Console.WriteLine("Cell {0} has the formula {1}", cell.CellReference, cell.CellFormula.Text);
                }
            }
        }
    }

    return hasFormula;
}

This can be called with the name of the file and the name of the sheet you're interested in although it would be trivial to update the code to iterate all sheets. An example call:

bool formulaExistsInSheet = OutputFormulae(@"d:\test.xlsx", "Sheet1");
Console.WriteLine("Formula exists? {0}", formulaExistsInSheet);

An example output from the above:

Cell C1 has the formula A1+B1

Cell B3 has the formula C1*20

Formula exists? True

If you're only interested if there are any cells in the sheet that have a formula you can simplify the above by using the Any extension method:

public static bool HasFormula(string filename, string sheetName)
{
    bool hasFormula = false;
    //open the document
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, false))
    {
        //get the workbookpart
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        //get the correct sheet
        Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).First();
        if (sheet != null)
        {
            //get the corresponding worksheetpart
            WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

            hasFormula = worksheetPart.Worksheet.Descendants<Cell>().Any(c =>
                c.CellFormula != null && !string.IsNullOrEmpty(c.CellFormula.Text));
        }
    }

    return hasFormula;
}

How to validate Excel range that it has error/warning using .NET?

The key is to check for the data type of the value held in the cell. If the data type is an Integer (Int32), then the value held is a CVErr value. To check for #N/A, the cell would be an Integer data type (not a Double!) holding the value -2146826246.

For more details, see the stack overflow question How to know if a cell has an error in the formula in C#.

Get error cells by GetCellValueeErrorType or a with a different approach

Seems like you need to test by type like this:

var workbook = package.Workbook;
var worksheet = workbook.Worksheets["Sheet1"];
foreach (var cell in worksheet.Cells)
{
    var x = cell.GetCellValue<object>();
    switch (x)
    {
        case double d:
            Console.WriteLine($"{cell.Address} is double: {d}");
            break;
        case ExcelErrorValue error:
            Console.WriteLine($"{cell.Address} with formula '{cell.Formula}' is error: {error.Type}");
            break;
    }
}

Assuming you have a sheet like:
Sample Image

Which give this as the output:

A1 is double: 1
B1 is double: 0
C1 with formula 'A1/B1' is error: Div0

RESPONSE TO COMMENTS:

A foreach would probably be more efficient since you can perform any other needed tasks inside of it but this is how to do it via LINQ:

var errorCells = worksheet
    .Cells
    .Where(c => c.GetCellValue<object>() is ExcelErrorValue)
    .ToList();

Console.WriteLine($"Number of error cells: {errorCells.Count}"); // Prints "1"

EPPlus cell.calculate() error when formula contains TRIM function

Remember the Epplus doesnt actually have access to the excel engine, it just generates raw XML files that excel interprets when the file is first opened.

So for Formula, the documentation does say you can use Calculate() to get a value like you could in Excel. But what it doesnt tell you is that it does not support all excel built in functions. And understand that the functions in Epplus/C# are NOT the exact same as what will be ran in Excel. They are simply Epplus's interpretation/copies of what excel has.

If you look at https://github.com/JanKallman/EPPlus/blob/master/EPPlus/FormulaParsing/Excel/Functions/BuiltInFunctions.cs:

namespace OfficeOpenXml.FormulaParsing.Excel.Functions
{
    public class BuiltInFunctions : FunctionsModule
    {
        public BuiltInFunctions()
        {
            // Text
            Functions["len"] = new Len();
            Functions["lower"] = new Lower();
            Functions["upper"] = new Upper();
            Functions["left"] = new Left();
            Functions["right"] = new Right();
            Functions["mid"] = new Mid();
            Functions["replace"] = new Replace();
            Functions["rept"] = new Rept();
            Functions["substitute"] = new Substitute();
            Functions["concatenate"] = new Concatenate();
            Functions["char"] = new CharFunction();
            Functions["exact"] = new Exact();
            Functions["find"] = new Find();
            Functions["fixed"] = new Fixed();
            Functions["proper"] = new Proper();
            Functions["search"] = new Search();
            Functions["text"] = new Text.Text();
            Functions["t"] = new T();
            Functions["hyperlink"] = new Hyperlink();
            Functions["value"] = new Value();
            // Numbers
            Functions["int"] = new CInt();
            // Math
            Functions["abs"] = new Abs();
            Functions["asin"] = new Asin();
            Functions["asinh"] = new Asinh();
            Functions["cos"] = new Cos();
            Functions["cosh"] = new Cosh();
            Functions["power"] = new Power();
            Functions["sign"] = new Sign();
            Functions["sqrt"] = new Sqrt();
            Functions["sqrtpi"] = new SqrtPi();
            Functions["pi"] = new Pi();
            Functions["product"] = new Product();
            Functions["ceiling"] = new Ceiling();
            Functions["count"] = new Count();
            Functions["counta"] = new CountA();
            Functions["countblank"] = new CountBlank();
            Functions["countif"] = new CountIf();
            Functions["countifs"] = new CountIfs();
            Functions["fact"] = new Fact();
            Functions["floor"] = new Floor();
            Functions["sin"] = new Sin();
            Functions["sinh"] = new Sinh();
            Functions["sum"] = new Sum();
            Functions["sumif"] = new SumIf();
            Functions["sumifs"] = new SumIfs();
            Functions["sumproduct"] = new SumProduct();
            Functions["sumsq"] = new Sumsq();
            Functions["stdev"] = new Stdev();
            Functions["stdevp"] = new StdevP();
            Functions["stdev.s"] = new Stdev();
            Functions["stdev.p"] = new StdevP();
            Functions["subtotal"] = new Subtotal();
            Functions["exp"] = new Exp();
            Functions["log"] = new Log();
            Functions["log10"] = new Log10();
            Functions["ln"] = new Ln();
            Functions["max"] = new Max();
            Functions["maxa"] = new Maxa();
            Functions["median"] = new Median();
            Functions["min"] = new Min();
            Functions["mina"] = new Mina();
            Functions["mod"] = new Mod();
            Functions["average"] = new Average();
            Functions["averagea"] = new AverageA();
            Functions["averageif"] = new AverageIf();
            Functions["averageifs"] = new AverageIfs();
            Functions["round"] = new Round();
            Functions["rounddown"] = new Rounddown();
            Functions["roundup"] = new Roundup();
            Functions["rand"] = new Rand();
            Functions["randbetween"] = new RandBetween();
            Functions["rank"] = new Rank();
            Functions["rank.eq"] = new Rank();
            Functions["rank.avg"] = new Rank(true);
            Functions["quotient"] = new Quotient();
            Functions["trunc"] = new Trunc();
            Functions["tan"] = new Tan();
            Functions["tanh"] = new Tanh();
            Functions["atan"] = new Atan();
            Functions["atan2"] = new Atan2();
            Functions["atanh"] = new Atanh();
            Functions["acos"] = new Acos();
            Functions["acosh"] = new Acosh();
            Functions["var"] = new Var();
            Functions["varp"] = new VarP();
            Functions["large"] = new Large();
            Functions["small"] = new Small();
            Functions["degrees"] = new Degrees();
            // Information
            Functions["isblank"] = new IsBlank();
            Functions["isnumber"] = new IsNumber();
            Functions["istext"] = new IsText();
            Functions["isnontext"] = new IsNonText();
            Functions["iserror"] = new IsError();
            Functions["iserr"] = new IsErr();
            Functions["error.type"] = new ErrorType();
            Functions["iseven"] = new IsEven();
            Functions["isodd"] = new IsOdd();
            Functions["islogical"] = new IsLogical();
            Functions["isna"] = new IsNa();
            Functions["na"] = new Na();
            Functions["n"] = new N();
            // Logical
            Functions["if"] = new If();
            Functions["iferror"] = new IfError();
            Functions["ifna"] = new IfNa();
            Functions["not"] = new Not();
            Functions["and"] = new And();
            Functions["or"] = new Or();
            Functions["true"] = new True();
            Functions["false"] = new False();
            // Reference and lookup
            Functions["address"] = new Address();
            Functions["hlookup"] = new HLookup();
            Functions["vlookup"] = new VLookup();
            Functions["lookup"] = new Lookup();
            Functions["match"] = new Match();
            Functions["row"] = new Row();
            Functions["rows"] = new Rows();
            Functions["column"] = new Column();
            Functions["columns"] = new Columns();
            Functions["choose"] = new Choose();
            Functions["index"] = new Index();
            Functions["indirect"] = new Indirect();
            Functions["offset"] = new Offset();
            // Date
            Functions["date"] = new Date();
            Functions["today"] = new Today();
            Functions["now"] = new Now();
            Functions["day"] = new Day();
            Functions["month"] = new Month();
            Functions["year"] = new Year();
            Functions["time"] = new Time();
            Functions["hour"] = new Hour();
            Functions["minute"] = new Minute();
            Functions["second"] = new Second();
            Functions["weeknum"] = new Weeknum();
            Functions["weekday"] = new Weekday();
            Functions["days360"] = new Days360();
            Functions["yearfrac"] = new Yearfrac();
            Functions["edate"] = new Edate();
            Functions["eomonth"] = new Eomonth();
            Functions["isoweeknum"] = new IsoWeekNum();
            Functions["workday"] = new Workday();
            Functions["networkdays"] = new Networkdays();
            Functions["networkdays.intl"] = new NetworkdaysIntl();
            Functions["datevalue"] = new DateValue();
            Functions["timevalue"] = new TimeValue();
            // Database
            Functions["dget"] = new Dget();
            Functions["dcount"] = new Dcount();
            Functions["dcounta"] = new DcountA();
            Functions["dmax"] = new Dmax();
            Functions["dmin"] = new Dmin();
            Functions["dsum"] = new Dsum();
            Functions["daverage"] = new Daverage();
            Functions["dvar"] = new Dvar();
            Functions["dvarp"] = new Dvarp();
            //Finance
            Functions["pmt"] = new Pmt();
        }
    }
}

You will see that Trim is not in there, unfortunately. You could, in theory, add it yourself like this:

    [TestMethod]
    public void Test1()
    {
        using (var package = new ExcelPackage())
        {
            package.Workbook.FormulaParserManager.LoadFunctionModule(new MyFunctionModule());

            var ws = package.Workbook.Worksheets.Add("Test");
            var cellTest = ws.Cells[1, 1];

            cellTest.Formula = "TRIM(\"Hello World\")";
            ws.Calculate();
            var cellCalculatedValue = cellTest.Value; //Will now show a proper value
        }
    }

    public class TrimFunction : ExcelFunction
    {
        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            ValidateArguments(arguments, 1);
            var result = arguments.ElementAt(0).Value.ToString().Trim();
            return CreateResult(result, DataType.String);

        }
    }

    public class MyFunctionModule : FunctionsModule
    {
        public MyFunctionModule()
        {
            Functions.Add("trim", new TrimFunction());
        }
    }

But, again, it has nothing to do with the actual function in excel - they are completely separate. The TRIM function above will only existing in the C# world. When excel opens the file, it will apply its own TRIM function.

How to Know If a Cell Has an Error in the Formula in C#