BUG: Datavalidation with REGEXTEST does not work

Aspose.Cells Product Family
,
1

I’m creating a Excel with .NET and trying to add a datavalidation to check if a cell value is an URL.
Consider the following code:

public void AddDataValidation(CellArea cellArea, Column column, Worksheet worksheet)
{
    var startCellName = worksheet.Cells[cellArea.StartRow, cellArea.StartColumn].Name;
    var validations = worksheet.Validations;
    
    var validation = validations[validations.Add(cellArea)];
    validation.Type = ValidationType.Custom;
    validation.Formula1 = $"""=REGEXTEST({startCellName}, "^(https?|ftp)://[^\s/$.?#].[^\s]*$" )""";
    validation.ShowError = true;
    validation.IgnoreBlank = true;
}

// Some where else in my code:
var cellArea = new CellArea()
{
    StartRow = HeaderRowCount,
    EndRow = worksheet.Workbook.Settings.MaxRow,
    StartColumn = i,
    EndColumn = i
};

AddDataValidation(cellArea, column, worksheet)

After saving the file to a stream and opening it, the Data validation does not work. When I open the data validation in Excel and press ‘OK’, it suddenly starts working. Which implies that my regex is correct.

I’ve simplified the regex to confirm it’s not a regex problem. This results in the same problem:

validation.Formula1 = $"""=REGEXTEST({startCellName}, "[0-9]" )""";

I’ve made a comparable function for checking if something is a number, and that works perfectly:

public void AddDataValidation(CellArea cellArea, Column column, Worksheet worksheet)
{
    var startCell = worksheet.Cells[cellArea.StartRow, cellArea.StartColumn];
    var validations = worksheet.Validations;
    
    var validation = validations[validations.Add(cellArea)];
    validation.Type = ValidationType.Custom;
    validation.Formula1 = $"=ISNUMBER({startCell.Name})";
    validation.ShowError = true;
    validation.IgnoreBlank = true;
}

I think this is a bug? I’m using Aspose.Cells/25.1.2

2

@johand13024

It seems you are experiencing an issue with the data validation using the REGEXTEST function in your Excel file created with Aspose.Cells. The fact that the validation only starts working after you manually confirm it in Excel suggests that there might be a problem with how the validation is being set up or recognized by Excel upon initial opening.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

  1. Check Excel Version: Ensure that you are using a version of Excel that supports the REGEXTEST function. This function is available in Excel 365 and Excel 2021. If you are using an older version, it may not recognize this function.

  2. Formula Syntax: Double-check the syntax of your formula. While your regex appears to be correct, ensure that the cell reference is formatted properly. You might want to try using a simpler formula first to see if the validation works without the regex.

  3. Validation Type: Make sure that the validation type is set correctly. You are using ValidationType.Custom, which is appropriate for custom formulas. However, if there are any issues with the formula itself, it may not validate as expected.

  4. Excel Calculation Options: Sometimes, Excel’s calculation options can affect how formulas are evaluated. Ensure that Excel is set to automatically calculate formulas. You can check this in Excel under Formulas > Calculation Options.

  5. Testing with Other Functions: Since you mentioned that a simpler validation using ISNUMBER works perfectly, you might want to test other built-in functions to see if they behave similarly to REGEXTEST. This can help isolate whether the issue is specific to the regex function.

  6. Update Aspose.Cells: Ensure that you are using the latest version of Aspose.Cells, as updates may include bug fixes and improvements related to data validation.

If the issue persists after trying these suggestions, it may be beneficial to reach out to Aspose support or check the Aspose.Cells documentation for any known issues related to data validation with regex.

For more information on data validation in Aspose.Cells, you can refer to the official documentation here.

If you have any further questions or need additional assistance, feel free to ask!

3

@Professionalize.Discourse I tried the recommendations and the issue persists.

My Excel supports REGEXTEST. When manually updating the validation, saving the file and reopening it, the validation still works.

It appears this is an Aspose.Cells issue.

4

@johand13024
Currently REGEXTEST is not supported yet by our component.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57695

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

Thanks for confirming. Is there an alternative way to validate for an URL while REGEXTEST is not supported?

6

@johand13024,

You are welcome. Once we support the feature, we will update you in the thread.

7

Please refer to the following sample code and write/update your code snippet accordingly.
e.g
Sample code:

void AddDataValidation(CellArea cellArea, Aspose.Cells.Column column, Worksheet worksheet)
{
    var startCellName = worksheet.Cells[cellArea.StartRow, cellArea.StartColumn].Name;
    var validations = worksheet.Validations;
    
    var validation = validations[validations.Add(cellArea)];
    validation.Type = ValidationType.Custom;
    validation.Formula1 = "OR(LEFT(A1, 8) = \"https://\", LEFT(A1, 6) = \"ftp://\")";

    validation.ShowError = true;
    validation.IgnoreBlank = true;
    validation.ErrorTitle = "Invalid URL";
    validation.ErrorMessage = "Please enter a valid URL starting with https:// or ftp://";
    
}

// Some where else in my code:
var cellArea = new CellArea()
{
    StartRow = 0,
    EndRow = 0,
    StartColumn = 0,
    EndColumn = 0
};

Workbook workbook  = new Workbook();
Worksheet worksheet =workbook.Worksheets[0];
Aspose.Cells.Column column = worksheet.Cells.Columns[0];
AddDataValidation(cellArea, column, worksheet);

workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.