First, thank you for an amazing API! It's a shame all the hours we save with it cannot be transferred to you! You'd be in for tons of free time. Anyway...
I am building a simple method to pull a cell's value but I'm running into a few issues. While I kind of solved the problem already, I'd like to know if I'm doing it right and if there's isn't a more straightforward way.
I have ran into several problems trying to read the "Value" of a cell if it involves formulas. Since the basic formulas work fine (tried SUM!), I guess this is a case of unsupported formulas (COUNTIF and IFERROR?). This is why I started reading
the ValueCached first as a workaround since I don't need to evaluate the formula (I'm just reading an existing workbook, everything is already calculated):
Using ClosedXML.Excel;
//Given a workbook, worksheet and namerange, pull the namerange's value as a string
public static string GetCellValue(string filename, string worksheet, string namerange)
{
string cellValue = string.Empty; //If an element cannot be found, the string will remain empty.
using (XLWorkbook wb = new XLWorkbook(filename))
{
//Ensure the worksheet exists.
if (wb.Worksheets.Any(ws => ws.Name == worksheet))
{
IXLWorksheet ws= wrkbrk.Worksheet(worksheet);
//Ensure the namedrange exists.
if (ws.NamedRanges.Any(nr => nr.Name == namerange))
{
IXLRange rng = ws.Range(namerange);
//Ensure the range has at least one used cell
if (rng.CellsUsed().Any())
{
IXLCell cell = rng.FirstCellUsed();
//First try to get the cached value
cellValue = cell.ValueCached;
//Else try to get the value
if (string.IsNullOrEmpty(cellValue))
{
cellValue = cell.Value.ToString();
}
}
}
}
}
return cellValue;
}
Also I think I might have found a bug.
When you run TryGetValue on an unsupported formula, it crashes. I would expect TryGetValue to just return False if it couldn't read my value correctly due to an unsupported formula. The way I did it above, ClosedXML will never have to compute a value even
if it can, since i'm using ValueCached first. If TryGetValue would not crash on unsupported formulas, I could first try to compute and then default to the cached value without having to catch an exception.
Thanks!
