CODEWITHSUNDEEP
excelepplus
romar
romar

Reputation: 813

Using EPPlus, how do I return a cell value as it has been formatted by Excel?

I use EPPlus to load an Excel file that has numeric cells having this special format: 

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

Hence, a cell value of 123456789 is displayed on Excel as 01.23.45.678.9

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 sometimes vary. How would I do this?

Thank you :)

Upvotes: 2

Views: 2854

Answers (1)

William
William

Reputation: 2191

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.

Upvotes: 2

Related Questions