This project has moved and is read-only. For the latest updates, please go here.

How do I return a cell value as it has been formatted by Excel?

Jul 30, 2014 at 11:47 AM
Edited Jul 30, 2014 at 1:43 PM
I use EPPlus to load an Excel file that has numeric cells having this custom number format:

00"."00"."00"."000"."0

Hence, a cell value of 202100083 is displayed on Excel as 02.02.10.008.3

It's a sort of a material coding standard. Now, I would like to use EPPlus to return the formatted value in a string. I am not really interested in the numeric value. Also, I don't want to do the formatting manually in my code because the number format may vary. How would I do this?

Thank you :)
Jul 31, 2014 at 8:08 AM
Edited Jul 31, 2014 at 8:13 AM
Update:

User wrummler has found my question on Stackoverflow, and he figured out that the problem is due to a bug in EPPlus. He suggested a nice workaround and he thankfully filed an issue for this here. Below is a quoting of his very helpful response.


This looks like an issue in EPPlus. (update: I filed an issue.)

If you run your example with EPPlus under a debugger, you'll see that EPPlus is producing the string value from ExcelRangeBase.cs:965 using the expression d.ToString(format, nf.Culture) where d is the converted double value of your cell's text, format is "00.00.00.000.0", and nf is an EPPlus ExcelFormatTranslator (but the latter is not important to this particular issue).

The issue is that an un-literalized . in a custom numeric format string is taken to be a decimal point. So the value of format at this point in the EPPlus code should be "00'.'00'.'00'.'000'.'0".

I'm not yet sure what would be the best fix for this, but it looks to require a change somewhere in ExcelNumberFormatXml.ExcelFormatTranslator.ToNetFormat.

In the meantime, you don't have to do the formatting manually in your own code. Before calling the Text property, you can set the number format to what it should be, e.g.:

range.Style.Numberformat.Format = "00'.'00'.'00'.'000'.'0";

Now range.Text should give you the string you were expecting.