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?
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?
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;
}
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.