4 decimal place values format/validation

Aspose.Cells Product Family
1

WebGrid V4.8, file version v2.0.2.2002.

I need to be able to display and enter values (no negative values allowed in my case) with up to 4 decimal places; I tried ##,##0.0000 for custom format and #,##0.0000 for regex validation with Number validation type, and "General" NumberType. This causes validation errors; I then removed RegEx value, this removes validation errors, but then when I attempt editing, my 4 decimal place values (which are for the most part 2 decimals followed by 2 zeros, e.g. "58.5300", turn into "58.5299987792969" when cell is edited. I can still enter values with up to 4 decimal places, but this is confusing to the users, as well as overflowing my cell width. What validation/number types/regex/custom formats should I be using? The underlying data type is a double... Ideally, I'd like to display numbers with a $ currency sign in front, removing the dollar sign when entering editing mode...

2

Hi,

Thank you for considering Aspose.

Please try the following validation code and see if it fits your need.

Validation validation = new Validation();

GridWeb1.WebWorksheets[0].Cells["b2"].Validation = validation;

validation.RegEx = "^\\d+.\\d{4}$";

GridWeb1.WebWorksheets[0].Cells["b2"].Custom = "$#,##0.0000";

Thank You & Best Regards,

3

Still not working. I am using bound custom objects and setting the custom format and validation in the worksheet collection editor/bind column editor (see attached pictures). tried with and without the $ sign , neither way worked, and I am still getting more than 4 decimals when unlocking the grid for editing after clicking in the cell to enter a new value

4

Hi,

We will look into your issue soon.

Thank you.

5

Hi,

Thank you for considering Aspose.

The Custom or NumberType properties indicate how to display the cell value. They don’t work for editing format. This behavior is according to MS Excel. The validation will validate the input from keyboard but not from code behind. So, we recommend that you round the double value before it is assigned to a cell.

The following code works fine:

Validation validation = new Validation();
GridWeb1.WebWorksheets[0].Cells["b2"].Validation = validation;
validation.RegEx = "^\\d+.\\d{4}$";
GridWeb1.WebWorksheets[0].Cells["b2"].Custom = "$#,##0.0000";
double d = Math.Round(12.56783333d, 4);
GridWeb1.WebWorksheets[0].Cells["b2"].PutValue(d);

If you retrieve data from database, it will be better to round double value in your select statement.

Thank You & Best Regards,