How to Data Validations in Microsoft Excel Spreadsheet using Aspose.Cells REST API

Aspose.Cells Cloud Product Family
1

Hi,

I’m currently evaluating your Cloud product for my use case and have a question wrt validations.

I’m trying to update a cell with validation. I’m able to get the validation, so it’s there, but I’m able to put any value into the cell.

For my test case, only the value ‘Yes’ or ‘No’ should be valid, but I’m able to put any value in cell A1.

How do I trigger the validation and get feedback whether the value in the cell passes the validation?

Here’s the response -

{
"Cell": {
"Name": "A1",
"Row": 0,
"Column": 0,
"Value": "foo",
"Type": "IsString",
"Formula": null,
"IsFormula": false,
"IsMerged": false,
"IsArrayHeader": false,
"IsInArray": false,
"IsErrorValue": false,
"IsInTable": false,
"IsStyleSet": true,
"HtmlString": "<Font Style=\"FONT-FAMILY: Arial;FONT-SIZE: 10pt;COLOR: #000000;\">foo</Font>",
"Style": {
  "link": {
    "Href": "/style",
    "Rel": "self",
    "Title": null,
    "Type": null
  }
},
"Worksheet": null,
"link": {
  "Href": "http://api.aspose.cloud/v1.1/cells/aspose-validation.xlsx/worksheets/Sheet1/cells/A1",
  "Rel": "self",
  "Title": null,
  "Type": null
}
},
"Code": 200,
"Status": "OK"
}

Here’s the validation -

{
"Validation": {
"AlertStyle": "Stop",
"AreaList": [
  {
    "EndColumn": 0,
    "EndRow": 0,
    "StartColumn": 0,
    "StartRow": 0
  }
],
"ErrorMessage": null,
"ErrorTitle": null,
"Formula1": "=Sheet1!$A$3:$A$4",
"Formula2": null,
"IgnoreBlank": true,
"InCellDropDown": true,
"InputMessage": "Click and enter a value from range Sheet1!A3:A4",
"InputTitle": null,
"Operator": "Between",
"ShowError": true,
"ShowInput": true,
"Type": "List",
"Value1": "System.Object[]",
"Value2": null,
"link": {
  "Href": "http://api.aspose.cloud/v1.1/cells/aspose-validation.xlsx/worksheets/Sheet1/Validations/0",
  "Rel": "self",
  "Title": null,
  "Type": null
}
},
"Code": 200,
 "Status": "OK"
}

Here’s the corresponding spreadsheet -

2

@alpeware

Thank you for contacting Aspose Support.

We are able to reproduce the issue on our end and we have logged the request (CELLSCLOUD-10131) for our Development team to fix this bug. We will update you here as soon as the bug is fixed.

Between I have written following cURL example to guide you what API would serve your purpose:

curl -v "https://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/validations/0" \
-X POST \
-d '{"Formula1": "=(OR(A1=\"Yes\",A1=\"No\"))", "Type": "Custom", "IgnoreBlank":true}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer wzDWFdkpG-RxRas4cxhKYgJ37Jjwv1xcZGQHI1acmUddJobcSUvO8PeKGDUOBZsQc09XUUcwVeZEf1Ro6cfixpz0j4pSOVFV0Hvm_5d01SMhmGec9fpUilM5z75t4f5J1HrQs8dEVJEi2G7qacWAWfuzzS_FObxf68DaoPsb84w9KIdDmagjV1D7VyhhT0hrT-EWikecPGNOKfxOtx_ZS0WosPfmdaR_N8OKxjq3VdtECjzeNSP0dEdECTshA-oAgsZulRiQOseFw-Sa8n6UOqznCVTJzfBfVDC5dj4oUapvUdwrvLqI4b1hiMpaav-Mh1aBfQEow7iUx66yM_hkdaSjWZcTefyq3W_gZsSx9pWEEzOubXZaHI3ncZ9jj8nseRBxlqxDQh5OLnZkbSvC-1IU9TPskdxRbzjArBbmuVePn1Ry"

Please check Aspose.Cells API Reference guide for a complete list of our APIs.

3

Great, thanks!

What’s your approx. ETA?

4

@alpeware

As you can see from the attached JIRA task, our Development team is working to fix the reported bug. We will keep you updated on the progress.

Thank you for your patience and understanding.

5

Thanks. Is there a way for me to access the JIRA ticket?

6

@alpeware

Unfortunately, not. But we will keep you updated on the progress.

7

@alpeware

We are pleased to inform you that the validation issue has been fixed.

For my test case, only the value ‘Yes’ or ‘No’ should be valid, but I’m able to put any value in cell A1.

For this use case please pass following parameters to "POST https://api.aspose.cloud/v1.1/cells/{name}/worksheets/{sheetName}/validations/{validationIndex}" API

{
  "AreaList": [
    {
      "EndColumn": 0,
      "EndRow": 0,
      "StartColumn": 0,
      "StartRow": 0
    }
  ],
  "Formula1": "=(OR(A1=\"Yes\",A1=\"No\"))",
  "Type": "Custom",
  "IgnoreBlank": true
}

The cURL example would be:

curl -v "https://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/validations/0" \
-X POST \
-d '{"AreaList": [{"EndColumn": 0, "EndRow": 0, "StartColumn": 0, "StartRow": 0}], "Formula1": "=(OR(A1=\"Yes\",A1=\"No\"))", "Type": "Custom", "IgnoreBlank":true}' \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer xjgJTMd5PSISHSnwt7e0gZBfGzc92UB7pxnAinwyUVQ0gp4cIZXJD6hcRrb28ZEWwnIBCk9h3EAv9BrgjzECvGL3lqtGBM69eqT1vsBoqXaOKL7ASQyJOUuCj9RanlOLR4_tntKKBWxoq0zZS3U5W-E16DX1ScHZoKSkl5W1QSM41xaoJA1VafZs5TzCAH533XvTqQlbIJj6JJCzW7P2dIQeWV9TO0u0lnmSbnsvnZ3dKxGLyYaFVpF_6MzDzucdPfZuGS3TSuRdeKtfL5Ig1P2ZcjmW7lvdmzw1c9mGYNKPjp-bgcWofLBO_4tfIWvhqN49DxwhTaz1elRwLiwQ1AGVpRzWHHqfcXxjY1CVyUe2TPBpIdNkmeJOBKsweaS6qZ0Mo_Et7MZV3_rFldrcJF2t9mKuaCePoNGkSMpBr89Yke4D"

and C# test case would be:

public void CellsWorksheetValidationsPostWorkSheetValidationTest()
{
      string name = BOOK1;
      string sheetName = SHEET1;
      int? validationIndex = 0;
      Validation validation = new Validation();
      CellArea area = new CellArea();
      area.StartRow = 0;
      area.EndRow = 0;
      area.StartColumn = 0;
      area.EndColumn = 0;
      validation.AreaList = new List<CellArea>();
      validation.AreaList.Add(area);
      validation.Formula1 = "=(OR(A1=\"Yes\",A1=\"No\"))";
      validation.Type = "Custom";
      validation.IgnoreBlank = true;
      string folder = TEMPFOLDER;
      UpdateDataFile(folder, name);
      UpdateDataFile(TEMPFOLDER, BOOK1);
      var response = instance.CellsWorksheetValidationsPostWorksheetValidation(name, sheetName, validationIndex, validation, folder);
      Assert.IsInstanceOf<ValidationResponse>(res`ponse, "response is ValidationResponse");
      Assert.AreEqual(response.Code, 200);
}