This project has moved. For the latest updates, please go here.

Failsafe way to pull Cell Values?

Oct 28, 2012 at 2:16 PM

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!

Coordinator
Oct 28, 2012 at 8:28 PM

It breaks my heart to see all you have to do to make such a simple task work. Pick up the latest source code and now you can do the following instead:

        public static string GetCellValue(string filename, string worksheet, string namerange)
        {
            var wb = new XLWorkbook(filename);
            var cellName = String.Format("'{0}'!{1}", worksheet, namerange);
            var cell = wb.Cell(cellName);
            return cell == null ? String.Empty : cell.ValueCached;
        }

As you already know, not all formulas are supported but if you need a few then let me know and I'll do my best to add them promptly. I still don't know how I want to handle the #NA and #ERR, maybe create an XLError with the name of the error and use it for both. I'm still thinking about this.

I think you're right, I'll fix the TryGetValue later.

Thanks for the help,

Oct 29, 2012 at 12:04 AM
Edited Oct 29, 2012 at 12:14 AM

Wow, thanks for the support!

Was this possible with the latest binaries as well or did you change something?

If I may suggest, the documentation should have this explained, I'm sure tons of people will want to read excel files using nameranges instead of hardcoded ranges :)

Oh, I almost forgot... Since XLWorkbook is IDisposable, should I stick to the using block? :)

Coordinator
Oct 29, 2012 at 1:47 AM

The workbook.Cell method is old but it only works with workbook scope named ranges. I modified it so it works with worksheet named ranges too. Come to think of it I need to modify it again so it returns the workbook named range if you specify the worksheet with a named range with workbook scope.

I'll add this to the documentation page (I thought it was already there).

90% of the time you don't need to worry about disposing the workbook/worksheet/ranges/whatever. The only times you do need to do this is if you're creating objects inside a loop. This is unnecessary in most situations (including this one) because the references are released when the workbook goes out of scope.

Oct 29, 2012 at 1:38 PM

Thank you!