I’m having a problem with the Aspose excel component that I’d like you to look into. I am setting cell.formula programatically to a VLOOKUP for each row. If the formula looks like this:
Note: I’m replacing the {r} with the current row number in my code this isn’t coming from a template.
The error produced is:
Invalid formula in data validation settings.
Stack trace:
at Aspose.Excel.Record.aj.c(String A_0) at Aspose.Excel.Record.aj.a(Validation A_0, String A_1) at Aspose.Excel.Record.aj.a(Validation A_0) at Aspose.Excel.Worksheet.c(bn A_0) at Aspose.Excel.Worksheets.b(bn A_0) at Aspose.Excel.Worksheets.a(UCOMIStream A_0) at Aspose.Excel.Record.c5.a(c3 A_0, t A_1, Worksheets A_2) at Aspose.Excel.Worksheets.a(String A_0, SaveType A_1, FileFormatType A_2, HttpResponse A_3)
I think this problem is caused by the data validation settings. Do you set any custom data validation criteria? Please send my your designer spreadsheet and source code. I will check this problem right now.
I think this problem is caused by the data validation settings. Do you set any custom data validation criteria? Please send me your designer spreadsheet and source code. I will check this problem right now.
In my code I do something along these lines to set the formula. The first example works 100%, switching out the formula to the more complex version causes the error (no other code changes).
string formula = “=VLOOKUP(H{r},ValidCategories!$A$2B$62000,2,FALSE)“
string formula = “=IF(ISNA(VLOOKUP(H{r},ValidCategories!$A$2B$62000,2,FALSE)), “”, VLOOKUP(H{r},ValidCategories!$A$2B$62000,2,FALSE))”
Since the only change is the formula, not sure what it can have to do with the validation code, but here is what it looks like:
private void DefineCategoryDataValidation(Worksheet w, int rowCount, byte columnNumber) { const string FORMULA = “=CategoryNames” ; CellArea area = new CellArea(); area.StartRow = 1 ; area.EndRow = rowCount ; area.StartColumn = columnNumber ; area.EndColumn = columnNumber ;
// if we didn’t find the existing validation, then create a new one Validation CategoryValidation = FindValidation(w, FORMULA) ; if (CategoryValidation == null) { CategoryValidation = AddManufacturerDataValidation( w ) ; CategoryValidation.Formula1 = FORMULA ; CategoryValidation.ErrorMessage = “Category name does not match the list of valid values.” ; }
private Aspose.Excel.Validation AddManufacturerDataValidation(Worksheet w) { Validations validations = w.Validations; Validation validation = validations[validations.Add()]; validation.Type = Aspose.Excel.ValidationType.List; validation.Operator = OperatorType.None; validation.ShowInput = false ; validation.IgnoreBlank = true ; validation.InCellDropDown = true ; validation.AlertStyle = ValidationAlertType.Warning ; validation.ErrorMessage = “Manufacturer name does not match the list of valid values.” ; validation.Formula1 = “=MfrNames”; return validation ; }
if (CategoryValidation != null) CategoryValidation.AreaList.Add(area) ; }
@kris_k,
Aspose.Excel is discontinued now and no more under development. It is replaced with Aspose.Cells which is much more advanced and feature-rich product. You can perform data validation in a variety of ways including validation lookup formulas as demonstrated in the following sample code:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
System.IO.Directory.CreateDirectory(dataDir);
// Create a workbook object.
Workbook workbook = new Workbook();
// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.Worksheets[0];
// Accessing the Validations collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;
// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
// Creating a Validation object
Validation validation = validations[validations.Add(ca)];
// Setting the validation type to whole number
validation.Type = ValidationType.WholeNumber;
// Setting the operator for validation to Between
validation.Operator = OperatorType.Between;
// Setting the minimum value for the validation
validation.Formula1 = "10";
// Setting the maximum value for the validation
validation.Formula2 = "1000";
// Applying the validation to a range of cells from A1 to B2 using the
// CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;
// Adding the cell area to Validation
validation.AddArea(area);
// Save the workbook.
workbook.Save(dataDir + "output.out.xls");
You may consult the following document for more information on data validation in workbooks: Data Validation
B$62000,2,FALSE)