Inconsistent Cell.Value with simple/complex formulas

Sep 30, 2011 at 10:02 PM

I don't know if this is a problem anywhere, but something I wanted to mention and see if anyone knew a reason for it.

If I have a simple formula for copying a cell, just "A1", then Cell.Value is the actual value in A1. If I do anything to that value, like "A1+0" then Cell.Value becomes the formula.

I've looked at the discussions surrounding calculating results of formulas (count me as another looking forward to that feature), and I'm okay with just using Cell.ValueCached for that and dealing with the limitations of when and how that gets updated.

My problem is when that original value in A1 is a DateTime (haven't looked for other data types or formatting to see if others share this behavior). In the both simple and complex cases above, the ValueCached is the integer representation instead of the formatted value, so I see "40816" instead of "9/30/2011". This just makes it harder to always pull the ValueCached when a formula is defined.

I'm guessing that this is Excel's doing and nothing ClosedXML is responsible for. I just thought the behavior was odd.

Coordinator
Oct 3, 2011 at 6:03 PM

I've been trying to find a good solution for your problem and I think the best way is for you to calculate the date yourself instead of adding methods to IXLCell to deal with cached values.

DateTime.FromOADate(Double.Parse("40816"))

Oct 3, 2011 at 6:22 PM

That's fine. It's not a big deal for me right now. I'm just writing some generalized wrappers to convert between DataTable and Excel worksheet and was trying to test some edge cases when I came across that. I'm still just ecstatic at being able to leave OleDb behind and not having to deal with the mixed data type issues.

If I ever get the free time and motivation, I'd love to take a look at the formula engine. I've dealt with a project called NCalc recently that sounds like that might be useful if that CodeProject FormulaEngine doesn't pan out.