We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Number Data Validation

Hi,
I have been adding number data validation programmatically using Aspose.Cells on a Cell Area.
I am adding validation only on some condition.
For example, I am adding columns 4, 5 and 6. I wanted 5 to be Number and show error message when user enters text data.
On my machine, everything works fine. But on my colleagues machine, when same excel is downloaded (for same data), 6 automatically has number validation attached along with 5.

Can you please help me why is it happening? Any version issue?

The issue is urgent, so please reply asap.

@shushilkumartwr848,

Thanks for providing us some details.

It looks strange that it is not working on other machine(s). Please make sure that same code is used with similar version of the product (you should use latest version if you are not already using it) on all machines. If you still could not evaluate and find any issue, kindly do provide your sample code (runnable) and sample files, we will check your issue soon.

Also, provide both environments details, including OS, MS Excel version and your target .NET framework version of the project, etc. This will help us to evaluate your issue precisely to consequently figure it out soon.

Hi Amjad,
Thanks for replying, it’s a web application and we are giving a download functionality to users.
Hence the server side code using Aspose.Cells product is same for both the users.

The only difference I see is the excel versions on different machines. Will Excel versions can cause the issue?

@shushilkumartwr848,

Well, different MS Excel versions might have different behavior for certain files or functionality. Similarly there are some differences between MS Excel (2007, 2010, 2013, 2016, etc.) and Excel 365. Anyways, you may share code segments, sample files and give details about Excel versions, we will evaluate your issue.

It seems, it’s working on Excel 365 and not on Excel 2016. My colleagues are having Excel 2016.
When my colleague sent me the file which was not working, I opened at my end and it started working.
Please find the code below.

int index = CellsHelper.ColumnNameToIndex(headerName);
foreach (var objectColumnNamesToAdd in listOfObjects)
{
index = index + 1;
workBook.Worksheets[sheetIndex].Cells.InsertColumn(index, false);
workBook.Worksheets[sheetIndex].Cells[headerRowNumber, index].Value =
objectColumnNamesToAdd.Value //string value
var cellNm = CellsHelper.ColumnIndexToName(index);
Column column = workBook.Worksheets[sheetIndex].Cells.Columns[index];
StyleFlag styleFlag = new StyleFlag();
Style stringStyle = workBook.CreateStyle();
styleFlag.NumberFormat = false;
column.ApplyStyle(stringStyle, styleFlag);
// Create Cell Area to apply validation
CellArea ca = CellArea.CreateCellArea(cellNm, cellNm);

            // Accessing the Validations collection of the worksheet
            ValidationCollection validations = workBook.Worksheets[sheetIndex].Validations;
            // Creating a Validation object
            int validationIndex = validations.Add(ca);
            Validation validationAnyValue = validations[validationIndex];
            // Setting the validation type to Decimal number
            validationAnyValue.Type = ValidationType.AnyValue;
             if (objectColumnNamesToAdd.DataType == Convert.ToString("Number"))
             {
                    Style style = workBook.CreateStyle();
                // Setting the formate of number to 0.00                    
                style.Number = 2;
                
                styleFlag.NumberFormat = true;                    
                
                column.ApplyStyle(style, styleFlag);

                // Creating a Validation object
                validations.RemoveArea(ca);
                Validation validation = validations[validations.Add(ca)];
                // Setting the validation type to Decimal number
                validation.Type = ValidationType.Decimal;

                // Setting the operator for validation to Between
                validation.Operator = Aspose.Cells.OperatorType.Between;

                // Setting the minimum value for the validation
                validation.Formula1 = "-999999999999.99";

                // Setting the maximum value for the validation
                validation.Formula2 = "999999999999.99";

                // Enable the error.
                validation.ShowError = true;

                // Set the validation alert style.
                validation.AlertStyle = ValidationAlertType.Stop;

                // Set the title of the data-validation error dialog box.
                validation.ErrorTitle = "Error";

                // Set the data validation error message.
                validation.ErrorMessage = " Enter a Valid Number";

                // Set and enable the data validation input message.
                validation.InputMessage = "";
                validation.IgnoreBlank = true;
                validation.ShowInput = true;
             }

}

@shushilkumartwr848,

Thanks for the code segment.

Your shared code segment is not compiled precisely as we are not sure about some of your variables/objects and its data. To evaluate your issue precisely we request you to create a standalone console application (without compile time errors), zip the project and attach it (you may exclude Aspose.Cells.Dll to minimize the size of the application). Also attach your output file which is generated.

Moreover, we are not sure about your issue as you described:

Could you also attach some screenshots taken in both MS Excel and Office 365 to denote the steps involved to demonstrate and highlight the issue. This will help us to analyze your issue thoroughly and to make sure that nothing should be missed when figuring out the issue for both MS Excel and Office 365.

We appreciate your cooperation in this regard.