Aspose Exception: Invalid formula in data validation settings?

Aspose.Cells Product Family
1

Laurence, can you help me to understand why the following code throws Aspose.Cells.ExcelException: Invalid formula in data validation settings. ? It seems that this code should work.

public void Decimal_2()
{
Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;

ArrayList headings = new ArrayList();
headings.Add("Empty Column");
headings.Add("Heading1");
cells.ImportArrayList(headings, 0, 0, false);

cells[1, 1].PutValue(-1.23);
cells[2, 1].PutValue(1.23);

Worksheet worksheet = excel.Worksheets[0];
Styles styles = excel.Styles;
Validations validations = worksheet.Validations;
CellArea cellArea = new CellArea();
ArrayList list = new ArrayList();
int i = validations.Add();

//////////////////////////////////////////////////
validationsIdea [I].Type = ValidationType.Decimal;
double min2 = double.MinValue;
double max2 = double.MaxValue;
//////////////////////////////////////////////////

validationsIdea [I].AlertStyle = ValidationAlertType.Warning;
validationsIdea [I].ErrorMessage =
string.Format("{0} {1}:{2} {3}:{4}", "Valid double range",
"Min", min2.ToString(), "Max", max2.ToString());
validationsIdea [I].Operator = OperatorType.Between;
validationsIdea [I].Formula1 = min2.ToString();
validationsIdea [I].Formula2 = max2.ToString();
cellArea = new CellArea();
cellArea.StartRow = 1;
cellArea.EndRow = cells.MaxRow;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
list = validationsIdea [I].AreaList;
list.Add(cellArea);

for (int j = 0; j < 2; j++)
{
worksheet.AutoFitColumn(j);
}

excel.Save("DecimalTest2.xls");

}


Thanks.

2

Note sure why a cute little light bulb appears in my post. I attached the code as a text file to this message. Thanks.

3

P.S. - I am using Aspose.Cells version 3.7.2.2.

4

This problem is caused by 2 reasons:

1. I had not thougth that the numeric string in validation settings could be in exponential notation.

2. If you try the following code, .NET will throw exception though I think it should not.

int min = double.Parse(double.MinValue.ToString());

So please try this attached fix and change a little in your code:

double min2 = double.MinValue / 1.00000001;
double max2 = double.MaxValue / 1.00000001;

5

Hi Laruence. I tried the dll that you attached and I edited my code as you suggested. There is no longer an Aspose exception, but if I add double.MinValue or double.MaxValue to the worksheet, no numbers appear in the resulting file and the second column is very very wide.

cells[1, 1].PutValue(-1.23);

cells[2, 1].PutValue(1.23);

cells[3, 1].PutValue(int.MinValue);

cells[4, 1].PutValue(int.MaxValue);

cells[5, 1].PutValue(double.MinValue);

cells[6, 1].PutValue(double.MaxValue);

// C# decimals have smaller range than double, but greater precision:

cells[7, 1].PutValue(decimal.MinValue);

cells[8, 1].PutValue(decimal.MaxValue);

6

Thank you for the information. I will fix this problem in one or two days.

7

Please try this attached fix. It solves the problem to put double value as double.MaxValue and double.MinValue.

8

What do you mean the second column is too wide? Please see the attached file created with the following code:

Excel excel = new Excel();
Cells cells = excel.Worksheets[0].Cells;
cells[1, 1].PutValue(-1.23);

cells[2, 1].PutValue(1.23);

cells[3, 1].PutValue(int.MinValue);

cells[4, 1].PutValue(int.MaxValue);

cells[5, 1].PutValue(double.MinValue);

cells[6, 1].PutValue(double.MaxValue);

// C# decimals have smaller range than double, but greater precision:

cells[7, 1].PutValue(decimal.MinValue);

cells[8, 1].PutValue(decimal.MaxValue);

excel.Save("d:\\test\\abc.xls");

9

Using the Aspose DLL that you most recently provided (version 3.8.1.5), I am seeing two problems. They are summarized in the attached text file that has C# code. When I run this code for Case /* A / I get a runtime exception. When I run it with Case / B */ I get the attached Excel document which has no numbers and a very wide second column.

10

For case A, it's a problem of .NET. It cannot convert back double.MaxValue and double.MinValue from string. Following code will throw this exception:

int min = double.Parse(double.MinValue.ToString());

For case B, it's caused by AutoFitColumn method. This method converts double.MaxValue and double.MinValue to a very long string which caused column B is too wide. I fixed it in the attached fix. Please try it.

11

Sorry for the delay. Your fix works. Will it be rolled into an official release, or is it already there? Thanks.

12

About Case A ... This from Microsoft:

ToString() only outputs up to 15 digits of precision, whereas internally double maintains 17. So the value is getting rounded, and the new value is outside of the acceptable range for a double. Try it with ToString(“G17”); that makes the exception go away.

13

This fix will be included in the public official release in a few days.

For ToString() issue, your information is helpful. Thank you.

14

I am also told that it’s better to use “R” than “G17”, but I have not researched this further.