Get error cells by GetCellValue<eErrorType> or a with a different approach

user_19618751 0
Viewed 38

formulas in excel can return different errors e.g. #div/0 But how to check a cell

package.Workbook.Worksheets["a"].Cells["g2"].GetCellValue<eErrorType>(); will return the error type if an error exists but will crash if the formula of the cell will not produce an error. As far as I can see, the enum of eErrorType does not contain a member like NoError :-(

I would like to use something like that:

var badCells = package.Workbook.Worksheets["a"].Cells.All(f => f.GetCellValue<eErrorType>()!???

Any other approach welcome

tx Perry

epplus c#
2 Answers

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: enter image description here

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"
user_13242840

After I have now tested a few variants, I am once again surprised. All variants are incredibly fast and the iteration through all cells is even the fastest with a good relation of formulas to values but we are talking about milliseconds. Test with 20000 values and 10000 formulas and 3 errors. (all less than 80 ms)

But if you have several operations on formula cells, it is certainly worthwhile to build up a range for them first.

Thanks again to Ernie S

Enclosed my small tests, if someone wants to rebuild these:

private int ByLinq0(ExcelWorksheet dataSheet)
{
// using Ernie S' code
int counter = 0;
 foreach (var cell in dataSheet.Cells)
{
 var x = cell.GetCellValue<object>();
 switch (x)
 { case ExcelErrorValue error:
 counter += 1;
 break;
 }
}
return counter;
}
private int ByLinq1(ExcelWorksheet dataSheet)
{
List<ExcelRangeBase> allBadFormulas = dataSheet.Cells.Where(f => f.Formula.Length > 0 && f.Value.ToString().StartsWith("#") && f.GetCellValue<object>() is ExcelErrorValue).ToList();
 return allBadFormulas.Count;
}
private int ByLinq2(ExcelWorksheet dataSheet)
{
List<ExcelRangeBase> allBadFormulas = dataSheet.Cells.Where(f => f.Formula.Length > 0 && f.GetCellValue<object>() is ExcelErrorValue).ToList();
 return allBadFormulas.Count;
}
private int ByLinq3(ExcelWorksheet dataSheet)
{
List<ExcelRangeBase> formulas = dataSheet.Cells.Where(f => f.Formula.Length > 0).ToList();
int counter = 0;
 foreach (var cell in formulas)
{
 var x = cell.GetCellValue<object>();
 switch (x)
 {

 case ExcelErrorValue error:
 counter += 1;
 break;
 }
}
return counter;
}
user_196187510
Related